B4A Library Spatialite

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.

 

[PABLO2013]

I think the error occurred when moving from version 3.8 to 3.82 B4A, not know really, but now it works ...tks

 

[warwound]

@PABLO2013

Remember that i sent you a version of the JTS library a few months back?
I bet you still had that version - i've updated the library since i first sent you a copy.

 

[PABLO2013]

my sincere thanks for it, yes now work fine...
I'm trying to compare polygons with polygons, polygons with lines, etc ....
but I see that GeometryFactory1.CreatePoint create point ... as I can do to create a polygon or a line or multiline ... tks

 

[PABLO2013]

I'm trying to compare polygons with polygons, polygons with lines, etc ....
but I see that GeometryFactory1.CreatePoint create point ... as I can do to create a polygon or a line or multiline ... tks

 

[warwound]

@PABLO2013

Can you tell me exactly which methods you need added to the JTS library?

 

[PABLO2013]

tks martin
GeometryFactory1 create poligon , line , multiline... and if it is not much hassle , some help for used it
tks

 

[vfafou]

Hi!
I'm very new to Geospatial data. I have read many docs about SpatiaLite but I've not found any way to insert polygons from a file with simple X;Y coordinate points.
Is there any way or any suggestion to do this?
Thank you in advance!

 

[vfafou]

It's OK! I formatted the data with the help of Excel and got the work done!

 

[PABLO2013]

tks martin
GeometryFactory1 create poligon , line , multiline... and if it is not much hassle , some help for used it
tks

 

[PABLO2013]

tks martin
GeometryFactory1 create poligon , line , multiline... and if it is not much hassle , some help for used it
tks

 

[PABLO2013]

tks martin
I need JTS , GeometryFactory1 create poligon , line , multiline... and if it is not much hassle , some help for used it.
Or say me how i make it
tks

 

[PABLO2013]

HI MARTIN I CAN ENCRIPT A STATILITE DATA BASE
TKS

 

[warwound]

HI MARTIN I CAN ENCRIPT A STATILITE DATA BASE
TKS

No thats currently not possible.
The library that encrypts sqlite databases isn't compatible with spatialite .

 

[Mashiane]

Thanks Martin

This link http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html shows a 404 anyway have googled spatialite, cool!

 

[PABLO2013]

greetings martin, i was watching your library JTS , a while back I ask to help me with other classes ... I know you're busy ... the original library has with many classes ... as could make for efficient transfer of all the library to operate in B4A,...... see that you use: AbsObjectWrapper, this will be the only way or is there another more efficient for both the jar to the xml in this particular case , could you tell me , thanks.

 

[PABLO2013]

greetings martin, i was watching your library JTS , a while back I ask to help me with other classes ... I know you're busy ... the original library has with many classes ... as could make for efficient transfer of all the library to operate in B4A,...... see that you use: AbsObjectWrapper, this will be the only way or is there another more efficient for both the jar to the xml in this particular case , could you tell me , thanks.

 

[PABLO2013]

greetings martin

Apology , you could implement LinesString creating geometryfactory , thanks

 

[mkvidyashankar]

hi

I have spatialite database having data of road network, I want to display graphically(Like map) the nearest road polyline withrespect to GPS coordinates. I am searching for the method, I didnot find any, Please help

 

[mkvidyashankar]

how to update a field( that is string) in spatilite database?

 

[gvoulg]

Hi Martin
Is it possible to use spatialite library with b4j?
Tried and get this error log in b4j project

Waiting for debugger to connect...
Program started.
https://www.google.com/maps/place/ΠΕΙΣΙΣΤΡΑΤΟΥ+38+ΠΑΛΛΗΝΗ
Unable to load sqlite_jni: java.lang.UnsatisfiedLinkError: no jsqlite in java.library.path
java.lang.UnsatisfiedLinkError: jsqlite.Database.internal_init()V
at jsqlite.Database.internal_init(Native Method)
at jsqlite.Database.<clinit>(Database.java:1025)
at uk.co.martinpearman.b4a.spatialite.Database.Initialize(Database.java:76)
at b4j.wmsKthmatologio.main._initialize(main.java:318)
at b4j.wmsKthmatologio.main._appstart(main.java:279)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:612)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:229)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:90)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at b4j.wmsKthmatologio.main.start(main.java:38)
at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$163(LauncherImpl.java:863)
at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$176(PlatformImpl.java:326)
at com.sun.javafx.application.PlatformImpl.lambda$null$174(PlatformImpl.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl.lambda$runLater$175(PlatformImpl.java:294)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$149(WinApplication.java:191)
at java.lang.Thread.run(Thread.java:745)
java.lang.RuntimeException: java.lang.UnsatisfiedLinkError: jsqlite.Database.internal_init()V
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:114)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:90)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at b4j.wmsKthmatologio.main.start(main.java:38)
at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$163(LauncherImpl.java:863)
at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$176(PlatformImpl.java:326)
at com.sun.javafx.application.PlatformImpl.lambda$null$174(PlatformImpl.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl.lambda$runLater$175(PlatformImpl.java:294)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$149(WinApplication.java:191)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.UnsatisfiedLinkError: jsqlite.Database.internal_init()V
at jsqlite.Database.internal_init(Native Method)
at jsqlite.Database.<clinit>(Database.java:1025)
at uk.co.martinpearman.b4a.spatialite.Database.Initialize(Database.java:76)
at b4j.wmsKthmatologio.main._initialize(main.java:318)
at b4j.wmsKthmatologio.main._appstart(main.java:279)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:612)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:229)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
... 12 more

Regards
George