CODEWITHSUNDEEP
sql-server-2008coordinatesgeosqlgeography
Baodad
Baodad

Reputation: 2491

SQL Server Geography STGeomFromWKB error - System.FormatException: 24201

In SQL Server 2008 R2, we have a table with a field of data type geography. When queried with

SELECT rowid,GeoCoor,
GeoCoor.STSrid as SRID,
GeoCoor.Lat as Lat,
GeoCoor.Long as Long
FROM dbo.Locations

we get rows like this: 

╔═══════╦════════════════════════════════════════════════╦══════╦══════════════════╦═══════════════════╗
║ rowid ║                    GeoCoor                     ║ SRID ║       Lat        ║       Long        ║
╠═══════╬════════════════════════════════════════════════╬══════╬══════════════════╬═══════════════════╣
║  1092 ║ 0xE6100000010C82C540E751804240BA86EFD400B45BC0 ║ 4326 ║ 37.0024994913356 ║ -110.812550767815 ║
╚═══════╩════════════════════════════════════════════════╩══════╩══════════════════╩═══════════════════╝

All of our data are points, not lines or polygons, and we are using SRID 4326.

I am trying to UPDATE the GeoCoor field of a record with the binary value of another geographic point stored in the table. Here is my code:

DECLARE @coor1 geography
SET @coor1 = geography::STGeomFromWKB(0xE6100000010C82C540E751804240BA86EFD400B45BC0,4326)

UPDATE dbo.Locations
SET GeoCoor = @coor1
WHERE RowID = 2657

However, the SET @coor1 = statement triggers an error in SQL Server Management Studio:

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.OpenGisWkbReader.ReadFirstPoint(ByteOrder byteOrder)
    at Microsoft.SqlServer.Types.OpenGisWkbReader.ParseWkbPointWithoutHeader(ByteOrder byteOrder)
    at Microsoft.SqlServer.Types.OpenGisWkbReader.Read(OpenGisType type, Int32 srid)
    at Microsoft.SqlServer.Types.SqlGeography.GeographyFromBinary(OpenGisType type, SqlBytes binary, Int32 srid)

Based on the initial query results, you can plainly see that the Latitude of 37.002499... is indeed between -90 and 90. It is the Longitude that is not between -90 and 90.

I believe this is a .NET bug in the geography::STGeomFromWKB method, that is incorrectly transposing the Lat and Long from the WKB data (or perhaps the .Lat and .Long methods are in error?!). My questions are:

Upvotes: 1

Views: 2065

Answers (1)

Whitney Chase
Whitney Chase

Reputation: 11

After perusing other sites I thought to reverse the order of my Lat/Long. That seemed to work. 

SELECT  Geography::Point(Latitude,Longitude, 4326) as c
FROM na.MyTable
where Latitude  Is Not Null

That generates the spatial data I am looking for (but of course something else is broken) :)

Upvotes: 1

Related Questions