Spatialite on Android: a quick tutorialBack to SpatiaLite-Android Wiki page
IntroductionThe 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 LicenseThe Spatialite-Android project has been released by the U.S. Army Geospatial Center. The project is released under LGPLv3 license. Getting started - the quick wayThe 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 wayTo 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 librariesThey can be downloaded from this link. The downloaded archive contains 2 basic components necessary to enable your project to use Spatialite:
Your first Spatialite enabled projectPrepare a datasetFollow 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 projectUse 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.
And that is it. Your project is already Spatialite enabled. Let's do some basic stuff with it in the next chapters. Open an databaseThe 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 softwareTo 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 queriesTo 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 queryLet'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 polygonThe 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 transformationYou 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 databaseOnce 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 togetherWe 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 itGeoPaparazziThe 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 |
spatialite-android-tutorial
Not logged in
