To help me to visualize geometries as I explore the capabilities of the spatial data type, I've been using SpatialViewer, a tool written by fellow SQL Server MVP [Simon Sabin[(http://sqlblogcasts.com/blogs/simons/).
As a baseline, I'll be using my highest quality, hand-drawn letter "S". This LineString is a simple geometry (it does not cross over itself), but is not closed (the starting and ending points are not the same).
DECLARE @g GEOMETRY
SET @g = 'LINESTRING ( 69 26, 69 23, 69 21, 67 20, 65 20,
63 18, 58 17, 52 17, 51 17, 49 17, 45 18, 44 20,
44 21, 42 26, 42 29, 42 32, 42 35, 43 35, 47 38,
50 41, 55 42, 58 42, 65 44, 66 44, 67 44, 68 45,
69 47, 70 48, 70 50, 71 51, 70 56, 68 60, 68 63,
66 65, 65 66, 63 68, 60 71, 59 71, 57 71, 55 71,
51 69, 45 65, 44 63, 42 62, 41 59, 41 57, 41 56,
41 54, 42 53 )'
STEnvelope
A bounding box is a rectangle that is defined by the combination of minimum and maximum X and Y values found in a given Geometry instance. The OGC standard method STEnvelope() returns the bounding box (a Polygon) for the instance on which it is invoked. All points of the original instance lie within the new Polygon.
Note: In the following T-SQL examples, I will trail the method calls with a ToString() so that we can examine the resulting WKT. Normally, you would just work with the geometry (i.e., you wouldn't need call ToString())..
SELECT @g.STEnvelope().ToString()
Result:
POLYGON ((41 17, 71 17, 71 71, 41 71, 41 17))
Note: In these pictures, the original geometry is drawn in black, and the new geometry is superimposed in red. If the new geometry has area, then that area will appear as light brown or tan.
STConvexHull
If something is convex, then it is thought of as having a surface that is curved or rounded outward from the center. For example, the side of an apple is convex. If you happen to take a bite out of the apple, however, then that the portion that has been removed is considered to be concave. To "correct" the concave portion of the half-eaten apple, we could fill in the hole with something like clay, but we would only need to restore enough material so that there was a straight line from the top of the hole to the bottom (and the same for side to side).
The OGC standard method STConvexHull() returns the minimal bounding convex Polygon for a geometry instance. That is, any convex parts of the original instance will be preserved, and any concave parts will be "filled in", so to speak, by defining a straight line to bypass them. Like STEnvelope(), all points of the original instance lie within the new Polygon.
SELECT @g.STConvexHull().ToString()
Result:
POLYGON ((71 51, 70 56, 68 63, 66 65, 65 66, 63 68, 60 71,
59 71, 57 71, 55 71, 51 69, 45 65, 42 62, 41 59,
41 57, 41 56, 41 54, 42 26, 44 20, 45 18, 49 17,
51 17, 52 17, 58 17, 63 18, 67 20, 69 21, 71 51))
STBuffer
What if you have an existing shape, and you want to make it bigger, but preserve the general... uh, shape of it? Then you would use the OGC standard method STBuffer(distance)! This method returns a Polygon that inflates the area around the original geometry instance by a number of units that you provide. Note that if the original instance is a Point, then the result will be a circle with a radius of the number of units that you provided. It is also possible to deflate an existing Polygon by supplying a negative buffer value.
SELECT @g.STBuffer(5).ToString()
Result:
POLYGON ((49 12, 51 12, 52 12, 58 12,
58.246719360351562 12.00609016418457,
58.492688179016113 12.024333953857422,
58.737458229064941 12.054683685302734,
58.98058032989502 12.097097396850586,
(... snipped for clarity ...)
48.693824768066406 12.009382247924805, 49 12))
What happens if the amount of buffering forces the inflated area to overlap with itself? The resulting Polygon may develop holes (the area within a hole is still considered part of the exterior of the Polygon). Here the buffer increases to 8, creating a hole:
SELECT @g.STBuffer(8).ToString()
Result:
POLYGON (( exterior ring points ), ( interior ring points ))
STExteriorRing, STInteriorRingN
The result of the very last example was a Polygon with one interior hole. OGC standards provide a way to access the various components of a polygon (exterior ring and interior rings) individually.
STExteriorRing() returns just the closed LineString of the Polygon itself.
SELECT @g.STBuffer(8).STExteriorRing().ToString()
Result:
LINESTRING (49 9, 51 9, 52 9, 58 9,
58.394750595092773 9.0097446441650391,
(... snipped for clarity ...)
48.022533416748047 9.0599403381347656,
48.5101203918457 9.0150127410888672, 49 9)
Similarly, STInteriorRingN(n) is used to return the closed LineString of interior rings.
Note: This method is accessing a member of a GeomCollection by index, which I plan to cover in a later post. What's important to know now is that indexing starts at 1, and you will get an error if you specify an index that does not actually exist in the collection.
SELECT @g.STBuffer(8).STInteriorRingN(1).ToString()
Result:
LINESTRING (48.893725268961383 48.937175344014442,
49.084709167480469 49.279642105102539,
(... snipped for clarity ...)
48.893725268961383 48.937175344014442)
Extended Methods
Microsoft has implemented some additional methods on Geometry instances to perform tasks that are beyond the scope of the OGC standards.
Reduce(tolerance) is a method that will simplify a given instance using the Douglas-Peucker algorithm. The result is a an approximation of the original instance containing a fewer number of points. The accuracy of the new shape improves as the provided tolerance value approaches zero, but more points are necessary to provide that accuracy.
SELECT @g.Reduce(5).ToString()
Result:
LINESTRING (69 26, 49 17, 42 26, 42 35, 70 48,
60 71, 42 62, 42 53)
BufferWithTolerance(distance, tolerance, relative) is very similar in function to STBuffer(), only it gives you more control over the accuracy of the result. The tolerance parameter, like in the Reduce() method, controls the amount of acceptable error a resulting line segment can be from what is ideal.
To understand how tolerance factors into the result, then picture a Point that is buffered into a circle. To truly represent a circle, we would need a Polygon consisting of a infinite number of points. This is not practical, but by allowing for error, we can generate a regular Polygon with any number of sides that behaves like a circle. Smaller tolerances result in Polygons with a very large number of very small sides. Likewise, larger tolerances result in Polygons with fewer sides, and your circle will start to resemble things like octagons, hexagons, and even squares.
Compare the image below generated with a less accurate tolerance (left image), to the STBuffer(8) example from above (right image). The difference is subtle, primarily because the scale that we're working with is pretty small. But, one difference to note is that the ends of the inflated "S" on the left are straight while the ones on the right are rounded. Overall, the the image on the right has more points, and thus is a more accurate the original "S" shape than the image on the left.
SELECT @g.BufferWithTolerance(8, 10, 0).ToString()
Result:
POLYGON (( ring points ), ( hole points ))
Comments