Supporting Spatial Databases - Revision history

Darkblueb at 02:42, 2 May 2012

2012-05-02T02:42:40Z

Wiki-JoWalsh at 14:32, 6 September 2006

2006-09-06T14:32:35Z

Wiki-JoWalsh at 14:32, 6 September 2006

2006-09-06T14:32:17Z

Wiki-JoWalsh at 19:27, 25 August 2006

2006-08-25T19:27:14Z

Wiki-Dfetter: /* PostGIS */

2006-08-19T17:21:39Z

PostGIS

Wiki-Dfetter: /* MySQL */

2006-08-19T17:19:13Z

MySQL

Wiki-JoWalsh at 21:00, 1 August 2006

2006-08-01T21:00:45Z

Wiki-JoWalsh at 18:51, 31 July 2006

2006-07-31T18:51:02Z

Wiki-Cholmes at 18:20, 31 July 2006

2006-07-31T18:20:35Z

Wiki-JoWalsh: /* References */

2006-07-31T17:49:48Z

References

← Older revision		Revision as of 02:42, 2 May 2012
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?

← Older revision		Revision as of 14:32, 6 September 2006
Line 7:		Line 7:
	=== Raster storage and query ===		=== Raster storage and query ===

−	~~[[Raster Storage Support]]~~

	=== State of spatial database support ===		=== State of spatial database support ===

@@ Line 3: / Line 3: @@
 What functions are people really using?
-=== Vector storage and query ==
+=== Vector storage and query ===
-=== Raster storage and query ==
+=== Raster storage and query ===
 [[Raster Storage Support]]

@@ Line 3: / Line 3: @@
 What functions are people really using?
-=== cholmes' braindump ===
+=== Vector storage and query ==
-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).
+=== Raster storage and query ==
 === State of spatial database support ===
@@ Line 28: / Line 30: @@
 ==== 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.
 == References ==

@@ Line 15: / Line 15: @@
 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.
+Underlying [http://geos.refractions.net GEOS] C library does the heavy lifting.
 ==== MySQL ====

@@ Line 21: / Line 21: @@
 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 full ACID compliance (afaik), 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)
+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 ====

← Older revision		Revision as of 18:51, 31 July 2006
Line 28:		Line 28:
	* [http://portal.opengeospatial.org/files/index.php?artifact_id=829&version=2 OGC Simple Features for SQL] (pdf)		* [http://portal.opengeospatial.org/files/index.php?artifact_id=829&version=2 OGC Simple Features for SQL] (pdf)
	* [http://docs.codehaus.org/display/GEOS/SpatialDBBox Spatial DB in a Box]		* [http://docs.codehaus.org/display/GEOS/SpatialDBBox Spatial DB in a Box]
		+
		+	== Notes ==
		+
		+	http://logs.qgis.org/osgeo/%23osgeo.2006-07-31.log

← Older revision		Revision as of 18:20, 31 July 2006
Line 4:		Line 4:


		+	=== cholmes braindump on spatial databases ===
		+
		+	Sorry for the unclean format, hopefully someone else can come along and extract stuff out.
		+
		+	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).
		+
		+	==== 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 full ACID compliance (afaik), 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 [http://docs.codehaus.org/display/GEOS/SpatialDerby 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.

	== References ==		== References ==

← Older revision		Revision as of 17:49, 31 July 2006
Line 10:		Line 10:
	* [http://postgis.refractions.net/docs/ch06.html#id2527996 PostGIS Extensions]		* [http://postgis.refractions.net/docs/ch06.html#id2527996 PostGIS Extensions]
	* [http://portal.opengeospatial.org/files/index.php?artifact_id=829&version=2 OGC Simple Features for SQL] (pdf)		* [http://portal.opengeospatial.org/files/index.php?artifact_id=829&version=2 OGC Simple Features for SQL] (pdf)
		+	* [http://docs.codehaus.org/display/GEOS/SpatialDBBox Spatial DB in a Box]