Using Spatial Data in SQL Server – The Left Hand Rule [Updated]
The best part?
I will give you more information about one specific technique “the Left-Hand Rule” that is much more efficient than the SQL Server’s own “ReorientObject”.
In short: if you want more efficient technique to use spatial data in your SQL Server, you will love this post.
Let’s get started.
What is spatial data in SQL Server?
Spatial data is also known as geospatial data. It also is information about a physical object that can be represented by numerical values in a geographic coordinate system.
In general, spatial data represents the location, size and shape of an object on planet Earth such as a building, lake, mountain or township.
Spatial data may also include attributes that provide more information about the entity that is being represented. For example, Geographic Information Systems (GIS) or other specialized software applications can be used to access, visualize, manipulate and analyze geospatial data.
Microsoft introduced two spatial data types with SQL Server 2008: geometry and geography. Geometry types are represented as points on a planar, or flat-earth, surface several years ago.
An example would be (7,8) where the first number represents that point’s position on the horizontal (x) axis and the second number represents the point’s position on the vertical (y) axis.
On the other hand, the Geography spatial data types, are represented as latitudinal and longitudinal degrees, as on Earth or other earth-like surfaces.
More information about the two special data types
The geometry data type supports planar, or Euclidean (flat-earth), data. The geometry data type both conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0 and is compliant with SQL MM (ISO standard). In addition, SQL Server supports the geography data type, which stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.
Differences between the geometry and geography Data Types
The two types of spatial data often behave quite similarly, but there are some key differences in how the data is stored and manipulated. You can read more about that in Microsoft website, here I am going to add the main bullets that you need to look for:
- How connecting edges are defined?
- How circular arc segments are defined?
- What are the measurements in spatial data types?
- What is the orientation of spatial data?
- Outer and inner rings are not important in geography data type.
But the purpose of this post is different, and I am not going to write about the two different spatial data types – geometry data type and geography data type. You can read more about them on the website of Microsoft. We are going to observe and define the so called “Left-Hand Rule”.
Now, let’s get back to the purpose of this post. Keep reading to see how to use this method.
How to use 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 Euclidean 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 Euclidean 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 front-end.
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 that you can follow the list with the bullets bellow:
- 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.
If you need to optimize your servers and your data base, you can check this Database Optimization Case Study and then you can contact our sales representatives for a quote.
CREATE TABLE sample (id int primary key, g geography); INSERT INTO sample VALUES (0, geography::Point(45, -120, 4326)), (1, geography::Point(45, -120.1, 4326)), (2, geography::Point(45, -120.2, 4326)), (3, geography::Point(45, -120.3, 4326)), (4, geography::Point(45, -120.4, 4326)); CREATE SPATIAL INDEX sample_idx on sample(g); SELECT id FROM sample WHERE g.Filter(geography::Parse( 'POLYGON((-120.1 44.9, -119.9 44.9, -119.9 45.1, -120.1 45.1, -120.1 44.9))')) = 1;