0

Possible Duplicate:
SQL Server 2008 Spatial: find a point in polygon

I am working on an application that uses an SQL Server 2008 database. In this database I have a table named Session that has two fields Longitude and latitude, which indicate the location of a user. On another table called Zone, I have an area attribute which is of type geometry. How can I check if the user's longitude and latitude coordinates belong to a certain geometry?

Thank you

Community
  • 1
  • 1
nick.katsip
  • 868
  • 3
  • 12
  • 32
  • 1
    See the answer to [this SO question][1]. [1]: http://stackoverflow.com/questions/11054149/sql-server-2008-spatial-find-a-point-in-polygon – Marcel N. Jun 28 '12 at 13:27
  • 1
    The answer conflates geography and geometry - The notes to the answer fixes the answer a bit, but doesn't really explain why SQL uses different data types that are incommensurate. – David Manheim Jun 28 '12 at 13:39

1 Answers1

3

You have a geometry, which stores a shape in Euclidean geometry, and you want to associate a point on the globe, represented by a latitude and longitude, with it, to see if it is inside. This won't work, due to the way SQL stores the data. You probably need to use Geography data types to check this - Latitude and longitude are points on a sphere (Actually geodetic data, since the earth is not quite a sphere.)

For more information on why they are different, see this explanation from microsoft. Also this answer on stackoverflow: GEOMETRY and GEOGRAPHY difference SQL Server 2008

To convert your data from geometry to geography, try: Geography::STGeomFromText(cast(GeomCol as varchar(max)), 4326)

Then you can use the STIntersects method, documented by microsoft here.

Community
  • 1
  • 1
David Manheim
  • 2,553
  • 2
  • 27
  • 42