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 - #1 we 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.