Spatial Data Support Coming To SQL Azure

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).

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