Aviation Technology

Image

Great Circle Routes using Geospatial Data

March 4, 2014 at 7:03 AMJared Nielsen

It turns out that planets aren't flat and there are no dragons beyond the margin of the page.  This means that you can't simply "connect the dots" on a globe that is warped into a two dimensional (2D) projection.  Mapping technologies have may projection types but for our purposes we are going to base our conversation on the EPSG Projection 4326 which is commonly used to create a nice rectangular shape for a map (highly popular with GPS navigation systems).  Setting the projection type is critical to your code because you will get strange "offsets" in your data if you keep them all on the same drawing surface size and shape.  In our previous example for Aviation Technology - #1we used this projection type in the following conversion from textual data to a projection based geography point object:

 
DECLARE @StartPoint geography
SELECT @StartPoint = geography::
STPointFromText('POINT(148.47539133747426 -20.031355678341452)', 4326)
Now we are going to use our knowledge of great circle routes to extend our point above to a connected "Route" as an aircraft would fly.  
 
DECLARE @StartPoint geography, @EndPoint geography, @Route geography SELECT @StartPoint = geography::STPointFromText('POINT(148.47539133747426 -20.031355678341452)', 4326) SELECT @EndPoint = geography::STPointFromText('POINT(115.45019875220305 -21.382500474560892)', 4326) SELECT @Route = geography::Parse(geometry::Parse(@StartPoint.STUnion(@EndPoint).ToString()).STConvexHull().ToString()) SELECT @route

The ability to create a Great Circle Route is necessary to begin true geospatial calculations for Aviation using Microsoft SQL Server and the Geography datatype.  Next we are going to explore the use of the geography datatype to identify FIR Boundary Intersections.

Microsoft Geography Coordinates Use Long/Lat

March 4, 2014 at 7:03 AMJared Nielsen
Microsoft SQL Server, Postgres and Oracle are all database platforms that support the Geography datatype and enable developers with the benefits of spatial database systems.  This article will explore the use of spatial database query leveraging geography objects which are constructed from samples of latitude and longitude coordinate pairs.

(X, Y) = (Long, Lat) - not Lat/Long

One of the first demystifiers is to understand how Microsoft has implemented the typical concept of "Lat/Long" or Latitude and Longitude.
 
 

The Globe is mapped into a grid structure and while we are generally used to referring to Latitude and Longitude, Microsoft has adopted an "X/Y" approach to the order of terms.  Where we would normally use latitude before longitude, Microsoft functions take their parameters with Longitude first and Latitude second.  In the globe to the right you will see the vertical bars as longitudes or "X" coordinates and the horizontal bars as latitudes or "Y" coordinates.  As an example you can convert a text coordinate pair to a geospatial point as follows:

 
DECLARE @StartPoint geography
SELECT @StartPoint = geography::
STPointFromText('POINT(148.47539133747426 -20.031355678341452)', 4326)
You will see that we're plotting a point near Australia and the X value = the Longitude value.  This is important to consider as you troubleshoot your geospatial queries and discover strange bugs where your geography objects get transposed and show up near the south pole somewhere.