Supporting Spatial Databases

This page is an attempt to survey spatial SQL storage and query support in open source databases. update 01may12 see http://en.wikipedia.org/wiki/Spatial_database

What functions are people really using?

State of spatial database support

PostGIS

PostGIS is the "gold standard" of spatial database support.

Development is sponsored and maintained by the good people at Refractions Research

Underlying GEOS C library does the heavy lifting.

MySQL

MySQL actually has decent spatial support, but without the last 20% it's relegated to close to useless. Very few people seriously use MySQL as a spatial backend. The problem is it does everything against the envelopes. So it can't be counted on to be accurate. It's sorta like an index against characters that when you ask for F* occasionally gives you one that starts with a G. It's not a huge deal, but if there's an alternative that gets it right, then you go with it.

The other big limitation is that the spatial support is only against the MyISAM tables, which don't offer any kind of ACID compliance, and I think may be even flakier with spatial stuff. Like they just can't be trusted with transactions, and thus for many who actually manage spatial data are close to worthless. It'd be cool to see a compiled spatial db in a box against mysql, and certainly possible. Or they could go and use GEOS (the disadvantage in my biased mind being that it forked from JTS awhile ago, and relies on updating by hand, whereas compiling straight from JTS allows you to pick up all their fixes. But GEOS seems to have some critical mass behind it, though no one seems incredibly happy with it, and it seems to be in a limbo between just totally forking and optimizing or trying to stay up with hand ports of JTS)

Derby

This is the spatial DB I'd love to see. Super small, acid compliant, pure java. They need a GiST, and a couple of other improvements David outlines. He's already done probably 95% of the work.

Others

It'd be great to see SQL Lite support spatial stuff, could make use of GEOS or a compiled spatial db in a box. There's also HSQL and a few other small open source DBs. It'd be really great to have a very light weight embeddable spatial database. I know we'd love it for GeoServer, and indeed it could perhaps be a replacement for ESRI's personal Geo database, but with none of their limits. But I'd say it needs to fully support Simple features for SQL, and perhaps an additional small subset of operations, to be useful.

overview braindump

First, I think it's worthwhile to support the whole simple features for sql specification. I'm a bit biased, since GeoServer makes extensive use of the full set of operations. But it's really nice to have the power and to be sure it's accurate. The other reason I'm for the full power is that David Blasby came up with a really easy way to implement it, see Spatial DB in a Box. It uses [JTS http://vividsolutions.com/jts/main.htm] to do all the 'hard' spatial operations, the crazy touches and intersects, in a very robust and accurate way. So if it's plugged in to spatial types, you basically get most of the simple features for sql specification for free. Note that it can compile to see using GCJ, David did experiments with PostGresql, and found that it would actually perform faster than postgis, and was much more maintainable than the hand coding of GEOS. The other thing needed for any spatial db is spatial indexing. This is required, or else queries on any table that's not a joke take way too long. Generally GIS guys aren't so interested in implementing the indexing, what's first needed is a GiST, which someone can slap an R-tree implementation to, and use that as the spatial index (I may have messed up some of the terminology, I'm mostly just replicating what Dave's braindumped to me - really, just read his spatial db in a box stuff, lots of valuable insights, on how the original architect of PostGIS would do it better the second time around)

References

• OGC SQL Functions (at refractions)
• PostGIS Extensions
• OGC Simple Features for SQL (pdf)
• Spatial DB in a Box

Notes

http://logs.qgis.org/osgeo/%23osgeo.2006-07-31.log