[GIS] SQL Error: Latitude values must be between -90 and 90 degrees

arcgis-desktoparcmapgeography-data-typesql server

I'm more of an ArcGIS user than a SQL Server user but I have polygon data in a 'geography' type in SQL 2008 R2. For a couple of these poly layers, I get this persistent drawing error in ArcMap:

One or more layers failed to draw:

Cultural Resource Site Polygon: Underlying DBMS error
[[Microsoft][SQL Server Native Client 10.0][SQL Server]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, Nullable1 z, Nullable1 m) at
Microsoft.SqlServer.Types.Validator.BeginFigure(Double x, Double y,
Nullable1 z, Nullable1 m) at Microsoft.SqlServer.Typ]
[db.layer]

I thought at first this might be related to other posts I've seen on incorrect ring orientation in polygons but those errors tend to say that the data exceeds a hemisphere. This error seems to suggest that I have vertices inside some of my polys that are perhaps backwards.

Unsure how to find them in SQL (any help would be great!), I turned to ArcMap and used the Feature Vertices to Points tool to create a set of points at every vertex of my polys. I then calculated X and Y on those points. I can't find any instance where my Y exceeds either 90 or -90.

Unsure at this point…

Best Answer

There's been a bug filed in Esri about this error message as:

Bug: Spatial query fails when features are stored in SQL Server Geography storage type and the filter shape is larger than one hemisphere

Related Solutions

[GIS] Change polygon “handedness” for SQL 2008 (reverse polygon vertex order)

Spatial Ed's blog had a concise solution. Here is some SQL demonstrating the transform:

DECLARE @geom GEOMETRY = 'POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))';
DECLARE @geog GEOGRAPHY = @geom.MakeValid().STUnion(@geom.STStartPoint()).STAsText()

And an excerpt from Ed's post:

The key to this behavior is the the STUnion() method. Since this is an OGC-based method, working on the entire geometry for a given feature, it forces polygons into the orientation required for the method—which just happens to be the one used for the Geography type [...]. This method illustrated is quite efficient, keeping overhead small [...].

[GIS] Getting coordinates of shortest distance between point and line MS SQL 2008

Here's something that should work with Geography datatypes. You may want to play around with the tolerances in the buffers to suit your data better. I've used CTEs to try and make the method clearer

DECLARE @PointID INT;
SET @PointID = 1;
DECLARE @SearchCount INT;
SET @SearchCount = 10;
DECLARE @SearchDistance INT;
SET @SearchDistance = 15000;

WITH Candidate AS ( -- Original Query
    SELECT TOP(@SearchCount) 
        a.PointID,
        a.pointGeom.STDistance(b.lineGeom) as [DistanceFromTarget],
        a.pointGeom,
        b.lineGeom,
        b.LineID
    FROM 
        LINES b,
        POINTS a
    WHERE 
        a.PointID = @PointID
        AND (a.pointGeom.STDistance(b.lineGeom)) < @SearchDistance
    ORDER BY a.pointGeom.STDistance(b.lineGeom)
    ),
    BufferIntersection AS ( -- Get first geography from the intersection of buffer
    -- May require a check to ensure a linestring
    SELECT PointID, pointGeom, LineID,
        pointGeom.BufferWithTolerance(pointGeom.STDistance(lineGeom) + 0.05, .01, 0).STIntersection(lineGeom).STGeometryN(1) AS Intersection
    FROM Candidate
    ),
    IntPoint AS ( -- Calculate the mid point for the intersection line.
    SELECT PointID, pointGeom, LineID,
        Intersection.STIntersection(Intersection.STPointN(1).BufferWithTolerance(Intersection.STLength() / 2.0, 0.001, 0)).STPointN(2) intPoint
    FROM BufferIntersection
    )
--put it together
SELECT PointID, LineID,
    Geography::STGeomFromText('LINESTRING(' + 
    CAST(CAST(pointGeom.Long AS NUMERIC(28,20)) AS VARCHAR(40)) + ' ' + CAST(CAST(pointGeom.Lat AS NUMERIC(28,20)) AS VARCHAR(40)) + ', ' + 
    CAST(CAST(intPoint.Long AS NUMERIC(28,20)) AS VARCHAR(40)) + ' ' + CAST(CAST(intPoint.Lat AS NUMERIC(28,20)) AS VARCHAR(40)) + ')',pointGeom.STSrid)
FROM IntPoint;

Related Question