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 boundary of governmental territory, like a county or a parish within your state.
In both of these cases, the time will come when you will want to know the length of the LineString (or length of the perimeter of the Polygon) and the area within the Polygon. OGC standard method STArea() returns a float indicating the area of the instance in square units (or 0, if the instance is not a Polygon and does not have area). STLength() returns a float indicating the length of the instance in units (or 0, if the instance is a Point and does not have length).
DECLARE @g GEOMETRY = 'POLYGON((10 10, 10 40, 40 40, 10 10))'
SELECT @g.STArea(), @g.STLength()
Results:
Area Length
450 102.426406871193
STCentroid
Thinking back to Mr. Bollenbacher's 10th Grade Geometry class, we had to use a compass and straight edge to construct lines bisecting the angles of polygons (primarily triangles). The point where the angle bisectors met was the exact center, or centroid, of the shape. Centroids are important because any line that passes through a centroid will divide the Polygon into two parts of equal area. It should be noted that a Centroid may not actually be on the surface of a Polygon.
The OGC standard method STCentroid() returns a Point indicating the centroid of the shape. If the instance is not a Polygon (or MultiPolygon), then NULL will be returned.
DECLARE @g GEOMETRY = 'POLYGON((10 10, 10 40, 40 40, 10 10))'
SELECT @g.STCentroid().ToString()
Results:
POINT (20 30)
Note: SpatialViewer displays an individual point as an X.
STWithin, STContains
Two OGC standard methods returns a 1 or 0 indicating whether all of the points of one instance exist entirely inside of another instance. STWithin() tests whether the base instance is inside of the parameter instance, while STContains() tests whether the parameter instance is inside of the base instance.
DECLARE @g geometry = 'POLYGON ((10 10, 13 30, 30 30, 30 15,
10 10))'
DECLARE @h geometry = 'LINESTRING (16 16, 16 24, 25 18)'
SELECT @g.STContains(@h), @g.STWithin(@h)
SELECT @h.STContains(@g), @h.STWithin(@g)
Results:
1 0
0 1
ST is {something}?
There are a number of OGC standard methods to check whether a given instance meets certain specifications: STIsClosed, STIsEmpty, STIsRing, STIsSimple, STIsValid
CLOSED: An instance is considered to be closed if the start point is the same as the end point. By definition, a Polygon has to be closed, and a Point is not closed. That really only leaves LineString. For a collection of objects to be considered closed, all of its members must be closed.
EMPTY: A Geometry can be initialized in a special way as to not contain any points. In SQL terms, this is sort of like having a NULL value, except it really is an instantiated object. For example, LINESTRING EMPTY is a valid LineString, but it has no points. Another humorous example is POINT EMPTY, which initializes to a Point without a Point.... so it's kind of Pointless, right? (thank you, I'm here all week, tip your waitress).
RING: An instance is considered to be a ring if it is both Closed and Simple.
SIMPLE: An instance is considered to be simple if it does not cross over itself or otherwise touch itself. For example, a LineString forming the letter 'S' is simple because it never comes in contact with itself. But, a LineString that forms a Figure-Eight (8) is not simple because it would have to cross over itself. Likewise, two circles (MultiPolygon) stacked on top of each other to form a Figure-Eight would not be simple because they touch each other.
VALID: A Geometry can cross over itself, but it cannot legally trace over itself. That is, picture a LineString that backtracks over itself at some point, kind of like how I write my letter "P". This is not considered to be Valid.
Tip: SQL Server will allow an invalid Geometry to be instantiated, and Microsoft has provided an extension method called MakeValid() that will convert the invalid instance into a valid instance. In the letter "P" example, instead of the vertical line going down and then back up (as I draw it by hand), the valid form will eliminate the duplication of points simply by start at the bottom and going up (so that the LineString never traces over itself). If it's not possible to simplify a shape in this way so that there is only one continuous path, then it will be broken up into multiple valid shapes (i.e., a MultiLineString, etc).
STX, STY, Z, M
Individual coordinates of a Point can be accessed via the OGC Standard properties STX and STY. Three-dimensional Points also have a Z coordinate, which can be accessed via Microsoft's extended Z property. Likewise, four-dimensional Points have a M (for Measure) coordinate, which can be accessed via Microsoft's extended M property. If Z or M is not defined for a given point, then NULL will be returned.
DECLARE @g geometry = 'POINT(1 2)'
DECLARE @h geometry = 'POINT(1 2 3 4)'
SELECT @g.STX, @g.STY, @g.Z, @g.M
SELECT @h.STX, @h.STY, @h.Z, @h.M
Results:
1 2 NULL NULL
1 2 3 4
STPointOnSurface
When working with spatial data, especially without using a viewer, it can be kind of difficult to pick an arbitrary point that is inside of a Polygon (or on a LineString). Thankfully, the OGC standard method STPointOnSurface() does just that. Given a Geometry instance, it will return a somewhat random point that is guaranteed to be located within the interior of that instance.
DECLARE @g geometry = 'POLYGON((10 10, 14 15, 50 12, 45 30,
10 30, 10 10))'
SELECT @g.STPointOnSurface().ToString()
Results:
POINT (23 25)
STSrid
All of my examples to this point have used the default Spatial Reference ID of 0 (for the Geometry type) simply because I have not been specifying one. The SRID is the mechanism that defines one geometry as being based on a different set of parameters than a geometry with a different SRID.
For example, you may have a set of shapes defined where each unit represents one meter, while another set of shapes is based on a reference system where each unit represents 1.5 inches. It's totally legal to mix these shapes together the same column of a table in your database, provided that you assign a different SRID to each. SQL Server does not need to know what units represent, because it will never permit the interaction of a shape from one SRID with a shape from another SRID.
The OGC standard property STSrid will get (or set) the SRID of the Geometry instance.
-- @g will have the default SRID = 0
DECLARE @g GEOMETRY = 'POLYGON((10 10, 10 40, 40 40, 10 10))'
-- @h is defined with SRID = 123
DECLARE @h GEOMETRY
= GEOMETRY::STGeomFromText('POLYGON((10 10, 40 10,
40 40, 10 10))',
123)
select @g.STUnion(@h).ToString()
-- Returns NULL because of different SRIDs. But, let's change
-- @g to use SRID = 123
SET @g.STSrid = 123
select @g.STUnion(@h).ToString()
-- Returns POLYGON ((10 10, 40 10, 40 40, 10 40, 10 10))
Jason, What's Next?
Enough of this flat Earth stuff! In the next part, I'll explore the Geography data type. This is where things really start to get interesting. Stay tuned!
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
SQL Server 2008: Spatial Data, Part 5
(next part) SQL Server 2008: Spatial Data, Part 7
SQL Server 2008: Spatial Data, Part 8
