Wednesday, February 13, 2008
SQL 2008 + VE: Kicking the Tires
Posted on Wednesday, February 13, 2008 11:49:55 PM (Mountain Standard Time, UTC-07:00)  Comments [3] | 
Categories: SQL 2008 | Virtual Earth

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.

image

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...

isp-project

and added an ADO.NET Data Flow Source and an ADO.NET Data Flow Destination to the design canvas.

IntegrationServices

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...pts-in-ve

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!

Wednesday, February 13, 2008 4:08:09 PM (Mountain Standard Time, UTC-07:00)
First we used GML for our shape feeds, now we are using GeoRss and now i think we are going to try KML.
Max Cutrell
Wednesday, February 13, 2008 7:10:10 PM (Mountain Standard Time, UTC-07:00)
Here's how I loaded data from a shapefile to SS 2008 Spatial
1. Used MapServer .NET to write a text file of each records point as WKT and a few attribute fields.
2. Using the SS 2008 management window, I created a database, then two tables - 1) one that would be the destination table with a geometry field, and 2) one just like it, but the geometry field was actually a nvarchar field.
3. I did a bulk import of the text file to the second table above, then did a "select into" from the second table to the first table. The "select into" command automatically converted the WKT to the binary shape when adding records to the spatial table.

It took about 5 minutes to run the entire process on a 300,000+ point table. Initial spatial performance was sluggish, but once I created a spatial index, it was pretty zippy.

BTW, I've used MapServer to publish a 500K+ point layer to VE with excellent rendering performance (once the file was indexed using shptree.exe). That eliminates the need to do things like point clustering, but you need to write your own function to show a point's attributes when a user clicks or hovers.
Tim Maddle
Wednesday, February 13, 2008 7:17:30 PM (Mountain Standard Time, UTC-07:00)
I should make it clear in my previous post - I overlaid the point layer as an image overlay using code based on the WMS overlay example you pointed to in an earlier post. My zoom level was fairly close at 15, but each image was dynamically generated and it was still pretty quick.
Tim Maddle
Comments are closed.