Spatialite 4 for Windows and Android

[Sebastian Krysmanski]

Just a quick note for people looking for a way to compile Spatialite 4 on Windows (with Visual Studio) or Android. I've compiled project files/build files for both and placed them in public Mercurial repositories:

• For Windows: https://bitbucket.org/mayastudios/spatialite-lib-windows
  $ hg clone https://bitbucket.org/mayastudios/spatialite-lib-windows
• For Android: https://bitbucket.org/mayastudios/spatialite-lib-android
  $ hg clone https://bitbucket.org/mayastudios/spatialite-lib-android

You need Mercurial (hg) to check out these repositories. You cannot just download them, as they contain sub repositories that won't be included in the download.

If you're just interested in the resulting binary, you can download them here:

https://bitbucket.org/mayastudios/files/src

 - Sebastian

[Qiaoyan Zhang]

hello sebastian,
i want to build spatialite 4 on windows (and enable lwgeom),
i set #define ENABLE_LWGEOM 1 in config-msvc.h

when i  execute following commands,
nmake /f makefile64.vc
nmake /f makefile64.vc install
get errors in step: link /debug /dll /out:spatialite_i.lib \
spatialite.obj : error LNK2019: unresolved external symbol splite_lwgeom_version
 referenced in function fnct_lwgeom_version
spatialite.obj : error LNK2019: unresolved external symbol gaiaMakeValid referen
ced in function fnct_MakeValid
spatialite.obj : error LNK2019: unresolved external symbol gaiaMakeValidDiscarde
d referenced in function fnct_MakeValidDiscarded
spatialite.obj : error LNK2019: unresolved external symbol gaiaSegmentize refere
nced in function fnct_Segmentize
spatialite.obj : error LNK2019: unresolved external symbol gaiaSplit referenced
in function fnct_Split
spatialite.obj : error LNK2019: unresolved external symbol gaiaSplitLeft referen
ced in function fnct_SplitLeft
spatialite.obj : error LNK2019: unresolved external symbol gaiaSplitRight refere
nced in function fnct_SplitRight
spatialite.obj : error LNK2019: unresolved external symbol gaiaAzimuth reference
d in function fnct_Azimuth
spatialite.obj : error LNK2019: unresolved external symbol gaiaGeoHash reference
d in function fnct_GeoHash
spatialite.obj : error LNK2019: unresolved external symbol gaiaAsX3D referenced
in function fnct_AsX3D
spatialite.obj : error LNK2019: unresolved external symbol gaia3DDistance refere
nced in function fnct_3DDistance
spatialite.obj : error LNK2019: unresolved external symbol gaiaMaxDistance refer
enced in function fnct_MaxDistance
spatialite.obj : error LNK2019: unresolved external symbol gaia3DMaxDistance ref
erenced in function fnct_3DMaxDistance
spatialite.dll : fatal error LNK1120: 13 unresolved externals

all these functions are about lwgeom. should i build a lwgeom.dll at first? can i get lwgeom.dll without compiling postgis source?

[Jim]

Sebastian,

I just downloaded the binaries for Spatialite 4.0.0 for Android and have a couple questions:

1. Will the binaries work with Android 4.2.2?

2. Do I need the Sqlite libraries or will the Android native Sqlite suffice?

3. Do you happen to have a sample Android app that you could send me for study?  I have looked at the Spatialite for Android tutorial, and while making some progress, I am at a point that I need other things to look at.  I am especially interested in a sample that queries a DB and lists the rows in a Listview or such BUT any sample will do.

Regards,
Jim O'Brien

[Sebastian Krysmanski]

Sorry for the late response. You need to open the solution file to build Spatialite on Windows. I didn't touch the make files and I think they're outdated. So you can't use them.

[Sebastian Krysmanski]

Hi Jim,

1) Yes, Spatialite 4 will work with Android 4.2.2. (I'm running my app on this Android version.)

2) I tried using the SQLite library that ships with Android but I got errors on my multi-threading unit tests. I haven't had time yet to figure out the exact problem, mainly because the self-compiled SQLite version works just fine.

3) Both the SQLite and Spatialite projects come with unit tests which show some examples of how to use the API. It's not perfect but it's a start.

Cheers
Sebastian

[Jim]

Sebastian,

I am having a problem determining how to set up an Eclipse project to use your Adndorid-Spatialite-4 support.

When I worked with the 3.0.1 support from Andrea Antonelo, I had to place the ' jsqlite' source directory in the 'src' directory of my project and I had to build a libs directory containing the 3 hardware dependent libraries (armeabi, etc).

I can't figure out the equivalent using the 4.0.0 support and I need the 4.0.0 support for my app which I am converting from Windows and C#.

Assume I create an "Hello World" app using the Eclipse ADT wizard and I add some code to open and query  (using ST_AsText for example) an existing Spatialite database.

What do I have to do to the project structure to add your support so that my project compiles and runs (either on the Emulator or on a real Android device).

While I am an experienced programmer (about 50 years), I am a complete novice in terms of Android development and Eclipse so any help would be greatly appreciated.  In fact a complete simple project would be gratefully received.

Regards,
Jim

[Sebastian Krysmanski]

Hi Jim,

if you're using my repository, there should be no difference between using Spatialite 3 and Spatialite 4.

I'm not a 100% certain but I think the (old) Android-Java-Spatialite library, you're referring to, was build as single .so file (like the SQLite amalgamation). This has been deprecated by Spatialite so now there should be a "sqlitex.so" and a "spatialite.so" file.

You need to load both (via System.LoadLibrary(), I guess) and then you need to call the sqlite3_load_extension() function. There's also an SQL function for that but I've never used it.

I hope this helps.

Regards,

Sebastian

--
You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/U11RzPqjBm4/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.
Visit this group at http://groups.google.com/group/spatialite-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Jim]

Sebastian,

OK, that explains a lot.

I was curious why there was only one library libsqlite.so with the 3.0.1 version.

So as I think out loud:

1. Is there a source directory like jsqlite in your package that I should add to my project?

2. Do I use the same imports?  For example:

import jsqlite.Database;
import jsqlite.Exception;
import jsqlite.Stmt;

3.Do  I put both sets of libraries (sqlite and spatialite) in the libs directory of my project?

4. I'll try to get that far and see how it goes.

This is all truly embarrassing.....

Regards,
Jim

[Sebastian Krysmanski]

1. No, my repository just contains the build scripts for the native libraries (.so). How you use them is up to you. I'm using them in my C# projects via P/Invoke (binding here).

2. 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.

3. Yes.

[Jim]

Are you programming for Android in C#? If so, how?

Jim


Sent from my Samsung Epic™ 4G

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.

[Sebastian Krysmanski]

Yes, I do. This is done via Xamarin who provide C# for Android, iOS, and OS X. It's not free, though. It costs about 1000$ per platform, if you want to use Visual Studio for development.

[Jim]

Sebastain,

I took a look at the link where the V4 Spatilaite bindings are that you referenced and I must admit I can't make the parts make a whole.

I have also purchased Xamarin so I can develop in C#.

If I could impose, do you happen to have a sample solution in C# for Xamarin that uses Spatailite so I can work with it as the basic structure for my app?

Nothing more elaborate than an app created with Xamarin "New" with the necessary bits added to use Spatialite, open a Spatialite DB, and load the Spatialite extensions so that it compiles and runs.

I have done a lot with Spatialite on Windows so such a sample would save me a huge amount of time as I have more than enough to learn with Android itself.

Again, anything you could provide would be greatly appreciated.

Regards,
Jim

[Sebastian Krysmanski]

Hey Jim,

I've added examples for both Windows and Android in the examples folders - both for Spatialite and for SQLite.

Alternatively, you can have a look here for SQLite and here for Spatialite.

Hope this helps.

Regards,
Sebastian

[Jim]

Sebastian,

Thank you very much.

A couple of questions:

1. Is there a "clone" request for BitBucket to get all this to my machine?

2. If I am building for Android (ultimately for a Nexus 10) on Xamarin but testing on the x86 Emulator (Intel Atom with Intel VT-x) do I build with the Android components or the X86 components? 

Dumb questions I know, but I had a bad day what with Google changing the SDK directory structure so that Xamarin won't build anything I do and Xaramin currenlty only supporting Google Maps Android V1 which doesn't exist anymore (that was fun to figure out)

Regards,
Jim

Regards,
Jim

[Jim]

Sebastian,

One more question if I may.

I have been studying the SpatialiteAndroid example and I don't see where it loads the spatialite library or uses it.

I have to admit I am just looking a the individual bits and not the whole solution so if you could point me in the right direction to download the example solution it would be very helpful.

Regards,
Jim

[Jim O'Brien]

Sebastian,

 

I have been trying to use your Spatialite 4 for Android in a straight Eclipse built Android app without success.

 

I looked at the samples you provides and for the Android example don't understand how to setup the libs directory and, more importantly, how to access the Sqlite and Spatialite functions from my code.

 

Are there things I need to write to access the libraries?

 

I know you use Mono and Xamarin, but could you provide an Eclipse based 'helloworld' project with the pieces I need or comments on how to get them?

 

I would greatly appreciate it as I need to get to Spatialite 4 for my app.

 

Regards,

Jim 

[Sebastian Krysmanski]

Hi Jim,

to download Spatialite, you need Mercurial and do a clone of my repository (basically on the command line, use: hg clone https://bitbucket.org/mayastudios/spatialite.net/)

As for loading Spatialite: You need to do the following:

1) Load the Spatialite native shared library (i.e. libspatialite.so). For this, either use System.loadLibrary("spatialite") or System.load("/path/to/libspatialite.so"). In my code I do this either in "SharedLibraryLoader" or in "MonoDroidLibLoader". I admit it's a bit complicated but MonoDroid currently doesn't support native libraries in Android Library projects. So I wrote a workaround.

2) You then need to register the Spatialite extensions in SQLite. For this you first need to enable extension loading by calling "sqlite3_enable_load_extension()" followed by calling "sqlite3_load_extension()". For Spatialite, I do this in the constructor of "SLDatabaseConnection" by calling the method "DatabaseConnection.LoadExtension()".

I currently don't have the time to provide a Java example. It may be more involved because you need access to two SQLite C function which may not be available in the JNI implementation you're using. I've never done JNI and find it quite cumbersome (compared to P/Invoke or JNA).

Hope this helps.
Regards,
Sebastian

[Jim]

Sebastian,

After several months of heath issues I am back working on some personal apps using your package.

I have managed to build the example, install the shared libraries, and run the example app.

I am using the latest Xamarin and the GenyMotion emulator.

The example app displays the activity screen and I press the button to start the test.

The example runs fine up to when it adds rows to the table when the output trace shows that there is some error with the SRID when the AddRow tries to do ToWKB.

So far this is the closest that I have come to having Spatialite available for C#, Xamarin, and Android.

As a practical matter, I only need to be able to use "SELECT" to access a database I created on Windows Spatialite C# programs.

Would it be possible for you to look into this?

Any help would be greatly appreciated.

Regards,
Jim

[Jim]

Sebastian,

Hopefully you will see this  because I am at a loss to explain what is going on.

I am working with SpatialiteAndroidExample and:

1. I can build the test database either in memory or on the sdcard.

2. If I "pull" the test database back to the PC, Spatialite_Gui and a program of mine (C# using Syatem.Data.Sqlite) have no trouble with it and all columns including the spatial ones are fine as is the Spatial Index.

Now the problem:

When either of  the following queries are executed  in the example against the test database:

            IPoint refPoint = dbCon.GeometryFactory.CreatePoint(new Coordinate(25, 35));
            foreach (DataRow row in dbCon.Query("SELECT text FROM test_table WHERE Intersects(geometry, ?)", refPoint)) {
                   WriteLine(row.GetString("text"));                
                   WriteLine ("Got a row");
            }


or

           foreach (DataRow row in dbCon.Query("SELECT id, text, ST_AsText(geometry) AS geom FROM test_table;")) {
                WriteLine(row.GetString("text"));
                WriteLine ("Got a row");
            }


I get an error that "text" is not a column name in the table (My debugging indicates that the error is at the line "WriteLine(row.GetString("text");")

If I go further and replace "WriteLine(row.GetString("text"));"  with

             WriteLine(row.GetColumnName(0) + "  ***  " + row.GetColumnName(1) + "  ***  " + row.GetColumnName(2)); );


I get no errors and see the following on the output:

      <kanji-like symbols> *** <kanji-like symbols> *** <kanji-like> symbols
      Got a row

This repeats for the number of rows inserted in the table, so the rows are in the table.

In another situation, if I force a Sqlite error (like a mal-formed statement), the Sqlite error message is also the "kanji-like" characters.

I am at a loss.

I did some looking around in the code but can't see anything obvious.

My un-informed guess is that it is in the processing between the processing by Sqlite of the query and when the Datarow is presented to the example.

If it was in the database itself, then the database wouldn't be correct when I "pulled" it back to the PC.

And I have had no problems moving Sqlite databases back of forth between Android (device or emulator) and a Windows PC.

As always, any help will be greatly appreciated.

Regards,
Jim
       

On Tuesday, May 21, 2013 5:46:18 AM UTC-6, Sebastian Krysmanski wrote: