Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6
Printer Friendly
This article is a compliment to our Postgres OnLine Magazine May/June 2008 issue Cross Compare of SQL Server, MySQL, and PostgreSQL
and our inaugural issue December 2007 PostGIS for geospatial analysis and mapping.
In this comparison we shall focus on the suitability of these for spatial analysis, mapping and general GIS processing. We are only
comparing these three databases because these are the ones we use most often and care about. Most of this information is gleened from the help docs and our general
understanding of availability of tools.
We shall note that in terms of affordability vs. functionality SQL Server 2008 and PostGIS are the front-runners. While the spatial functionality and speed of MySQL significantly lags that of
SQL Server 2008 spatial and PostGIS, we felt it important to include because MySQL has such a large install base. If MySQL spatial capabilities does just enough of what you need and at adequate speed, by all means there is no need to swap it out.
The IBM DB2 and Oracle Spatial
offerings are in general more costly and while the free versions of those do offer a great bit of functionality, they are limited to number of processors
that can be used, functionality and size of database.
Oracle 11G only provides Locator (and not Oracle Spatial) for their express and Non-Enterprise, for Enterprise - its an additional priced offering on top of the regular enterprise price to upgrade your Locator to Spatial. While Oracle 11G is a bit more generous than its 10G offering, locator spatial functions are significantly less than Oracle Spatial and are missing critical functions such as ST_Intersection and ST_Centroid.
Although Oracle locator does provide geodetic support, it has vastly less functionality than their Oracle Spatial offering. See Simon's Oracle Locator vs. Enterprise Spatial licensing limitations and Oracle 11G locator white paper.
IBM charges for geodetic as a separate priced blade on-top of their regular offerings. Their IBM DB2 Express-C does have a freely downloadable DB2 Spatial Extender which by accounts appears to be rich and they do have
the unique generosity of allowing up to 2 processors, unlimited size db, but limited to 4G on board memory. See DB2 Express-C and DB2 Spatial Extender White paper for more details.
If you have dreams of database farms and clouds, deployment of custom apps for many clients, the licensing costs of Oracle offerings
will probably break the bank at least a little or make your product more pricey than it needs to be. IBM is not too bad and its express may be adequate.
SQL Server while its not free except for its express version, all its spatial is fully-supported in its express (so no major hidden gotchas), its pricing is low enough, install base high, and inertial costs of switching a db platform that you can't discount it on price alone especially if you are a windows-only shop with a wealth of applications built on SQL Server. Its tool set is also very nice for general db management.
General Overview
| Feature | SQL Server 2008 (RC0) | MySQL 5.1/6 | PostgreSQL 8.3/PostGIS 1.3/1.4 |
|---|
| OS | Windows XP, Windows Vista, Windows 2003, Windows 2008 |
Windows XP, Windows Vista, (haven't tested on 2008), Linux, Unix, Mac |
Windows 2000+ (including Vista and 2003, haven't tested on 2008), Linux, Unix, Mac |
| Licensing | Commercial - Closed Source, Various levels of features based on version, Express version has full spatial support but limitation on database size and only use one processor. | Commercial Open Source (COSS), some parts GPL. Here is an interesting blog entry on the subject MySQL free software but not Open Source. The comments are actually much more informative than the article itself. |
FLOSS (PostgreSQL is BSD, PostGIS is GPL Open Source - you can use for commercial apps but if you make changes to the core libraries of PostGIS, you need to give that back to the community) |
| Free GIS Data Loaders | shp dataloader for SQL Server 2008 developed by Morten Nielsen (doesn't yet work with RC0) | OGR2OGR, shp2mysql.pl script | included shp2pgsql, OGR2OGR, QuantumGIS SPIT, SHP loader for PostGIS also developed by Morten using SharpMap.NET various others |
| Commercial GIS Data Loaders | Manifold, Safe FME Objects, ESRI ArcGIS 9.3 (in a later service pack) |
Safe FME Objects | Manifold, FME Objects, ESRI ArcGIS 9.3 |
| Application drivers available specifically for spatial component |
? Not yet - SharpMap.NET eventually and probably built into new ADO.NET 3.5+ | GDAL C++, SharpMap via OGR, AutoCAD FDO |
SharpMap.Net, JDBC postgis.jar included with postgis, JTS etc. tons for Java, GDAL C++, AutoCad FDO beta support |
| Free Object/Relational Mapping |
NHibernateSpatial (this is a .NET object relational spatial mapper) - beta support | Hibernate Spatial - this is a java object relational mapper |
NHibernateSpatial and HibernateSpatial |
| Free Desktop Viewers and Editors | Will be built into SQL Manager, but not available in RC0 and only useful for viewing | GvSig | OpenJump, QuantumGIS, GvSig, uDig |
| Commercial Desktop Viewers and Editors | ESRI ArcGIS 9.3 Server SDE later service pack, Manifold, FME | FME | ESRI ArcGIS 9.3 Server, ZigGIS for desktop, Manifold, FME |
| Web Mapping ToolKits - it must be said things like OpenLayers and various other scripting frameworks that can accept GML will work with any of these databases and your favorite web scripting language | Manifold, MapDotNet, ArcGIS 9.3 (in later service pack), UMN MapServer see, MapGuide Open Source (using beta FDO driver) |
UMN Mapserver, GeoServer, MapGuide Open Source | Manifold, MapDotNet, ArcGIS 9.3, UMN Mapserver, GeoServer, FeatureServer, MapGuide Open Source (using beta FDO driver) |
| Spatial Functions | Both OGC SFSQL MM and Geodetic custom (over 70 functions) | OGC mostly only MBR (bounding box functions) few true spatial relation functions, 2D only |
Over 300 functions and operators, no geodetic support except for point-2-point non-indexed distance functions, custom PostGIS for 2D and some 3D, some MM support of circular strings and compound curves |
| Spatial Indexes (from reports Oracle also uses some sort of R-Tree indexing scheme and can use quadtree, IBM DB2 uses quadtree, Spherical Voronoi Tessalation, IBM Informix uses R-Tree. Note R-Tree indexes are self-tuning and do not require grid setup | Yes - 4 level Multi-Level grid hierarchy (BOL says its B-Tree based) with tessalation as described Isaac Kunen Multi-Level Grid requires defining an index grid for optimal performance | R-Tree quadratic splitting - indexes only exist for MyISAM |
GIST - a variant of R-Tree |
| True Geodetic support - support for true measurement along a spherical coordinate (it must be noted Oracle and IBM provide geodetic support, although IBM Informix/DB2 have it as an additional Blade add-on in addition to standard spatial) | Yes - with caveats - must use Geography type which has the following constraints, no single geometry may overlap hemispheres, intersections,
some operations are undefined for two geometries in separate hemispheres. SRID must be defined in spatial ref. Orientation of polygons is important in Geography. According to BOL - ST_Distance is only supported when one of the geography instances is a point ( NOTE: Isaac Kunen says the BOL docs are wrong about ST_Distance and this restriction has been lifted) . Fewer spatial functions available for geography than geometry | No |
No |
| Shared Hosting | Many | Many |
Much fewer, but ramping up.
It must be noted that if you have a dedicated Linux/Windows server,
and aren't afraid to be your own admin (or to get a qualified consultant such as us), then your options are much wider.
List of community recommended PostGIS Hosters
|
Now for this section, we can't really itemize all the functions of each offering. We will just focus on the functions
we consider most commonly used in spatial analysis and geoprocessing. For this section we are treating SQL Server 2008 as two products
because functionality available for its planar (Geometry) is not the same as functionality available for Geography. In theory you can convert between the 2 types by using the GeomFromWKB
and AsBinary functions that each type provides if they have the same SRID, but without Transform function, this is of limited use.
Please also note that these offerings have not officially come out yet. SQL Server 2008 is currently at RC0 and will be released in another 2-3 months. From accounts we have read, the functionality that is in RC0 is pretty much what you can expect in the RTM except that the RTM will have integrated spatial viewing in the SQL Management studio.
MySQL just came out with 5.1 RC and will soon be out with 5.1 and won't come out with 6 for probably another year or so. In general though they haven't done much with improving their spatial support so the 5+ is just more stable than the 4.0 and 6.0 will probably have more stability and the query optimizer is improved which will indirectly help spatial queries, but not much intro of new functionality.
PostGIS is currently at 1.3.3 and will be coming out with
1.3.4 very shortly. For all intensive purposes there is not much of a difference between 1.3.3 and 1.3.4 aside from bug fixes, ST_DWithin speed improvement and ST_AsGeoJSON. 1.4 will have vast speed improvements for doing cascaded aggregate unions, a bit better curve support.
For more extensive details - for MySQL check out - MySQL 5.1 Spatial Extensions,
MySQL 6 Spatial Extensions,
PostGIS 1.3.4,
PostGIS 1.4 SVN in progress,
SQL Server 2008 Web docs, SQL Server 2008 Books Online download.
For SQL Server 2008 Books OnLine, if you are running a Windows Installer service lower than 4.5, then you'll get an error "The installation package can not be installed by Windows Installer Service. You must install a windows service pack that contains a newer version of the windows installer service. ".
If you get such an error then - download and install the Windows Installer service 4.5 from http://www.microsoft.com/downloads/details.aspx?FamilyID=5a58b56f-60b6-4412-95b9-54d056d6f9f4&DisplayLang=en
Spatial Functionality
| Feature | SQL Server 2008 (RC0) Geometry | SQL Server 2008 (RC0) Geography | MySQL 5.1/6 | PostgreSQL 8.3/PostGIS 1.3/1.4 |
|---|
| Supported Geometry Types | Polygon, Point, LineString, MultiLineString, MultiPoint, MultiPolygon, GeometryCollection 2D support also storage for 3D and 4D (M,Z) |
Polygon, Point, LineString, MultiLineString, MultiPoint, MultiPolygon, GeometryCollection, 2D, ability to store 3D, 4D (M and Z) | 2D, can store 3D 4D(e.g. M and Z) but no functions do anything with those) - Polygon, Point, LineString, MultiPoint, MultiPolygon,MultiLineString, GeometryCollection |
2D, some 3D, 4D (support for storing Z,M but most spatial functions ignore the higher dimensions) and some curve support - Polygon, Point, LineString, MultiPoint, MultiPolygon, MultiLineString, GeometryCollection, CircularString, CompoundCurve, CurvePolygon, MultiCurve, MultiSurface |
| Transform - ability to transform from one spatial ref to another | No - need 3rd-party tools, Geometry can use any SRID between 0 and 999999 | No, but less need for transform since spherical coordinates that cover the globe can be used. SRID must be defined in sys.spatial_reference_systems | No |
ST_Transform - Yes for 2D and 3D, but not for Circular Curve Types |
| Geometry Output functions - these are important particular for web-development when using the various javascript, flash apis |
STAsBinary(), STAsText(), AsGML(), AsTextZM(), will have a builder api in the RTM for extending to support more output formats | Same as the geometry ones | AsBinary(), AsText() |
ST_AsBinary(), ST_AsText(), ST_AsSVG(), ST_AsGML(), ST_AsKML(), ST_AsGeoJson() -- new in 1.3.4, ST_AsEWKT(), ST_AsHexEWKB() |
| Geometry Input functions | STGeomFromText(),STGeomFromWKB(), GeomFromGML() | Same as Geometry | GeomFromText(), GeomFromWKB() | ST_GeomFromText(), ST_GeomFromWKB() |
| Intersects and Intersection | STIntersects(), STIntersection() (spatial ref of both geoms must be the same) | Same as geometry except an intersection resulting in a polygon that overlaps 2 hemispheres will return null |
MBRIntersects()* bounding box only (spatial refs of both geoms must be the same), they do not implement Intersection yet | ST_Intersects(), ST_Intersection() (spatial ref must be the same) |
| Other Non-Agg key relationship and relation outputs | STContains(), STDifference(), STDisjoint(), STEquals(), STOverlaps(), STRelate(), STSymDifference(), STTouches(), STWithin() |
STDisjoint() |
MBRContains(), MBREqual(), MBROverlaps, MBRTouches(), MBRWithin(), bounding box only (spatial refs of both geoms must be the same) (these are aliased in 6.0 to names Contains(), Overlaps(), Equals()... but do the same as MBR) |
ST_Contains(), ST_Disjoint(), ST_Difference(), ST_Equals(), ST_Overlaps(), ST_Relate(), ST_SymDifference(), ST_Touches(), ST_Within() (spatial ref must be the same) |
| Accessors and Editors | BufferWithTolerance() (this is similar to PostGIS variant of Buffer that takes 3 args (defaulted to 8 number of segs to use to approximate a quarter circle), MakeValid(), Reduce() - similar to PostGIS ST_Simplify(),
STBoundary(), STBuffer(),STCentroid(), STConvexHull(), STDimension(), STEndPoint(), STExteriorRing(), STGeometryN(), STGeometryType(), STInteriorRingN(), STIsClosed(), STIsEmpty(), STIsSimple(), STNumGeometries(), STNumPoints() (only applies to LINESTRINGS), STPointN((), STStartPoint(), STSRID(), STUnion() |
NumRings(), RingN(), STArea(), STBuffer(), STDimension(), STEndpoint(),STGeometryN(), STGeometryType(), STIsClosed(), STIsEmpty(), STLength(), STNumGeometries(), STNumPoints(), STPointN(), STSrid(), STStartPoint(), STUnion() |
Centroid(), Dimension(), EndPoint(), Envelope(), ExteriorRing(), GeomtryN(), GeometryType(), InteriorRingN(), IsClosed(), IsRing(), NumPoints(), PointN(), SRID(), StartPoint() |
ST_Affine(), ST_Boundary(), ST_Buffer() (2 variants similar to STBuffer() and BufferWithTolerance()), ST_Centroid(), ST_ConvexHull(), ST_Dimension(), ST_EndPoint(), ST_ExteriorRing(), ST_GeometryN(), ST_GeometryType(), ST_InteriorRingN(), ST_IsClosed(), ST_IsEmpty(),ST_IsRing(), ST_ISSimple(), ST_NumGeometries(),ST_NumPoints() (only applies to linestrings), ST_NPoints() - returns num vertexes regardless of geometry type, ST_PointN(), ST_Simplify(), ST_StartPoint(), ST_SRID(),ST_Translate(), ST_Union(), various MakeLine,MakePolygon, buildarea etc. |
| Measurement | STArea(), STLength(), STDistance() (measurements in unit of spatial ref) |
Same as geometry - except measurements can be in meters, sq meters, or units of spatial ref |
Area(), GLength(), Distance() |
ST_Area(), ST_Length(), ST_Distance(), ST_Distance_Spheroid(),
ST_Length_Spheriod (non-sphere, non-spheriod functions units are in spatial ref, sphere and spheroid are in meters but only work for point geometries) and equivalents for 3D geometries. |
| Linear Referencing - things like returning fractions of lines, approximating point location along a line based on num address |
none built in, but can roll your own with CLR. UPDATE Isaac Knunen has some linear referencing functions already built in the new CodePlex SQL Server Spatial Tools project. |
none built in - check out new SQL Server Spatial tools code plex project |
none |
ST_Line_Interpolate_point(), ST_Line_Substring(), ST_line_locate_point(), ST_locate_along_measure(), ST_locate_between_measures() |
| Spatial Aggregates (functions like SUM but for spatial) | Apparently none in this release so have to roll your own with CLR. There are numerous examples of this on the web.
UPDATE SQL Server 2008 Spatial Tools contains aggregate functions for Union and Envelop similar to PostGIS ST_Union and ST_Extent functions |
none |
none |
ST_Extent(), ST_Collect(), ST_Union(), ST_Accum(), ST_MakeLine(), ST_Polygonize() |
Post Comments About Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6
|
