Using Spatial Data in SQL Server – The Left Hand Rule
When defining spatial data in SQL Server there are two main objects – geometry and geography. They are very similar; however, geometry does not include corrections of the convex hulls for the curvature of the earth. If we define a geometric polygon, it is in Euclidian geometry (in a plane), whereas defining with the same coordinates a geographical polygon it is non-planar. Why is this a problem? Try calculating the “face” of both polygons – geography will be bigger, since it’s a part of a sphere surface. If your project requires working in the Euclidian plane, then go with the geometry. However, make sure you use geography with maps! Besides accuracy, geography will help when using various coordinates’ standards and simplify conversion between them.
Geographic objects, being used for filtering purposes in MS SQL Server, adhere to the so called “Left hand rule”. That means the area where the search occurs is selected as if looking over ones left shoulder, while walking around the area. In case that the points are received in clockwise order, the search area specified is the whole world, except the selected polygon, which is counter intuitive to what the user sees as a selected area in the frontend. In the case the coordinates that represent the polygon are received in clockwise order, the points are selected in reverse order and a new instance of the geographic object is constructed to represent the search area. This method is much more efficient than the SQL Server’s own “ReorientObject”, which will create a FullGlobe instance, with an excluding area the polygon in question.
Other corrections that should be applied to the geographic objects is a check of their validity according to OGC standards for simple feature access.
Basically it means a list of checks:
- Polygons are topologically closed;
- The boundary of a Polygon consists of a set of linear rings that make up its exterior and interior boundaries;
- No two rings in the boundary cross, and the rings in the boundary of a polygon may intersect at a point but only as a tangent;
- A polygon may not have cut lines, spikes or punctures;
- The interior of every polygon is a connected point set;
- The exterior of a polygon with 1 or more holes is not connected. Each hole defines a connected component of the exterior.
If the geography object fails any of these checks the issue found is automatically fixed and a new corrected object is constructed.
We are using it in a 3D world, using geography and the above tricks will get you to 2.5D.