SQL Server geography latitude longitude error

问题:

I'm using geography data type for calculating shortest distance.

CREATE TABLE Landmark (
    Id int,   
    Latitude FLOAT,
    Longitude FLOAT
)

INSERT Landmark VALUES
(1, 49.242458, -123.153465),
(2, 49.249381, -122.866683)

WITH GeographyLandmark AS
(
    SELECT Id, geography::STPointFromText('POINT(' + CAST(Latitude AS VARCHAR(20)) + ' ' + CAST(Longitude AS VARCHAR(20)) + ')', 4326) Location
    FROM Landmark
)

--this query calculates distance between point and localizations in meters
SELECT Id, geography::STPointFromText('POINT(' + CAST(49.2424566 AS VARCHAR(20)) + ' ' + CAST(-123.1534623 AS VARCHAR(20)) + ')', 4326).STDistance(Location) Distance
FROM GeographyLandmark

But I am getting this error:

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.Types.ForwardingGeoDataSink.BeginFigure(Double x, Double y, Nullable1 z, Nullable1 m) at Microsoft.SqlServer.Types.CoordinateReversingGeoDataSink.BeginFigure(Double x, Double y, Nullable1 z, Nullable1 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.ParseText(OpenGisType type, SqlChars taggedText, Int32 srid) at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid) .

What's the problem here?

回答1:

It looks like you've got the latitude and longitude switched. A couple of things:

1. An easy sanity check is to use the Lat and/or Long extended properties. Here is the doc for Long.

2. Another extension that Microsoft's implementation provides is the Point static method for creating points. So instead of having to create the WKT for a point to pass to STPointFromText, you can just do geography::Point(Latitude, Longitude, SRID). Here is the doc for that.