THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Enjoy Another Sandwich -- Kent Tegels

Yummy slices of SQL Server between slices of .NET and XML

A detour: Building a MultiPolygon with SqlGeometryBuilder

The RTM version of SQL Server 2008 and recently release of the CLR updates with Visual Studio 2008 SP1 gave us the SqlGeometryBuilder and SqlGeographyBuilder classes to work with. These are very handy, simple APIs but, well, lets just say that the documentation on them is a bit lacking (Isaac Kunen's as some discussion of them at Our Upcoming Builder API). This morning, I wanted to write a few bits of .NET code that did the same work as this T-SQL statement:

declare @p geometry = geometry::STGeomFromText('MULTIPOLYGON(((-77.054700 38.872957,-77.057962 38.872620,-77.058547 38.870079,-77.055592 38.868840,-77.053217 38.870656,-77.054700 38.872957),(-77.056972 38.870639,-77.055851 38.870219,-77.054875 38.870864,-77.055452 38.871804,-77.056784 38.871655,-77.056972 38.870639)),((-77.056408 38.875290,-77.056947 38.875224,-77.057466 38.873598,-77.057273 38.872737,-77.055335 38.873020,-77.055499 38.874058,-77.056408 38.875290)))',4326);

The trick here is how do you delimit the rings of a polygon and how do you make a collection? It helps to keep four simple rules in mind:

  1. After instantiating the Builder, immediately set the Spatial Reference ID (SRID) you want to use. You must do this before you define any elements in the collection.
  2. In the case of a collection type, you need to call .BeginGeometry passing in a member of the OpenGisGeometryType enumeration for the desired collection.
  3. Each figure in the collection needs to started with a call to .BeginGeometry as well.
  4. Collection members must be well-formed. The collection must also be well-formed.

Keeping all that in mind, here's example method for constructing the geometry shown above:

private static SqlGeometry CreateMultipolygon() {
  // Create a new Geometry Builder to work with
  SqlGeometryBuilder gb = new SqlGeometryBuilder();
  // Set the Spatial Reference ID to 1
  // Start the collection
  // Start the first element in this collection
  // Define the first element (figure)
  gb.AddLine(-77.057962, 38.872620);
  gb.AddLine(-77.058547, 38.870079);
  gb.AddLine(-77.055592, 38.868840);
  gb.AddLine(-77.053217, 38.870656);
  gb.AddLine(-77.054700, 38.872957);
  // End the first element (figure)
  // Define the second figure
  gb.BeginFigure(-77.056972, 38.870639);
  gb.AddLine(-77.055851, 38.870219);
  gb.AddLine(-77.054875, 38.870864);
  gb.AddLine(-77.055452, 38.871804);
  gb.AddLine(-77.056784, 38.871655);
  gb.AddLine(-77.056972, 38.870639);
  // End the first polygon
  // Define the second polygon
  gb.BeginFigure(-77.056408, 38.875290);
  gb.AddLine(-77.056947, 38.875224);
  gb.AddLine(-77.057466, 38.873598);
  gb.AddLine(-77.057273, 38.872737);
  gb.AddLine(-77.055335, 38.873020);
  gb.AddLine(-77.055499, 38.874058);
  gb.AddLine(-77.056408, 38.875290);
  // End (close) the collection
  // Return that as a SqlGeometry instance
  return gb.ConstructedGeometry;

Published Friday, August 15, 2008 11:36 AM by ktegels



david wei said:

I have a question here.

I have a city table with lat / long data; I draw a polygon in the map,  then need a query to return all cites inside that polygon. Loop through each city is not efficient. Can we get a set based solution?


August 15, 2008 1:41 PM
New Comments to this post are disabled

About ktegels

Kent Tegels passed away on July 31, 2010. Kent was an Adjunct Professor at Colorado Technical University and a member of the technical staff at PluralSight. He was recognized by Microsoft with Most Valuable Professional (MVP) status in SQL Server for his community involvement with SQL Server and .NET. Kent held Microsoft Certifications in Database Administration and Systems Engineering, and contributed to several books on data access programming and .NET. He was a well known industry speaker, and resided in Sioux Falls, South Dakota.
Privacy Statement