Loading Collections into Virtual Earth v6

Update: Keith from the VE team advised on the 17th November 2007 that this issue is now fixed. Closer to 3 weeks than the advised 3 days, but at least it’s resolved.

Microsoft launched version 6 of the Virtual Earth API last week. Keeping with the product’s tradition, they broke some core features too. In particular, the ability to load a collection from maps.live.com directly in to the map control.

We use this approach on a number of our sites (the latest being visitscandinavia.com.au) because it basically gives us the mapping CMS for free. The client can create pushpins with text and photos, draw lines and polygons, all in the maps.live.com interface. They then just copy-paste the collection ID into our web CMS.

The problem is that in V6, the load method doesn’t throw any errors but it also doesn’t load any pins. We tried rolling back to V5, but that just brought back old bugs (like the pushpin popups appearing in the wrong place if you actually use a proper CSS column layout instead of tables).

This is the workaround I came up with (inspired by http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2276610&SiteID=1).

Loading of GeoRSS feeds still work fine, and we can get to our collections as GeoRSS feeds (the UI is a bit convoluted, but you can do it). Of course, we can’t load the feed directly from maps.live.com though because that would be a cross-domain call.

The proxy to get around this is pretty simple – just a generic handler (ASHX) in ASP.NET:

namespace SqueezeCreative.Stb.WebUI
    public class VirtualEarthGeoRssLoader : IHttpHandler
        public void ProcessRequest(HttpContext context)
            string collectionId = context.Request[“cid”];
            string geoRssUrl = string.Format(“http://maps.live.com/GeoCommunity.asjx?action=retrieverss&mkt=en-us&cid={0}”, collectionId);

            WebRequest request = WebRequest.CreateDefault(new Uri(geoRssUrl));
            WebResponse response = request.GetResponse();

            string geoRssContent;
            using (StreamReader reader = new StreamReader(response.GetResponseStream()))
                geoRssContent = reader.ReadToEnd();

            context.Response.ContentType = “text/xml”;

        public bool IsReusable
            get { return false; }

We can then call this handler from out client-side JS like so (where B33D2318CB8C0158!227 is my collection ID):

var layer = new VEShapeLayer();
var veLayerSpec = new VEShapeSourceSpecification(VEDataType.GeoRSS, ‘VirtualEarthGeoRssLoader.ashx?cid=B33D2318CB8C0158!227’, layer);
map.ImportShapeLayerData(veLayerSpec, function() {}, true);

Their ETA for fixing this was 3-5 days, but that seems to have gone out the window.

I hope this helps in the mean time!

Geographic Proximity Searches in SQL 2005

Disclaimer: I don’t actually know anything about SQL performance. The techniques described below have been gleaned from other sources, seem to work for me, and it makes sense why they would. If you know more about SQL, please correct me. ūüôā

Talking to Darren around Tech.Ed today, he expressed a need for a way to do proximity searches in SQL. These are queries along the lines of “give me all the records that talk about locations within 50km of X”. Now, in SQL 2008 this is baked into the product, but for SQL 2005 and below we need to do the maths ourselves.

The general query is relatively simple: calculate the distance-from-X for each row, then filter by the distance. Performing 6 trigonometric operations for every row, on every query is a pretty sure way to kill your average database though.

Instead, we add some persisted calculated columns to our table like so:

CREATE TABLE [dbo].[Locations]
    [LocationID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Locations_LocationID] DEFAULT (newid()),
    [Title] [varchar](100) NOT NULL,
    [Latitude] [decimal](9, 6) NOT NULL,
    [Longitude] [decimal](9, 6) NOT NULL,
    [ProximityX]  AS (cos(radians([Latitude]))*cos(radians([Longitude]))) PERSISTED,
    [ProximityY]  AS (cos(radians([Latitude]))*sin(radians([Longitude]))) PERSISTED,
    [ProximityZ]  AS (sin(radians([Latitude]))) PERSISTED,

        [LocationID] ASC

Basically, we take the reusable parts of our calculation and store them between queries. Storing these precalculated components means that our comparisons can be done using relatively simple maths and only one trig function per row.

Because they are persisted calculated columns, they are only calculated when the row is created or updated. SQL manages this for us.

Finally, here’s a stored procedure to query that table:

CREATE PROCEDURE [dbo].[FindLocationsNearby]
   @CenterLat float,
   @CenterLon float,
   @SearchRadius float
    –Store the radius of the earth (in km so that we can search by km)
    declare @EarthRadius float
    set @EarthRadius = 6378.14

    –Calculate the X, Y and Z axis values for the center point
    declare @CenterX float
    declare @CenterY float
    declare @CenterZ float
    set @CenterX = cos(radians(@CenterLat)) * cos(radians(@CenterLon))
    set @CenterY = cos(radians(@CenterLat)) * sin(radians(@CenterLon))
    set @CenterZ = sin(radians(@CenterLat))

    –Perform the database search
    SELECT    Title,
            Distance = @EarthRadius * acos(ProximityX * @CenterX + ProximityY * @CenterY + ProximityZ * @CenterZ)
    FROM    Locations
    WHERE    @EarthRadius * acos(ProximityX * @CenterX + ProximityY * @CenterY + ProximityZ * @CenterZ) <= @SearchRadius
    ORDER BY Distance ASC

This technique was extracted from an Access/SQL 2000 article I found on MSDN.

CSS Tip: Hover effects done properly

(This post is also available in Spanish, translated by Maria Ramos from Webhostinghub.com)

I was checking out Readify’s cool new TFS Now! offering this evening. Under the hood, they’ve solved quite a number of technical challenges which you can hear about during DEV316 at Tech.Ed next week.

This post was triggered by something that jumped out at me when looking at their consumer facing site though… In reality, you’ll find what I talk about here on almost every website that uses hover effects. I’ve chosen to use the TFS Now! site as a the example here because it’s a fairly simple real world page that clearly demonstrates the issue.

On their homepage, you’ll notice these nice big bananas:


When you hover over one of them, you get some feedback:


All good so far. The CSS used to achieve this is rather simple:

div#pageContainer div.homepage-sections div.benefits a {
    background:transparent url(../yellow-button.png) no-repeat scroll 0%;

div#pageContainer div.homepage-sections div.benefits a:hover {
    background:transparent url(../yellow-button-hover.png) no-repeat scroll 0%;

Firing up a copy of Fiddler shows pretty clearly that IE loads both the normal image, and hover image as part of the page load:


When you mouse-over the button, IE has the hover image cached so you get the rollover effect instantly.

This is where the problem starts…

When you mouse-off, IE re-loads the normal image. Even though the image is in the client side cache, IE still decides that it needs to call the server to check for any updates. During this time, you get a noticeable flash where there is no image displayed at all. You can see the calls in Fiddler, and the Flash is noticeable even on most localhost tests.


The Solution

It took me a few years of web development to¬†come across a solution for this that didn’t involve the messy pre-loaders and ugly JavaScript hover scripts prominent throughout the 90s. In the end, the solution was remarkably simple and provided a number of other benefits. They’re called sprites.

First up, combine the two image assets into one. For this example, the buttons were 215x98px so I placed them on a 215x196px canvas in Photoshop:


Even if you have unused pixels in one of the grid spaces, keep them both the same size.

This can now be applied using even less CSS then we had before:

div#pageContainer div.homepage-sections div.benefits a {
    background: transparent url(../yellow-button.png) no-repeat left top;

div#pageContainer div.homepage-sections div.benefits a:hover {
    background-position: left -98px;

You’ll notice that in the hover rule, instead of changing the image I’m now just sliding the image further up within the element.

Loading both styles in the one resource solves the flashing issue, as the image is never actually changing – it’s just moving. We also get some instant IO savings as a result:


While¬†1.7KB might not seem like much, you need to remember that in this scenario that equates to an almost 30% saving. And that doesn’t even include the 798 bytes of HTTP headers we¬†saved too.

Extending this idea and including all¬†six states (two states¬†each for¬†three buttons) as a 3×2 grid gives us even better savings.

The next thing to remember is that you can only have two simultaneous HTTP connections to the same server. Considering the homepage of TFS Now! currently triggers a total of 25 requests, there’s a lot of blocking going on there. Just by optimizing these buttons, we could reduce that by 20%.

The Benefits

All up, with this simple change we:

  • Removed the blank flash on mouse-out
  • Reduced data¬†transfer by¬†~30%
  • Reduced server requests by¬†20%, and thus noticeably improved page load time
  • Removed almost duplicate code from the CSS

At this point, I’d say it isn’t worth changing, however¬†it’s a useful technique to keep in mind next time you’re building out a site.

Particularly in cases where you have lots of similar-sized images, such as toolbar icons for a web-interface, you can get significant benefits to both real and perceived performance by loading them as a single image rather than as a series of blocking requests.

Update: Following up from Mitch’s concern that different browsers may have issues moving the image, I can confidently reply that this technique has worked everywhere I’ve ever tried to use it.¬†At Squeeze Creative, everything is tested in IE6 PC, IE7 PC, FF1.5 PC, FF2.0 PC, S3.0 PC, O9 PC, S1.3 Mac, S2.0 Mac, S3.0 Mac, FF1.5 Mac, FF2.0 Mac, and O9 Mac. All up, that’s a pretty exhaustive set of tests. Historically we have tested even more platforms (like FF1.0) and this technique stood up pefectly then too. I focussed on IE7 throughout the post as it shows the clearest ‘flash’, however the problem and the solution are relevant to all browsers.


Was this post useful for you? Let me know!