Search code examples
sqliteh2postgisspatialite

Functional Completeness Comparison of common, free GIS/Geo-Databases (PostgreSQL/PostGIS, SQLite/SpatiaLite, H2/H2GIS, ...)

For an implementation of GIS features we have to chose between various alternatives. Paid licence fees should be avoided so we narrowed it down to the 3 above. But maybe one should/could consider others as well?

Our main criterias are:

  • no licence fees
  • ideally no limitations on the usage of the whole system (for public, governmental organizations, adjustments/development)
  • (XML) input data in the xx GB range (low redundancy)
  • (XML) output in the xx GB range (low redundancy) (transformed input data)
  • good performance (RAM usage, CPU usage, disk usage) and throughput
  • SFA-SQL (OGC standard)
    • (focus for the question)
    • compatibility desired but not strictly necessary
    • high feature coverage is not needed, but desireable (at least "basic" operations)
    • (other search tags: SFS, SFSQL, Simple Feature Access)
  • DB-GIS and sysadm/dbadm experience of users: one should be able to install and configure the DB system
  • DB-GIS experience of developers: most experience with PostgreSQL/PostGIS, others minor
Solution

  • It was not easy to gather important information for the above points from various sites so I'll try to sum it up as best as possible (and add to it later).
    our findings so far (feel free to adjust/add):

    lists of spatial databases or comparisons

    (1): the pure "function names amount" calculations below should not be taken as a completely qualifiable measure since signature design (overloading, composability, ...) can have a bigger impact on actual flexibility and functional coverage. (=> less functions could theoretically be "better")

    SQLite with SpatiaLite

    • embedded database
    • written in C
    • ~440(1) geo functions (2018-10-09 / 4.3.0)
      • see calc function on bottom
    • SFA-SQL-coverage:

      • SpatiaLite extends SQLite's existing spatial support to cover the OGC's SFS specification (Wikipedia)

      • SpatiaLite supports several open standards from the OGC and has been listed as a reference implementation for the proposed GeoPackage standard (Wikipedia)

    H2 with H2GIS

    • embedded database
    • written in Java
    • ~191(1) geo functions (2018-10-09 / 1.4.0)
    • SFA-SQL-coverage:

      • H2GIS implements the SQL routines describe in "OpenGIS ® Implementation Standard for Geographic information - Simple feature access - Part 2: SQL option" OGS (2018-10-09 / 06-104r4 )

    PostgreSQL with PostGIS

    • common client-server database
    • ~310(1) geo functions (2018-10-09 / 2.5)
      • see calc function on bottom
    • SFA-SQL-coverage:

      • PostGIS is registered as "implements the specified standard" for "Simple Features for SQL" by the OGC.4 PostGIS has not been certified as compliant by the OGC. (Wikipedia)

    our preference/choice

    (This is very subjective and only for informative reasons here!)

    We decided for PostGIS simply because

    • we feel the risk regarding performance and mass data processing is lowest for our needs
    • we may use other common or more advanced relational database functionality in the future
    • we have a lot of experience with it
    • we ship the whole system in a pre-installed VM, so the embeddability of the DBMS is not important for us or the customer at this point

    appendix: helper functions for counting geo functions

    (JavaScript based on/tested with Firefox console)

    JavaScript for counting functions on SpatiaLite functions site

    result:

    "-#19  version  (tr#0 - tr#21)
-#15  generic  (tr#21 - tr#38)
-#08  global  (tr#38 - tr#48)
-#26  math  (tr#48 - tr#76)
+#06  error  (tr#76 - tr#84)
+#20  length_cvt  (tr#84 - tr#106)
+#02  dms_cvt  (tr#106 - tr#110)
-#14  blob  (tr#110 - tr#126)
+#32  p0  (tr#126 - tr#160)
+#11  p1  (tr#160 - tr#173)
+#11  p2  (tr#173 - tr#186)
+#03  p3  (tr#186 - tr#191)
+#13  p3misc  (tr#191 - tr#206)
+#20  p4  (tr#206 - tr#228)
+#01  repair  (tr#228 - tr#231)
+#02  compress  (tr#231 - tr#235)
+#09  cast  (tr#235 - tr#246)
+#04  dims-cast  (tr#246 - tr#252)
+#04  p5  (tr#252 - tr#258)
+#14  p6  (tr#258 - tr#274)
+#07  p7  (tr#274 - tr#283)
+#03  p8  (tr#283 - tr#288)
+#03  p9  (tr#288 - tr#293)
+#02  p10  (tr#293 - tr#297)
+#08  p11  (tr#297 - tr#307)
+#11  p12  (tr#307 - tr#320)
+#03  p13  (tr#320 - tr#325)
+#08  p14  (tr#325 - tr#335)
+#28  p14b  (tr#335 - tr#365)
+#03  p14c  (tr#365 - tr#370)
+#16  p14d  (tr#370 - tr#388)
+#10  p15  (tr#388 - tr#400)
+#23  p15plus  (tr#400 - tr#425)
+#22  p16  (tr#425 - tr#449)
-#02  p16metacatalog  (tr#449 - tr#453)
+#41  p16style  (tr#453 - tr#496)
-#03  p16isometa  (tr#496 - tr#501)
+#07  p16fdo  (tr#501 - tr#510)
+#23  p16gpkg  (tr#510 - tr#535)
+#04  p17  (tr#535 - tr#541)
+#04  p18  (tr#541 - tr#547)
+#33  xmlBlob  (tr#547 - tr#582)
+#12  srid  (tr#582 - tr#596)
+#17  advanced  (tr#596 - tr#615)
gis functions: 440"

    function:

    x=$x("//tr/td/h3/a[@name]")   // header anchors
os=''                         // output string
trs=$x("//tr")                // tr nodes
gf=0                          // gis functions
nongis= ['version','generic','global','math','blob','p16metacatalog','p16isometa']  // non-gis function sections/anchors
for (var i=0; i< x.length; i++ ) {
  isgs = nongis.indexOf( x[i].attributes['name'].value ) == -1  // is gis section
  c= trs.indexOf( x[i].parentNode.parentNode.parentNode )       // current header index
  n= (i == x.length-1) ? trs.length+1 : trs.indexOf( x[i+1].parentNode.parentNode.parentNode )  // next header index
  os+= (!isgs ? '-' : '+' ) + '#' + ((n-c-2 > 9 ? '' : '0') + (n-c-2)) + '  ' + x[i].attributes['name'].value + '  (tr#' + c + ' - tr#' + n + ')\n'
  gf+= isgs ? n-c-2 : 0
}
os += 'gis functions: ' + gf

    JavaScript for counting functions on PostGIS functions site

    $x('//a[@name="PostGIS_TypeFunctionMatrix"]/../../../../..//tbody/tr').length