I've coupled ESRI's ArcGIS with SQL Server 2008 R2 in an attempt to allow users to search for geographical points using a polygon (with ArcGIS' drawing toolbar). I've run up against an issue that's not making any sense to me. It seems easiest to illustrate it visually:
I draw a rectangle along the top of the northern hemisphere and get results I wouldn't expect (I know there are more points up there):
I draw a smaller square within that rectangle and get the results I was expecting:
Can anyone explain why this might be happening and if there's some way around it? It seems like a SRID mismatch, but I'm using 4326 for ArcGIS and for SQL Server.
If I switch to using
geometry instead of
geography the rectangle works fine. I assume this is because geometry isn't taking into account the curvature of the earth, which isn't present on the flattened-out map.
geometry has problems of its own though (my map wraps infinitely left/right, and so some of my polygons refer to points outside 180 degrees, which
geometry doesn't seem to support).
For reference, here are the results in SQL Server:
For the rectangle:
For the square:
UnitsSearchByLocation looks like this:
ALTER PROCEDURE [dbo].[proc_UnitsSearchByLocation] @polyText varchar(4096), @wkid int AS declare @g geometry set @g = @polyText declare @poly geography begin try -- try the polygon as provided, although it has the potential -- to be oriented incorrectly set @poly = geography::STPolyFromText(@polyText, 4326) select * from Unit where Location.STIntersects(@poly) = 1 end try begin catch -- assume the error was a result of the orientation being wrong -- and attempt to correct it declare @validPolyText varchar(4096) set @validPolyText = @g.MakeValid().STUnion(@g.STStartPoint()).ToString() set @poly = geography::STPolyFromText(@validPolyText, @wkid) select * from Unit where Location.STIntersects(@poly) = 1 end catch GO
Although it seems irrelevant to me, I'm using this Imagery from ESRI (: - https://server.arcgisonline.com/ArcGIS/rest/services/ESRI_Imagery_World_2D/MapServer
Thanks for any help you can provide..