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.

 

[warwound]

Here's a relatively simple example that shows the basics of using Spatialite.

To start with i downloaded the country admin boundary data for the planet from: http://www.naturalearthdata.com/downloads/110m-cultural-vectors/.
The downloaded data is in shapefile format.

I used SpatialiteGUI to create a new empty database and then used it's 'import shapefile' option to import the shapefile into the database.
Each country being represented in the database by a column with a Polygon data type.

Next i created a B4A Class to encapsulate all of the database stuff:

Sub Class_Globals

   Type Coordinate( _
     Latitude As Double, _
     Longitude As Double _
   )

   Type CountryBoundary( _
     Continent As String, _
     NameLong As String _
   )

   Private DATABASE_FILENAME As String="ne_110m_admin_0_countries.sqlite"
   Private DATABASE_PATH As String=File.DirDefaultExternal
   '   SRID is the 'spatial reference id', a value of 4326 corresponds to WGS84 projection
   Private SRID As Int=4326
   Private TABLE_NAME As String="ne_110m_admin_0_countries"
  
   Private SpatialiteConstants As Spatialite_Constants
   Private SpatialiteDatabase As Spatialite_Database
End Sub

Public Sub Initialize

   If Not(File.Exists(DATABASE_PATH, DATABASE_FILENAME)) Then
     File.Copy(File.DirAssets, DATABASE_FILENAME, DATABASE_PATH, DATABASE_FILENAME)
   End If
  
   SpatialiteDatabase.Initialize
   SpatialiteDatabase.Open(DATABASE_PATH, DATABASE_FILENAME, SpatialiteConstants.SQLITE_OPEN_READONLY)
End Sub

Public Sub Close
   SpatialiteDatabase.Close
End Sub

Public Sub GetCountryBoundarys(Coordinate1 As Coordinate) As List
   '   queries the spatialite database for the CountryBoundarys that contains Latitude, Longitude
   '   returns a List of CountryBoundarys
   '   the List will not be initialized if no CountryBoundarys contains Latitude, Longitude
   '   if more than one CountryBoundary contains Latitude, Longitude then the List will be sorted alphabetically by long name
   Dim ResultSet As List   '   will return a List of CountryBoundary types
  
   Dim SpatialiteStatement As Spatialite_Stmt
   SpatialiteStatement=SpatialiteDatabase.Prepare("SELECT name_long, continent FROM "&TABLE_NAME&" WHERE ST_Within(MakePoint(?, ?, ?), Geometry) ORDER BY name_long ASC;")
   SpatialiteStatement.BindDouble(1, Coordinate1.Longitude)
   SpatialiteStatement.BindDouble(2, Coordinate1.Latitude)
   SpatialiteStatement.BindInt(3, SRID)
  
   Do While SpatialiteStatement.Step
     Dim CountryBoundary1 As CountryBoundary
     CountryBoundary1.Initialize
     CountryBoundary1.NameLong=SpatialiteStatement.ColumnString(0)
     CountryBoundary1.Continent=SpatialiteStatement.ColumnString(1)
     If Not(ResultSet.IsInitialized) Then
       ResultSet.Initialize
     End If
     ResultSet.Add(CountryBoundary1)
   Loop
  
   SpatialiteStatement.Close
  
   Return ResultSet
End Sub

And finally an Activity module that creates an instance of the Class and tests for the existence of 3 geographical points within all of the country boundaries in the database:

Sub Process_Globals

End Sub

Sub Globals
   Dim Coordinates As List
   Dim CountryFinder1 As CountryFinder
End Sub

Sub Activity_Create(FirstTime As Boolean)
   Coordinates.Initialize
   CountryFinder1.Initialize
  
   CreateCoordinate(52.75718, 0.39)   '   Norfolk, UK
   CreateCoordinate(34.3, -41.8)     '   center of North Atlantic
   CreateCoordinate(21.86, -78.58)     '   Cuba
  
   For Each Coordinate1 As Coordinate In Coordinates
     Dim CountryBoundarys As List
     CountryBoundarys=CountryFinder1.GetCountryBoundarys(Coordinate1)
     If CountryBoundarys.IsInitialized Then
       For Each CountryBoundary1 As CountryBoundary In CountryBoundarys
         Log("CountryBoundary found for ("&Coordinate1.Latitude&", "&Coordinate1.Longitude&"): "&CountryBoundary1.NameLong&" ("&CountryBoundary1.Continent&")")
       Next
     Else
       Log("No CountryBoundary found for ("&Coordinate1.Latitude&", "&Coordinate1.Longitude&")")
     End If
   Next
  
   CountryFinder1.Close
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

The logged output of the project is:

LogCat connected to: CM7-Blade
--------- beginning of /dev/log/system
--------- beginning of /dev/log/main
** Activity (main) Create, isFirst = true **
CountryBoundary found for (52.75718, 0.39): United Kingdom (Europe)
No CountryBoundary found for (34.3, -41.8)
CountryBoundary found for (21.86, -78.58): Cuba (North America)
** Activity (main) Resume **

It works exactly as expected.
The code successfully queries the database for rows where the row Polygon contains a Point.

Project attached.

Martin.

 

[warwound]

This example is a bit more complex than the previous example.

I started by downloading english-places-shape.zip from: http://mapcruzin.com/free-england-arcgis-maps-shapefiles.htm.
This is a shapefile containing 21057 English places.
I imported the shapefile into a new empty spatialite database using SpatialiteGUI - each place now represented by a Point type column in the database.
As part of the import process i also created a spatial index on the Point type column - we'll see how such an index can improve database performance in the example project.

The aim of the B4A project was to use the B4A HttpServer library to create a map tile server that runs on the android device.
The tile server's original task was to:

• Accept requests for map tiles over HTTP.
• Create a blank 256 pixel square Bitmap for each request received.
• Query the database for places that are within the bounds of the tile to be served.
• Draw each place from the query result on to the Bitmap.
• Return the Bitmap as a JPG or PNG image via HTTP.

Once i got that working i added a 'web service' to the tile server.
So as well as processing requests for map tiles, the tile server also:

• Accepts requests for JSON data.
• Each JSON request contains a latitude, longitude and zoom parameter.
  The tile server queries the database for places near to the latitude, longitude position.
• The query result is converted from a List of Map objects to a string using the JSON library.
• Finally this string is returned via HTTP.

So in the example project i create a basic Google Map, and add a TileOverlay to it.
The TileOverlay is configured to request tiles over HTTP from my tile server running on the device.
The map displays the TileOverlay on top of the built in 'road map' map type.
(The tile server creates transparent PNG tiles - where no places are drawn on the tiles, the PNG is transparent).

Pan and zoom the map and the tile server receives and responds to requests for tiles.
Admittedly on a low power device this solution is not really practical, but on a reasonable spec device it all works pretty smoothly.

Tap the map and the tapped position's latitude and longitude coordinates along with the map zoom level are sent in an HTTP request to the tile server.
The tile server detects if the tap was a tap on one or more places and returns a JSON string that lists any places that were within the 'tap area'.
The example project then simply displays each place as a Toast message.

The source code for the TileServer service contains two versions of the query that gets places from the database to be drawn on a Tile.
One version makes no use of the spatial index created earlier, the other version makes use of the spatial index.

I started the app and let the map request the tiles from the tile server required to display the initial map view.
I did this twice - once with the query that doesn't use the spatial index and once with the query that does use the spatial index.

With no spatial index the tile rendering times were:

Places count: 37, tile creation took 1098 ticks
Places count: 35, tile creation took 622 ticks
Places count: 42, tile creation took 521 ticks
Places count: 13, tile creation took 684 ticks
Places count: 19, tile creation took 480 ticks
Places count: 28, tile creation took 606 ticks
Total rendering time: 4011 ticks

With the spatial index used, the tile rendering times were:

Places count: 13, tile creation took 204 ticks
Places count: 37, tile creation took 177 ticks
Places count: 28, tile creation took 125 ticks
Places count: 35, tile creation took 137 ticks
Places count: 19, tile creation took 180 ticks
Places count: 42, tile creation took 221 ticks
Total rendering time: 1044 ticks

Using the spatial index was nearly 4 times faster than not using the spatial index.
This is tested on a rather old HTC Desire S running a custom android 4.2.2 ROM - a 1GHz single core CPU.
In theory the tile server can accept more than once request at a time so i'd expect performance on multi core CPU devices to be far better - more cores can be in use at any one time.

On my Galaxy S3 the rendering times when using the spatial index were:

Places count: 13, tile creation took 136 ticks
Places count: 28, tile creation took 103 ticks
Places count: 21, tile creation took 128 ticks
Places count: 36, tile creation took 124 ticks
Places count: 42, tile creation took 84 ticks
Places count: 37, tile creation took 143 ticks

Performance on the S3 is very smooth - it's very useable.

So example project #1 demonstrates getting Polygons from a database that contain a Point - the Point being part of the query.
This example demonstrates getting Points from a database where the Points are within a Polygon - the Polygon being part of the query.

This project is 1.7MBs in size - the bulk of that being the spatialite database - that's too big to attach to a forum post so instead you can download it from: http://b4a.martinpearman.co.uk/spatialite/, look for the archive 'TileServer-GoogleMaps.zip'.

Note that in order to compile this example you will need these additional libraries:

• GoogleMaps: http://www.b4x.com/android/forum/threads/google-maps-android-v2-tutorial.24415/
• GoogleMapsExtras: http://www.b4x.com/android/forum/threads/googlemapsextras.26277/
• HttpServer: http://www.b4x.com/android/forum/th...er-in-your-android-application.25984/#content
• Mercator: http://b4a.martinpearman.co.uk/spatialite/
  This small library (Mercator_library_files.zip) contains methods to convert between WGS84 and screen coordinates.
• Spatialite - link in first post in this thread.

You will also need to choose a private signing key and update the manifest with your Google Maps API key.

I hope to create a similar example that uses OSMDroid instead of Google Maps - i'll add a new post to this thread when i have that working.

Martin.

 

[Fifi Donkor]

Hi Martin,
Thanks for this. I have done extensive desktop GIS work using both spatialite and postGIS. This is something I have been looking for for a while. I am not in the position to check this out now (busy with other things) but will do so in the near future. I hope to give you some feedback. Off my head, I can tell you that if you could make a custom b4a view that can take spatial data and render it on screen without necessarily using Google Maps, I (hopefully some others too) will be willing to pay some $$ to have it.

 

[psciga]

Hello Martin,

at first, thank you for your great lib!
In the first step I have created a database in the GUI. The I've createt a new table an added one row:

CREATE TABLE latlon (
  id INTEGER NOT NULL
    PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL);

SELECT AddGeometryColumn('latlon', 'graphik',
  4326, 'POINT', 'XY');

INSERT INTO latlon
    (id, name,graphik)
  VALUES (NULL, 'Punkt',
    GeomFromText('POINT(13.94045 50.95397)', 4326));

Using this DB the following code run fine:

Sub Globals
    Dim dbPfad As String
    dbPfad = File.DirRootExternal & "/autogis/sqlite/"
    Dim dbName As String
    dbName = "peter2.sqlite"
    Dim sqDb As Spatialite_Database
    Dim sqKonst As Spatialite_Constants
    Dim GKS As String
End Sub

Sub Activity_Create(FirstTime As Boolean)
    GKS = progStart
     Msgbox(GKS,"")
End Sub

Sub progStart As String
'### DB kopieren
    Dim anzZeil As Int
    Dim anzSpalt As Int
    Dim Ergebnis As String
    Dim sqStm As Spatialite_TableResult
    Dim SQL As String

    File.Copy(File.DirAssets,dbName,dbPfad,dbName)
    sqDb.Initialize
    sqDb.Open(dbPfad,dbName,sqKonst.SQLITE_OPEN_READWRITE)

    SQL = "select astext(ST_Transform(graphik,31469)) as res from latlon;"
    sqStm = sqDb.GetTable(SQL)
    anzZeil = sqStm.NumberRows
    anzSpalt = sqStm.NumberRows
    Ergebnis = sqStm.Rows(0,0)
    sqDb.Close
    Return Ergebnis
End Sub

But how to insert a new row? I've tried

Dim sqCb As Spatialite_Callback
  sqCb.Initialize("Ereignis")
   SQL = "INSERT INTO latlon (id, name,the_geom) VALUES (Null, 'geldern1', GeomFromText('POINT(6.42307 51.39870)', 4326));"
   sqDb.Exec(SQL,sqCb) 

Sub Ereignis
   Msgbox("ereignis","")
End Sub

But nothing happend. What I have done wrong?

Thanks in Advance - Peter

 

[warwound]

The Spatialite_Callback object raises three events:

• Columns(ColumnData() As String)
• NewRow(RowData() As String) As Boolean
• Types(TypeData() As String)

You've initialized the Spatialite_Callback:

sqCb.Initialize("Ereignis")

So you want to listen for events:

Sub Ereignis_Columns(ColumnData() As String)
  ' do something
End Sub

Sub Ereignis_NewRow(RowData() As String) As Boolean
  ' do something and return a Boolean value
End Sub

Sub Ereignis_Types(TypeData() As String)
  ' do something
End Sub

That's the theory, however when i first developed the library i found the Spatialite_Callback events were not raised.
I suspected the callbacks within the library were being executed in a non UI thread and planned to do some testing but never had time to do those tests .

I simply used the Spatialite_Database Exec method with a Null parameter as the Spatialite_Callback when executing INSERT queries.

Maybe the Spatialite_Callback isn't executed when an INSERT query is executed but only executed when a SELECT query is executed?
I didn't have time to check...

Anyway - try adding the correct three Subs to handle the 3 events that Spatialite_Callback raises.
Add a Log statement to each Sub.
If no events are raised and you want to help fix the problem then post again.
I'll compile the library with some logging statements so we can detect where the problem is.

By the way you said 'nothing happened', you meant the event Sub was not executed - but did the INSERT query actually execute and add a row to your table?

Martin.

 

[psciga]

Hello,

I added the three Subs. But when I call e.g

sqDb.Exec(SQL,Ereignis_NewRow())

comes a compiler error:

Error description: Missing parameter.
Occurred on line: 91
sqDb.Exec(SQL,Ereignis_NewRow())

What are the parameters for call back routines?

Then I've tried

sqDb.Exec(SQL,Null)

Here comes no error, but nothing happens. This means no error, but also no new row was inserted.

 

[warwound]

Hello,

I added the three Subs. But when I call e.g

sqDb.Exec(SQL,Ereignis_NewRow())

comes a compiler error:

Error description: Missing parameter.
Occurred on line: 91
sqDb.Exec(SQL,Ereignis_NewRow())

You should use the syntax that you originally posted:

sqDb.Exec(SQL,sqCb)

Pass the Spatialite_Callback as the parameter.
Now you can see if any of the 3 callback events are raised.

Martin.

 

[psciga]

Hello Martin,

thanks for your effort. I have done the following:

    sqDb.Open(dbPfad,dbName,sqKonst.SQLITE_OPEN_READWRITE)
    'sqCb.Initialize("Ereignis_Columns")
    'sqCb.Initialize("Ereignis_NewRow")
    sqCb.Initialize("Ereignis_Types")
    SQL = "INSERT INTO latlon1 (id, name,the_geom) VALUES (Null, 'geldern2', GeomFromText('POINT(6.5 51.39870)', 4326));"
    sqDb.Exec(SQL,sqCb)
    sqDb.Close

And here the Callbacks:

Sub Ereignis_Columns(ColumnData() As String)
  ' do something
  Log("columns")
End Sub

Sub Ereignis_NewRow(RowData() As String) As Boolean
  ' do something and return a Boolean value
  Log("newrow")
End Sub

Sub Ereignis_Types(TypeData() As String)
  ' do something
  Log("types")
End Sub

The results are the same. And no log entries.

Peter

 

[warwound]

Can you download this modified version of the library .jar file and replace your existing .jar file with this modified version.
(Backup your existing Spatialite.jar!).
http://b4a.martinpearman.co.uk/spatialite/Spatialite.debug.zip

Now right-click the B4A libraries panel and choose 'refresh'.
Finally run your project again.

Do you see anything in the logs such as 'Spatialite_Callback columns', 'Spatialite_Callback newrow' or 'Spatialite_Callback types'?

Thanks.

Martin.

 

[psciga]

Do you see anything in the logs such as 'Spatialite_Callback columns', 'Spatialite_Callback newrow' or 'Spatialite_Callback types'?
Martin.

Sorry, no.....

 

[psciga]

Sorry Martin,

big shame on me! .... It was a 50 cm problem, my SQL string was wrong!

SQL = "INSERT INTO latlon1 (id, name,the_geom) VALUES (Null, 'geldern2', GeomFromText('POINT(6.5 51.39870)', 4326));"

There is no column "the_geom" in my table. The correct name is "graphik".

I found my mistake in the log:

....
jsqlite.Exception: table latlon1 has no column named the_geom
at jsqlite.Database._exec(Native Method)
at jsqlite.Database.exec(Database.java:187)
at uk.co.martinpearman.b4a.spatialite.Database.Exec(Database.java:68)
at com.battefeld.spatialite1.splite._vv2(splite.java:58)
....

The reason for it is, that I'm usually look only for the filtered Log. After your last post I was looking for the complete Log.

... S* copy&paste.

Sorry Martin for the trouble!

Thanks again - Peter

P.S.: Have you recieved my PM from today?

 

[M6SOFT]

Hi Martin,
I'm looking for newer version of spatialite library because this version is affected by bug when transform coordinates between Krassowski ellipsoid and WGS ellipsoid.
I found this thread:
https://groups.google.com/forum/#!topic/spatialite-users/U11RzPqjBm4
Here is link for v.4.0.0 binaries: https://bitbucket.org/mayastudios/f...72575/spatialite/spatialite-4.0.0/?at=default
Is it possible to use this binaries to build B4A library?
I dont know if this version solve my transformation problem but maybe it use newer version of proj4 library.

Greg

 

[warwound]

The binaries for version 4.0.0 are just that - binaries.

The wrapped version 3.0.1 library contains binaries and a java class that is the bridge between the C/C++ binaries and java.
This java class allows the C/C++ binaries and java to communicate with each other.
And the b4a library allows b4a and the java class to communicate with each other.

Version 4.0.0 does not work like that.
It seems as if you have to:

• Load binaries for SQLite and Spatialite.
• Call the sqlite3_load_extension() function.
• Now you can execute SQLite queries that use Spatialite features.

The instructions are vague/non-existent as are any examples.

On the Google Group thread, the author of version 4.0.0 states:

Spatialite "just" adds some SQL functions and data types to SQLite. So, in theory there should be no difference in how you use SQLite. However, you'll need some library to handle the geometry data types, if you don't want to fall back to WKT.

What does that mean?
Does version 4.0.0 not contain the geos and proj4 libraries?

I'll say that i can compile the version 4.0.0 binaries into a library file but have no idea how you'd use those binaries in either java or b4a.

Your problem is a bug with the version of proj4 that is compiled into version 3.0.1 of Spatialite?
Take a look at the build instructions for version 3.0.1: http://www.gaia-gis.it/gaia-sins/spatialite-android/spatialite-android-build-steps.txt.
Version 4.7.0 of proj4 is compiled into version 3.0.1 of Spatialite.

Is this bug you have a known bug that has been fixed?
If so, which version of proj4 contains the fix?
Can we (i!) compile version 3.0.1 of Spatialite with the version of proj4 that contains the fix?

There's another 'how to build spatialite for android' tutorial here: https://code.google.com/p/spatialite-android/.
Notice that the instructions use version 4.8.0 of proj4?
Is the bug fixed in version 4.8.0 of proj4 so we can try to build using this tutorial instead of the tutorial at gaia-gis.it?

Martin.

 

[gvoulg]

Hi Martin
Many thanks for your efforts .
Spatialite library is very impressive and I am using it in a project.
What i want to do is, in a few words: select a point on screen and read the information from spatial database based on polygon where the polygon resides (successfully so far) .
The background tiles are custom tiles i made with MapTiler using Georeferenced OrthoPhotos (from Satellite images) .
They reside on my tablet's sd cadr in OsmDroid directory and I can draw them offline using OSMDROID.
My problem is that I want to draw the polygons. Querying the database I get the lat long coordinates of the polygon.But how I can draw some polygons over the map with Osmdroid;
Its easier to do that with qoogle maps libraries but then I cannot using the same method using offline tiles on my tablet's card.
Any ideas?
Did you make any progress with OsmBonusPack library? I suppose that it solves the polygon draw problem in osmdroid.
Thanks in advance
George

 

[M6SOFT]

The binaries for version 4.0.0 are just that - binaries.

The wrapped version 3.0.1 library contains binaries and a java class that is the bridge between the C/C++ binaries and java. ...
What does that mean?
Does version 4.0.0 not contain the geos and proj4 libraries?

I'll say that i can compile the version 4.0.0 binaries into a library file but have no idea how you'd use those binaries in either java or b4a.

I was looking for something newest. I haven't experience with building libraries so thank You for explanation.

Your problem is a bug with the version of proj4 that is compiled into version 3.0.1 of Spatialite?
Take a look at the build instructions for version 3.0.1: http://www.gaia-gis.it/gaia-sins/spatialite-android/spatialite-android-build-steps.txt.
Version 4.7.0 of proj4 is compiled into version 3.0.1 of Spatialite.

Is this bug you have a known bug that has been fixed?
If so, which version of proj4 contains the fix?
Can we (i!) compile version 3.0.1 of Spatialite with the version of proj4 that contains the fix?

I found similar problems (about 130m shift after transformation) on polish forum. This bug was described here http://trac.osgeo.org/gdal/ticket/3579#comment:6.
From 05.2010 this bug was included in autotest procedures (http://trac.osgeo.org/gdal/changeset/19719/) so i hope current versions proj4 working correctly (i tested windows version OSGeo4W and results are OK)

There's another 'how to build spatialite for android' tutorial here: https://code.google.com/p/spatialite-android/.
Notice that the instructions use version 4.8.0 of proj4?
Is the bug fixed in version 4.8.0 of proj4 so we can try to build using this tutorial instead of the tutorial at gaia-gis.it?

Martin.

Seems like 4.8.0 is correct. Martin You helped me many times and I know that this probably take a lot of time. I would be very grateful but i know also that this bug is probably important for only me. If You have time to build this library with new proj4 - it will be fantastic.

 

[psciga]

Hi Martin,

thanks again for your lib. After struggeling some hours with the EXEC function I found a way for transforming coordinates from WGS 84 to a local system. By using GetTable it is very simple:

SQL = "SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT("
SQL = SQL & Lon & " " & Lat & ")',4326),"
SQL = SQL & sridTarget & ")) As trans_geom;"
sqStm = splDB.GetTable(SQL)
Result = sqStm.Rows(0,0)

The result is a string.

Maybe it helps somebody else.

Peter

 

[warwound]

@gvoulg
Look at the documentation for OSMDroid PathOverlay: http://b4a.martinpearman.co.uk/osmdroid/OSMDroid_3_0_8.html
See the PathOverlay has a property:

PathStyle As Style [write only]
Set the path style used to draw the path. Valid values are: PathOverlay.FILL PathOverlay.FILL_AND_STROKE PathOverlay.STROKE Default value is PathOverlay.STROKE

I think that you draw a polygon by creating a PathOverlay with a closed set of points and setting the PathStyle to FILL or FILL_AND_STROKE.
Ideally i'd expose the android Paint object to b4a to give complete control over the PathOverlay rendering.
I'll look at doing that with the new version 4.1 of OSMDroid.

@M6SOFT
I'll attempt a built of Spatialite using version 4.8.0 of proj4 over the weekend and post again with my results.

@psciga


And @everyone the new version of OSMDroid is progressing well.
I'll hope to get some of you to beta test it over the weekend.

Martin.

 

[warwound]

@M6SOFT

I've compiled Spatialite using the source and instructions found here: https://code.google.com/p/spatialite-android/.

So it's compiled using version 4.8.0 of proj4, looking at the proj4 website i see that version 4.8.0 is the latest version.
So if this doesn't fix your bug then you're probably out of luck...

Anyway, the newly compiled version can be downloaded from here: http://b4a.martinpearman.co.uk/spatialite/SpatialiteMyBuild.zip.
I've tested the newly compiled version using the two demo projects in this thread and both demos work as expected.

So can you test with your project and see if the projection bug is fixed or not and then update this thread?

Thanks.

Martin.

 

[warwound]

Spatialite updated to version 1.10

This update adds no new functionality.

I have recompiled the library so that it uses a newer patched version of the PROJ.4 library.
This fixes a bug when transforming coordinates between Krassowski ellipsoid and WGS ellipsoid.

I have also modified the library so that if an Exception occurs in the library, that exception will be thrown to your b4a code.
Previously the library silently caught and logged any Exceptions that occured - making it tricky to debug errors in your b4a code.

The new version has also shrunk to just 5.36MBs, the previous version was 6.95MBs in size.

To upgrade to the new version just overwite the old library files with the new library files, no changes need to be made to any existing code.

The new version can be downloaded from here: http://b4a.martinpearman.co.uk/spatialite/, Spatialite_library_files_v1.10.zip is the file you want to download.

Martin.