Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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:

  1. 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): result 1 with a rectangle

  2. I draw a smaller square within that rectangle and get the results I was expecting: result 2 with a square

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: enter image description here

For the square: enter image description here

And 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..

share|improve this question
1  
Have you seen this thread: gis.stackexchange.com/questions/6822/…? It looks like you really have the same question: the bottom line of your first "rectangle" is being interpreted as a geodesic between its endpoints (exactly as you coded). In reality--despite how it is being drawn--it curves sharply upwards to very high latitudes. When you drew the second square, the endpoints of the bottom line were closer to each other and the geodesic (not drawn) is better (but not perfectly) approximated by the line segment that was drawn. –  whuber Jul 11 '13 at 20:19
    
@whuber Wow, thanks for the info--it's a lot take in. Can you recommend a solution for my problem? It seems like a common use case. I imagine if my polygon were of a higher resolution (having more points), then this problem wouldn't occur. Aside from that, would it be wise to use the geometry data type and somehow get around the issues I have with it by massaging the polygons somehow? –  Langdon Jul 12 '13 at 0:50
    
Your options include using a more suitable projection for the area of interest; subdividing the rectangle's edges to turn it into a more detailed spherical polygon; or doing the selection based on projected coordinates rather than geographic coordinates. –  whuber Jul 12 '13 at 13:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.