SQL Server 2008 Spatial: find a point in polygon

Question

I am using SQL Server 2008 spatial data types. I have a table with all States (as polygons) as data type GEOMETRY. Now I want to check if a point's coordinates (latitudes, longitudes) as data type GEOGRAPHY, is inside that State or not.

I could not find any example using the new spatial data types. Currently, I have a workaround which was implemented many years ago, but it has some drawbacks.

I've both SQL Server 2008 and 2012. If the new version has some enhancements, I can start working in it too.

Thanks.

UPDATE 1:

I am adding a code sample for a bit more clarity.

declare @s geometry  --GeomCol is of this type too. declare @z geography --GeogCol is of this type too.  select @s = GeomCol from AllStates where STATE_ABBR = 'NY'  select @z = GeogCol from AllZipCodes where ZipCode = 10101 

Answer 1

I think the geography method STIntersects() will do what you want:

DECLARE @g geography; DECLARE @h geography; SET @g = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326); SET @h = geography::Point(47.653, -122.358, 4326)  SELECT @g.STIntersects(@h) 

Answer 2

If you cannot change the data-type for the stored polygons to GEOGRAPHY then you can convert the input latitude and longitude to GEOMETRY and use the STContains or STIntersects against the converted value.

DECLARE @PointGeography GEOGRAPHY = geography::Point(43.365267, -80.971974, 4326) DECLARE @PointGeometry GEOMETRY = geometry::STGeomFromWKB(@PointGeography.STAsBinary(), 4326);  SELECT @PolygonGeometry.STContains(@PointGeometry); 

Going the opposite direction -- trying to convert the GEOMETRY polygons to GEOGRPAHY -- is error-prone and likely to fail from my experience.

And note that if you try to create the GEOMETRY point directly from the latitude and longitude values then the STContains (or STIntersects) won't work (i.e. won't give a match when they should).