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.

Hello, world!

I know, I know that I should have started blogging long ago but I could not find time for this until now. I have been pretty busy with my work and my Imagine Cup project. My team "Atlas" competed in the Software Design category and we won The Engineering Excellence Achievement Award. Being in Paris for the Imagine Cup 2008 World Finals was an amazing experience. We even got the opportunity to chat with S. Somasegar (who by the way is a great guy) in person. We met lots of interesting people, saw lots of interesting things and even I managed to buy my new LEGO Technic set.
You can take a look at the blog of my team mate Boryana where she writes about her experience with the Imagine Cup competition.

So, now that things are now a bit more settled and I finally managed to find some time for my first blog post. From now on here I will post things that I found interesting. Mostly about programming, .NET, C#, technology, etc...you know...computer geek stuff.