SpatiaLite: Extends SQLite core to support Spatial SQL capabilities | Hacker News Reader

SpatiaLite: Extends SQLite core to support Spatial SQL capabilities

gaia-gis.it

164 pointsthunderbong29 comments

It’s not actively developed. The nearest neighbor functionality in the released version is deprecated and both it and the development replacement have a recently filed bug that makes you worry about accuracy. The docs are also out of date

This. I strongly recommend not using it at this point in time.

SQlite really lends itself to having a spatial backend and would likely become a bit player in this space if it did, but it probably needs a major overall first.

Strongly recommending not using it in a blanket way is not helpful.

I have used it extensively in an embedded product for many years, and it has just worked.

Sure it doesn't have all the postgis goodies, but it has enough that it's not usually hard to find a good way to do the spatial queries you need.

I'm quite surprised by this thread so far, given how robust it's been for me for many years using it in many different ways.

To be specific, they changed the KNN backend, but the docs refer to the new unreleased backend. The old one is deprecated but unreleased, which is a strange confusing situation.

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=KNN2

For the average HN reader spatialite is a tool which should be considered. If the KNN issues are a breaking point for someone, they are probably already in postgis territory.

It is immensely useful as is with no active development, although I'll have to take your word for that, I wasn't aware it isn't active - that has never affected me after years of heavy use

I prefer the GeoPackage format [1]. Very similar to SpatiaLite it is based on SQLite, but as official OGC standard I anticipate better integration and support in the long run.

[1]: https://www.geopackage.org/

> GeoPackage is supported in SpatiaLite as of version 4.2.0.

https://www.geopackage.org/implementations.html

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.2.0...

Is there anything like this for time-series sensor data (temperature, humidity, etc)?

Geopandas adds gis operations to pandas, which is good with time series, if that helps. Can read and write several common formats.

There is the OGC SensorThings API spec for accessing sensor data. For storage I would assume something like GeoPackage or SpatiaLite would be sufficient.

Does this do spatial queries? It doesn’t seem so.

Yes, it does.

See the extensive list of spatial query operators here: http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html

No, it doesn't. It is designed as lightweight format for data storage and exchange. Usually it is used in the context of other GIS applications like QGIS. However, you can also use SpatiaLite to apply spatial functions to GeoPackage files.

I feel you should retract this comment so as to not mislead people. See extensive spatialite documentation on spatial operators here: http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html

But GeoPackage, which the question referred to, does not support spatial functions in and of itself. But I also wrote that you can use e.g. SpatiaLite to apply spatial functions to geometries stored in GeoPackage. But then this is not really different from using any other application like QGIS or gdal.

Apparently it's also trivial to use it in Python via `pip install spatialite`[1], but the GitHub repo [2] seems to be dead.

[1] https://pypi.org/project/spatialite/

[2] https://github.com/malexer/spatialite

No you need the library installed separately

SpatiaLite is a great extension for spatial functions, it is very useful for implementing location-based services. And I like the idea of extending SQLite just for the simplicity of file base DB.

A slightly related problem I encountered several times is reverse geocoding: given a point, a map, which polygon the point is in? I figured out that the problem is solvable via rasterization and run-length-encoding, which doesn't need a spatial library, and works efficiently with any SQL DBs without GIS/spatial extensions. I created a website (https://reverse-geocoding.com/) to demonstrate the idea, which has a GPS-to-country DB in SQLite format.

If you understand the underlying mechanism and are willing to work more low level, then this rtree package is about x50 faster in my experience:

https://github.com/tidwall/rtree.c

There are python bindings for it (with absolutely no documentation) here:

https://pypi.org/project/rtreecpy/

Just adding another piece of info. R-Tree is a generic solution to spatial queries. For static datasets that don't need to update, quadtree or kd-tree is simpler to understand and implement.

> Using SQLite + SpatiaLite you can effectively deploy an alternative open source Spatial DBMS roughly equivalent to PostgreSQL + PostGIS

Any info on performance differences?

I have to disagree with the "roughly equivalent" statement. Its missing core functionality relative to PostGIS. For example, the Geography type and associated operators (which is functionally distinct from Geometry) is completely absent.

I feel this is misleading. You can use various spatial operators which are functional on geographic CRSs. For the type of situation you would usually use this for, it handles geographic just fine. But agreed it isn't explicit like in postgis

Spatialite is such an amazing and little talked about tool. I think most people go straight for its bigger cousin POSTGIS which is the correct decision 99% of the time.

However there are situations where you don't want a full blown database server and an embedded db is better.

I have seen one company just store the geometries as well known text in sqlite, then act all confused when their application runs incredibly slow.

There is also a JS port

https://github.com/jvail/spl.js

> SpatiaLite is licensed under the MPL tri-license terms

One of the greatest things about SQLite is its license (or should I say, lack thereof). It is a pity that so many extensions to SQLite (this included) elect not to follow that example

I've tried it, and it works well, but setting it up is not trivial...

Paraphrased from the docs [1]:

> SpatiaLite is smoothly integrated into SQLite to provide a complete and powerful Spatial DBMS (mostly OGC-SFS compliant). Using SQLite + SpatiaLite you can effectively deploy an alternative open source Spatial DBMS roughly equivalent to PostgreSQL + PostGIS. The difference between them is mainly architecture: PostgreSQL + PostGIS uses a client/server architecture suitable for sophisticated Spatial Data infrastructures at the cost of complexity; SQLite + SpatiaLite has much simpler architecture more appropriate for desktop, stand-alone, and personal activities.

> SpatiaLite supports direct SQL access to several commonly used external datasources: ESRI Shapefiles, DBF Archive Files, TXT/CSV/XLS ...

> SpatiaLite actively supports many alternative standard Geometry notations: WKT [Well Known Text] and WKB [Well Known Binary], PostGIS own EWKT and EWKB [Extended WKT / WKB], GML [Geography Markup Language], KML [Keyhole Markup Language, used by Google Maps and Google Earth], GeoJSON [Geometry Java Script Object Notation], SVG [Scalable Vector Graphics].

[1]: https://www.gaia-gis.it/gaia-sins/splite-doxy-5.0.1/index.ht...

Reply on news.ycombinator.com