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