spatialite-android-tutorial
Not logged in

Spatialite on Android: a quick tutorial

Back to SpatiaLite-Android Wiki page

logo Credits

This Wiki page has been kindly contributed by Andrea Antonello, a skilled Java devoloper with a sound specific experience on Android, and actively involved in several GFOSS projects (PSC member of uDig and maintainer of GeoPaparazzi).

Many thanks to Andrea for his thorough documentation effort !!!

Introduction

The Android operating system for mobile devices uses as its default relational database SQLite. Even if since the appearence of SpatiaLite in the geospatial world it looked like a perfect match to have it available by default for the system, developers had to wait for a while to use spatialite on Android. And even now, that it is available, it is not integrated in the operating system and it is not accessible through the standard Android persistence APIs.

That should not scare away any developer eager to play real geospatial on Android devices. The Spatialite-Android project is simple to use and stable.

In this document we will help new developers to get started with their first Spatialite enabled application. It is meant for developers that have at least a minimal knowledge about Android application development (having built and run the hello world app shoul be enough).

Authors and License

The Spatialite-Android project has been released by the U.S. Army Geospatial Center.

The project is released under LGPLv3 license.

Getting started - the quick way

The quickest way is to download the source project of this documentation, browse it and run it. It should work out of the box and show you where libs need to be put and how to run queries.

The source code can be found here.

The sqlite database used for this tutorial can be downloaded in the download area of the same project page.

Getting started - the slow way

To get started we just need to get the Spatialite libraries and add them to our new Android application project.

Once that is done, there are few steps to perform to get things working. The API is very small and simple and basically bases on a manual building of the queries to send to the database.

Note that it would be possible to create an Android library to reference from the project, but to keep this tutorial short, we will just show how to integrate the library directly into our new project.

Downloading the libraries

They can be downloaded from this link.

The downloaded archive contains 2 basic components necessary to enable your project to use Spatialite:

  • the native spatialite libraries: they are places in a folder named libs
  • and contains several folders for different platforms that hold the main library, called libspatialite.so
  • the basic API classes, contained in the jsqlite package

Your first Spatialite enabled project

Prepare a dataset

Follow this tutorial to create a spatialite database that contains the comuni_2011.shp (find references to where to get it here) data. Copy the created database on the mobile device's external storage card.

Create the basic hello world project

Use the Android eclipse new project wizard to create a basic hello world example for Android. It will generate you a ready to use Android application that shows a nice View with Hello World on it.

Once the hello world project can be run, we can start to add the spatialite part.

The first step is to copy the main components of the Spatialite-Android project as mentioned before.

  • copy the native libs into the root of the newly created Android project
  • copy the java package of the base API into you new project's source folder

And that is it. Your project is already Spatialite enabled. Let's do some basic stuff with it in the next chapters.

Open an database

The following will open the database named italy.sqlite that is placed in your sdcard. If the database doesn't exist, it will be created empty (due to the SQLITE_OPEN_CREATE flag).

        try {
            File sdcardDir = ""; // your sdcard path
            File spatialDbFile = new File(sdcardDir, "italy.sqlite");

            db = new jsqlite.Database();
            db.open(spatialDbFile.getAbsolutePath(), jsqlite.Constants.SQLITE_OPEN_READWRITE
                    | jsqlite.Constants.SQLITE_OPEN_CREATE);
        } catch (Exception e) {
            e.printStackTrace();
        }

Once you have opened the database, you are ready to do some spatial magic.

Get versions of the software

To get the versions of spatialite, geos and proj that are in use, you can use the following method:

    public String queryVersions() throws Exception {
        StringBuilder sb = new StringBuilder();
        sb.append("Check versions...\n");

        Stmt stmt01 = db.prepare("SELECT spatialite_version();");
        if (stmt01.step()) {
            sb.append("\t").append("SPATIALITE_VERSION: " + stmt01.column_string(0));
            sb.append("\n");
        }

        stmt01 = db.prepare("SELECT proj4_version();");
        if (stmt01.step()) {
            sb.append("\t").append("PROJ4_VERSION: " + stmt01.column_string(0));
            sb.append("\n");
        }

        stmt01 = db.prepare("SELECT geos_version();");
        if (stmt01.step()) {
            sb.append("\t").append("GEOS_VERSION: " + stmt01.column_string(0));
            sb.append("\n");
        }
        stmt01.close();

        sb.append("Done...\n");
        return sb.toString();
    }

Check plain sqlite queries

To see if it all works, we can do a plain sqlite query and see if we have the database setup with the data. Assuming you imported the shapfile Comuni_2011 into a table named Comuni_11, which has a field called NOME, the following gives you the first 5 entries:

    public String queryComuni() {
        sb.append("Query Comuni...\n");

        String query = "SELECT NOME" + //
                " from Comuni_11" + //
                " order by NOME;";
        sb.append("Execute query: ").append(query).append("\n");
        try {
            Stmt stmt = db.prepare(query);
            int index = 0;
            while( stmt.step() ) {
                String nomeStr = stmt.column_string(0);
                sb.append("\t").append(nomeStr).append("\n");
                if (index++ > 5) {
                    break;
                }
            }
            sb.append("\t...");
            stmt.close();
        } catch (Exception e) {
            error(e);
        }

        sb.append("Done...\n");

        return sb.toString();
    }

Your first spatial query

Let's finally query for the geometries, if there are any. The resulting WKT format of the geometries is shortened in order to be printed:

    public String queryComuniWithGeom() {
        sb.append(SEP);
        sb.append("Query Comuni with AsText(Geometry)...\n");

        String query = "SELECT NOME" + //
                " , AsText(Geometry)" + //
                " as geom from Comuni_11" +  //
                " where geom not null;";
        sb.append("Execute query: ").append(query).append("\n");
        try {
            Stmt stmt = db.prepare(query);
            while( stmt.step() ) {
                String nomeStr = stmt.column_string(0);
                String geomStr = stmt.column_string(1);
                String substring = geomStr;
                if (substring.length() > 40)
                    substring = geomStr.substring(0, 40);
                sb.append("\t").append(nomeStr).append(" - ").append(substring).append("...\n");
                break;
            }
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            sb.append(ERROR).append(e.getLocalizedMessage()).append("\n");
        }
        sb.append("Done...\n");

        return sb.toString();
    }

Get the area of a polygon

The ST_Area function is available to get the area from a geometry:

    public String queryComuniArea() {
        sb.append(SEP);
        sb.append("Query Comuni area sum...\n");

        String query = "SELECT ST_Area(Geometry) / 1000000.0 from Comuni_11" +  //
                ";";
        sb.append("Execute query: ").append(query).append("\n");
        try {
            Stmt stmt = db.prepare(query);
            double totalArea = 0;
            while( stmt.step() ) {
                double area = stmt.column_double(0);
                totalArea = totalArea + area;
            }
            sb.append("\tTotal area by summing each area: ").append(totalArea).append("Km2\n");
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            sb.append(ERROR).append(e.getLocalizedMessage()).append("\n");
        }
        sb.append("Done...\n");

        return sb.toString();
    }

Obviously you could also do the sum directly inside the query.

Simple coordinate transformation

You can also do some coordinate transformation. Try with:

    public String doSimpleTransform() {


        sb.append("Coordinate transformation...\n");

        String query = "SELECT AsText(Transform(MakePoint(" + TEST_LON + ", " + TEST_LAT + ", 4326), 32632));";
        sb.append("Execute query: ").append(query).append("\n");
        try {
            Stmt stmt = db.prepare(query);
            if (stmt.step()) {
                String pointStr = stmt.column_string(0);
                sb.append("\t").append(TEST_LON + "/" + TEST_LAT + "/EPSG:4326").append(" = ")//
                        .append(pointStr + "/EPSG:32632").append("...\n");
            }
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            sb.append(ERROR).append(e.getLocalizedMessage()).append("\n");
        }
        sb.append("Done...\n");

        return sb.toString();

    }

setting TEST_LON and TEST_LAT to the coordinates to transform, in the above case from SRID 4326 to 32632.

A more complex query

    public String queryComuniNearby() {
        sb.append(SEP);
        sb.append("Query Comuni nearby...\n");

        String query = "SELECT Hex(ST_AsBinary(ST_Buffer(Geometry, 1.0))), ST_Srid(Geometry), ST_GeometryType(Geometry) from Comuni_11" +
                " where NOME = 'Bolzano';";
        sb.append("Execute query: ").append(query).append("\n");
        String bufferGeom = "";
        String bufferGeomShort = "";
        try {
            Stmt stmt = db.prepare(query);
            if (stmt.step()) {
                bufferGeom = stmt.column_string(0);
                String geomSrid = stmt.column_string(1);
                String geomType = stmt.column_string(2);
                sb.append("\tThe selected geometry is of type: ").append(geomType).append(" and of SRID: ").append(geomSrid)
                        .append("\n");
            }
            bufferGeomShort = bufferGeom;
            if (bufferGeom.length() > 10)
                bufferGeomShort = bufferGeom.substring(0, 10) + "...";
            sb.append("\tBolzano polygon buffer geometry in HEX: ").append(bufferGeomShort).append("\n");
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            sb.append(ERROR).append(e.getLocalizedMessage()).append("\n");
        }

        query = "SELECT " + NOME + ", AsText(ST_centroid(Geometry)) from " + COMUNITABLE + //
                " where ST_Intersects( ST_GeomFromWKB(x'" + bufferGeom + "') , Geometry );";
        // just for print
        String tmpQuery = "SELECT " + NOME + " from " + COMUNITABLE + //
                " where ST_Intersects( ST_GeomFromWKB(x'" + bufferGeomShort + "') , Geometry );";
        sb.append("Execute query: ").append(tmpQuery).append("\n");
        try {
            sb.append("\tComuni nearby Bolzano: \n");
            Stmt stmt = db.prepare(query);
            while( stmt.step() ) {
                String name = stmt.column_string(0);
                String wkt = stmt.column_string(1);
                sb.append("\t\t").append(name).append(" - with centroid in ").append(wkt).append("\n");
            }
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            sb.append(ERROR).append(e.getLocalizedMessage()).append("\n");
        }
        sb.append("Done...\n");

        return sb.toString();
    }

The above will extract the polygon of the region Bolzano, buffer it and then find all geometries that intersect with that buffer. It is a simple way to find the regions surrounding Bolzano.

Close the database

Once you do not need the connection to the database any longer, remember to release the resource by closing the connection:

db.close();

Putting it all together

We have created a simple Android project, which implements the simple queries seen in the above chapters. It will have a view with a button, which, once pressed, will execute in a row all the queries and show the results.

Projects implementing it

GeoPaparazzi

The GeoPaparazzi project supports the Spatialite-Android in a more complex way than described in this short tutorial.

GeoPaparazzi enables the use of spatialite databases as data sources and visualizes them on the map view. Being the mapview forced on a Mercator projection, the datasets are extracted and reprojected directly in spatialite. It is also possible to query layers by tapping on the map.

If you are interested in the queries used, have a look into its code on the homepage, the project is open source.


Back to SpatiaLite-Android Wiki page