SQL Spatial VS Oracle Spatial RRS feed

  • General discussion

  • I was just wondering how others experience has been.

    I started on a geodetic (geography data type) project in Oracle Spatial in October of 2008. We thought SQL would be pretty rough b\c it is so new and didn't want to be the guinea pigs. However, we have had a lot of severe performance problems in addition to some Java Null pointer exception bugs from Oracles native functions. We finally got some bugs fixed and have things situated and with enough query hints for our Oracle process to be tolerable.

    We started testing with SQL Spatial a few weeks ago and am finding that the performance is normally twice as good and sometimes better. B\c SQL has less functions we have to use a few combinations of its limited functionality to do things like touch or inside and covered by etc. But once those are in place they are still performing much better than their Oracle counter parts. The main draw back is tuning the spatial indexes in SQL takes a lot of trial and error as for different data sets and the different parameters given the index the results vary dramatically (whereas the Oracle spatial indexes are basically self-tuned). I also haven't ran into any bugs yet.

    So in general, SQL seems to be more stable and perform much better.

    What has other peoples experience been who have used both SQL and Oracle spatial?
    Thursday, April 2, 2009 6:38 PM

All replies

  • A quick comment to start with - Touch, Inside, and Covered by (Within) are all implemented in SQL Server 2008 - but only for the (planar) geometry datatype, not the geodetic geography type. But, as you say, there's generally workarounds if you really need to test for a specific kind of intersection when STIntersects() won't do.

    The spatial featureset in SQL Server still feels new, but that has plus sides as well as down sides. Yes, it's not as feature-rich as some other more established products (I've used Oracle Spatial as well as IBM DB2 geodetic in the past), but it actually feels more exciting - there is lots of active development, as well as in accompanying technologies (such as the Virtual Earth Silverlight control that can provide a scalable dynamic spatial interface to the SQL Server 2008 backend), and there seems to be quite a buzz in the development communities about it.
    For me, SQL Server 2008 spatial occupies an interesting middle-ground:
    - If spatial is your core business, you'll probably still be using Arc*** together with a dedicated DB Oracle backend.
    - If you're a casual developer who wants a pretty map, you'll probably be using Google Maps or Virtual Earth with a MySQL DB backend.
    But there's an enormous market inbetween those two, and that's where SQL Server 2008 spatial might just fit in. I don't think that people will migrate to SQL Server just because of its spatial features, but people who are already using SQL Server (2000/2005/2008) as a general database will start to see the benefits of spatial and extend their databases to include that functionality. Added to that, it is of course easier to combine with other Microsoft technologies, such as using the SqlServer.Types.dll assembly within a .NET project.

    I've not experienced any stability bugs to date (documentation bugs, yes, but these are getting much better!) but it's still relatively early days - most of my spatial work has been in development/test projects and I haven't had that many applications rolled out into a stable production environment yet, but there's certainly nothing that concerns me.
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, April 2, 2009 7:16 PM