Spatial

Entity Framework Spatial: A Real World Example

Jason Follas — Wed, 27 Jul 2011 17:57:46 GMT

Background

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, CO₂ 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.

Data

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

FacilityID	FacilityName	City	State	Geocode
1	Greenfield, IA	Greenfield	IA	POINT (-94.4547843933106 41.3151755156904)
2	Spring Green, WI	Spring Green	WI	POINT (-90.053981 43.17431)
3	Tomah, WI	Tomah	WI	POINT (-90.477058 43.989319)
4	Fremont, IN	Fremont	IN	POINT (-84.9314403533936 41.7186070559443)
5	Fargo, ND	Fargo	ND	POINT (-96.8667125701904 46.8985894795683)
6	Waxahachie, TX	Waxahachie	TX	POINT (-96.8427014350891 32.4424403136322)
7	Hood River, OR	Hood River	OR	POINT (-121.51526927948 45.630620334868)
8	Vinton, VA	Vinton	VA	POINT (-79.863876 37.263329)
9	Casa Grande, AZ	Casa Grande	AZ	POINT (-111.78155422210693 32.882073958767954)
10	Mountain Top, PA	Mountain Top	PA	POINT (-75.896477 41.141327)
11	Winlock, WA	Winlock	WA	POINT (-122.926218509674 46.5449155194259)
12	Durant, OK	Durant	OK	POINT (-96.4133548736572 34.0001619910696)
13	Mooresville, NC	Mooresville	NC	POINT (-80.7865476608277 35.6316281732984)

FormulaComponent Sample Data

FormulaComponentID	Name	Percentage	SourceLocation	Geocode
14	Limestone	13	Genola, UT	POINT (-111.808204650879 40.0098667779887)
1	Silica Sand	75	Houck, AZ	POINT (-109.241695404053 35.2062151838369)
27	Soda Ash	12	Trona, CA	POINT (-117.311668395996 35.6955040738332)
15	Limestone	13	Genola, UT	POINT (-111.808204650879 40.0098667779887)
2	Silica Sand	75	Houck, AZ	POINT (-109.241695404053 35.2062151838369)
28	Soda Ash	12	Trona, CA	POINT (-117.311668395996 35.6955040738332)
16	Limestone	13	Chicago, IL	POINT (-87.6176834106445 41.5738476278005)
3	Silica Sand	75	Overton, NV	POINT (-114.4313621521 36.5146030619859)
29	Soda Ash	12	Green River, WY	POINT (-109.448783397675 41.5090754257687)

Spatial Querying Algorithm

Input: Job Site Latitude/Longitude

Steps:

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

Calculate the distance between the job site and each facility.
Filter the list of facilities to just those where distance < 500 miles.
Order the list of facilities by distance in ascending order.
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:

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

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

Implementation

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 GetResults(double latitude, double longitude)
{
   DbGeography jobsite = CreatePoint(latitude, longitude);
   Facility nearestFacility = GetNearestFacilityToJobsite(jobsite);

   if (nearestFacility != null)
   {
      return new Tuple(true, SumQualifyingPercentages(nearestFacility, jobsite));
   }

   return new Tuple(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

Jason Follas — Thu, 21 Jul 2011 23:21:05 GMT

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

Jason Follas — Thu, 21 Jul 2011 01:56:01 GMT

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:

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.
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!

Spatial Data and the Entity Framework

Jason Follas — Sun, 14 Feb 2010 20:14:47 GMT

July 28, 2011 Note: This is an outdated post. Recently, the ADO.NET team has released a CTP with Spatial support as a first class citizen of the Entity Framework!!! See the following posts that I wrote as I explored the new API:

http://www.jasonfollas.com/blog/archive/2011/07/20/entity-framework-spatial-first-look.aspx

http://www.jasonfollas.com/blog/archive/2011/07/21/entity-framework-spatial-dbgeography-members.aspx

http://www.jasonfollas.com/blog/archive/2011/07/27/entity-framework-spatial-a-real-world-example.aspx

The Entity Framework does not support using User Defined Types (at least in the SQLCLR sense of the term) as properties of an entity. Yesterday, Julie Lerman contacted me to see if we could find a workaround to this current limitation, particularly for the SQL Server Spatial Types (Geometry and Geography).

Whenever I hear of someone wanting to use Spatial data in their application, my first thought is always “what do they want to do with the data once they have it?” This is because most of the time (in my limited observation), an application does not need the spatial data itself, but rather, it just needs to use that data in the predicate of a query (i.e., the query results contain no spatial information). For example, an application might want all zipcodes that are within 50 km of a point, but the application doesn’t need the actual shapes that define each zip code.

But, assuming that the developer knows what they are doing and has a legitimate reason to include a spatial type in the results, then how can they use the Entity Framework to get the spatial data into their application? That was our quest.

Entity Framework Primitive Types

Admittedly, I know very little about EF. So, my approach to this problem spent a lot of time using .NET Reflector to try to understand what the EF designer was doing behind the scenes (this also proved to be a a good way to understand EF better!). The first thing that I wanted to figure out is how EF determines which primitive type to use for each SQL Server type.

I downloaded and imported the States data from the US Census Data for SQL Server 2008 project on Codeplex. Then, I used the Entity Data Model Designer in VS2010 to generate a model based on my database which resulted in an entity without the geometry property. Looking at the XML for the .edmx file, I saw the following:


 <EntityType Name="State">
   <Key>
     <PropertyRef Name="StateID" />
   Key>
   <Property Name="StateID" Type="int" Nullable="false" />
   <Property Name="StateName" Type="nvarchar" Nullable="false" MaxLength="50" />
 EntityType>

I don’t believe that EF is hating on “geometry” specifically (the 6005 warning). Rather, I think that if the SQL Server type cannot be mapped to a .NET type from the BCL, then it simply does not know how to handle it. Certainly, they don’t want to try to map to a type that is not included in the .NET Framework itself (as would be the case for the Spatial data types).

But, what is EF using to determine the mappings?

I looked long and hard, but couldn’t quite figure out the mechanism that gets invoked when the model is generated. But, I think the key might lie in the Microsoft.VisualStudio.Data.Providers.SqlServer.SqlMappedObjectConverter.GetFrameworkTypeFromNativeType() method:

// Disassembly by Reflector protected override Type GetFrameworkTypeFromNativeType(string nativeType)
{
    switch (this.GetProviderTypeFromNativeType(nativeType))
    {
        case 0:
            return typeof(long);

        case 1:
        case 7:
        case 0x13:
        case 0x15:
            return typeof(byte[]);

        case 2:
            return typeof(bool);

        case 3:
        case 10:
        case 11:
        case 12:
        case 0x12:
        case 0x16:
            return typeof(string);

        case 4:
        case 15:
        case 0x1f:
        case 0x21:
            return typeof(DateTime);

        case 5:
        case 9:
        case 0x11:
            return typeof(decimal);

        case 6:
            return typeof(double);

        case 8:
            return typeof(int);

        case 13:
            return typeof(float);

        case 14:
            return typeof(Guid);

        case 0x10:
            return typeof(short);

        case 20:
            return typeof(byte);

        case 0x20:
            return typeof(TimeSpan);

        case 0x22:
            return typeof(DateTimeOffset);
    }
    return typeof(object);
}

For SQL Server, the Native Types come from the System.Data.SqlDbType enumeration:

// Disassembly by Reflector public enum SqlDbType
{
    BigInt = 0,
    Binary = 1,
    Bit = 2,
    Char = 3,
    Date = 0x1f,
    DateTime = 4,
    DateTime2 = 0x21,
    DateTimeOffset = 0x22,
    Decimal = 5,
    Float = 6,
    Image = 7,
    Int = 8,
    Money = 9,
    NChar = 10,
    NText = 11,
    NVarChar = 12,
    Real = 13,
    SmallDateTime = 15,
    SmallInt = 0x10,
    SmallMoney = 0x11,
    Structured = 30,
    Text = 0x12,
    Time = 0x20,
    Timestamp = 0x13,
    TinyInt = 20,
    Udt = 0x1d,
    UniqueIdentifier = 14,
    VarBinary = 0x15,
    VarChar = 0x16,
    Variant = 0x17,
    Xml = 0x19
}

My conclusion here was that if the SQL Server type could only be mapped to System.Object in the BCL (using the GetFrameworkTypeFromNativeType() method), then EF will not support using that field as a property of the entity. This coincides with the fact that to ADO.NET, the Geometry (and Geography) type is a User Defined Type (0x1d).

UPDATE: After all of this, I discovered that in System.Data.Entity.dll, there is a method that is probably a better candidate for what is actually used: System.Data.SqlClient.SqlProviderManifest.GetEdmType(). This method contains a similar switch{} as the code listed above, only it is EDM-specific instead of returning BCL types. Feel free to examine it using Reflector if you're curious about its contents.

The Workaround

Having figured out that piece of the puzzle, I was left with trying to figure out a workaround. If ADO.NET was unable to map a Geometry to a type in the BCL, then could we cast the Geometry as something that would be mappable?

SQL Server serializes spatial objects to binary when it saves the data in a table (documented here: http://msdn.microsoft.com/en-us/library/ee320529.aspx):

This binary data can be used to deserialize (“rehydrate”) the object in .NET code, which is exactly what SQL Server does when it needs to use the spatial objects. So, we just need to find a way for EF to pull these down as a byte array.

Looking back at the GetFrameworkTypeFromNativeType function from above, it appears that EF will likely recognize Binary, Image, Timestamp, and Varbinary all as SQL Server types that need to map to byte arrays. Perfect!

So, by creating a view in SQL Server that casts the Geometry column as a Varbinary(MAX), EF would recognize it as a type that could be mapped as an entity’s property.

CREATE VIEW vStates
AS  SELECT  StateID
          , StateName
          , CAST(geom AS VARBINARY(MAX)) AS geom
    FROM    dbo.State

Note: Julie had come up with this same solution at the same time, as our emails crossed paths reporting to one another.

Regenerating the EF model (using this view instead of the table) proved my assumption: the “geom” column now appeared as a Binary property of the vStates entity.

However, we’re not quite done yet. The point of this exercise was to get an instance of the spatial type to use in our .NET application. To do that, the Read(BinaryReader) instance method on SqlGeometry (or SqlGeography) must be invoked (using a MemoryStream as the intermediate between the byte[] and the BinaryReader).

The entire logic to retrieve the contents of the table and instantiate one of the Spatial types is as follows:

var entities = new SpatialEntities();
var vStates = entities.vStates;

// pull one of the entities from the collection var geo2 = vStates.ToArray()[16];  
var sqlGeom = new Microsoft.SqlServer.Types.SqlGeometry();

// Deserialize the bytes to rehydrate this Geometry instance using (var stream = new System.IO.MemoryStream(geo2.geom))
{
    using (var rdr = new System.IO.BinaryReader(stream))
    {
        sqlGeom.Read(rdr);
    }
}

// Now let's prove that we have it. Dump WKT to Debug. 
System.Diagnostics.Debug.Write(sqlGeom.ToString());

Output:

GEOMETRYCOLLECTION (LINESTRING (-99.530670166015625 39.132522583007812, -99.530670166015625 39.13250732421875), LINESTRING (-99.791290283203125 39.131988525390625, -99.791290283203125 39.131973266601562), …

So it worked!

Finally, an extension method would make this code a bit more general purpose:

public static class Extension
{
    public static Microsoft.SqlServer.Types.SqlGeometry AsSqlGeometry(this byte[] binary)
    {
        var ret = new Microsoft.SqlServer.Types.SqlGeometry();

        using (var stream = new System.IO.MemoryStream(binary))
        {
            using (var rdr = new System.IO.BinaryReader(stream))
            {
                ret.Read(rdr);
            }
        }

        return ret;
    }

The test code above then becomes a bit more readable after the refactoring:

var entities = new SpatialEntities();
var vStates = entities.vStates;

// pull one of the entities from the collection var geo2 = vStates.ToArray()[16];
var sqlGeom = geo2.geom.AsSqlGeometry();

// Now let's prove that we have it. Dump WKT to Debug. 
System.Diagnostics.Debug.Write(sqlGeom.ToString());

Helpful information:

For Jeff: Spatial Querying

Jason Follas — Wed, 04 Mar 2009 23:08:55 GMT

A quick sample of usage as requested by my friend Jeff McWherter:

Querying for all data in a table where the "zip code polygon" is within 20 miles of a geocode point (must convert 20 miles to meters in the predicate)::

select * 
from dbo.fe_2007_us_zcta500 
where Boundary.STDistance('POINT(-79.8884595930576 43.2609696686268)') < (20 * 1609.344)

Knowledge++ [4]

Jason Follas — Sun, 22 Feb 2009 02:45:26 GMT

I recently developed a spatially-aware .NET application that did not use SQL Server 2008 as the backend (this enterprise was still on SS2005, but we needed the spatial support in the application today). While the application worked properly on my laptop, it was a huge failboat when deployed to the server environment.

I had previously posted that you can get the Microsoft.SqlServer.Types library from MS Downloads, but it turns out that this alone is not sufficient to allow your application to run. You also need to ensure that the SQL Server 2008 Native Client is also installed (regardless of whether you're accessing a SS2008 instance or not). Update! You actually don't... read below.

Both the Types library and the Native Client can be downloaded from the following:

http://www.microsoft.com/downloads/details.aspx?FamilyID=228de03f-3b5a-428a-923f-58a033d316e1&DisplayLang=en

My discovery source: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=355402&wa=wsignin1.0

UPDATE: Per Isaac Kunen (in this blog post's comments as well as offline discussion), the missing component from the Types library is simply an updated version of the C Runtime. The fix of using the Native Client is a hack in this case because its MSI actually installs the updated CRT (which the MSI for the Types library should have done also - it's a goof that MS Downloads hasn't been updated with an updated version of the Types API after the above Connect feedback was answered...

The Microsoft Visual C++ 2008 redistributable by itself can be downloaded from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=A5C84275-3B97-4AB7-A40D-3802B2AF5FC2&displaylang=en

Knowledge++ [3]

Jason Follas — Thu, 29 Jan 2009 14:32:28 GMT

When you write a query that SELECTs a SQL Server 2008 Spatial type, the returned result is a binary value. Without closely examining the bytes, I just assumed that this was WKB (Well-Known Binary).

Well, as it turns out, the bytes that are returned are simply a serialized version of the .NET object (the UDT has a Write() method that it calls internally to serialize the object in binary). This is NOT directly compatible with WKB.

My discovery source: http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/40ee9466-d7bf-4340-b295-53217ae5128e

Using SQL Server Spatial Objects as ADO.NET Parameter Values

Jason Follas — Thu, 11 Dec 2008 19:04:12 GMT

I've previously mentioned that the SQL Server 2008 Spatial data types are freely available for use in your .NET applications, regardless of whether you have SQL Server 2008 or not. This allows you to incorporate some powerful spatial capabilities right into your application.

(Look for "Microsoft SQL Server System CLR Types" on this page: http://www.microsoft.com/downloads/details.aspx?FamilyID=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en )

However, in most usage scenarios, there will come a time when you have an instance of a SQL Server spatial object in your .NET application, and need to commit it to your SQL Server 2008 database. How would you do this, without losing fidelity or resorting to serialization of the object to WKT first?

The solutions is to create a Parameter object of type System.Data.SqlDbType.Udt. Then set the UdtTypeName parameter to the SQL Server-recognized type name (i.e., for SqlGeometry, you would simply use Geometry).

The following code demonstrates executing an UPDATE statement that sets the value of a Spatial field to a newly constructed object.

using (SqlConnection conn = new SqlConnection("Server=.;Integrated Security=true;Initial Catalog=scratch"))
{
   using (SqlCommand cmd = new SqlCommand("UPDATE fe_2007_us_zcta500 SET Boundary=@boundary WHERE id=@id", conn))
   {
      SqlParameter id = cmd.Parameters.Add("@id", System.Data.SqlDbType.Int);
      SqlParameter boundary = cmd.Parameters.Add("@boundary", System.Data.SqlDbType.Udt);
      boundary.UdtTypeName = "geometry";
      SqlGeometry geom = SqlGeometry.Parse("POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))");
      boundary.Value = geom;
      id.Value = 123;
      conn.Open();
      cmd.ExecuteNonQuery();
      conn.Close();
   }
}

SqlGeography: Ring Orientation of Polygon Interior Rings (Holes)

Jason Follas — Tue, 25 Nov 2008 14:14:18 GMT

I have mentioned before how the Ring Orientation for the exterior ring of a Polygon is significant when instantiating a SqlGeography object. In this case, a Counter-Clockwise orientation is required so that as an observer walks along the path, the interior of the Polygon is always to their left.

But, what I have never really seen documented (or paid attention to, at least) is the fact that the interior rings, or holes, of a Polygon also have specific Ring Orientation requirements.

In keeping with the "Left-handed" rule, interior rings must be defined in a Clockwise manner - the opposite orientation of the shape's exterior ring. This is because holes within a Polygon are considered to be part of the exterior of the shape, so the observer walking in a Clockwise direction is still keeping the Polygon's interior to their left.

(I should note here that the Ring Orientation for SqlGeography is the exact opposite of ESRI's ShapeFile format, which is why Ring Orientation has been on my mind for the past few days).

Spatial: Determining Ring Orientation

Jason Follas — Mon, 24 Nov 2008 18:00:46 GMT

A Ring is a list of points such that the starting point and ending point are the same (forming a closed shape). The order the you define the points that make up a Ring - known as Ring Orientation - is significant, for various data formats (including SQL Server's Geography type) imply special meaning for rings that are defined in a clockwise manner as opposed to a counter-clockwise manner.

Given a list of points with no additional context, it can be difficult to determine the Ring Orientation being used.

For example, suppose that you have a generic list of points that represent the boundary of a postal code, and that you wish to use these points in order to construct a Polygon instance using the SqlGeography type. SqlGeography happens to use "Left-handed" ordering, so that as an observer walks along the set of points in the order defined, the "inside" of the polygon is always to their left. This also implies that the exterior ring of a Polygon is defined in a counter-clockwise manner.

If you try to define a polygon with an area greater than a single hemisphere (this is a nice way to say "if you screw up and use the wrong orientation"), then the SqlGeography type will throw an exception. So, aside from using Try-Catch, what can you do?

While researching solutions to this problem, I stumbled upon a paper entitled "A Winding Number and Point-in-Polygon Algorithm" from the Colorado State University. It turns out that a simple algorithm with O(n) complexity can be used to determine if a point is within a Polygon, and a side effect also provides the Ring Orientation. The key to this algorithm is determining the trend of the ring at each crossing of an axis.

Since I was only interested in Ring Orientation (and not point enclosure detection), I didn't need to use this particular algorithm. Instead, I took inspiration from the winding concept, and created a simpler derivative algorithm:

Iterate the point collection and determine the extreme "left" and "right" points
Normalize the line segments connected to these points so that they each have the same "X" dimension length
Compare the "Y" values of the normalized segments to establish the trend through that extreme point (i.e., is the "previous" segment above or below the "next" segment)
In the spirit of the Winding algorithm, use opposite orientations for the left and right points so that the results coincide with one another
A negative result (negative indicates Clockwise orientation, positive result indicates Counter-Clockwise orientation, and a result of zero would be undefined

I've actually written (and posted) several versions of this algorithm, each time discovering some edge case exception that would cause me to take down the post and rewrite the algorithm. I believe the code below works for all simple polygons on a Cartesian coordinate system (read: I have more testing to see if this will work with an ellipsoidal model, like SqlGeography).

Note: The following code is generic in nature, and as such, I've defined my own Point structure instead of using a SqlGeometry or SqlGeography, etc.

struct Point
{
    public double X { get; set; }
    public double Y { get; set; }
}
enum RingOrientation : int
{
    Unknown = 0,
    Clockwise = -1,
    CounterClockwise = 1
};
RingOrientation Orientation(Point[] points)
{
    // Inspired by http://www.engr.colostate.edu/~dga/dga/papers/point_in_polygon.pdf
    // This algorithm is to simply determine the Ring Orientation, so to do so, find the
    // extreme left and right points, and then check orientation
    if (points.Length < 4)
    {
        throw new ArgumentException("A polygon requires at least 4 points.");
    }
    if (points[0].X != points[points.Length - 1].X || points[0].Y != points[points.Length - 1].Y)
    {
        throw new ArgumentException("The array of points is not a polygon.  The first and last point must be identical.");
    }
    int rightmostIndex = 0;
    int leftmostIndex = 0;
    for (int i = 1; i < points.Length; i++)
    {
        if (points[i].X < points[leftmostIndex].X)
        {
            leftmostIndex = i;
        }
        if (points[i].X > points[rightmostIndex].X)
        {
            rightmostIndex = i;
        }
    }
    Point p0; // Point before the extreme
    Point p1; // The extreme point
    Point p2; // Point after the extreme
    double m; // Holds line slope
    double lenP2x;  // Length of the P1-P2 line segment's delta X
    double newP0y;  // The Y value of the P1-P0 line segment adjusted for X=lenP2x
    RingOrientation left_orientation;
    RingOrientation right_orientation;
    // Determine the orientation at the Left Point
    if (leftmostIndex == 0)
        p0 = points[points.Length - 2];
    else
        p0 = points[leftmostIndex - 1];
    p1 = points[leftmostIndex];
    if (leftmostIndex == points.Length - 1)
        p2 = points[1];
    else
        p2 = points[leftmostIndex + 1];
    m = (p1.Y - p0.Y) / (p1.X - p0.X);
    if (double.IsInfinity(m))
    {
        // This is a vertical line segment, so just calculate the dY to
        // determine orientation
        left_orientation = (RingOrientation)Math.Sign(p0.Y - p1.Y);
    }
    else
    {
        lenP2x = p2.X - p1.X;
        newP0y = p1.Y + (m * lenP2x);
        left_orientation = (RingOrientation)Math.Sign(newP0y - p2.Y);
    }
    // Determine the orientation at the Right Point
    if (rightmostIndex == 0)
        p0 = points[points.Length - 2];
    else
        p0 = points[rightmostIndex - 1];
    p1 = points[rightmostIndex];
    if (rightmostIndex == points.Length - 1)
        p2 = points[1];
    else
        p2 = points[rightmostIndex + 1];
    m = (p1.Y - p0.Y) / (p1.X - p0.X);
    if (double.IsInfinity(m))
    {
        // This is a vertical line segment, so just calculate the dY to
        // determine orientation
        right_orientation = (RingOrientation)Math.Sign(p1.Y - p0.Y);
    }
    else
    {
        lenP2x = p2.X - p1.X;
        newP0y = p1.Y + (m * lenP2x);
        right_orientation = (RingOrientation)Math.Sign(p2.Y - newP0y);
    }
    if (left_orientation == RingOrientation.Unknown)
    {
        return right_orientation;
    }
    else
    {
        return left_orientation;
    }
}
void Test()
{
    // Simple triangle - left extreme point is vertically "in between" line segments
    Point[] points = new Point[]
    {
        new Point(5,-1),
        new Point(0,0),
        new Point(5,1),
        new Point(5,-1)
    };
    System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.Clockwise);
    Array.Reverse(points);
    System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.CounterClockwise);
    // Case where both line segments are above the left extreme point
    points = new Point[]
    {
        new Point(2,1),
        new Point(0,0),
        new Point(1,1),
        new Point(2,1)
    };
    System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.Clockwise);
    Array.Reverse(points);
    System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.CounterClockwise);
    // Case where both line segments are below the left extreme point
    points = new Point[]
    {
        new Point(2,-1),
        new Point(0,0),
        new Point(1,-1),
        new Point(2,-1)
    };
    System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.CounterClockwise);
    Array.Reverse(points);
    System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.Clockwise);
    // Case where line segment is vertical (slope cannot be determined)
    points = new Point[]
    {
        new Point(0,0),
        new Point(0,1),
        new Point(1,1),
        new Point(1,0),
        new Point(0,0)
    };
    System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.Clockwise);
    Array.Reverse(points);
    System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.CounterClockwise);
    // Case where angle thru left extreme point is a right angle
    points = new Point[]
    {
        new Point(0,0),
        new Point(1,1),
        new Point(1,-1),
        new Point(0,0)
    };
    System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.Clockwise);
    Array.Reverse(points);
    System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.CounterClockwise);
    // Real-world case from a SHP file
    points = new Point[]
    {
        new Point(-156.92467299999998,20.738695999999997),
        new Point(-156.924636,20.738822),
        new Point(-156.924608,20.73894),
        new Point(-156.92458,20.739082),
        new Point(-156.92460599999998,20.739234),
        new Point(-156.924551,20.739326),
        new Point(-156.924507,20.739241999999997),
        new Point(-156.924482,20.739082),
        new Point(-156.924466,20.738854999999997),
        new Point(-156.924387,20.738602999999998),
        new Point(-156.924308,20.738325),
        new Point(-156.924239,20.738063999999998),
        new Point(-156.92424,20.737887999999998),
        new Point(-156.924285,20.737811999999998),
        new Point(-156.924475,20.73762),
        new Point(-156.92458299999998,20.737603999999997),
        new Point(-156.924754,20.737579),
        new Point(-156.924851,20.737731),
        new Point(-156.924956,20.738101),
        new Point(-156.924909,20.738343999999998),
        new Point(-156.924818,20.738487),
        new Point(-156.92467299999998,20.738695999999997)
    };
    System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.Clockwise);
    Array.Reverse(points);
    System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.CounterClockwise);
}