Spatial Data Support Coming To SQL Azure

  • Article

Last Wednesday (March 17th), Dave Robinson of the SQL Azure Team announced upcoming support for spatial data in SQL Azure.  You can view the announcement at the MIX Conference in the video of Dave’s presentation, Building Web Applications with Microsoft SQL Azure (go to 18:40 on the timeline for the start of the spatial data portion of his talk).

Dave reports that spatial will be available in the SU3 release of SQL Azure, tentatively scheduled for this June.

I’ve been using SQL Azure with spatial support for a couple of weeks now and it works just like the spatial data support in SQL Server 2008 – same spatial data types, spatial methods and spatial indexes.  It works in SQL Server Management Studio just like you would expect (see below).

image

Query performance is excellent.  In the above example, the query executed in under a second against a table containing over 1.8 million rows (GeoNames points-of-interest for the United States).

The only real differences that I have found between SQL Server and SQL Azure are true for all data:

1. You need to have a clustered index on the table you are inserting data into.

Note that this can be satisfied by a primary key, which in turn is a requirement for creating a spatial index. You can view this a good thing for spatial ;-)

2. You may need to break your data loads up into chunks to prevent the connection to SQL Azure from timing out.

If you are loading data from an existing SQL Server database, a great tool is the SQL Azure Migration Wizard found on CodePlex. It currently offers some minor complaints about spatial data when you run it (spatial indexes in particular) but does the right thing anyway.

If I’m not mistaken, SQL Azure will become the first pure cloud database to offer spatial support.  It will be interesting to watch how this new feature is put to use…

Technorati Tags: Spatial,Spatial Data,SQL Azure,SQL Server,SQL Server 2008,Migration Assistant

Comments

  • Anonymous
    March 25, 2010
    Interested in staying current on this issue Good Article

  • Anonymous
    April 17, 2010
    Is there any chance Azure will also include SQL Spatial Tools built-in, since we cannot load CLR assemblies? Without it, we have no way to do a myriad of tasks such as aggregate union geographies.

  • Anonymous
    April 20, 2010
    @bytenik This is something that I have been thinking about.  While I can't promise anything, I will see what what I can do.   In the short term, consider processing your spatial data in SQL Server on a machine that you control of and uploading data processed with the SQL Server Spatial Tools to SQL Azure.  While this doesn't address the issue of spatial query support from extensions in SQL Azure, it would allow you at least have your data in the "form" you need. In the long term, virtual all of the SQL Spatial Tools (in one form or another) will find their way into the next major release of SQL Server and hence into SQL Azure.

  • Anonymous
    August 05, 2010
    I work for the Department of Geography in University of the Aegean, Greece. We have developed an ArcGIS Silverlight-based app called Virtual Fire (URL: 195.251.137.205/virtualfire) which is a kind of a Decision Support System about forest fires. We are now enhancing the app by participating to a new project and try to deploy it to Windows Azure. Our app uses the ArcGIS Silverlight control (URL: help.arcgis.com/.../index.html) with ArcGIS Server/MS SQL Server. Could you please give us some feedback about the following issues?

  1. Is there any straightforward way to upload a MS SQL Server DB to SQL Azure?  Does SQL Server now fully supports spatial features?
  2. Does Mapit for Azure (blogs.msdn.com/.../sql-azure-lessons-learned-esri.aspx) support all spatial feature types or only points? How much does a licence for ArcGIS Server+ MapIt cost?What other options do I have?
  3. Our on-premise app uses ESRI ArcObjects in order to compute some raster files dynamically and update the SQL DB. Is there any way to use ArcObjects in MS Azure? Is there any limitation because of  the fact that it is not allowed to run azure apps in admin mode?
  4. How can i get more info/feedback  about ESRI + Windows Azure? Thank you very much.