B4A Library Spatialite

Discussion in 'Additional libraries, classes and official updates' started by warwound, Jan 2, 2014.

  1. warwound

    warwound Expert Licensed User

    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 5.36MBs 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_v1.10.zip).
    Also available at that link is SpatialiteGUI - a small Windows utility that will likely be very handy for you developers.

    Martin.
     
    Last edited: Mar 2, 2014
  2. warwound

    warwound Expert Licensed User

    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:

    Code:
    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:

    Code:
    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.757180.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:

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

    Project attached.

    Martin.
     

    Attached Files:

  3. warwound

    warwound Expert Licensed User

    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:

    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.
     
    M6SOFT likes this.
  4. Fifi Donkor

    Fifi Donkor Member Licensed User

    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.
     
  5. psciga

    psciga Active Member Licensed User

    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:

    Code:
    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:

    Code:
    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


    Code:
    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
     
  6. warwound

    warwound Expert Licensed User

    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:

    Code:
    sqCb.Initialize("Ereignis")
    So you want to listen for events:

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

    Sub Ereignis_NewRow(RowData() As StringAs 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 likes this.
  7. psciga

    psciga Active Member Licensed User

    Hello,

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

    Code:
    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

    Code:
    sqDb.Exec(SQL,Null)
    Here comes no error, but nothing happens. This means no error, but also no new row was inserted.
     

    Attached Files:

  8. warwound

    warwound Expert Licensed User

    You should use the syntax that you originally posted:

    Code:
    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.
     
  9. psciga

    psciga Active Member Licensed User

    Hello Martin,

    thanks for your effort. I have done the following:
    Code:
    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:

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

    Sub Ereignis_NewRow(RowData() As StringAs 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
     
  10. warwound

    warwound Expert Licensed User

    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.
     
  11. psciga

    psciga Active Member Licensed User

    Sorry, no.....
     
  12. psciga

    psciga Active Member Licensed User

    Sorry Martin,

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

    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?
     
  13. M6SOFT

    M6SOFT Member Licensed User

    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
     
  14. warwound

    warwound Expert Licensed User

    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:

    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.
     
  15. gvoulg

    gvoulg Member Licensed User

    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
     
  16. M6SOFT

    M6SOFT Member Licensed User

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

    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)

    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.
     
  17. psciga

    psciga Active Member Licensed User

    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:

    Code:
    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
     
  18. warwound

    warwound Expert Licensed User

    @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
    :cool:

    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.
     
  19. warwound

    warwound Expert Licensed User

    @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.
     
  20. warwound

    warwound Expert Licensed User

    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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice