SQL Server - Question about Geography type in Sql Server 2008

Asked By nick fox on 07-Jun-08 04:13 AM
I read the documentation on msdn about the new geography type but
there was a one thing that I couldn't find.

Is it possible to determine if a point (lat/lng) is inside of a
geographical polygon. I know I can do it in c#, but I want to know if
there is a sql server built-in function that can do this.

thanks
Nick




Plamen Ratchev replied on 04-Jun-08 11:58 PM
Take a look at STIntersects, it returns 1 if a geometry instance intersects
another geometry, and 0 if it does not:
http://msdn.microsoft.com/en-us/library/bb933899(SQL.100).aspx

For example:

DECLARE @point GEOGRAPHY;
DECLARE @polygon GEOGRAPHY;

SET @point = geography::Parse('POINT(49.274138 -123.098562)');
SET @polygon = geography::Parse('POLYGON((47.0 -124.0, 47.0 -122.0,
50.0 -122.0, 50.0 -124.0, 47.0 -124.0))');

SELECT @polygon.STIntersects(@point);

HTH,

Plamen Ratchev
http://www.SQLStudio.com
nick fox replied on 07-Jun-08 04:14 AM
Thank you Plamen. I suspected that that might be the function I needed
but I was sure if it would work with a point and a polygon rather than
2 polygons.

nick