I spent today doing some research on using SQL 2008 as a back end for a Virtual Earth application. Here’s a rough breakdown of the steps I took to get some stuff up and running.
Get All the Parts and Pieces
First, download the November CTP of SQL Server 2008
I originally installed the Express version, but that did not include the Management Studio. Install the full version by downloading the DVD Image. You can mount the image using MagicISO Mounter
During the installation, select everything to ensure that you get the Management Studio and SQL Server Integration Services (aka “SQL Server Business Intelligence Development Studio”).
Connecting from Visual Studio 2005
Download the Visual Studio 2005 patch allows it to connect to SQL 2008
This will allow you to connect to the database engine, and while the connection works, Visual Studio tries to parse the queries and complains on spatial data types. You get a message like this, and then the query proceeds.
Getting Started…
I started with John O’Brien’s post and code titled “Virtual Earth and SQL 2008 Spatial – a first impression”. Once I got this running, I started to experiment with my data.
Loading X,Y Data into SQL 2008 Geometry
My test data set has 250,000 records, with a Lat and Long stored as fields in the table. My first hurdle was to get the table from a SQL 2005 instance to my 2008 instance. Enter SQL Server Integration Services – Microsoft’s ETL platform. This is essentially an add-in that puts more functionality into Visual Studio.
I created a new Integration Services Project in Visual Studio…
and added an ADO.NET Data Flow Source and an ADO.NET Data Flow Destination to the design canvas.
When setting up the destination, I was prompted to create the table which was handy. It’s important to note that these tools do not know about the spatial data types so I could not add the geography column until the data was transferred.
Add Geometry Column and Load
I added the “Location” geography column did this right in SQL Management Studio 2008 – very simple. Then I used some SQL to convert the Latitude / Longitude columns into points in the Location column:
update TestPoints
set location =
geography::STPointFromText(‘Point(‘ + CAST(Latitude as varchar(50)) + ‘ ‘ + cast(Longitude as varchar(50)) + ‘)’, 4326)
from TestPoints
where Latitude is not null and longitude is not null and Latitude <= 90 and latitude >=-90
The data I was working with had a few instances where the Lat and Long were reversed, hence the where clause.
Viewing in Virtual Earth
As I mentioned earlier, I used John O’Brien’s sample code to get things up and running quickly. I just had to change the connection string in his code, create some new stored procs that used my table instead of his, and change the call in his Web Service. And Voila…
Overall Impression
So this is pretty simple stuff really – show some points on the map. But most of my effort was in getting SQL 2008 installed with all the bells and whistles, and getting Visual Studio to talk to it. Beyond that things went really smoothly.
No doubt that there are many issues left – not the least of which is actually dealing with the 250,000 points. Luckily John has a post on implementing point clustering!
I will also be looking at the Vector Tiling stuff as I’ve got some polygons that need to be put into the map as well – but that will be another post!
