B4A Library Spatialite

warwound · Jan 2, 2014

Spatialite wraps the splite-android project enabling you to create databases with geo-spatial features and make queries using geo-spatial functions.

Spatialite is an enhancement of the SQLite database.
It has dedicated column data types for spatial features: Points, Polylines and Polygons, and various query functions that enable fast and efficient retrieval of spatial data from the database.
The official reference for version 3.0.0 of spatialite can be found here: http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html.
(The B4A spatialite library is based on version 3.0.1 of spatialite).

Spatialite
Comment: Spatialite is an SQLite extension for the Android platform.
Currently version 3.0.1 of Spatialite is wrapped by this library.
Spatialite is compiled using geos v3.3.6 and proj4 v4.8.0 patched.
Version: 1.10

Spatialite_Blob
Methods:
- Close
- IsInitialized As Boolean
Properties:
- InputStream As InputStreamWrapper [read only]
  Return InputStream for this blob.
- OutputStream As OutputStreamWrapper [read only]
  Return OutputStream for this blob.
Spatialite_Callback
Events:
- Columns (ColumnData() As String)
- NewRow (RowData() As String) As Boolean
- Types (TypeData() As String)
Methods:
- Initialize (EventName As String)
- IsInitialized As Boolean
Spatialite_Constants
Fields:
- SQLITE_OPEN_AUTOPROXY As Int
- SQLITE_OPEN_CREATE As Int
- SQLITE_OPEN_DELETEONCLOSE As Int
- SQLITE_OPEN_EXCLUSIVE As Int
- SQLITE_OPEN_FULLMUTEX As Int
- SQLITE_OPEN_MAIN_DB As Int
- SQLITE_OPEN_MAIN_JOURNAL As Int
- SQLITE_OPEN_MASTER_JOURNAL As Int
- SQLITE_OPEN_NOMUTEX As Int
- SQLITE_OPEN_PRIVATECACHE As Int
- SQLITE_OPEN_READONLY As Int
- SQLITE_OPEN_READWRITE As Int
- SQLITE_OPEN_SHAREDCACHE As Int
- SQLITE_OPEN_SUBJOURNAL As Int
- SQLITE_OPEN_TEMP_DB As Int
- SQLITE_OPEN_TEMP_JOURNAL As Int
- SQLITE_OPEN_TRANSIENT_DB As Int
- SQLITE_OPEN_URI As Int
- SQLITE_OPEN_WAL As Int
Spatialite_Database
Methods:
- BusyTimeout (Milliseconds As Int)
  Set the timeout for waiting for an SQLite table to become unlocked.
- Close
  Close the SpatiaLite database file.
- CreateAggregate (FunctionName As String, NumberArgs As Int, Function1 As Function)
  Create aggregate user defined function.
- CreateFunction (FunctionName As String, NumberArgs As Int, Function1 As Function)
  Create regular user defined function.
- ErrorMessage As String
  Return last error message of SQLite3 engine.
- Exec (Query As String, Callback1 As CallbackImpl)
  The Callback events never seem to get raised, check if this is executing on a non UI thread.
  Or catch the exception in this method to debug...
- GetTable (Query As String) As TableResult
- GetTable2 (Query As String, MaxRows As Int) As TableResult
- Initialize
- IsInitialized As Boolean
- LastError As Int
  Return the code of the last error that occurred in any of the Exec methods.
- LastInsertRowId As Long
  Return the row identifier of the last inserted row.
- Open (DatabasePath As String, DatabaseFileName As String, OpenMode As Int)
  Open a SpatiaLite database file.
- OpenBlob (DatabaseName As String, TableName As String, ColumnName As String, Row As Long, ReadWrite As Boolean) As Blob
  Open an SQLite3 blob.
  The returned Blob will not be initialized if an error occurs.
- Prepare (Query As String) As Stmt
  Prepare and return SQLite3 statement for SQL.
- SetEncoding (Encoding As String)
  Set character encoding.
Spatialite_Function
Events:
- Function (FunctionContext1 As FunctionContext, Arguments() As String)
- LastStep (FunctionContext1 As FunctionContext)
- Step (FunctionContext1 As FunctionContext, Arguments() As String)
Methods:
- Initialize (EventName As String)
  Initialize this user defined Function.
  Raises the events:
  Function(FunctionContext1 As FunctionContext, Arguments() As String).
  LastStep(FunctionContext1 As FunctionContext).
  Step(FunctionContext1 As FunctionContext, Arguments() As String).
- IsInitialized As Boolean
Spatialite_FunctionContext
Methods:
- Count As Int
  Retrieve number of rows for aggregate function.
- IsInitialized As Boolean
- SetError (Error As String)
  Set function result from error message.
- SetResultBytes (Result() As Byte)
  Set function result from byte array.
- SetResultDouble (Result As Double)
  Set function result from double.
- SetResultInt (Result As Int)
  Set function result from integer.
- SetResultString (Result As String)
  Set function result from string.
- SetResultZeroBlob (Size As Int)
  Set function result as empty blob given size.
Spatialite_Stmt
Methods:
- BindBytes (Position As Int, Bytes() As Byte)
  Bind positional byte array to compiled statement.
- BindDouble (Position As Int, Double1 As Double)
  Bind positional double value to compiled statement.
- BindInt (Position As Int, Integer1 As Int)
  Bind positional integer value to compiled statement.
- BindLong (Position As Int, Long1 As Long)
  Bind positional long value to compiled statement.
- BindNull (Position As Int)
  Bind positional SQL null to compiled statement.
- BindParameterCount As Int
  Return number of parameters in compiled statement.
- BindParameterIndex (Name As String) As Int
  Return index of named parameter in compiled statement.
- BindParameterName (Position As Int) As String
  Return name of parameter in compiled statement.
- BindString (Position As Int, String1 As String)
  Bind positional String to compiled statement.
- BindZeroBlob (Position As Int, Length As Int)
  Bind positional zero'ed blob to compiled statement.
- ClearBindings
  Clear all bound parameters of the compiled statement.
- Close
  Close the compiled statement.
- Column (ColumnIndex As Int) As Object
  Retrieve column data as Object from exec'ed statement.
- ColumnBytes (ColumnIndex As Int) As Byte[]
  Retrieve blob column from exec'ed statement.
- ColumnCount As Int
  Retrieve number of columns of exec'ed statement.
- ColumnDatabaseName (ColumnIndex As Int) As String
  Return database name of column of statement.
- ColumnDeclaredType (ColumnIndex As Int) As String
  Return declared column type of statement.
- ColumnDouble (ColumnIndex As Int) As Double
  Retrieve double column from exec'ed statement.
- ColumnInt (ColumnInt As Int) As Int
  Retrieve integer column from exec'ed statement.
- ColumnLong (ColumnIndex As Int) As Long
  Retrieve long column from exec'ed statement.
- ColumnName (ColumnIndex As Int) As String
  Return column name of column of statement.
- ColumnOriginalName (ColumnIndex As Int) As String
  Return origin column name of column of statement.
- ColumnString (ColumnIndex As Int) As String
  Retrieve string column from exec'ed statement.
- ColumnTableName (ColumnIndex As Int) As String
  Return table name of column of statement.
- ColumnType (ColumnIndex As Int) As Int
  Retrieve column type from exec'ed statement.
- IsInitialized As Boolean
- Prepare As Boolean
  Prepare the next SQL statement for the Stmt instance.
- Reset
  Reset the compiled statement without clearing parameter bindings.
- Status (Op As Int, Flag As Boolean) As Int
  Return statement status information.
- Step As Boolean
  Perform one step of compiled statement.
Spatialite_StringEncoder
Methods:
- Decode (String1 As String) As Byte[]
  Decodes the given string that is assumed to be a valid encoding of a byte array.
  Typically the given string is generated by the StringEncoder Encode method.
- Encode (Bytes() As Byte) As String
  Encodes the given byte array into a string that can be used by the SQLite database.
  The database cannot handle null (0x00) and the character '\'' (0x27).
  The encoding consists of escaping these characters with a reserved character (0x01).
- EncodeX (Bytes() As Byte) As String
  Encodes the given byte array into SQLite3 blob notation, ie X'..'
Spatialite_TableResult
Methods:
- AtMaxRows As Boolean
  Flag to indicate MaxRows condition.
- Clear
- IsInitialized As Boolean
Properties:
- Column() As String [read only]
  Column names of the result set.
- MaxRows As Int [read only]
  Maximum number of rows to hold in the table.
- NumberColumns As Int [read only]
  Number of columns in the result set.
- NumberRows As Int [read only]
- Rows() As String [read only]
  Rows of the result set.
  Returns an Array of String Arrays, where each String Array represents a row of the result.
- Types() As String [read only]
  Types of columns of the result set or Null.

Please note that i have only tested a small number of the library objects, if you find any bugs please post a bug report and i'll do my best to fix it.

The Spatialite.jar library file is over 14MBs in size so too large to attach to a forum post.
So you can find the library files here: http://b4a.martinpearman.co.uk/spatialite/ (Spatialite_library_files_v2.20.zip).
Also available at that link is SpatialiteGUI - a small Windows utility that will likely be very handy for you developers.

Martin.

warwound · Mar 7, 2014

Spatialite support for x86 based devices

Spatialite contains C libraries that enable it to work on devices with either ARM or x86 CPUs.

If you open Spatialite.jar and look in it's lib folder you'll see these C libraries.
There's libraries for older and newer ARM CPUs and a library for x86 CPUs.
The x86 library is a massive 7.3MBs in size.

As an experiment i compiled a b4a project both with and without x86 support.

By not supporting x86 CPUs the compiled APK was over 2MBs smaller.
The 7.3MB x86 library file size (once compressed and compiled into the APK) is over 2MBs.

So i'm posting this as a 'for your information'.
If you do not need to support android devices with x86 CPUs then you might want to remove that support and reduce your APK size:

Rename Spatialite.jar to Spatialite.zip
Open Spatialite.zip using WinZIP etc
Delete the folder x86 located in the lib folder
Close Spatialite.zip
Rename Spatialite.zip back to Spatialite.jar

If you have the b4a IDE running be sure to right click the library pane and click 'refresh'.

Martin.

psciga · Mar 7, 2014

Hi Martin,

thank you for this information!

Peter.

PABLO2013 · Mar 27, 2014

I consider http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.1.0.html and now I understand a little more about " ST_Within ( MakePoint ( ? ? ) " , however you Might I help with show as in SpatiaLite lib - b4a :

1. i need search for the minimum distance of a given point (eg: gps ) , some geometries of points and as stated in b4a
2 . the search for the minimum distance of a given point (eg: GPS ) , a few lines and geometries as stated in b4a
3. the search for the minimum distance of a given line (eg: gps ) , some geometries lineasy as stated in b4a
4 . the search for the minimum distance of a given point buffer , some geometries and Points as stated in b4a
5 . the search for the minimum distance of a given line buffer , to a point geometries as stated in b4a
Most particularly I ask for your valuable help to find ways to draw my polygons is an overlay osm , I made a great study of the osm threads and SpatiaLite and one I do not understand very well and need get the lat -log coordinate of each point of my polygons ,
thanks .

Gabriel Conti · Apr 20, 2014

Hola Martin, excelente trabajo, trabajo en GIS hace muchos años y justamente estoy tratando de realizar aplicaciones móviles. Tu publicación me viene excelente.
Estaré investigando tu trabajo.
Y trataré de realizar aportes

English
Hi Martin, great job, working in GIS for many years and I am just trying to make mobile applications. Your post comes excellent.
I will be researching your work.
And I try to make contributions

Gabriel Conti · Apr 20, 2014

Peter, también es un excelente aporte lo tuyo
Realicé el mismo trabajo pero con un web service a un postgis en un servidor con ip fija. No conocia esta base de datos spatial lite.
Gracias

English by google

Peter is also a contribution execlente yours
I performed the same job but with a web service to a postgis on a server with fixed ip. Did not know this spatial data base lite.
Thank you!

warwound · Apr 20, 2014

I was recently asked (by @PABLO2013) how to select polygon points from a spatialite table.
Using the existing spatialite library it seemed as though creating a query using AsText on the geometry column was the only solution.
That returns the geometry as a string to b4a and that string requires parsing to extract the required info.

The b4a code to parse that string needed to be able to handle simple polygons, complex multi polygons, polygons with inner rings etc.
Not a straightforward task.

So i wrapped a small part of the Vivid Solutions JTS Topology Suite library.
The parts i wrapped were mainly the WKBReader and WKTReader classes - and the classes required in order to work with WTBReader and WKTReader.

Now i can select a geometry using AsBinary and pass each row of the resultset to the JTS WTBReader's Read method.
The Read method accepts an array of Bytes - AsBinary selects the geometry as an array of Bytes.
The Read method returns a Geometry object.

The Geomtery object makes it easy to work with complex geometries such as multi polygons - no more hit and miss parsing of strings.

I've uploaded a reference document for my partial implementation of the JTS Topology Suite to here: http://b4a.martinpearman.co.uk/jts/JTS.html.

If there's any interest from forum members i'll upload my JTS library and an example project.

Martin.

PABLO2013 · Apr 20, 2014

THANKS MARTIN
REALLY I AM VERY GRATEFUL AND I STILL PRACTICING TO RESPECT THE ISSUES IS VERY INTERESTING.
GREETINGS

hypergreatthing · Apr 21, 2014

this is great.
Question though Martin:
Any way for this to be wrapped into sqlcipher as well?
Thanks,

warwound · Apr 21, 2014

hypergreatthing said:
Any way for this to be wrapped into sqlcipher as well?

Not unless you have a good knowledge of the android NDK, C and probably many other things too!
Have a look at this Google search.

See there's been more than one attempt to combine spatialite and sqlcipher?
The first result i see is: https://github.com/illarionov/android-sqlcipher-spatialite.

If you want both spatialite and sqlcipher you'll need to create an entire new library wrapping an android library such as the one above.

Whether or not these libraries that have already combined spatialite and sqlcipher have the same syntax as the library that my b4a library uses i don't know - it could be similar or completely different.

Martin.

Gabriel Conti · Apr 21, 2014

Excelente Martin! Gracias

Gabriel Conti · Apr 27, 2014

Hola Martin, te consulto, con la librería JTS me mencionaste anteriormente, utilizando el método contains, puedo saber si una coordenada obtenida con el gps está incluida dentro de una geometría X que se encuentre en una base de datos spatialite? Podrias subir el proyecto ejemplo con JTS. Muchas gracias.

Como siempre, en ingles gracias al traductor de Google.

Hi Martin, i consult with JTS library that mentioned above, using the contains method, i can know if a coordinate obtained with the gps is included within a geometry X that is in a data base SpatiaLite? Could you upload the sample project with JTS.
Thank you very much.
As always in english thanks to google translator.

warwound · Apr 28, 2014

If you have a database containing Geometry types such as Polygon and you want to find any Polygon that contains a Point then you ought to look at the CountryFinder demo in post #2.
That shows how to query the database for Polygons that contain a Point.

Using JTS is also possible but is a two step solution, the CountryFinder requires just one step.
If you use JTS you'd first have to select all Polygons from your database, then iterate through them using the Geomtry Contains method to establish whether each Polygon contains a Point.

Anyway you're welcome to take a look at the JTS library and a simple demo project.
The demo project uses the same database as the CountryFinder demo in post #2.
It gets all available countries from the database and populates a Spinner with the country names.
Select a country from the Spinner and the demo selects that country's Geometry from the database.
The Geometry could be a simple Polygon or a complex Polygon with inner rings etc.
The demo logs how many Polygons exist in the Geometry and then iterates through each Polygon logging the latitude and longitude of each of it's Points.
(Select United Kingdom from the Spinner to see a Geometry that contains more than one Polygon).

JTS library files and demo project attached.

Martin.

Gabriel Conti · Apr 28, 2014

Martin, thank you very much, as always very clear reply, I will be studying the issue. thanks

PABLO2013 · Jun 16, 2014

PABLO2013 · Sep 6, 2014

tks , jts library JTS_GeometryFactory no found:

CreatePoint (Coordinate1 Como Coordinate ) Como Point
Inicialice (SRID Como Int )
IsInitialized Como Boolean

PABLO2013 · Sep 6, 2014

como=as

warwound · Sep 6, 2014

PABLO2013 said:
tks , jts library JTS_GeometryFactory no found:

CreatePoint (Coordinate1 Como Coordinate ) Como Point

Inicialice (SRID Como Int )

IsInitialized Como Boolean

The JTS library files are attached to this post: http://www.b4x.com/android/forum/threads/spatialite.36296/page-2#post-241098

PABLO2013 · Sep 6, 2014

GeometryFactory1.Initialize (Unknown member ???????)

Point1=GeometryFactory1.CreatePoint(Coordinate1) (Unknown member ???????)

................................

in your sample "PolygonsFromSpatialite" you used , GeometryFactory1 and members (CreatePoint,Initialize) but i cannot used it, tks

warwound · Sep 7, 2014

That's odd!
I'll take a look and see what's gone wrong.

PABLO2013 · Sep 7, 2014

greetings martin
solved it with your help, I reinstalled the jts library and now everything works fine, thanks.

see that jts (GeometryFactory1.CreatePoint) creates a point, but as a polygon or a line or multiline is created.

advance .thanks

B4A Library Spatialite

Expert

Active Member

Well-Known Member

Member

Member

Expert

Well-Known Member

Member

Expert

Member

Member

Expert

Attachments

Member

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency