Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features
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:
- 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.
|SQL Server 2008 Release 2
|Oracle 11G Release 2
|PostgreSQL 8.4/PostGIS 1.5
|Windows XP, Windows Vista, Windows 2003, Windows 2008 (runs in both 32-bit and 64-bit)
|R2 does not yet work on Mac apparently,10G seems to be the last to work on Mac. Linux, Unix, Windows 2003, Windows 2008 (runs in both 32-bit and 64-bit)
|Windows 2000+ (including Vista, 2003, Windows 7, 2008) - (currently only runs in 32-bit mode for windows, but can allocate more memory than the 4 gig limit partly because its multi-process (rather than multi-threaded) and delegates much of memory management to the OS), Linux, Unix, Mac (both 32-bit/64-bit supported for all non-Windows OS)
|Proprietary - 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.
|Proprietary Closed Source comes in free Express with Oracle Locator, Standard, Standard Edition One packaged with Oracle Locator, Enterprise packaged with Oracle Locator with option to buy Oracle Spatial add-on
|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)
|Versions and Pricing
Express - free limit 1 processor, 10 gig db Note that SQL Server 2008 had a 4gig limit
Standard - starts at ~$7500 per processor limit 4 processors
Enterprise ~$29,000 per processor
Datacenter - ~$58,000 per processor
Parallel Data Warehouse - ~$58,000 per processor
Prices negotiable, but not as negotiable as Oracle unless buying in volume.
Express (XE) Free 1 processor, 4 gb db, 1 gig ram
Personal Edition ~$450 per user
Standard Edition One ~$5800 per proc
Standard Edition ~$17,500 per proc
Enterprise Edition ~47,500 per proc + option to buy Oracle Spatial add on for another $17,500 per proc
NOTE: We hear these prices are very negotiable if you consider fighting with sales reps a fun sport and a good use of your time and/or you are big enough
to not be laughed out of the room.
|In the famous words of Jeff Atwood, much like other Open Source software, there is only one edition - The Awesome Edition. If you don't want to spend your life figuring out why you can't use
all your memory or processors or whether you are in violation of your license for accidentally using a function you weren't supposed to, or waiting for your loan to be approved so you can buy that extra database processor license for that extra processor you just added to your awesome server, then PostGIS is probably your answer.
|Free GIS Data and ETL Loaders
|shp dataloader for SQL Server 2008 developed by Morten Nielsen
|shp2sdo packaged with Oracle, OGR2OGR (not compiled in by default needs Oracle libraries), GeoKettle
|included shp2pgsql, shp2pgsql-gui (introduced in PostGIS 1.5) which is a graphical equivalent of the shp2pgsql commandline loader, OGR2OGR (no license required to compile in and many precompiled binaries have it compiled in), QuantumGIS SPIT, SHP loader for PostGIS also developed by Morten using SharpMap.NET, GeoKettle,
Spatial Data Integrator
|Commercial GIS Data Loaders
|Safe FME Objects, ESRI ArcGIS 9.3+ (in a later service pack), Cadcorp
|Safe FME Objects, ESRI ArcGIS (all versions)
|Safe FME Objects, ESRI ArcGIS 9.3+, CadCorp
|Application drivers available specifically for spatial component
|SharpMap.NET (thanks Bill Dollins), NetTopologySuite
|SharpMap.NET, JDBC postgis.jar included with postgis, JTS etc. tons for Java, OGR/GDAL
|Free Object/Relational Mapping
|HibernateSpatial NHibernateSpatial (this is a .NET object relational spatial mapper) (just geometry)
|Hibernate Spatial - this is a java object relational mapper, NHibernate Spatial
|NHibernateSpatial and HibernateSpatial (just geometry)
|Free Desktop Viewers and Editors
|Built into packaged SQL Manager
|Packaged with Oracle via OBE, GvSig, QuantumGIS (via additional extensions), OpenJump (via additional extensions), uDig, GeoRaptor for Oracle SQL Developer
|OpenJump, QuantumGIS, GvSig, uDig
|Commercial Desktop Viewers and Editors
|ESRI ArcGIS 9.3 Server SDE, Manifold, CadCorp, Autocad FDO, MapInfo 10+
|ESRI ArcGIS SDE (all versions), FME, Manifold,CadCorp, Autocad FDO, MapInfo
|ESRI ArcGIS 9.3 Server, ZigGIS for ArcGis/ArcMap desktop, Manifold, FME, CadCorp, Autocad FDO, MapInfo 10+
|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 (with ArcSDE license), UMN MapServer see, GeoServer, MapGuide Open Source (using beta FDO driver)
|Manifold, MapDotNet, UMN Mapserver, GeoServer, MapGuide Open Source, ArcGIS all versions (with ArcSDE license)
|Manifold, MapDotNet, ArcGIS (9.3+ with ArcSDE license), UMN Mapserver, GeoServer, FeatureServer, MapGuide Open Source (FDO beta driver)
|Both OGC SFSQL MM and Geodetic custom (over 70 functions)
|A fair number and even esoteric ones for geodetic and planar (2D), but fundamental stuff like intersections and unions you aren't licensed to do with Locator product. On the upside we hear the nearest neighbor NN packaged in both Locator and Spatial rocks.
|Over 300 functions and operators, geodetic support in PostGIS 1.5 for all basic types mostly focused on proximity calculations, most SQL/MM 2D and some 2.5D, basic MM curved support
|Spatial Indexes (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, but with grids you may be able to squeeze out extra performance with a bit more effort)
|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
|GIST - a variant of R-Tree
|True Geodetic support - support for true measurement along a spherical coordinate (it must be noted IBM provide geodetic support, although IBM Informix/DB2 have it as an additional priced Blade add-on in addition to standard planar spatial)
|Yes - with caveats - must use Geography type which has the following constraints: no single geometry may overlap hemispheres,
most operations involving 2 geometries, such as intersections, are undefined for geometries more than a hemisphere apart. SRID must be defined in spatial ref. Orientation of polygons is important in Geography. Fewer spatial functions available for geography than geometry
|Yes as a single type geometry. Oracle does do some under the covers transformation when doing operations on geodetic data it has no internal support for and also allows operations between two geometries with different SRIDS.
|Yes - PostGIS followed same model as SQL Server -- two type geometry and geography. Geography is however currently limited to only WGS 84 long lat. While you can stuff more than a hemisphere in a geography field you shouldn't since distance/intersection calculations will be often wrong because it uses great circle model. SQL Server 2008 prevents you from making this mistake.
|Hosting Options - If you are doing any serious spatial with any of these, you really should get your own dedicated server.
|Many, but only windows hosting which tends to still be pricier and less plentiful than Linux hosting. With a shared box, you usually can't install CLR stuff. There is also SQL Azure for cloud computing which now supports spatial, though evidentally no custom CLR install. If you need the added functions provided by Spatial Tools project, SQL Azure is not currently a happy option.
|Few and expensive unless you do express. Probably need your own dedicated server and pay your own licensing then you can use any dedicated Linux/Windows hosting and manage your own.
|Although not widely publicized, the dedicated hosting story for PostgreSQL/PostGIS is better than it is for Oracle or SQL Server 2008 (as far as spatial is concerned).
If you have a dedicated Linux/Windows hosted server,
and aren't afraid to be your own admin (or to get a qualified consultant such as us to help out with mentoring and advanced support). Number of spatial consultants with PostGIS experience is increasing rapidly. Refractions and OpenGeo are the frontrunners for Enterprise level support contracts and full service GIS training with specific focus on PostGIS and open source GIS. Most of the PostGIS core developers work for companies providing consulting support around PostGIS and at generally lower or comparable price you would get for Oracle or SQL Server spatial. By consulting
with them, not only do you get great support, but you can sponsor additional features in PostGIS that you need for your particular workload. Many many more smaller consultants focused on just the PostGIS side. You can host on any cloud thing
like Amazon EC or GoGrid or any dedicated server for that matter. Many Linux distros provide PostgreSQL/PostGIS via Yum or apt-get so a fairly painless install. Windows pre-compiled installs are available for the latest PostGIS/PostgreSQL via Stackbuilder, and a click - next next 5-10 minute install away. You can host on Linux or Windows and they won't be throwing in any additional database licensing cost on to your bill as they do with Oracle and SQL Server.
List of community recommended Hosters good for PostGIS.
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.
|SQL Server 2008 R2 Geometry
|SQL Server 2008 R2 Geography
|PostgreSQL 8.4/PostGIS 1.5 Geometry
|PostgreSQL 8.4/PostGIS 1.5 Geography
|Supported Geometry Types
|OGC basic types 2D/2.5D, but no curved support.
|OGC basic types 2D/2.5D, but no curved support.
|OGC basic types 2D/2.5D, with curved support.
|OGC basic types 2D/2.5D/3D with curved support (can do volumetric 3D support).
|OGC basic types 2D/2.5D, with curved support.
|OGC basic types 2D/2.5D, with no curved support.
|Transform - ability to transform from one spatial ref to another
|No - need 3rd-party tools, Geometry can use any SRID between 0 and 999999. Spatial Tools free CLR add-on does provide limited transform support.
|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
|Yes - SDO_CS package SDO_CS.TRANSFORM
|Yes - same as Locator
|Yes - ST_Transform
|No but easy-work around ST_Transform(geometry(geog), desiredsrid)
|STAsBinary, STAsText, AsGML (only 3.0), AsTextZM, a builder api for extending to support more output formats
|Same as the geometry ones
|Get_WKB, GET_WKT, SDO_UTIL package TO_WKBGEOMETRY, TO_WKTGEOMETRY, TO_GML311GEOMETRY, TO_GMLGEOMETRY, TO_KMLGEOMETRY
|Same as Locator
|ST_AsBinary, ST_AsText, ST_AsSVG, ST_AsGML (both 2 and 3 version), ST_AsKML (both 2 and 3 version), ST_AsGeoJson, ST_AsEWKT, ST_AsHexEWKB
|Same as for geometry
|Geometry Input functions
|STGeomFromText, STGeomFromWKB, GeomFromGML (only 3.0 version)
|Same as Geometry
|SDO_Geometry (accepts WKT and binary), and array input version, SDO_UTIL package: FROM_GML311GEOMETRY, FROM_GMLGEOMETRY , FROM_GML311GEOMETRY, FROM_KMLGEOMETRY
|Same as Locator
|ST_GeomFromText, ST_GeomFromWKB, ST_GeomFromGML*, ST_GeomFromKML* (GML and KML input are new in 1.5)
|ST_GeogFromText, ST_GeogFromWKB, no GML and KML input. Easy workaround geography( ST_Transform ( ST_GeomFromGML( somegml ), 4326 ) )
|Intersects,Intersection,Difference, Basic Union
|STIntersects, STIntersection, STUnion, STSymDifference (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
|SDO_Intersects no Intersection, Union or difference
|via SDO_GEOM SDO_DIFFERENCE, SDO_INTERSECTION, SDO_UNION (can span different spatial refs too)
|ST_Intersects, ST_Intersection, ST_Union, ST_Difference, ST_SymDifference (spatial refs of both geoms must be the same)
|ST_Intersects (native support), ST_Intersection (intersection relies on internal transform to best fitting spatial ref and then convert back to geography)
|Other Non-Agg key relationship and relation outputs
|spatial ref must be the same: STContains, STDifference, STDisjoint, STEquals, STIntersects, Filter, STOverlaps, STRelate (SQL Server doesn't support the variant that returns the matrix that PostGIS and Oracle Spatial offer), STSymDifference, STTouches, STWithin
Note STDIstance() < somevalue is equivalent to PostGIS ST_DWithin and Oracle SDO_Within_Distance and uses a spatial index just as those do
|Spatial ref must be the same: STDisjoint, STIntersects
|All relationship operators in Oracle Spatial are available in Locator (except for SDO_GEOM.RELATE):
Most of these return true/false based on Oracle RELATE MASKS except for the NN, NN_DISTANCE
(spatial ref can be different) - SDO_ANYINTERACT (means they are not Disjoint -- they intersect)
, SDO_INSIDE (this is presumably what PostGIS and SQL Server call ST_/STWithin)
|Same as locator + SDO_GEOM.RELATE (note: RELATE returns the MASK keywords that define the relationship, SDO_RELATE returns if 2 geometries are related by the passed in matrix)
|Spatial ref must be the same: ST_Contains, ST_CoveredBy, ST_Covers, ST_Disjoint, ST_Difference, ST_DWitin (this is similar in concept to Oracle SDO_Within_Distance), ST_Equals, ST_Intersects, ST_Overlaps, ST_Relate (overloaded function -- one variant returns true (similar similar to Oracle's SDO_RELATE, and the other returns the matrix SDO_GEOM.RELATE), ST_Touches, ST_Within, &&
|Spatial ref must be the same: ST_CoveredBy, ST_Covers, ST_DWithin,ST_Intersects, && is closest equivalent to Oracle and SQL Server SDO_Filter, filter operations.
|Accessors, Editors, Processors, Relationship outputs (OGC type)
|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, STIsValid, STNumGeometries, STNumInteriorRing, STNumPoints (only applies to LINESTRINGS), STPointN(, STStartPoint, STSRID, STUnion, sTX,STY
|Long,Lat, NumRings, RingN, STArea, STBuffer, STDimension, STEndpoint,STGeometryN, STGeometryType, STIsClosed, STIsEmpty, STLength, STNumGeometries, STNumPoints, STPointN, STSrid, STStartPoint, STUnion
|All functions in the mdsys package which includes ST_Centroid, ST_ConvexHull. All functions in SDO_GEOM package: SDO_BUFFER, SDO_CENTROID, SDO_CONVEXHULL, SDO_POINTONSURFACE, SDO_TRIANGULATE. EXCEPT FOR: SDO_DIFFERENCE, SDO_INTERSECTION, SDO_UNION, RELATE
|Everything in mdsys + SDO_GEOM. including SDO_DIFFERENCE, SDO_INTERSECTION
|ST_Boundary, ST_Buffer (2 variants similar to STBuffer and BufferWithTolerance), ST_Centroid, ST_ClosestPoint, ST_ConvexHull, ST_Dimension, ST_EndPoint, ST_ExteriorRing, ST_GeometryN, ST_GeometryType, ST_InteriorRingN, ST_IsClosed, ST_IsEmpty,ST_IsRing, ST_ISSimple, ST_IsValid, ST_Length, ST_LongestLine, ST_NumGeometries, ST_NumInteriorRings, ST_NumPoints (only applies to linestrings), ST_NPoints - returns num vertexes regardless of geometry type, ST_NumInteriorRings, ST_PointN, ST_ShortestLine, ST_Simplify, ST_StartPoint, ST_SRID, ST_Translate, ST_Union,ST_X, ST_Y
|ST_Buffer most other functions you need to cast to geometry and then back to geography. Full list
|Extra non-OGC non-Aggregate type functions used in higher processing
|MakeValid ( affine and translate functions available via Spatial Tools CLR add-on)
|IN SDO_GEOM - SDO_ALPHASHAPE, SDO_ARC_DENSIFY, SDO_CLOSEST_POINTS, SDO_CONCAVEHULL, SDO_TRIANGULATE, VALIDATE_GEOMETRY_WITH_CONTEXT, VALIDATE_LAYER_WITH_CONTEX
IN SDO_UTIL - AFFINETRANSFORMS (AFFINETRANSFORMS is new in 11G but workarounds for older versions), APPEND, BEARING_TILT_FOR_POINTS, ELLIPSE_POLYGON,
EXTRACT, EXTRACT3D (similar in concept to PostGIS ST_Dump* and ST_CollectionExtract functions),
|Same as locator
|ST_Affine, ST_AddPoint, ST_BuildArea, ST_ClosestPoint, ST_CollectionExtract, ST_CurveToLine (similar to Oracle SDO_ARC_DENSIFY), ST_Dump, ST_DumpPoints, ST_DumpRings, ST_IsValidReason (similar in concept to Oracle VALIDATE_GEOMETRY_WITH_CONTEXT), ST_LineToCurve, ST_LongestLine, ST_MakeLine,ST_MinimumBoundingCircle, ST_Reverse (similar to Oracle REVERSE_LINESTRING, except works for all geometries), ST_SetPoint, ST_ShortestLine, ST_Translate
|None - but many you can get by casting between geography and geometry
|STArea, STLength (for Polygons gives the perimeter), STDistance (measurements in unit of spatial ref)
|Same as geometry - measurements in meters and sq meters
|via mdsys.st_polygon new in 11G (ST_Area, ST_Length) -- always had ST_Distance with choice of units.
|via mdsys.st_polygon and SDO_Geom - ST_Area, ST_Length, ST_Distance with choice of units.
|ST_Area, ST_Distance, ST_Distance_Spheroid, ST_HausdorffDistance, ST_Length, ST_Perimeter,
ST_Length_Spheriod , ST_MaxDistance (non-sphere, non-spheriod functions units are in spatial ref, sphere and spheroid are in meters)
|ST_Area, ST_Distance, ST_Length (also works for polygons and behaves as ST_Perimeter), units always in meters/sq meters
|Linear Referencing - things like returning fractions of lines, approximating point location along a line based on num address
|None built in, but availabe via free extension - 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
|Yes (SDO_LRS package)
DEFINE_GEOM_SEGMENT, REDEFINE_GEOM_SEGMENT, CLIP_GEOM_SEGMENT, CONCATENATE_GEOM_SEGMENTS, LRS_INTERSECTION (same as PostGIS ST_Intersection,STIntersection), SPLIT_GEOM_SEGMENT, TRANSLATE_MEASURE, REVERSE_MEASURE
|ST_AddMeasure ST_Line_Interpolate_point, ST_Line_Substring, ST_line_locate_point, ST_locate_along_measure, ST_locate_between_measures
|No but can cast back and forth between geography and geometry to use most geometry linear functions
|Spatial Aggregates (functions like SUM but for spatial) - PostGIS and Oracle Spatial are clearly winners here
|None, but free open source SQL Server Spatial Tools add-on includes some as CLR functions.
SQL Server 2008 Spatial Tools contains aggregate functions Geometry Envelop similar to PostGIS ST_Extent and Oracle Aggr_MBR functions for geometry
|GeographyUnion via SQL Server 2008 Spatial Tools free CLR library
|All aggregates supported in Oracle Spatial except for SDO_AGGR_UNION according to this.
|Yes - SDO_AGGR_CENTROID, SDO_AGGR_CONCAT_LINES, SDO_AGGR_CONVEXHULL, SDO_AGGR_LRS_CONCAT, SDO_AGGR_MBR, SDO_AGGR_SET_UNION, SDO_AGGR_UNION and with Oracle Spatial R2, you can even do a ST_Union_Set which has existed forever in PostGIS.
|ST_Extent, ST_Collect, ST_Union (can be used for any geometry -- so combines functionality of Oracle SDO_AGG_Union, SDO_AGGR_CONCAT_LINES), ST_Accum, ST_MakeLine, ST_Polygonize and also supports overloaded functions for these that take as input arrays of geometry objects. It must be noted that most of the
Oracle aggregates are supported here by another name or can be easily replicated using ST_Collect or ST_Union and another function - e.g. ST_ConvexHull( ST_Collect( geom ) ) = SDO_AGGR_CONVEXHULL. ST_Centroid(ST_Union(geom)) = SDO_AGGR_CENTROID, ST_Union(array_of_geoms) = SDO_AGGR_SET_UNION, ST_Extent = SDO_AGGR_MBR.
Post Comments About Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features