Android and iOS binaries

[bricelam]

I'm not sure if there are SpatiaLite ports available for these, but it would be awesome to make Xamarin work if there are.

[SonicScholar]

I was able to compile SpatiaLite C-style Library in iOS using this tutorial here. https://gist.github.com/aaronpk/0252426d5161bc9650d8

I don't know if/how you could load the extension in iOS. You can embed iOS dynamic frameworks. I'm not sure how System.Data.SQLite loads extensions, but I'm assuming it's a dynamic load of a binary at runtime - I could be wrong but I think that kind of thing is harder to do "correctly" in a way that would make it through the Apple App Store review process. (Since they have stricter rules about dynamically loading and executing binary code, etc.)

It would be sweet to use SpatiaLite functionality purely through the System.Data.SQLite library calls!

[groege]

The biggest problem seems to be the licensing right? LGPL/GPL would be a big problem for the app stores.
I believe MPL 1.1. would allow ios/android apps in the store right?

[bricelam]

I don't know anything about native iOS development, but when you execute SELECT load_extension('mod_spatialite'), SQLite calls the VFS...

https://github.com/sqlite/sqlite/blob/master/src/loadext.c#L528

...which I believe just calls dlopen:

https://github.com/sqlite/sqlite/blob/master/src/os_unix.c#L6369-L6372

So is it just a matter of deploying libmod_spatialite.dylib (lol, is it even dylib on iOS??) to the right directory? I believe NuGet and Xamarin will do this for us if we put it under lib\xamarinios in the package.

[bricelam]

Hmm... maybe not. Looks like SQLitePCL.raw uses a shim assembly with sqlite3.a embedded. (looking at the SQLitePCLRaw.lib.e_sqlite3.ios package)

[groege]

I believe we only just need to compile mod_spatialite.so
I've been trying to do this for 2 days now... no success

[groege]

Finally I've managed to build the dll!
It does load AND the migration works as well.
But as soon as I try to insert a geomtry into the database i get an sqlite exception.
Can you inform me about what ./configure parameters you used for spatialite?
What would also help would be the build sqlite library so i can build against it.
Once this works I will give you the project so we can add android support for spatialite

[bricelam]

I just re-distribute the msys 2 binaries. I can dig into what they use...

[groege]

That would be awesome!

[groege]

Though I'd also like to ask about licensing.. It seems to me that depending on how the spatialite configure parameters are used, we might have to use a LGPL or GPL license which isn't very good for the Android app store right?
If it is LGPL I think it is possible to just host an APK file that can be unpacked so people can replace the so file...
Since the App store doesn't allow for direkt APK download it seems to be necessary right?

[groege]

Oh I forgot, I'm working on the Android build - will try to build for ios later

[groege]

libs.zip
Working mod_spatialite.so.so files.
FYI its weird that its asking for mod_spatialite.so.so instead of mod_spatialite.so
Let me know if these files work for all cases we need for EF CORE.
If it does, should I create a github project with the build files? Or how should be proceeed?

[bricelam]

I'd also like to ask about licensing

Licensing is a mess. I've collected the ones involved here: https://github.com/bricelam/mod_spatialite-NuGet/blob/master/src/mod_spatialite/NOTICE.md

Statically linking everything into one library may be problematic...

Should I create a github project with the build files? Or how should be proceeed?

You can send a PR here. Maybe we can automate everything using GitHub Actions or something.

[bricelam]

I guess it's finally time for me to buy a Mac mini...

[bricelam]

Here is the MSYS2 package script: https://github.com/msys2/MINGW-packages/blob/master/mingw-w64-libspatialite/PKGBUILD

[groege]

First of all I've used https://github.com/geopaparazzi/libjsqlite-spatialite-android and removed all unnecessary projects, so I think we should mention geopaparazzi wherever we put this build (he basically unwittingly did the largest part of the work).

spatialite_efcore2.6.zip

I created a build-able zip containing only the projects that are included in my build.
Note that I've built the project only with "geos", "libiconv", "spatialite", "proj" and "sqlite" (the sqlite files I've extracted from my Xamarin project, only copied the header files from the original project)

So I've removed
-freexl (MIT): "FreeXL is an open source library to extract valid data from within an Excel (.xls) spreadsheet."
I don't think we need it but I can re-add it
-libxml2(MIT): "Libxml2 is the XML C parser and toolkit developed"
I don't think we need it but I can re-add it
Anyway let me know if you want me to re-include them

So if we look at the licenses:
geos (LGPL)
libiconf (LGPL)
spatialite (GPL, LGPL, MPL)
proj (MIT)

The lowest common denominator is LGPL, meaning that the mod_sqlite.so.so files are basically LGPL. I hope I understand this correctly.

[groege]

I found something else: android/ndk#702
Seems like beginning with api version 28 (android 9) iconv will be part of android ndk.

Do you know of a way to deploy different so files depending on the api level?

[groege]

spatialite_efcore2.6_api28.zip

Spatialite using the built in iconv lib (API Level >= 28)
For now it's not really worth it as long as GEOS is LGPL.

[bricelam]

I'm sure there's a way. I see a RID for android.21. I'll do some more digging

[bricelam]

I like the idea of stripping it down to only what EF Core needs. I'm going to take a stab at this on Windows/MSYS2.

I'm also going to start setting up GitHub Actions to automate building for different platforms and bundling everything up into a NuGet package.

[groege]

The GEOS library is still a headache since it's LGPL, do you know if there is an alternative that we can replace it with? (Will be more work but it would remove the issues especially with the Apple App Store)

If no such library exists I think we should see if anyone would implement one. (I would do it but I don't have the resources right now - at least as long as I want to have some private life XD)

[bricelam]

I doubt we can replace GEOS. It’s the heart of the spatial logic used by both SpatiaLite and PostGIS. It’s a C++ port of JTS (like NTS is a C# port) What’s the issue with the Apple App Store and the LGPL?

[SonicScholar]

We've run up against this problem as well. It has to do with something about static linking if I remember right. https://trac.ffmpeg.org/ticket/1229 https://lists.osgeo.org/pipermail/geos-devel/2012-August/005980.html

…

________________ Collin Tewalt ctewalt@gmail.com

On Sun, Sep 22, 2019 at 11:29 AM Brice Lambson ***@***.***> wrote: I doubt we can replace GEOS. It’s the heart of the spatial logic used by both SpatiaLite and PostGIS. It’s a C++ port of JTS (like NTS is a C# port) What’s the issue with the Apple App Store and the LGPL? — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#6?email_source=notifications&email_token=AAH5GPW6VWN2PWU745LZCJTQK6TQRA5CNFSM4F6O5W4KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD7JK5WY#issuecomment-533901019>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAH5GPVZKKH4NOW4CQNSRETQK6TQRANCNFSM4F6O5W4A> .

[groege]

Maybe (far in the future) a managed sqlite implementation that uses NetTopologySuite might be a better solution?
If i see this correctly GEOS does pretty much the same as NetTopologySuite right?
Or is it possible to somehow replace GEOS with NetTopologySuite by incejcting the managed NetTopologySuite into Spatialite?

[bricelam]

No easy path forward here. I’ll see what (if anything) works without GEOS.

[bricelam]

technically, the NuGet package wouldn’t be violating anything, just everyone using the NuGet package

[groege]

Since GEOS and NetTopologySuite are basically the same thing, it would be nice to replace GEOS with NetTopologySuite.
So I did some research if it is possible to call managed from unmanaged code and this actually exists for the .Net Framework but not for NetStandard:
https://github.com/3F/DllExport

Here is an explanation: https://stackoverflow.com/a/57534869
So if it was NetStandard compatible, we'd just have to create the exportable functions right?

I thisnk this would actually even be better than GEOS since using both GEOS and NetTopologySuite is basically code duplication (more or less XD)

We do need NetStandard support first though....

[groege]

https://docs.microsoft.com/en-us/xamarin/tools/dotnet-embedding/

trying this out now ;)
Though it is failing atm

[groege]

Well seems like there is no point form me to continue since
NetTopologySuite.CoordinateSystems is LGPL as well....
So we'll have to wait until Xamarin is .NETStandard 2.0 compatible to use the NetTopologySuite 2.0.0 (which does not have any dependencies and therefore the BSD licence is what counts)

[SonicScholar]

Seems like we need an initiative to create an MIT licensed library to do a full rewrite of Geos or NTS. I don’t know how realistic this is for someone like Microsoft to do, but I know this would really open the door for a lot of folks who are trying to make apps with a reliable fully featured cross platform spatial database. My guess is if the initiative was there, a bunch of developers would come out of the woodwork to crank it out. --

…

________________ Collin Tewalt ctewalt@gmail.com

[groege]

Well i think the quickest way to get to a proper license would be to use NTS 2.0 (which as BSD and no other dependencies) - it seems like there is already an Mono Android version supported by .NETStandard 2.0.
I was able to use the Embeddinator-4000 to generate a so library with which we can access the managed NetTopologySuite.
So it is possible to replace the GEOS C++ code with the C# NetTopologySuite, by using a Shim generated with Embeddinator-4000

I did start to try and replace the code but I think we'd need someone who knows spatialite better than myself.

Just to let you see how the NetTopologySuite.h looks like using the Embeddinator:
NetTopologySuite.zip

Though obviously a proper managed implementation of the whole thing would be much, much better.

Also the shared library (libNetTopologysuite.so) is 1.87MB large...

[groege]

So I've come to think that it may be less work if we created something in place of spatialite, since ef core only uses a fraction of the functions anyway AND we basically when doing statements we convert nettopologysuite objects into strings/binaries to create sqlite queries that are parsed and used by the geos lib and then we get strings/binaries back that we convert to nettopologysuite again

If we create a plugin for this:
https://www.openhub.net/p/csharp-sqlite

We can implement the geo functions more easily AND it will run on any platform since its fully managed c# code -> also we don't even have to serialize the nettopologysuite objects and can use them within the query directly (if we implement it that way)

The disadvantage right now is that the project is currently not maintained, but I think if we'd include it within EF Core the community is sure to maintain it.

[groege]

Since https://www.openhub.net/p/csharp-sqlite is no longer maintained, I forked the code from someone on github, removed all unnecessary projects, migrated to netstandard2.1 and did some minor code fixes and it builds properly.
Now it should be a lot simpler to add spatial functionality into SQLite if we replace the so libraries with this.
What do you think, might this be a long term solution? (IMHO it would be nice if this project would be part of the microsoft ef core repos)

Also NetTopologySuite has a rtree implementation that could be used for the index as well ;)

[bricelam]

Thinking about this some more, you can do a lot without SpatiaLite. You could use an EF Core value converter to store geometries as WKT/B and implement the spatial operations as UDFs backed by NTS. There would be a lot of conversation to/from WKT and you couldn’t leverage spatial indexes, but at least it would be functional...

[groege]

Thank you for the information!
How about a geometry index - that's the one feature I and probably most others cannot live without?
Can this be implemented in a similar way?

[bricelam]

SpatiaLite's spatial indexes are actually pretty hard to use right. They require JOINs to the R*Tree index virtual table or using the RTreeIntersects or RTreeDistWithin functions in your queries. None of it is well supported by EF Core.

BUT, as far as I can tell they don't require GEOS...

[bricelam]

My current plan of iOS:

• Look again at dynamic loading on iOS. There seems to be a lot of misinformation surrounding this, and some people claim it's possible
• If it's not possible, build a version of SpatiaLite without GEOS and use NTS to implement the missing OGC operations that result from it.

My bigger plan:

• Use GitHub Actions to compile binaries for Windows (via MSYS2), Linux (.NET Core distros), macOS, Android, and iOS.
• Use minimal and consistent (as much as possible) options: disable proj, freexl, and libxml2

[groege]

Thank's again for the information.

• If it's not possible, build a version of SpatiaLite without GEOS and use NTS to implement the missing OGC operations that result from it.

If this is the way to go with IOS, I think we should use that same c code for Android as well - since it won't be much additional work AND solve the LGPL issues.

[groege]

@bricelam
Just as additional Info, this provides a good overview which function require specific libraries included in spatialite
Let me know if I can do anything that helps us to get rid of GEOS (since LGPL is not acceptable for IOS and Android even if it technically works)
http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html

[adammulroy]

Are there any work arounds for getting NTS to work on android? I'm not sure how to build mod_spatialite for android myself. Can you guys point me in the right direction ?

[bricelam]

Sorry, the last time I compiled for Android was over ten years ago. 😁

[groege]

spatialite_efcore2.6_api28.zip

Spatialite using the built in iconv lib (API Level >= 28)
For now it's not really worth it as long as GEOS is LGPL.

See the link - you can use this for compiling

[dalgarins]

@groege, I have downloaded your zips, because I'm trying to generate mod_spatialite, but in every compilation the result is the same, the file mod_spatialite is missing.

I would like to know, what I need to do to generate that file?

[groege]

@groege, I have downloaded your zips, because I'm trying to generate mod_spatialite, but in every compilation the result is the same, the file mod_spatialite is missing.

I would like to know, what I need to do to generate that file?

you need to rename the libspatialite.so to mod_libspatialite.so then it should work
I thought I did change the output file name, seems like I didn't I has been a while since I worked on it.

[groege]

My current plan of iOS:

• Look again at dynamic loading on iOS. There seems to be a lot of misinformation surrounding this, and some people claim it's possible
• If it's not possible, build a version of SpatiaLite without GEOS and use NTS to implement the missing OGC operations that result from it.

My bigger plan:

• Use GitHub Actions to compile binaries for Windows (via MSYS2), Linux (.NET Core distros), macOS, Android, and iOS.
• Use minimal and consistent (as much as possible) options: disable proj, freexl, and libxml2

@bricelam Any update on this? Since EF6 seems like very much work (I'll be surprised with a November release without any cuts to planned features), can I guess that before EF7 there will probably not much happen with this issue?

[bricelam]

No updates. This work is currently very low priority for the EF team. I may look into it again as part of dotnet/efcore#22138 whenever that becomes a priority.

I'm worried that even after I finally figure out how to compile the binaries and add them to the NuGet package, I'll run into the same NuGet/runtime limitations that I hit on Linux and macOS...

[bricelam]

https://github.com/gstf/libspatialite-ios

[aritchie]

I have a working set of binaries for spatialite on iOS, Android, & macOS. It did require some additions beyond the traditional SqliteConnection.LoadExtension("mod_spatialite")

It feels hacky... there may be some licensing concerns... and the binaries are not built via a script. I did it with some manual compiles and copying over the binaries from brew.

I've got it in a closed source repo at the moment. Would you like to see if this is something we can add to this library to centralize everything?

[bricelam]

@aritchie If it can be automated in a shell script or GitHub Actions, I think there's value in putting those here. Even if we don't distribute the binaries, it will give others a good starting place for their own experiments.

[asim27]

@aritchie can u please share the link to working set of binaries for spatialite on iOS, Android, & macOS.
I am looking for spatialite version >= 5.0

[aritchie]

@asim27 I'm unable to share at the moment due to a project constraint. The project also isn't using 5.0. I stuck to v4 due to some noted issues with 5