Spent the last day or so cooking up a quick demo for our marketing guys. You can check it out here: http://65.101.234.201/mac/ 

It's pretty basic - it shows GPS points and photos from our mobile asset collection vehicle.

The demo is a small data set, but the points are in SQL 2008. They are clustered on the fly, and sent to the browser. As we add more data into this, I suspect that we will render a roads layer as tiles, and then add click handlers to the map which will allow a user to select a road segment, which will then go and get the points. We may also look at thinning out the points a bit more - partially so there is less data send to the browser, and partly because few users need to see every single photo.

The popup shows the two camera views as small thumbnails, with Next/Prev buttons to view... drum roll... the next/previous photos.

If you click on a photo in the pop-up it will display a larger set of images in a YUI panel. Again with the next/prev.

In the other SQL 2008 stuff I've done, I used stored procs to get the data, but this time I opted for dynamic sql, and it works quite nicely - also will make it easier to add more data into the mix (no need for additional sprocs)

I've been working with Dojo on another project, and I must say that the Yahoo User Interface library (aka YUI)  is refreshingly simple - if you don't need a whole ton of stuff, definitely look at YUI before resorting to Dojo or ExtJs.

Right now this site is clearly really limited but that's the nature of "demos". I'll be adding more functionality to this site (additional data sets etc), and if anything interesting comes up I'll post about it.

I've been working with Virtual Earth and SQL 2008 - mainly pulling points out of the database, clustering them, and throwing them on the map. All well and good, but I was getting some weirdness when zoomed out to the first of second zoom levels.

Specifically I was getting this error:

The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation.

The issue was that the bounding box of the map view port was more than a "hemisphere" in size, and so I was going to have to split up these queries into SQL Server. Now that sounds simple enough, but took some experimenting to work out exactly how to do this. Steve Kass (I believe of the SQL Server team @ Microsoft) has a post on the hemisphere requirement that talks about ring-direction. While we're on that sub-topic, ring-direction matters a lot here. The "left side of the line" is the "inside", and if you get it wrong you will also get this same exception.

So - my observations - it seems that your geometry has to be less than a hemisphere. The "exceeds" in the error message is a red herring.

For example the "western hemisphere" as defined below will throw an exception

However, if we tuck these values in a little bit...

All is well again.

Further experiments seem to indicate that which way you cut the globe matters as well. Despite having 1000's of points in the souther hemisphere, this query does not return any points

If I then split the southern hemisphere into two parts - East and West, I get the points again...

and

So - it would seem that the "hemisphere" they are referring to is really an EAST - WEST range. As long as your query geometry is less than 180 degrees wide, things are pretty good. Take this query, which is a patch that is ~180 wide and 90 high centered over 0,0.

This works just fine, so clearly we are not restricted by the actual Lat/Lon values just the range of values.

So as far as I can tell the rules for a valid geometry are:
1) Latitude (Y) Range must be less than or equal to 90 degrees

2) Longitude (X) Range must be less than 180 degrees

Hope this helps someone else avoid a few hours of head scratching and manual SQL querying.

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!