<table cellspacing="10" cellpadding="30"><tr>
<td width="100%">

<h1>Spatialite on Android: a quick tutorial</h1>
Back to <a Wiki page<br><br>
<table bgcolor="ffffd0" cellpaddind="8" cellspacing="8">
<tr><td><img alt="logo"></td>
<td>
<b>Credits</b><br><br>
This Wiki page has been kindly contributed by <a Antonello </a>, a skilled Java devoloper with a sound specific experience on Adroid, and actively involved in several GFOSS projects (PSC member of <a and maintainer of <a
Many thanks to Andrea for his thorough documentation effort !!!

</table>

<h2>Introduction</h2>

<p>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.

<p>That should not scare away any developer eager to play real geospatial on Android devices. The <a project is simple to use and stable.

<p>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).

<h2>Authors and License</h2>

<p>The Spatialite-Android project has been released by the <a Army Geospatial Center</a>.
<p>The project is released <a LGPLv3 license</a>.

<h2>Getting started</h2>

<p>To get started we just need to get the Spatialite libraries and add them to our new Android application project.
<p>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.
<p>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.

<h3>Downloading the libraries</h3>

<p>They can be downloaded from <a link</a>.
<p>The downloaded archive contains 2 basic components necessary to enable your project to use Spatialite:
<ul>
<li><b>the native spatialite libraries</b>: they are places in a folder named and contains several folders for different platforms that hold the main library, called <b>libspatialite.so</b>
<li>the <b>basic API classes</b>, contained in the <b>jsqlite</b> package</li>
</ul>

<h3>Your first Spatialite enabled project</h3>

<h4>Prepare a dataset</h4>

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

<h4>Create the basic hello world project</h4>

<p>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 <i>Hello World</i> on it.

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

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

<ul>
<li>copy the native libs into the root of the newly created Android project</li>
<li>copy the java package of the base API into you new project's source folder</li>
</ul>

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

<h4>Open an database</h4>

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

<verbatim>
        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();
        }
</verbatim>

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

<h4>Get versions of the software</h4>

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

<verbatim>
    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();
    }
</verbatim>


<h4>Check plain sqlite queries</h4>

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:

<verbatim>
    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);

                if (index++ > 5) {
                    break;
                }
            }
            sb.append("\t...");
            stmt.close();
        } catch (Exception e) {
            error(e);
        }

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

        return sb.toString();
    }
</verbatim>

<h4>Your first spatial query</h4>

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:

<verbatim>
    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();
    }
</verbatim>

<h4>Get the area of a polygon</h4>

The <i>ST_Area</i> function is available to get the area from a geometry:

<verbatim>
    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();
    }
</verbatim>

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

<h4>Simple coordinate transformation</h4>

You can also do some coordinate transformation. Try with:

<verbatim>
    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();

    }
</verbatim>

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

<h4>A more complex query</h4>

<verbatim>
    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);
                - 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();
    }
</verbatim>

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.

<h4>Close the database</h4>

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

<verbatim>
db.close();
</verbatim>

<h3>Putting it all together</h3>

<p>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.

<h2>Projects implementing it</h2>

<h3>GeoPaparazzi</h3>

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

<p>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.

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

<br><hr>
Back to <a Wiki page