A View Inside My Head

Jason's Random Thoughts of Interest


Entity Framework Spatial: A Real World Example


From the Wikipedia article, Leadership in Energy and Environmental Design (LEED) is described as “an internationally recognized green building certification system, providing third-party verification that a building or community was designed and built using strategies intended to improve performance in metrics such as energy savings, water efficiency, CO2 emissions reduction, improved indoor environmental quality, and stewardship of resources and sensitivity to their impacts.”

In my own words, LEED is a certification system that awards points for following certain environmentally-friendly practices when constructing a building. In the end, a building can be qualify for one of four different levels of certifications, based on the number of points: Certified, Silver, Gold, Platinum. There are often tax benefits associated with having a LEED certification, and many new government buildings (especially Federal) are required to be LEED certified.

Two points in particular (out of of 100, or so) from the LEED checklist are related to geospatial data. One point is awarded if at least 20% of the building materials (by cost) used in construction were manufactured within 500 miles of the job site. A second point is awarded if 10% of the raw materials of those building materials were extracted, harvested, or recovered within 500 miles of the job site.

As a window glass manufacturer, Tempuri Glass is often asked to provide data about its products that are being considered for use in construction. Tempuri Glass may have a certain advantage over its competitors if it can quickly show that its products would count towards these two points for any arbitrary job site.


Tempuri is a simple organization, making only a single type of product (Soda Lime glass) that is then cut into different sizes per order. Therefore, regardless of how many different sized glass panes are produced by a given facility, the ingredients for that glass is the same. The formulas used will be different between facilities, though, since the raw ingredients will be sourced from different locations, and adjustments may need to be made to the ratios due to environmental factors (things like: elevation, temperature, humidity, etc).

So, for our data model, we just need to know where each facility is, and then the formula used to make the glass at that facility (including the ingredients of that formula and the location where they were harvested from).


Within the data store, the [Geocode] columns of the Facility and FormulaComponent tables use the SQL Server geography type. This is useful for the large-scale/real-world distance calculations that Tempuri Glass needs to perform, since the way that you calculate distance on an sphere or ellipsoid (like the Earth) is vastly different than on a flat map.

In the Entity Framework model (using the June 2011 CTP), the SQL Server geography types are mapped as the new System.Data.Spatial.DbGeography type. This makes the geospatial data a first class citizen of our data model, and not just a castable opaque BLOB, as was the case in the past.

Geospatial data can take on many forms, including Points, Line Strings, Polygons, and collections of these shapes. Even though it’s not apparent from the data model, our [Geocode] data will contain only Points (i.e., a single Latitude/Longitude pair). Likewise, a job site will be specified as a single Point, though there is no hard requirement for this because distance can still be calculated between a Polygon and a Point with no coding change required.

Facility Sample Data







Greenfield, IA



POINT (-94.4547843933106 41.3151755156904)


Spring Green, WI

Spring Green


POINT (-90.053981 43.17431)


Tomah, WI



POINT (-90.477058 43.989319)


Fremont, IN



POINT (-84.9314403533936 41.7186070559443)


Fargo, ND



POINT (-96.8667125701904 46.8985894795683)


Waxahachie, TX



POINT (-96.8427014350891 32.4424403136322)


Hood River, OR

Hood River


POINT (-121.51526927948 45.630620334868)


Vinton, VA



POINT (-79.863876 37.263329)


Casa Grande, AZ

Casa Grande


POINT (-111.78155422210693 32.882073958767954)


Mountain Top, PA

Mountain Top


POINT (-75.896477 41.141327)


Winlock, WA



POINT (-122.926218509674 46.5449155194259)


Durant, OK



POINT (-96.4133548736572 34.0001619910696)


Mooresville, NC



POINT (-80.7865476608277 35.6316281732984)


FormulaComponent Sample Data









Genola, UT

POINT (-111.808204650879 40.0098667779887)


Silica Sand


Houck, AZ

POINT (-109.241695404053 35.2062151838369)


Soda Ash


Trona, CA

POINT (-117.311668395996 35.6955040738332)




Genola, UT

POINT (-111.808204650879 40.0098667779887)


Silica Sand


Houck, AZ

POINT (-109.241695404053 35.2062151838369)


Soda Ash


Trona, CA

POINT (-117.311668395996 35.6955040738332)




Chicago, IL

POINT (-87.6176834106445 41.5738476278005)


Silica Sand


Overton, NV

POINT (-114.4313621521 36.5146030619859)


Soda Ash


Green River, WY

POINT (-109.448783397675 41.5090754257687)


Spatial Querying Algorithm

Input: Job Site Latitude/Longitude


A. Query for closest facility to Job Site within 500 miles:

  1. Calculate the distance between the job site and each facility.
  2. Filter the list of facilities to just those where distance < 500 miles.
  3. Order the list of facilities by distance in ascending order.
  4. The first element (if any) will be the closest facility, and also signifies that the product qualifies as being manufactured within 500 miles

B. If there is a facility within 500 miles, then sum the percentage of formula components that were sourced from within 500 miles of the Job Site:

  1. Calculate the distance between the job site and each of the facility’s formula components
  2. Filter the list of formula components to just those where distance < 500 miles
  3. Sum the Percentages

Output: Boolean of whether the product qualifies; Percentage of the product’s ingredients that qualifies.


Before we can calculate distance using an instance method of the DbGeography type, we need to actually create an instance to represent the Job Site. DbGeography is immutable and does not have a constructor, so instead, a static method must be called to create a new object. There are a number of these factory methods available to create specific kinds of shapes (Point, Line String, Polygon, etc) given different kinds of input (text, byte arrays).

For simplicity, let’s use the .Parse() method, which accepts Well-Known Text (WKT) as input, and assumes a Spatial Reference ID of 4326 (the same coordinate system that GPS and internet mapping sites use).

Note: WKT uses a (Longitude, Latitude) ordering for points, which adheres to the same concept as (X, Y) ordering for Cartesian coordinates.

private static DbGeography CreatePoint(double latitude, double longitude)
return DbGeography.Parse(String.Format("POINT({1} {0})", latitude, longitude));

The first spatial query, written as a LINQ expression, finds the closest qualifying facility. Since SRID 4326 uses meters as the unit of measure, we need to convert 500 miles into meters within the predicate:

private Facility GetNearestFacilityToJobsite(DbGeography jobsite)
    var q1 = from f in context.Facilities
    let distance = f.Geocode.Distance(jobsite)
    where distance < 500 * 1609.344
    orderby distance
    select f;

return q1.FirstOrDefault();

Assuming that a facility was returned, a second LINQ expression can be used to find the sum of Percentage from qualifying Formula Components:

private decimal SumQualifyingPercentages(Facility nearestFacility, DbGeography jobsite)
var q2 = from fc in nearestFacility.Formula.FormulaComponents
where fc.Geocode.Distance(jobsite) < 500 * 1609.344
select fc;

return q2.Sum(c => c.Percentage.GetValueOrDefault(0));

Finally, putting all of the parts together (using a Tuple<> for the output):

private Tuple<bool, decimal> GetResults(double latitude, double longitude)
DbGeography jobsite = CreatePoint(latitude, longitude);
Facility nearestFacility = GetNearestFacilityToJobsite(jobsite);

if (nearestFacility != null)
return new Tuple<bool,decimal>(true, SumQualifyingPercentages(nearestFacility, jobsite));

return new Tuple<bool, decimal>(false, 0);

private void PerformQuery()
double latitude = 47.63752;
double longitude = -122.13343;

var results = GetResults(latitude, longitude);

Entity Framework Spatial: DbGeography and DbGeometry Members

DbGeography Static Property Return Type DbGeometry Static Property Return Type
DbGeography.DefaultSrid int DbGeometry.DefaultSrid int
DbGeography Static Method Return Type DbGeometry Static Method Return Type
DbGeography.FromBinary(byte[] geographyBinary, int srid) DbGeography DbGeometry.FromBinary(byte[] geometryBinary, int srid) DbGeometry
DbGeography.FromGml(string geographyMarkup, int srid) DbGeography DbGeometry.FromGml(string geometryMarkup, int srid) DbGeometry
DbGeography.FromText(string geographyText, int srid) DbGeography DbGeometry.FromText(string geometryText, int srid) DbGeometry
DbGeography.GeographyCollectionFromBinary(byte[] geographyBinary, int srid) DbGeography DbGeometry.GeometryCollectionFromBinary(byte[] geometryBinary, int srid) DbGeometry
DbGeography.GeographyCollectionFromText(string geographyText, int srid) DbGeography DbGeometry.GeometryCollectionFromText(string geometryText, int srid) DbGeometry
DbGeography.LineFromBinary(byte[] geographyBinary, int srid) DbGeography DbGeometry.LineFromBinary(byte[] geometryBinary, int srid) DbGeometry
DbGeography.LineFromText(string geographyText, int srid) DbGeography DbGeometry.LineFromText(string geometryText, int srid) DbGeometry
DbGeography.MultilineFromBinary(byte[] geographyBinary, int srid) DbGeography DbGeometry.MultilineFromBinary(byte[] geometryBinary, int srid) DbGeometry
DbGeography.MultilineFromText(string geographyText, int srid) DbGeography DbGeometry.MultilineFromText(string geometryText, int srid) DbGeometry
DbGeography.MultipointFromBinary(byte[] geographyBinary, int srid) DbGeography DbGeometry.MultipointFromBinary(byte[] geometryBinary, int srid) DbGeometry
DbGeography.MultipointFromText(string geographyText, int srid) DbGeography DbGeometry.MultipointFromText(string geometryText, int srid) DbGeometry
DbGeography.MultipolygonFromBinary(byte[] geographyBinary, int srid) DbGeography DbGeometry.MultipolygonFromBinary(byte[] geometryBinary, int srid) DbGeometry
DbGeography.MultipolygonFromText(string geographyText, int srid) DbGeography DbGeometry.MultipolygonFromText(string geometryText, int srid) DbGeometry
DbGeography.Parse(string geographyText) DbGeography DbGeometry.Parse(string geometryText) DbGeometry
DbGeography.PointFromBinary(byte[] geographyBinary, int srid) DbGeography DbGeometry.PointFromBinary(byte[] geometryBinary, int srid) DbGeometry
DbGeography.PointFromText(string geographyText, int srid) DbGeography DbGeometry.PointFromText(string geometryText, int srid) DbGeometry
DbGeography.PolygonFromBinary(byte[] geographyBinary, int srid) DbGeography DbGeometry.PolygonFromBinary(byte[] geometryBinary, int srid) DbGeometry
DbGeography.PolygonFromText(string geographyText, int srid) DbGeography DbGeometry.PolygonFromText(string geometryText, int srid) DbGeometry
DbGeography Instance Property Return Type DbGeometry Instance Property Return Type
g.Area double? g.Area double?
    g.Boundary DbGeometry
    g.Centroid DbGeometry
    g.ConvexHull DbGeometry
g.Dimension int g.Dimension int
g.EndPoint DbGeography g.EndPoint DbGeometry
    g.Envelope DbGeometry
    g.ExteriorRing DbGeometry
g.GeometryType string g.GeometryType  
g.IsClosed bool? g.IsClosed bool?
g.IsEmpty bool g.IsEmpty bool
    g.IsRing bool?
    g.IsSimple bool
    g.IsValid bool
g.Latitude double?    
g.Length double? g.Length double?
g.Longitude double?    
g.M double? g.M double?
g.NumGeometries double? g.NumGeometries int?
    g.NumInteriorRing int?
g.NumPoints int? g.NumPoints int?
    g.PointOnSurface DbGeometry
g.ProviderValue object g.ProviderValue object
g.Srid int g.Srid int
g.StartPoint DbGeography g.StartPoint DbGeometry
g.WellKnownValue WellKnownValue DbGeographyWellKnownValue g.WellKnownValue DbGeometryWellKnownValue
g.WellKnownValue.Srid int g.WellKnownValue.Srid int
g.WellKnownValue.WellKnownBinary byte[] g.WellKnownValue.WellKnownBinary byte[]
g.WellKnownValue.WellKnownText string g.WellKnownValue.WellKnownText string
    g.X double?
    g.Y double?
g.Z double? g.Z double?
DbGeography Instance Method Return Type DbGeometry Instance Method Return Type
g.AsBinary() byte[] g.AsBinary() byte[]
g.AsGml() string g.AsGml() string
g.AsText() string g.AsText() string
g.Buffer(double distance) DbGeography g.Buffer(double distance) DbGeometry
    g.Contains(DbGeometry other) bool
    g.Crosses(DbGeometry other) bool
g.Difference(DbGeography other) DbGeography g.Difference(DbGeometry other) DbGeometry
g.Disjoint(DbGeography other) bool g.Disjoint(DbGeometry other) bool
g.Distance(DbGeography other) double g.DistanceDbGeometry other) double
g.GeometryN(int index) DbGeography DbGeography g.GeometryN(int index) DbGeometry
    g.InteriorRingN(int index) DbGeometry
g.Intersection(DbGeography other) DbGeography g.Intersection(DbGeometry other) DbGeometry
g.Intersects(DbGeography other) bool g.Intersects(DbGeometry other) bool
    g.Overlaps(DbGeometry other) bool
g.PointN(int index) DbGeography g.PointN(int index) DbGeometry
    g.Relate(DbGeometry other, string matrix) bool
g.SpatialEquals(DbGeography other) bool g.SpatialEquals(DbGeometry other) bool
g.SymmetricDifference(DbGeography other) DbGeography g.SymmetricDifference(DbGeometry other) DbGeometry
    g.Touches(DbGeometry other) bool
g.Union(DbGeography other) DbGeography g.Union(DbGeometry other) DbGeometry
    g.Within(DbGeometry other) bool

Entity Framework Spatial: First Look

Today, I began to look into the Entity Framework June 2011 CTP which includes first class support for Spatial data types.  The ADO.NET team created an abstraction layer, based on the OGC simple features specification, with a goal being to support spatial implementations from multiple vendors.  As you would expect, SQL Server 2008 Spatial is supported out of the box.

For some reason, I was expecting a lot of work to be done on the client-side within their abstraction data type.  I was pleasantly surprised to see EF pass the heavy lifting to the underlying data store as part of the query.

For instance, I have a table in my database called Facility with a Geography column named [Geocode].  This field contains a point (single latitude/longitude pair) identifying the location of the Facility.  Even though this would normally be represented in client-side code as a SqlGeography type, EF wraps it in the new DbGeography type (i.e., the abstraction data type for ellipsoidal data).

My first query was a LINQ expression to return a list of all facilities that are within 500 miles of a given location:

var q = from f in context.Facilities
let p = DbGeography.Parse("POINT(-83 45)")
where f.Geocode.Distance(p) < 500 * 1609.344
select new { f.FacilityID, wkt=f.Geocode.AsText() };

A couple things about this query:

  1. The default SRID of 4326 is used.  This spatial reference system uses meters for distance, so my predicate needs to convert the 500 miles into meters.  Like the SqlGeography.Parse() method, DbGeography.Parse() will default to 4326.
  2. The return type is an anonymous type.  I wanted to see how the DbGeography type's .AsText() method was executed (i.e., would it be expressed in the resulting query, or would it be handled client side, etc).

When executed, the LINQ expression above generates the following TSQL:

SELECT [Extent1].[FacilityID] AS [FacilityID],
 [Extent1].[Geocode].STAsText() AS [C1]
FROM [dbo].[Facility] AS [Extent1]
WHERE ([Extent1].[Geocode].STDistance(geography::Parse(N'POINT(-83 45)'))) < cast(804672 as float(53))

As you can see, the DbGeography.AsText() was translated into the STAsText() method in the query.  And, as you might expect, the predicate's DbGeography.Distance() was properly translated into STDistance() in the TSQL WHERE clause.

The other thing that I was worried about was not having access to the actual SqlGeography type returned from the database.  I was surprised to see that EF's DbGeography has a property called ProviderValue that returns the native type that the provider supports!