Go to content Go to navigation Go to search

Spatial Database Comparison

Tuesday January 27 2009 at 02:44

I thought I would try and create a table comparing all the native spatial types of the main database products around today.

Because this is a “work in progress”, I will be contantly updating it. Where I have something wrong or have missed a product, please email me and I will correct the table.

Regina makes a point about PostGIS vs ESRI’s Spatial Type. When I have downloaded and installed PostgreSQL I have done so via the public PostgreSQL download site. PostGIS is shipped with the Windows installer so as far as I am concerned PostGIS (which I have never downloaded from Refractions) is thus the “official” native type for PostgreSQL until such times PostgreSQL itself moves to revoke that status by removing it from the standard installers.

Dale Lutz pointed me at the SpatialLite implementation for SQLLite. Here the SpatialLite implementation is maintained completely separately from SQLLite so, at the moment, I don’t think I can include it in the table.

With regards ESRI. ESRI is not a manufacturer of a generic database which could be used to completely supplant Oracle, SQL Server etc. It makes extension products (ESRI spatial type) for some commercial databases but this product is in direct competition with the owner and manufacturer of these products. Therefore I do not consider it to be a native extension. (I also cannot test or use it without having to pay ESRI for the privilege – not so with the main vendors with their Express editions or the free open source alternatives.) Since MapInfo also creates and sells SpatialWare I may consider creating a separate table for these products once I am satisfied that the table below is accurate, easy to read and complete.

While 1 I have included a column on the storage format of the spatial type I want to make something perfectly clear: it is irrelevant. I was taught in computer science about Codd’s relational theory and have always believed in the quality of the mathematics and science that went into that theory. That most of the modern relational databases do not fully implement Codd’s theory is an accepted fact. But even so we should aspire to trying to implement relational theory as best we can. In this light I want to quote something that Chris Date and Hugh Darwen wrote in their book Foundation for Future Database Systems: The Third Manifesto.

“What we are saying is that, in the relational world, a domain is a data type, system- or user-defined, whose values are manipulable soley by means of the operators defined for the type in question (and whose internal representation can be arbitrarily complex but is hidden from the user).” [Emphasis added by myself]

Here is my attempt at classification/documentation.




































































Database



Extension or Component



Storage


Format 1



1) Storage


Format Compliance



Dimensions



Spatial


Reference System



Geometric


Organisation



Access


Standard



Indexing



Oracle


Locator / Spatial

Oracle


Object consisting of numbers and arrays



SQL/MM components (arrays, numbers and objects)



3D


+ Measure



EPSG



OGC


SFS




SQL/MM (circular arcs)



Proprietary


but open eg


sdo_util.GetVertices;


SDO_INSIDE()






Also




SQL/MM



QuadTree




(no longer recommended)




and




RTree


(preferred)



SQL


Server 2008


Spatial

Proprietary


(Actually


.NET Common Language Runtime – CLR – Data Types)



None



2.5D


+ Measure



EPSG



OGC


SFS 1.1



OGC


1.1 + Extensions eg MakeValid()


BufferWithTolerance()


Reduce()


(Douglas-Peuker)




Not Spatial Schema tables (eg


GEOMETRY_COLUMNS and SPATIAL_REF_SYS



Grid



PostgreSQL


PostGIS

Extended


WKB



OGC


SFS 1.1






EPSG



OGC


SFS


SQL/MM (partially)



SFS


1.1.0


SQL/MM + proprietary extensions eg


ST_AddPoint(linestring, point, [<position>]);


ST_Affine(geometry,


float8, float8, float8, float8, float8, float8)



R-Tree


index implemented on top of. GiST



MySQL



none

Proprietary



None






ISO/EPSG



OGC


SFS



OGC


1.1



RTree



IBM


DB2


Spatial Extender

Smart


Large Object (BLOB)



Unknown



2.5D



ISO/EPSG



OGC


SFS 1.1



OGC


1.1 + extensions eg SE_VertexUpdate() SE_VertexAppend()


SE_VertexDelete()



RTree



Informix


DB2



GeoDetic Blade

RTree
Spatial Blade RTree

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [2]

Great table Simon! Good stuff in there.

I wonder if the latest SQLite spatial extensions (see http://www.jasonbirch.com/nodes/2008/05/06/184/sqlite-for-fdo-with-sugar-free-ogr/ and http://geopdf.blogspot.com/2008/07/spatialite-alternative-spatial-database.html) might be worthy of mention, though they are early days.

— Dale · 27 January 2009, 11:31 · #

Simon —
One nit-pick for the PostgreSQL, I would put PostGIS as part of the label.

As much as I would like to think PostGIS is the only spatial extender for PostgreSQL, the fact that it is not part of the PostgreSQL core, one could argue it is on the same level as ESRI ST_Geometry for PostgreSQL offering and I’m not sure the same comments you made apply to it.
http://www.esri.com/news/podcasts/transcripts/introducingarcsdeimplementationforpostgresql.pdf

We did a similar survey a while back but left out IBM and Oracle.

http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008_postgis_mysql_compare

— Regina · 28 January 2009, 04:29 · #

Article Navigation: