My first struggle with geospatial data in MS SQL Server 2008

Couple of days ago I decided that it was time to finally introduce myself with this cool new feature of Report Builder 3.0 for integrating maps in reports. I figured that first I should get some spatial data available in my database so I can actually report on it, because the one that is in Microsoft’s sample databases is not doing much help to me (I am located in Bulgaria after all and wanted to produce a sample report that is over a “known territory”). So my struggle began.

Today was the third day I was looking for any geographical data regarding Bulgarian cities and it seemed that my search was doomed. However, just an hour ago, I found some data in the web site of USA’s National Geospatial-Intelligence Agency (http://earth-info.nga.mil/gns/html/cntry_files.html). It seemed that there is hope after all :-). I downloaded the file with data for Bulgaria and imported it into my MSSQL instance. Of course the data there was not directly converted into spatial data but contained the longitude and latitude of all cities in my country. So I had to create a new column with data type GEOGRAPHY and use a very simple function to convert longitude and latitude to POINT:

<em><font color="#0000ff">UPDATE [BulgarianCities]
SET [GeoSpatialLocation] = geography::STPointFromText('POINT(' + CAST([LONG] AS VARCHAR(20)) + ' ' + 
                    CAST([LAT] AS VARCHAR(20)) + ')', 4326)
</font></em>

For additional reference for STPointFromText you can go here.

So I finally had everything needed in order to proceed with my first report with integrated map. How it turned out, you can find in one of my next posts.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.