Search code examples
sqlsql-serverspatialgeographywgs84

Validation of a point (WGS84) within geometry field on sql server

I have been trying to validate if a WGS84 point is inside a geometry field on SQL Server 2012.

The scenario is this, I have the political delimitation of a geographyc zone in a SQL Server 2012 database (postal code and everything), validated.

Then, I have also, points in WGS84 system locating some infraestructure with, postal codes submmited by providers, that I want to validate.

I need to validate if each point (WGS84) belong to the postal code submmited with it.

I have no idea where to start.

Thanks for the help!.

Omar

Solution

  • To make your life easier you'll need to first alter table 1 to include a geography column, which will hold the converted geometry data. You can convert as follows:

    UPDATE [Table1] SET [NewGeographyColumn] = GEOGRAPHY::STGeomFromWKB([GeometryColumn].STAsBinary(), 4326);

    Now that you have a Geography column with a matching SRID to your Table2 Points, ensure you create a SpatialIndex on this new column.

    You can then run a spatial query to ensure that your received data is correct:

    DECLARE @receivedLatitude DOUBLE = 0;
DECLARE @receivedLongitude DOUBLE = 0;
DECLARE @receivedPostalCodeName VARCHAR(10) = 'Postcode';

DECLARE @receivedPoint GEOGRAPHY = GEOGRAPHY::Point(@receivedLatitude, @receivedLongitude, 4326);

SELECT * FROM [Table1] WHERE [NewGeographyColumn].STIntersects(@receivedPoint) AND [PostalCodeName] = @receivedPostalCodeName;

    Or at least something to that effect to validate....

    Hope it helps.