In our prior compare -- we compared PostgresQL 8.3/PostGIS 1.3-1.4, SQL Server 2008 and MySQL 5+ (now owned by Oracle). Many people would have preferred seeing Oracle or IBM DB2 in the mix since few use MySQL seriously for spatial work. So by popular demand we are replacing our compare of MySQL with that of Oracle. A couple of exciting things have happened since that last comparison.

• PostGIS released PostGIS 1.5 which introduced native geodetic support in the form of a new type called geography. PostGIS geography type is very similar in concept to SQL Server 2008 geography type. PostGIS 1.5 also introduced ST_GeomFromGML/KML input as well as improved distance and additional distance functions.
• Microsoft recently came out with SQL Server 2008 Release 2 (R2) to complement their 2010 family of products (Office 2010 (Excel 2010/SharePoint 2010 PowerPivot) and VS 2010 ) and SQL Azure spatial support for their cloud offering with compatible functionality to the SQL Server 2008 regular deployments. Other enhancements for spatial, is more integration with Reporting Services.
• Oracle 11G R2 came out and they finally have a windows version too. Other great new feature in Oracle 11G R2, Oracle provides you a script to uninstall all the Oracle Spatial enterprise features you aren't supposed to have in Locator so you don't accidentally cough cough use stuff you are not licensed to use. Oracle has even added more functions to the 11G R2 Locator function set according to this article Oracle Locator vs Oracle Spatial License Troubles . Though the manual does suggest you can use some SDO package functions and you can use other aggregate functions in the SDO package except for the aggregate SDO_Aggr_Union. Oracle 11G R2 introduced an SDO_AGGR_SET_UNION which is, by comments we have heard, designed to be faster than the standard SDO_AGGR_UNION. Many more features for Oracle Spatial 11G and 11G R2.
• PostgreSQL 9.0 is expected out in June or July 2010. Its name changed from 8.5 to 9.0 because of the new replication features, that bring PostgreSQL up a notch. In 9.0 there is hot slave and streaming replication options. Unfortunately native Nearest Neighbor (KNN) spatial index support did not make the 9.0 deadline cut, so won't be seen till 9.1. Given the PostgreSQL release cycle, we can expect to see it in another 12 to 16 months in production. 9.0 will also have at least a beta release of native 64-bit for windows version. The KNN patch will mean some nearest neighbor queries that require spanning huge sparse distances can achieve up to 300 times improvement in speed.
• Oracle bought Sun and in so doing, owns MySQL, and plans to spend much more money on it than Sun did. Possibly making the crappy spatial support in it less crappy.

In this new comparison, we'll be comparing the geodetic support offered by these three commendable spatially enabled DBMS as well as standard OGC planar geometry support. Oracle, you will notice, we will compare a bit differently because Oracle splits their product slightly differently than PostGIS and SQL Server.

• In PostgreSQL/PostGIS and SQL Server you get both geometry and geography full support as well as all the functions that work with them at no extra cost but as two separate data types. The geometry datatype being generally richer in function support than the geography one.
• In Oracle, regardless of whether you are running Locator or the full Oracle Spatial product you get geodetic support packaged in with regular planar support in one geometry type. However, the functions you are allowed to use in Locator are a small subset of what you get with Oracle Spatial. For example, if you are using Oracle Locator, you do not have license to use the Intersection, Difference, Union functions packaged with SDO_GEOM. These are fairly critical functions for spatial visualizing and processing. You can only get Oracle Spatial iff you have Oracle Enterprise and you need to pay an additional cost above your Oracle Enterprise to get it.

So in short -- we will have 6 columns (because we are really comparing 6 things - two for each product) and we will not be going into the additional items Oracle throws in their Oracle Spatial version - e.g GeoRaster, advanced 3D support, Topology and Network support, Drive time etc.. We have no idea how good those features are. We will say that the PostGIS WKT Raster project offers similar raster analytic functionality provided by Oracle GeoRaster and ArcGIS server. It already has quite a bit of functionality now and can be used alongside PostGIS 1.4- upcoming PostGIS 2.0.

Most of this information is gleaned from the help docs and our general understanding of availability of tools as well as our familiarity with each product.

Full Disclosure -- Regina is on the PostGIS project steering committee. Leo and Regina, Co-founders Of Paragon Corporation, are writing a book PostGIS in Action due out later this year (2010). Paragon Corporation makes most of their profit doing SQL Server consulting (more than 40%) but most of a non-spatial nature - Financial applications, Web applications with SQL Server backends and BI reporting. Paragon Corporation makes a modest income doing PostgreSQL consulting and most of it on spatial database consulting using PostGIS. Paragon Corporation makes no money doing Oracle consulting, but makes a good chunk of change doing MySQL consulting against PHP web app front ends, which hmm is now owned by Oracle. We will try earnestly not to let our party lines get in the way, but we may slip.

We shall note that in terms of affordability vs. functionality, for most common spatial use cases, I think most would agree PostGIS and SQL Server 2008 are the front-runners. Oracle has a lot of bells and whistles that PostGIS and SQL Server lack, but much of that bell and whistle you can only tap into if you are using Oracle Spatial which requires an Oracle Enterprise license + Oracle Spatial License($$$$$). PostGIS has more advanced processing functions than SQL Server 2008 or Oracle Locator, but doesn't have tight of integration with Microsoft's large SharePoint, Microsoft CRM, MapPoint offerings much of which is thrown in for free if you have a windows server. On the Oracle side -- presumably if you are running a PeopleSoft or Oracle Financials and had a need for spatial support, it may be easier with Oracle database. That is pure speculation of course.

It has been claimed by several Oracle folks who have used PostGIS, that PostGIS aggregate union functionality is much better than Oracle Spatial's, or at least in prior versions of Oracle, which some have complained was slow vs. same data set in PostGIS before cascaded union implementation (afer though we have no links to point to, same dataset in PostGIS after cascaded union 1.4 is on par with ArcGIS (under 1 second). Note the new Oracle 11G R2 set union works with arrays of geometries similar in concept to the PostGIS ST_Union(geom[]) overloaded array version of spatial aggregate union and its unclear how much faster this new unioning is compared to prior Oracle Spatial AGGR_UNION. To be fair to Oracle Spatial -- it must be noted that when using a long lat based spatial ref, it does use a round earth model instead of treating it as planar as do PostGIS and ArcGIS. What is for sure is that if you haven't shelled out the money for Oracle Enterprise and Oracle Spatial, Oracle still doesn't think you are worthy enough to do a union or intersection of any kind at all even in the 11GR2 offering, so the discussion is kind of moot if you are using Oracle Locator.

As relational and spatial database consultants, we don't consider any of these database products as an either or proposition. All support ANSI-SQL constructs fairly nicely and in a standard way. All treat spatial types as first class database citizens and with fairly recognizable OGC SF SQL syntax and function names. Though the spatial queries you will write in each are slightly different, if you know one, you can easily understand another. They are all one big happy family of spatial relational databases with their occasional family bickerings that drive competition. Most importantly the knowledge you gain in one is fairly applicable to the others. Although spatial types are first class citizens in these databases, they do require special GIS knowledge to fully take advantage of them. You will find that PostGIS / SQL Server 2008 spatial / Oracle Locator (Spatial) consultants have more in common with each other than when you compare them to their other vendor specialists. For example an Oracle Spatial/ SQL Server 2008 spatial/ PostGIS consultant have more in common with a each other than they do with their Oracle RAC / SQL Server 2008 database mirroring or PostgreSQL replication consultants. They all walk the same walk and talk the same GIS lingo. All these databases have become so feature rich that there are numerous specialists focused on a particlular piece of the database product whether that be spatial, OLAP, tuning, replication, and then are generalists in thier non-specialties. In fact you will find many database spatial/GIS consultants highly skilled in the spatial component of all 3 database products.

Aside from what is there or what is missing in each in spatial, there are other reasons to pick one over the other or pick more than one:

• Price
• Integration with your current stack or available stacks (PostGIS has the best support in Open Source GIS stack followed by Oracle and then SQL Server), SQL Server has best support in Microsoft Stack, as far as other commercial product support. As far as .NET and Java integration all 3 are about tied. For Python PostGIS is a much stronger contender than the other 2.
• Support consultants available and pricing of said consultants
• Community Support
• Core Enterprise SQL features: stored procs, views, windowing functions, recursive queries, synonyms etc. For example PostgreSQL has probably the richest language support for stored procs/functions. You can write database stored functions and triggers in R, Python, PLPGSQL, SQL, Perl, C, Java, etc. and in upcoming 9.0 anonymous functions via the new DO command or even mix languages in a single function with PL/Parrot. In 8.4 PostgreSQL introduced SQL standard windowing functions (better than SQL Server 2008 R2, but not as good as Oracle's) and Common Table Expressions (CTE), recursive CTEs. Oracle 11GR2 has the best window function support and introduced ANSI compliant recursive CTEs in R2. SQL Server has the weakest Window function support of them all, but has had CTE and Recursive CTEs for longer. PostgreSQL is the only one of the 3 that lacks synonym support.

Why would you ever want to pick more than one. You may if you have for example an internal network that runs entirely on SQL Server and ties in with things such as Microsoft CRM or Microsoft Sharepoint or Microsoft Reporting Services (now with the better R2 spatial support) that can talk most easily to SQL Server. SQL Server and Oracle Locator lack some of the advanced processing functions PostGIS provides and have fewer free and about the same number of proprietary priced tools to export data out of it than does PostGIS. As such you may want to use PostGIS to process data that SQL Server/ Oracle Locator don't have built in functions for and have communication channels that transport data back and forth between the 2. The processing costs of SQL Server and Oracle are also more prohibitive as you pile on more and more processors and memory to service your traffic. As such you may prefer to use PostGIS for your front-facing customer service traffick to build webservices that all freely available citizen tools can use and SQL Server/Oracle for your internal needs and to service your existing Oracle/SQL Server bolted applications. You can save quite a bit of money easily by using PostGIS/PostgreSQL or by adding it to your existing arsenal of tools. Why buy another Oracle server to service your DMZ traffic when you can have a free PostGIS/PostgreSQL/EnterpriseDb with similar functionality and speed with very little learning curve.

We don't consider speed much of a deciding factor, since its too open to interpretation and all databases with enough processors (3 or more), fast disks, and well tuned tables structures and indices can handle terrabytes or more of spatial data and achieve results in under 5 seconds or less for most garden variety spatial queries. From experience with these databases and other experiences we have read from people who work day in and day out with these, there is no clear cut speed winner that someone can't easily argue against or for and say -- what if you tried this workload or tweaked this parameter or compare on servers having 10 core duo processors with gazillions of ram for this workload and arguments if what workload is really realistic for common use cases. In theory (though don't have a workload to prove in practice) - for queries that can benefit from a high-degree of parallelism and where you have a machine with several processors, both Oracle and SQL Server should beat out PostgreSQL because PostgreSQL plain vanilla planner doesn't currently support parallelism strategies (note there are add-ons like GridSQL that change this). However, for servicing a lot of queries at once, which is general use case particularly for web traffic -- the playing ground levels and falls on the side of PostgreSQL which can use as many processors and memory your OS can allocate to service all queries without the additional arbitrary limits on memory and processor artificially enforced by Oracle and SQL Server.

We will parrot some anectdotal comments we have heard:

• PostGIS and PostgreSQL support communities fix issues faster than the proprietary vendor support line and answer user questions faster than the proprietary vendor support lines.
• With PostGIS you only have a newsgroup, a bug tracker, and an IRC channel to complain about bugs or get query support, unless you buy third-party support, but with Oracle and Microsoft you have email, a user news forum, and peace of mind that there exists a big company with a 24-hour phone support hot-line you can call when you find a bug. You have the luxury of cursing on line while elevator music plays in the background until someone with a clue understands you enough to correctly route you.
• If you have a complete Microsoft stack, a Microsoft database will work better in that stack. Better integration with SharePoint, Microsoft Dynamics CRM, and Microsoft Reporting Services which comes packaged with SQL Server including express. Oracle and PostgreSQL just can't beat that if your company relies heavily on these applications.
• Oracle has a bigger R&D budget focused on database and state of the art advanced nautical racing technology :) and an even bigger more impressive sales force focused on informing you of this great technology. An Oracle database is the best that money can buy if only you could get money to grow on trees.

Now on with the show.

General Overview

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, web applications and geoprocessing. For this section we are treating SQL Server 2008 and PostGIS as two products because functionality available for their planar (Geometry) is not the same as functionality available for their 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 support (that SQL server 2008 still lacks built-in), this is of limited use in SQL Server.

As mentioned we are treating each as two separate products.

Spatial Functionality