bing-maps

Fixing SQL Server Spatial "Not a Valid Instance of Geography" Errors in C#

The project we're working on needs the ability for a user to define a polygon on a map, like this:

A screenshot of Bing Maps, showing a fully-created polygon

Then, we need to save that defined polygon as a SQL Geography type in our database, which we access using Entity Framework. EF supports the DbGeography type, so we are using that to actually send the polygons to the database.

NOTE: for this post, the database server is SQL Server 2008 R2, though I am not sure if the error presented below will occur in other versions of SQL Server.

In order to get to the point where we can create the DbGeography object, though, one of the things we do is submit a string in the well known text format, a commonly-accepted markup language for dealing with vector geometry (i.e. representing areas on a map). We were taking that well-known text and converting it into type DbGeography like so. Our well-known text might look like this:

"{{ "Polygon": "POLYGON((-112.27117 33.4646,-112.27186 33.54476,-112.23959 33.61741,-112.2286 33.666,-111.9972 33.67057,-112.01162 33.5768,-112.03771 33.56307,-112.0384 33.46117,-112.27117 33.4646))" }}"

Which we then converted into DbGeography by using the FromText() method:

var mapObject = DbGeography.FromText(wellKnownText);  

This worked for a little while, but then we started noticing a rather strange bug. If we created a polygon by defining the points in a counter-clockwise manner, then that polygon would be created with no issues. A counter-clockwise manner looks like this (first point created is in lower left, near Tolleson):

A screenshot of Bing Maps, showing a polygon being created counter-clockwise

However, if we created a polygon in a clockwise manner, the system would throw a nasty error (first point is again near Tolleson):

A screenshot of Bing Maps, showing a polygon being created clockwise

That nasty error that we started encountering was this doosy:

"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@1"): The supplied value is not a valid instance of data type geography. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision."

Which tells me absolutely nothing. It might as well have been written in Klingon.

I took to googling around, and came across a few interesting forum posts, one of which pointed me toward the right answer. The issue was that SQL Geography expects any polygons to be defined using the left-hand rule, which is this:

"If you walk along the polygon from the first point defined to each successive point, the interior of the polygon will be on your left-hand side."

When we are defining the polygons, doing so counter-clockwise places the interior of the polygon as the left-hand side of the imaginary line-walker. However, defining the polygons clockwise makes the exterior of the polygon the line-walker's left-hand side, and so SQL Server thought the polygon we were defining was the entire rest of the world, minus the polygon. Obviously SQL Server won't allow an area so huge (the upper limit for area in SQL Geography is a hemisphere), so it gave us the above error.

So now that we know what the error is (and we care about fixing it), how do we fix it? Like this:

  1. First take the well-known text string and create an instance of SQL Geography from it.
  2. Then, get the inversion of that object (e.g. get the complete area not "inside" the defined polygon). The two polygons, when combined together, will cover the entire world.
  3. Whichever of these two polygons is smaller is the one we want, so use that one.

The code for this looks like the following:

public static class DbGeographyExtensions  
{
    public static DbGeography CreatePolygon(string wellKnownText)
    {
        //First, get the area defined by the well-known text using left-hand rule
        var sqlGeography =
        SqlGeography.STGeomFromText(new SqlChars(wellKnownText), DbGeography.DefaultCoordinateSystemId)
        .MakeValid(); 

        //Now get the inversion of the above area
        var invertedSqlGeography = sqlGeography.ReorientObject(); 

        //Whichever of these is smaller is the enclosed polygon, so we use that one.
        if (sqlGeography.STArea() > invertedSqlGeography.STArea())
        {
            sqlGeography = invertedSqlGeography;
        }
        return DbSpatialServices.Default.GeographyFromProviderValue(sqlGeography);
    }

}

That works like a charm, at least for our uses. Hopefully it'll work for yours as well.

If this helps you out (or you have a better solution, or you can read Klingon), please let me know in the comments!

Happy Coding!

Geocoding with Bing Maps REST Services in .NET

A major project we're working on in my team (which I've alluded to before) requires that we implement geocoding for a given address. This means that, given a valid address, we should have the ability to find that address latitude and longitude coordinates so that we can store those values in our database.

A projection of the world, showing continents and oceans Winkel triple projection by Strebe, used under license

I've dealt with geocoding before, but never in a .NET server-side application and never using Bing Maps REST services, which is a requirement for this application. This post is mostly written so I'll be able to find it again, but hopefully some of you intrepid readers out there will get use out of it too.

Enough talk. Show me the code!

Setup

First of all, in order to even call the Bing Maps REST services you'll need a Bing Maps Key. If you want to use the code in this demo, be sure to supply your own, valid Bing Maps key.

Second, we need to know where the Bing Maps REST services exist. For this demo, we will use the Query service call using an unstructured URL. The basic format for that URL looks like this (I have omitted some of the query string options for brevity):

http://dev.virtualearth.net/REST/v1/Locations?query=locationQuery&maxResults=maxResults&key=BingMapsKey  

Whenever we make a request to this service, we will receive back a JSON response (though this is configurable). Which means that our system will need to be able to read and deserialize JSON objects. But what objects would the system deserialize to? Turns out, Microsoft has already provided the data contracts necessary for deserializing Bing Maps API data; all we have to do is copy those contracts to our local project.

Now, we've got three pieces: the location of the service, the key necessary to call the service, and the contracts necessary to read data returned by the service. Let's build some examples!

Geocoding an Address

First, let's imagine that we get an address that looks like this:

1700 W Washington St, Phoenix, AZ 85007

In order to make the call to the API, we'd insert this address into the URL format from above, so that our request would look like this:

http://dev.virtualearth.net/REST/v1/Locations?query=1700 W Washington St Phoenix, AZ 85007&key=BingMapsKey  

But how do we do that in code? We want to accept an address and return a latitude and longitude. Here's how we set that up:

public class LatLong  
{
    public double Latitude { get; set; }
    public double Longitude { get; set; }
}

public static class GeocodeHelper  
{
    public static LatLong Geocode(string address)
    {
        string url = "http://dev.virtualearth.net/REST/v1/Locations?query=" + address + "&key=BingMapsKey";
    }
}

OK great, now we've got the URL we need to hit to get results, but what do we use to actually make that request? We use a class called WebClient and a method called DownloadString:

string url = "http://dev.virtualearth.net/REST/v1/Locations?query=" + address + "&key=BingMapsKey";

using (var client = new WebClient())  
{
    string response = client.DownloadString(url);
}

Now, the response will be JSON, so we must find some way to translate that into the contracts we acquired from Bing earlier. We can do this using DataContractJsonSerializer and a MemoryStream:

string response = client.DownloadString(url);  
DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(Response));  
using (var es = new MemoryStream(Encoding.Unicode.GetBytes(response)))  
{
    var mapResponse = (ser.ReadObject(es) as Response); //Response is one of the Bing Maps DataContracts
}

Now, we have the response coded as Contracts. One last thing we want to do is take the latitude and longitude of the geocoded address and give them to our own LatLong class. We can do this like so:

Location location = (Location)mapResponse.ResourceSets.First().Resources.First();  
return new LatLongResult()  
{
    Latitude = location.Point.Coordinates[0],
    Longitude = location.Point.Coordinates[1]
};

Our finished Geocode method looks like this:

public static LatLong Geocode(string address)  
{
    string url = "http://dev.virtualearth.net/REST/v1/Locations?query=" + address + "&key=BingMapsKey";

    using (var client = new WebClient())
    {
        string response = client.DownloadString(url);
        DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(Response));
        using (var es = new MemoryStream(Encoding.Unicode.GetBytes(response)))
        {
            var mapResponse = (ser.ReadObject(es) as Response); //Response is one of the Bing Maps DataContracts
            Location location = (Location)mapResponse.ResourceSets.First().Resources.First();
            return new LatLongResult()
            {
                Latitude = location.Point.Coordinates[0],
                Longitude = location.Point.Coordinates[1]
            };
        }
    }
}

Running this method for the address we had above (1700 W Washington St Phoenix, AZ 85007) returns coordinates of 33.449001, -112.093643.

Reverse Geocoding

But what if we have a set of coordinates and want to get back the address? We can do this via reverse-geocoding the latitude and longitude.

First thing we need is a new URL format:

http://dev.virtualearth.net/REST/v1/Locations/point?key=BingMapsKey  

Note that point is latitude and longitude separated by a comma. So, using the latitude and longitude we got by geocoding the earlier address, the URL would look like this:

http://dev.virtualearth.net/REST/v1/Locations/33.449001,-112.093643?key=BingMapsKey  

Now we can write the method. Once we get the response back, the serialization code is the same. Here's the complete AddressResult and

public class AddressResult  
{
    public string AddressLine { get; set; }
    public string Locality { get; set; } //Roughly a city or town
    public string AdminDistrict { get; set; } //Roughly a state, province, or other similar area
    public string PostalCode { get; set; }
}

public static class GeocodeHelper  
{
    public static AddressResult ReverseGeocode(double latitude, double longitude)
    {
        using (var client = new WebClient())
        {
            var queryString = "http://dev.virtualearth.net/REST/v1/Locations/" + latitude.ToString() + "," + longitude.ToString() + "?key=BingMapsKey";

            string response = client.DownloadString(queryString);
            DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(Response));
            using (var es = new MemoryStream(Encoding.Unicode.GetBytes(response)))
            {
                var mapResponse = (ser.ReadObject(es) as Response);
                Location location = (Location)mapResponse.ResourceSets.First().Resources.First();
                return new AddressResult()
                {
                    StreetAddress = location.Address.AddressLine,
                    Locality = location.Address.Locality,
                    AdminDistrict = location.Address.Locality,
                    PostalCode = location.Address.PostalCode
                };
            }
        }
    }
}

If we feed back the coordinates we got from the geocoding earlier, we get the following address:

1570 W Washington St. Phoenix, AZ 85007

Notice that that is not the same address that we fed into the geocoding method. Geocoding is not 100% precise, especially when dealing with things like addresses (it is entirely possible to have the same street address in the same city but in different zip codes, and that's just one of the weird little things geocoding has to take into account). That said, in our system, where we needed to store a lat/long for every address entered, this solution works out just fine.

Summary

Bing Maps provides a full-featured REST API that allows consumers to geocode and reverse-geocode locations. We needed a way to access this API in our server-side code, and this solution seems to work for us. It's quick, it's relatively simple, and it gets us the data we desire.

There's a couple of downsides, though:

  • No async/await support. One of the MSDN samples does this. I'd like to add support for async/await in a later version.
  • Reverse geocoding is not very precise. Not something I expect to be able to fix.
  • I'm nitpicking, but there's a lot of string manipulation in the above samples. I feel like there should be a better way, I just dunno what it is.

Overall, we're pretty happy with this solution. It works for now, anyway.

Have any of you dear readers worked on geocoding in .NET, whether using Bing Maps or some other service? How did it go? I'd love to hear your stories in the comments.

Happy Coding!