CODEWITHSUNDEEP
sqlsql-servergeometryspatialgeography
Rajanand Ilangovan
Rajanand Ilangovan

Reputation: 155

How to convert a column from Geometry to Geography in SQL Server 2012 Spatial table?

I have 2 tables (area & location) and I want to check the lat & long co-ordinate in one table is present in with in the area of the another table.

Table name: AREA Columns: OBJECTID (int),
AREA_NAME (varchar(50)), GEOM (Geometry)

Table name: LOCATION Columns: ID (int), LATITUDE (varchar(50)), LONGITUDE (varchar(50))

Sample data from Area table:

SELECT OBJECTID
,AREA
,GEOM
,GEOM.STAsText() 
FROM AREA

AREA TABLE 

SELECT 
ID
,LATITUDE 
,LONGITUDE  
FROM LOCATION

LOCATION TABLE

I want to check whether the location (i.e, latitude and longitude co-ordinates) is with in the area or not. The area table is having a column GEOMETRY column. So I would like to convert this geometry column to geography column. Once I convert to geography, I can check whether the location is within the area or not using STIntersects().

But when I try to convert, I get an error as Latitude values must be between -90 and 90 degrees. 

SELECT GEOGRAPHY::STGeomFromText(CAST(GEOM AS VARCHAR(MAX)), 4326) FROM AREA

Full Error message:

Msg 6522, Level 16, State 1, Line 70 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.ParseLineStringText()
at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePolygonText()
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) .

I'm new to this sql server spatial data. Could someone guide me to do this task? Thanks! :)

Update on 20170414: Here is the sample data from AREA & LOCATION table after I have converted the lat&long co-ordinates to Geometry.

Upvotes: 1

Views: 3865

Answers (1)

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

It seems your area is in web mercator coordinates, not latitude and longitude. In mercator coordinates are counted in meters from (0,0), not degrees.

Try convert lat lon to web mercator, then create geometry point using new coordinates,

Here is code exapmle (JS):

var degrees2meters = function(lon,lat) {
        var x = lon * 20037508.34 / 180;
        var y = Math.log(Math.tan((90 + lat) * Math.PI / 360)) / (Math.PI / 180);
        y = y * 20037508.34 / 180;
        return [x, y]
}

x= -77.035974
y = 38.898717

console.log(degrees2meters(x,y))

// should result in: -8575605.398444, 4707174.018280

https://gist.github.com/springmeyer/871897

Upvotes: 0

Related Questions