What is Reddit's opinion of SpatiaLite?

From 3.5 billion Reddit comments

I think its great as a simple GIS database. It also works great as a backend database for smaller web GIS applications. Its also handy in that it can be put in an application and loaded to a mobile device for offline use.

For a file storage format I prefer geopackage because spatialite has about 5MB of overhead in every file that I assume is all the SQL code for the spatial functions which geopackage does not have. Thats a good bit of extra backage if you just want to email some data to someone.

I agree that the documentation could be better but it implements the same OGC SFS standard as PostGIS and so most of the functions are interchangeable. If you're not sure you can check http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html

My main concern is that I'm not sure how much effort is going into maintaining it anymore. Does anyone know if it is really an open source project or if its being maintained just by Alessandro Furieri? I'd love to see it get some foundation support. Its too good to let slip away IMHO.

try the gui tool for sqlite3/spatialite: http://www.gaia-gis.it/gaia-sins/windows-bin-amd64-test/

then you can import some test data to make your life easier.

Then check out what to do in python. There's a bunch of short demos around the web. But the gui up there should make life a little more easy to learn the SQL part.

That's just use Standard SQL select query with ogr2ogr and in the WHERE clause you put what field and then operators = or contains or having or like. Dialect SQLITE gives you even more options you can use all the Spatialite functions and do things like CAST or buffer or makepoint or intersect http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html https://gdal.org/user/sql_sqlite_dialect.html

You can avoid parsing XMLs and KMLs on the device. There's a Spatialite DB port for Android: https://github.com/sevar83/android-spatialite

There's an example with pre-package geometry data (done with ContentProvider but not necessary. You can work directly with the SQLiteDatabase API): https://github.com/sevar83/SpatiAtlas

What you can do in your case is to pre-build a database file by using the KML-import functions on your dev PC. Or using the nice Spatilaite GUI software. Then embed the DB file as an asset or raw resource. Then depending on your usage you can perform fast R-Tree spatial queries - for example - "select all geometries in bounding box". There are many many functions: http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.4.0.html Once you obtain the geometry objects from the DB you can use the JTS library which is the most reliable tool for geometry. I don't know if it's not a an overkill in your case but that's a stable ground.

TBH the data size quoted (1000s of polygons) doesn't sound all that hard for PostGIS to handle. At work we do on-the-fly pruning of the OpenStreetMap data to render custom maps. That's polygon/polygon intersections against ~100G of data, if it can manage that in 50-300ms it can manage lookup of their geofences.

Hell, there's even this, which would let you keep simple deployment/operations/scaling and still probably get similar performance "for free".

The alternative would be SpatialLite:

http://www.gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html Search for join. You want to build spatial indexes when the data is loaded. Which will take some time for MasterMap. A guide for working with spatialite can be found: https://www.gaia-gis.it/fossil/libspatialite/wiki?name=benchmark-4.0 it's an example for clipping but should provide a starting point.

So the database itself doesn't require any installation and you can get a viewer here: http://www.gaia-gis.it/gaia-sins/