Code for this post can be found in the SQL Spatial project in GitHub.
“The Open Geospatial Consortium (OGC) is an international not for profit organization committed to making quality open standards for the global geospatial community. These standards are made through a consensus process and are freely available for anyone to use to improve sharing of the world’s geospatial data.” ~ www.opengeospatial.org
OGC was formed in 1994 and is the standards organization for commercial, governmental, nonprofit and research organizations world wide to collaborate. The Open Geographic Resource Analysis Support System (GRASS) was the previous organization name. SQL Server’s spatial data types follow the standards that OGC maintain. These standards are available at the following links.
OpenGIS Implementation Standard for Geographic InformationSQL Server supports the OGC methods for both geography and geometry data types. In this post we will review some of the common methods used when working with spatial data.
In order to test out these methods we will use the conformance tests in Annex C of the SFS standard. The test data set is synthetic, developed by hand, to exercise the functionality of the standard. The set makes up a map of a fictional location called Blue Lake. Below is an overview of Blue Lake.
The semantics of this data set are as follows.
a) A rectangle of the Earth is shown in UTM coordinates. Horizontal coordinates take meaning from POSC Horizontal Coordinate System #32214. Note 500,000 m false Easting, and WGS 72 / UTM zone 14N. Units are metres.
b) Blue Lake (which has an island named Goose Island) is the prominent feature.
c) There is a watercourse flowing from north to south. The portion from the top neatline to the lake is called Cam Stream. The portion from the lake to the bottom neatline has no name (Name value is ―Null‖).
d) There is an area place named Ashton.
e) There is a State Forest whose administrative area includes the lake and a portion of Ashton. Roads form the boundary of the State Forest. The ―Green Forest‖ is the State Forest minus the lake.
f) Route 5 extends across the map. It is two lanes wide where shown as a heavy black line. It is four lanes wide where shown as a heavy grey line.
g) There is a major divided highway, Route 75, shown as a heavy double black line, one line for each part of the divided highway. These two lines are seen as a multiline.
h) There is a bridge (Cam Bridge) where the road goes over Cam Stream, a point feature.
i) Main Street shares some pavement with Route 5, and is always four lanes wide.
j) There are two buildings along Main Street; each can be seen either as a point or as a rectangle footprint.
k) There is a one-lane road forming part of the boundary of the State Forest, shown as a grey line with black borders.
l) There are two fish ponds, which are seen as a collective, not as individuals; that is, they are a multi-polygon.
Here is a screen shot of all the tables visualized.
Code for this post can be found in the SQL Spatial project in GitHub. These are the following methods that are covered in the example code.
- STArea
- STAsText
- STBoundary
- STBuffer
- STCentroid
- STContains
- STConvexHull
- STCrosses
- STDifference
- STDimension
- STDisjoint
- STDistance
- STEndPoint
- STEnvelope
- STEquals
- STExteriorRing
- STGeometryN
- STGeometryType
- STInteriorRingN
- STIntersects
- STIsClosed
- STIsEmpty
- STIsRing
- STIsSimple
- STLength
- STLineFromWKB
- STNumGeometries
- STNumInteriorRing
- STNumPoints
- STOverlaps
- STPointN
- STPointOnSurface
- STPolyFromWKB
- STRelate
- STSrid
- STStartPoint
- STSymDifference
- STTouches
- STUnion
- STWithin
- STX
- STY
SQL Stack 