GEOGRAPHY::STGeomFromText -- problem with latitudes

Question

Post reply

GEOGRAPHY::STGeomFromText -- problem with latitudes

boeledi

Ten Centuries

Points: 1151
More actions
September 18, 2010 at 8:27 pm

#224180

Dear All,
I have a very weird issue using GEOGRAPHY::STGeomFromText.
Here is the stored procedure I am trying to run:
CREATE PROCEDURE [dbo].[VE_GetIntersectingPoints]
(
@WKT VARCHAR(MAX)
)
AS
BEGIN
SELECT ID, Title, Price, [Description], Coordinates.Lat as Latitude, Coordinates.Long AS Longitude
FROM PropertiesForSale
WHERE Coordinates.STIntersects(GEOGRAPHY::STGeomFromText(@WKT, 4326)) = 1
The value of WKT is pass is the following:
POLYGON((49.03786794532642 -141.50390625, 20.79720143430697 -141.50390625, 20.79720143430697 -88.76953125, 49.03786794532642 -88.76953125, 49.03786794532642 -141.50390625))
and I obtain the following exception, while latitudes are between -90 and 90 degrees...
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.OpenGisWktReader.ParseLineStringText()
at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePolygonText()
at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)
at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.STGeomFromText(SqlChars geometryTaggedText, Int32 srid)
.
My database is configured with "Collation = SQL_Latin1_General_CP1_CI_AS"
Is this due to the collation or to something else?
If this would be due to the collation, how can I solve this issue, knowing that I need this collation at the database level (multi-lingual database, used in Europe)?
Any help is more than welcome.
Many thanks in advance,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

prvmine SSCertifiable Points: 7007 More actions · Answer 1

I appears that your lat/longs are in reversed order.

to see how SRID returns values the following example from bol (STLineFromWKB (geography Data Type))

DECLARE @g geography;

SET @g = geography::STLineFromWKB(0x010200000002000000D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC08716D9CEF7D34740, 4326);

SELECT @g.ToString();

returns the following

LINESTRING (-122.36 47.656, -122.343 47.656)

so if you change set statemen to the following it appears to work

set @WKT = 'POLYGON(( -141.50390625 49.03786794532642, -141.50390625 20.79720143430697, -88.76953125 20.79720143430697, -88.76953125 49.03786794532642, -141.50390625 49.03786794532642))'

boeledi Ten Centuries Points: 1151 More actions · Answer 2

Thanks a lot for your answer. I switched the data and it now works.

Didier