Recent work has given me my first real exposure to the spatial data types of SQL Server. Even though I’m not using these directly, as they are nicely abstracted behind already existing stored procedures and functions, I do find getting to know them very interesting. One problem with the reporting project I’m busy with though, is that the current Entity Framework (EF) RTM does not support the spatial data types, and I’ve had to use good ol’ ADO.NET and typed data sets instead.
I thought I’d do a little research into spatial data in EF over the weekend, as was delighted to find that the Entity Framework June 2011 CTP caters for spatial data. I have Jason Follas to thank for this, for his Entity Framework Spatial: First Look blog post. In the work I’m doing right now, a common task is finding e.g. all the suburbs in a given radius from one particular suburb, and Jason’s very first example query is a very similar distance query. When I saw that, I knew my research was going to be fun.

To illustrate how to find closest suburbs to a given suburb, I have created a simple EF data model, with just a Suburb table, which has one spatial field of SQL Server data type geometry:

Here the Geometry property contains a polygon that describes the borders of the suburb, with vertices defined in terms of degrees latitude and longitude. This shows just one of the neat DdGeometry methods, Distance. The two null checks in the CenterDistanceFromCenterDistanceFrom method are because a DbGeometry may not always be a shape, and thus not always have a valid Centroid property:

public class Suburb
{
    public int Id { get; set; }
    public string PostalCode { get; set; }
    public string Name { get; set; }
    public int CityId { get; set; }
    public DbGeometry Perimeter { get; set; }

    public double? ClosestDistanceFrom(Suburb there)
    {
        if ((there == null) || (there.Perimeter == null))
        {
            return null;
        }
        return Perimeter.Distance(there.Perimeter);
    }

    public double? CenterDistanceFrom(Suburb there)
    {
        var centerHere = this.Perimeter.Centroid;
        var centerThere = there.Perimeter.Centroid;

        if ((centerHere != null) && (centerThere != null))
        {
            return centerHere.Distance(centerThere);
        }
        return null;
    }
}

And now for the part you’ve all been waiting for, a LINQ to Entities query using a spatial column:

public IEnumerable GetClosestSuburbs(Suburb homeSuburb)
{
    var dbContext = new GeometryEntities();
    var weAreHere = dbContext.Suburbs.FirstOrDefault(s => s.Name == homeSuburb.Name);
    if (weAreHere == null)
    {
        return new List();
    }
    var inRadius = dbContext.Suburbs
                            .Where(s => s.Name != homeSuburb.Name)
                            .OrderBy(s => s.Perimeter.Distance(homeSuburb.Perimeter))
                            .Take(20)
                            .ToList();
    return inRadius;
}

In the above code, I first retrieve my home suburb, Morninghill, and then query for the twenty closest suburbs, by ordering by distance from Morninghill, ascending. Distance, as used in this example, is the distance between the ‘centre’ of Morninghill and another suburb, hence my using the Centroid property, which returns a Point that is effectivly the ‘centre’ of a Polygon. Properties and functions like Centroid are available out of the box with the new Spatial data types.
CodeProject