Spatialite vs Android-Spatialite

[chivasblue]

Hello, I have been playing with spatialite on my desktop. My dataset is very simple consisting of city names and their locations (Geometry Point), and the road passing through them (Geometry Linestring). I need to execute very basic sql's like ST_Distance,ST_ClosestPoint and ST_Transform. These work fine on the latest spatialite desktop version I downloaded. Now I am looking to move this functionality to Android. I downloaded the android spatialite library from https://github.com/mrenouf/android-spatialite and was successfully able to execute ST_Distance and ST_Transform, however ST_ClosestPoint fails. I am assuming that it is due to the fact that the libraries are old and the project is no longer updated/maintained. I am trying to find the equivalent newer libraries from your repository but having a hard time. Can you please point me in the right direction? Thanks in advance!

[mj10777]

this depends of course on your needs, but on the following page:

https://github.com/geopaparazzi/libjsqlite-spatialite-android/wiki

is the best starting point.

With next link, shown on the above link as '201400726.libjsqlite.4.2.0.tar.bz2 - 9.6 MB'

• you can download the per-compiled libraries that are used presently in the geopaparazzi project.

https://raw.github.com/geopaparazzi/libjsqlite-spatialite-android/master/archive/20140726.libjsqlite.4.2.0.tar.bz2

Normally you should only need to copy these into your project 'lib/' directory to get things working.

• this version is the latest non-beta version

Report back if you have any other problems with this

Mark

[chivasblue]

Thanks, that library helped and I am able to execute the ST_ClosestPoint function now. My next question is do I need to first create a spatialite database using the spatialite Windows gui, execute SELECT InitSpatialMetaData() to initialize the metadate, and then copy it to the phone to be able to use the functions from this library, or can I use a sqlite database to execute the spatial functions? For example, can I have a sqlite database with the point and linestring data stored as text datatype in the sqlite database, then retrieve it and convert it to geometry using GeomFromText function from the library, and use the spatial functionality? If yes, are there any disadvantages of storing geometry data in text column as opposed to a geometry column? The reason for asking the question is the size of sqlite database is usually around 332 KB, and for spatialite database the minimum size is around 4MB. Now adding the android-spatialite libraries adds another 3 MB to the application (after adding only processor specific .so file), and I am trying to reduce the overall size as far as possible.

Thanks!

[mj10777]

This driver should be able to create a sqllite Database from scratch.

• as apposed to the former driver, which had problems with this

After creation of the Database then

• SELECT InitSpatialMetaData() must be call to then have this Database as a spatialite Database -- without this you cannot use the spatial-functions (i.e. GeomFromText)

As to the size problem:

• without the original geometry format in the database -- this will become very slow, since all queries would have to make the geometries in memory --- there will also be no spatial-Index which is very important when searching

This should be avoided where possible, but this is a question of priorities

[chivasblue]

Thanks, that helped a lot!