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:

UPDATE [BulgarianCities]
SET [GeoSpatialLocation] = geography::STPointFromText('POINT(' + CAST([LONG] AS VARCHAR(20)) + ' ' + 
                    CAST([LAT] AS VARCHAR(20)) + ')', 4326)

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s