A View Inside My Head

Jason's Random Thoughts of Interest

NAVIGATION - SEARCH

SQL Server 2008: Spatial Data, Part 5

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, 30 30))'

Spatial_5_1

STDifference


STDifference() returns a new instance consisting of all points from the base instance that do not contain points from the parameter instance.

 

  SELECT @g.STDifference(@h).ToString();

Result:

POLYGON ((10 10, 40 10, 40 30, 30 30, 30 40, 10 40, 10 10))

Spatial_5_2

 

STIntersection


STIntersection() returns a new instance containing only the points that are in common between the base instance and the parameter instance.

 

  SELECT @g.STIntersection(@h).ToString();

Result:

POLYGON ((30 30, 40 30, 40 40, 30 40, 30 30))

Spatial_5_3

 

STSymDifference


STSymDifference() returns a new instance containing only the points that are unique to both the base instance and the parameter instance (i.e., it excludes the points that STIntersection() would return).

In this case, the set of points is actually two different Polygons.  Because STSymDifference() needs to return a single instance of something, it will wrap those two Polygons into a collection (MultiPolygon).

 

  SELECT @g.STSymDifference(@h).ToString();

Result:

MULTIPOLYGON (((40 30, 50 30, 50 50, 30 50, 30 40, 40 40, 40 30)), 
              ((10 10, 40 10, 40 30, 30 30, 30 40, 10 40, 10 10)))

 Spatial_5_4

 

STUnion


STUnion() returns a new instance containing all of the points of the base instance and the parameter instance merged together.

 

  SELECT @g.STUnion(@h).ToString();

Results:

POLYGON ((10 10, 40 10, 40 30, 50 30, 50 50, 30 50, 
30 40, 10 40, 10 10))

Spatial_5_5

 

Blended Types


The instance methods described above do not work just for Polygons.  You can actually use them on different types, or even collections of different types. 

For instance, if we look at the results of using a LineString as the base instance and a Polygon as the parameter instance, STDifference() will return a MultiLineString constisting of the points from the original LineString that do not lie within the Polygon:

  DECLARE @g geometry = 'LINESTRING(9 9, 40 40)'DECLARE @h geometry = 'POLYGON((15 15, 15 30, 30 30, 30 15, 15 15))'SELECT @g.STDifference(@h).ToString();

Results:

MULTILINESTRING ((40 40, 30 30), (15 15, 9 9))

Spatial_5_6 

 

STIntersection() will return the points from the original LineString that do lie within the Polygon:

  SELECT @g.STIntersection(@h).ToString();

Results:

LINESTRING (30 30, 15 15)

Spatial_5_7

 

STUnion() cannot determine a single common Geometry type, so it will return a mixed collection of types:

  SELECT @g.STUnion(@h).ToString();

Results:

GEOMETRYCOLLECTION 
(
     LINESTRING (40 40, 30 30), 
     POLYGON ((15 15, 30 15, 30 30, 15 30, 15 15)), 
     LINESTRING (15 15, 9 9)
)

Spatial_5_8

 

SQL Server 2008: Spatial Data, Part 1

SQL Server 2008: Spatial Data, Part 2

SQL Server 2008: Spatial Data, Part 3

SQL Server 2008: Spatial Data, Part 4

(next part) SQL Server 2008: Spatial Data, Part 6

SQL Server 2008: Spatial Data, Part 7

SQL Server 2008: Spatial Data, Part 8

kick it on DotNetKicks.com

Pingbacks and trackbacks (1)+