Spatial
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, 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...
DbGeography Static Property
Return Type
DbGeometry Static Property
Return Type
DbGeography.DefaultSrid
...
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...
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...
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)
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...
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
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...
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...
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...
In this, the eighth part in a series on the new Spatial Data types in SQL Server 2008, I'll step away from the database and do a little spatial coding using .NET.
Redistributable .NET Library
Up to this point in the series, I have demonstrated a lot of interesting (?) things that you can do with the new Spatial data types (Geometry and Geography) in SQL Server 2008. You might be thinking, "That's swell, and all, but I wish I could do some of that stuff without needing to be tethered to a database." Well, you know what? You can!
I mentioned in...
The Open Geospatial Consortium's Simple Features specification, which SQL Server 2008's Geometry data type is based upon, defines standards for working with spatial data using a flat-earth (projected planar) model. Ironically, these standards don't exactly cover the intricacies of using an ellipsoidal model, which is needed to "accurately" represent the world that we live in. In other words, the OGC standards define how to work with paper maps of the world, but not globes.
Fortunately, the SQL Server team recognized that that the Geometry type is inadequate for a lot of scenarios, and implemented a second data type just for representing...
In the Part 4 and Part 5 of the series, I demonstrated some instance methods of the Geometry type that returned a new Geometry based on existing instances. In this part, I will concentrate on instance methods and properties of the Geometry type that return scalar values and Points.
STArea, STLength
Typically, your spatial data will represent something from the real world. A LineString may be the collection of points gathered from a GPS device, and together they may represent the path that you took from your home to the office. A Polygon may be the collection of points around the...
In the previous part of this series, I demonstrated instance methods that transformed a single Geometry type into another useful Geometry. In this post, we'll go a step further and show methods that allow two or more instances to interact with one another in order to produce a new Geometry.
For my baseline, I'll use two Polygons that overlap each other:
DECLARE @g geometry
= 'POLYGON((10 10, 40 10, 40 40, 10 40, 10 10))'
DECLARE @h geometry
= 'POLYGON((30 30, 50 30, 50 50, 30 50,...
In this, the 4th post in a series (Part 1, Part 2, Part 3) on the new spatial data types in SQL Server 2008, I'll explain some of the methods that are used to transform a single Geometry instance into another useful Geometry instance. Note that I'm using Geometry for simplicity, but these techniques also work with Geography. Edit: Ok, after starting to take a hard look at Geography, I realized that A LOT of the methods that Geometry offers are not implemented in Geography. :-/ Sorry to mislead you.
Useful TipTo help me to visualize geometries as I explore the...
In the previous parts of this series (Part 1, Part 2), I introduced the Geometry and Geography data types, the various subclasses (Point, LineString, Polygon, etc), and demonstrated a little bit of the Well-Known Text (WKT) syntax. These two posts were primarily informational in nature, and didn't touch SQL Server at all. Let's change that!
Instantiating the UDT
The Geometry and Geography data types are implemented as User Defined Types (UDT) written in .NET. They are automatically installed with the server, and are available for use by any SQL Server 2008 database. For this post, I will use the Geometry type to...
In the first part of this series, I provided a somewhat verbose description of the two different types of Spatial data supported by SQL Server 2008: Geometry (flat Earth model) and Geography (ellipsoidal model). Now, let's dive in a bit deeper, and take a look at some of the specifics.
Standards
From their website, the Open Geospatial Consortium, Inc. (OGC) is described as "a non-profit, international, voluntary consensus standards organization that is leading the development of standards for geospatial and location based services." Unlike me, these folks are the real experts in the field who understand both the problems and the solutions...
Around the 1995 timeframe, two very different systems opened my eyes to the power of geospatial data, particularly how it can be used to enable an analyst to make better business decisions. At the time, I was working in IT at the hub sorting facility for an overnight freight company that specialized in heavyweight cargo. This company not only had a fleet of aircraft flying freight around the country, but also operated a large fleet of trucks (both linehaul and LTL).
Shortly after leaving that company, I was tasked with working on an e-commerce system that had some unique sales territory...
Beyond Relational
The introduction of SQLCLR in SQL Server 2005 allowed for very rich User Defined Types to be utilized. This meant that a developer could create a single object that contained multiple data points (properties) and could also perform calculations internally (methods), yet store that instance in a single field of a single row in a database table. Suddenly, any type of data could be stored and queried in the database, instead of just strings and numbers.
Fast forward to the present time, as Microsoft is celebrating the launch of SQL Server 2008 (notice that in the brilliance that is known...