2

It seems like most of the classic spatial function available in PostGIS (i.e. ST_Buffer) are usable with Spatialite, but some others don't seem to be supported in Spatialite (i.e.: no such functions: ST_ApproximateMedialAxis).

Is there a list available showing what is supported within QGIS DB-Manager for example?

|improve this question
  • I haven't used Spatialite much, but I had a look at the list in kartoza-geek's answer, and it would seem that most of the geos derived functions are available. However, I notice, there is no real support for 3D (this comes from sfcgal), topology or raster functions. Nor is there any clustering support. Postgis also has all kinds of useful additions like pgPointCloud and pgRouting. Further, Postgres is a far more fully featured RDBMS than Spatialite. So, the real question is, what functionality do you need? Spatialite is awesome and has it's uses, don't get me wrong. – John Powell Jan 10 '19 at 10:11
  • Yes I agree, I do think PostGIS is 'better', I was just after a shortcut via Spatialite because *.sqlite file are a lot easier to set up and share. I was also looking at using geopackage files, which are based on *.sqlite – Leo Jan 10 '19 at 21:19
4

You need to look at the documentation to see the list of functions that spatialite supports natively. https://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html

|improve this answer
4

In general, all the functions that come from GEOS are exposed in Spatialite. However, ST_ApproximateMedialAxis is an example of a function that comes from sfcgal, written by OSLandia, which is a wrapper round the underlying CGAL library. This is how most of the 3D functions have been exposed to Postgis. You have to compile sfcgal separately, or, use 

CREATE EXTENSION postgis_sfcgal;

So, unless there is project underway to provide sfcgal/cgal to Spatialite, you will most likely be out of luck.

Other things that will be lacking from Spatialite are other projects that have been added to Postgis over the years by 3rd parties -- raster, topology, pgRouting, pgPointCloud, to name a few.

Furthermore, Spatialite lacks much of the higher level RDBMS functionality that comes with Postgres. This is not a criticism, Spatialite simply has a different use case.

So, you are unlikely to find a one-to-one mapping between the spatial functionalities of the two DBs, except that anything that comes from GEOS will be likely be included.

The question really is what is your use case, what type of queries, how many users, how big a DB, etc.

|improve this answer
  • Thanks for your input, that's excellent. These limitations affect Geopackage files too then, right? – Leo Jan 10 '19 at 21:27
  • Sorry i have no idea about geopackages, but if based on spatialite then I assume so. I didn't say Postgis was better, just a different use case :D But, presumably, geopackages can be imported to anything? It's a data transfer format, not a suite of functions? – John Powell Jan 10 '19 at 21:50
  • I think so. It seems to have been evolving quite a lot recently so its capabilities are changing... wait and see – Leo Jan 10 '19 at 23:57
  • 2
    @Leo GeoPackage describes a specification on top of SQLite containers to hold compound vector and raster data in standardized form in a single file. it supports the spatial SQL/MM standard (i.e. simple geometry types), but provides no spatial data manipulation (i.e. 'ST' functions) means by itself; GeoPackage != SpatiaLite – ThingumaBob Jan 11 '19 at 23:17
  • ... and a neat little overview this is @JohnPowell ,) – ThingumaBob Jan 11 '19 at 23:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.