Difference between revisions of "Supporting Spatial Databases"
Wiki-JoWalsh (talk | contribs) m |
|||
| (6 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
This page is an attempt to survey spatial SQL storage and query support in open source 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? | What functions are people really using? | ||
| + | === Vector storage and query === | ||
| − | === | + | === Raster storage and query === |
| − | |||
| − | + | === 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 [http://refractions.net/ Refractions Research] | ||
| + | |||
| + | Underlying [http://geos.refractions.net GEOS] C library does the heavy lifting. | ||
==== MySQL ==== | ==== MySQL ==== | ||
| Line 14: | Line 23: | ||
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. | 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 | + | 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 ==== | ==== Derby ==== | ||
| Line 21: | Line 30: | ||
==== Others ==== | ==== 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. | 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 [http://docs.codehaus.org/display/GEOS/SpatialDBBox 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 == | == References == | ||
Latest revision as of 18:42, 1 May 2012
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?
Vector storage and query
Raster storage and query
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