March 4, 2014 at 7:03 AM
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.