Importing ESRI Shapefile data in SQL Server 2008

Hi again. For my Imagine Cup project I needed GIS data about US cities so that I can make some interesting spatial queries. Actually finding the GIS data was the easy part. Moreover it was in a ESRI Shapefile format which has become standard for exporting/distributing GIS data.
So far, so good.
Now here comes the more interesting part - how do I import that Shapefile into SQL Server 2008. Fortunately, Morten Nielsen has made the very helpful SQL Server 2008 Spatial Tools. There were some problems with the SQL Server 2008 RC0 due to the change of (latitude, longitude) ordering. However, recently Morten updated his tools and now they work again (thank you Morten).

With these tools importing the Shapefile data into SQL Server 2008 is easy. You just connect to your SQL Server and then select the .shp file and some other options like the type of the geom field (in my case Geography), SRID, etc.

Here the magic SRID number means that the WGS 84 spatial reference system should be used.
You can easily see that by executing the following query:

select * from sys.spatial_reference_systems where spatial_reference_id = 4326





When you are ready, just press the "Upload to Database" button and wait a little.
And now after your ESRI Shapefile data is finally in your SQL Server 2008 you can do some interesting queries like for example:


declare @p geography
set @p = geography::STPointFromText('POINT(-121.7944 38.1172)', 4326)
select top(50) CITY_NAME, geom.STDistance(@p) as Distance, geom.STStartPoint().ToString() as StartPoint from USTowns order by Distance asc

That was all for now. I hope that I will be able to blog more often in the future.