Wednesday, January 7, 2015

Geospatial Data in SQL – Lat Lon or Lon Lat? That is the question!

Just wanted to share some fun using Geographic data in SQL. It’s been many years since I’ve touched Geographic data (and that was in VB6 not SQL), but it’s pretty cool really.

I have a lat lon coming in from Shazam. 37.454336 -122.184769

I took those coordinates and pushed them into and it shows Palo Alto, CA.

I then compared it to the address of the TheatreSysID that comes from Shazam and it is also in Palo Alto, CA.

Good so far. At least I know where the point is supposed to be!

But when I run this SQL: SELECT geography::STPointFromText('Point (37.454336 -122.184769)', 4326)

I get:

Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":  System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
System.FormatException: at Microsoft.SqlServer.Types.GeographyValidator.ValidatePoint(Double x, Double y, Nullable`1 z, Nullable`1 m)
at Microsoft.SqlServer.Types.Validator.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
at Microsoft.SqlServer.Types.ForwardingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
at Microsoft.SqlServer.Types.CoordinateReversingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePointText(Boolean parseParentheses)
at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)

We say Lat/Lon when we talk about spatial data and that means Latitude is the first number, right?

And it sure looks like it is between -90 and 90, doesn’t it? Well, maybe not …

If I flip the values and run: SELECT geography::STPointFromText('Point (-122.184769 37.454336)', 4326)

It does not error out and gives me a value… but is it the right one? And why a different format than Google… I mean it rules the world right? We can’t function without it!

So, I googled a bit and found this:

Yeah.. Microsoft has to be backwards. They do it Lon Lat. OK. OK. They aren’t backwards. They are following the Open Geospatial Consortium standard which does X Y … where X is the horizontal axis (longitude) and Y is the vertical axis (latitude).

Anyway… fun fact for the day.

Oh, and there are some good looking blogs by Dave Does Data on MSDN .. might be outdated and a bit oversimplified, but worth a read.

I keep meaning to watch one of Jason Horner’s presentations on Geo data … guess I should get on it!