SQL Server 2008 Spatial: find a point in polygon
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.
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
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)
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).
declare @g geometry set @g=geometry::STGeomFromText('POLYGON((-33.229869 -70.891988, -33.251124 -70.476616, -33.703094 -70.508045, -33.693931 -70.891052,-33.229869 -70.891988))',0) DECLARE @h geometry; SET @h = geometry::STGeomFromText('POINT(-33.3906300 -70.5725020)', 0); SELECT @g.STContains(@h);
- You shouldn't be mixing Geometry and Geography. Geometry is for FLAT PLANES, Geography is for SPHEROIDS (like Earth).
- You "should" reconcile the SRIDs to deal with this. Each SRID (e.g. 2913 = NZG2000) describes a transformation relationship. Each SRID can be used to map to/from a uniform sphere, which is how you get from one to another.
- Until you get to a "same" SRID on both values, many for the .STxXX functions will return NULL (you might have default 0 in both cases)
- If they are not the same but you pretend they are, you may have errors on the edge cases.
- If you spend some "precalc" time, you can determine the top/left and bottom/right points for the bounding rects involved (and store them), and use those values in indexes to limit the records to check. Unless A T/L < B B/R and A B/R > B T/L they cannot overlap, which means a simple 4 AND numeric check in your WHERE will limit your STWithin checks
Here's an example I used in SRID 2193. All roads within a 3km radius of a given point, and inside a specific school zone
DECLARE @g geometry SELECT @g = GEO2193 FROM dbo.schoolzones WHERE schoolID = 319 SELECT DD.full_road_name, MIN(convert(int, dd.address_number)), MAX(convert(int, dd.address_number)) FROM ( select A.* from dbo.[street-address] A WHERE (((A.Shape_X - 1566027.50505) * (A.Shape_X - 1566027.50505)) + ((A.Shape_Y - 5181211.81675) * (A.Shape_Y - 5181211.81675))) < 9250000 and a.shape_y > 5181076.1943481788 and a.shape_y < 5185097.2169968253 and a.shape_x < 1568020.2202472512 and a.shape_x > 1562740.328937705 and a.geo2193.STWithin(@g) = 1 ) DD GROUP BY DD.full_road_name ORDER BY DD.full_road_name
In case you have table (example: SubsriberGeo) where one of the columns (example: Location) has geography Points as values and you'd like to find all Points from that table that are inside polygon here is a way to do it:
WITH polygons AS (SELECT 'p1' id, geography::STGeomFromText('polygon ((-113.754429 52.471834 , 1 5, 5 5, -113.754429 52.471834))', 4326) poly ), points AS (SELECT [SubscriberId],[Location] as p FROM [DatabaseName].[dbo].[SubscriberGeo]) SELECT DISTINCT points.SubscriberId, points.p.STAsText() as Location FROM polygons RIGHT JOIN points ON polygons.poly.STIntersects(points.p) = 1 WHERE polygons.id IS NOT NULL;