locked
Trying to cheat the Single Hemisphere limitation RRS feed

  • General discussion

  • Hi folks,

        I'm trying to cheat the single hemisphere problem (for a bounding box) by finding which latitude is further away from the equator and using the hemisphere. Not the best solution, but at least I can get some results. Possibly a better solution might be to split the bounding box into two boxes, one for each hemisphere.

    Anyways, this is what i'm trying to do and it's not working. I swear i understood this spatial stuff, until a simple problem like this stumps me.

    this is my code :-

    1DECLARE @BoundingBox GEOGRAPHY 
    2 
    3DECLARE @TLLong DECIMAL(18, 10), 
    4    @TLLat DECIMAL(18, 10), 
    5    @BRLong DECIMAL(18, 10), 
    6    @BRLat DECIMAL(18, 10) 
    7    
    8    -- Hardocode some values for the purpose of this test. 
    9    SET @TLLong = -98.4375 
    10    SET @TLLat = 82.11838360691267 
    11    SET @BRLong = 98.4375 
    12    SET @BRLat = 0 
    13    
    14SET @BoundingBox = GEOGRAPHY::STGeomFromText('POLYGON((' + CONVERT(VARCHAR(18), @TLLong) + ' ' + CONVERT(VARCHAR(18), @TLLat) + ',
    15        ' + CONVERT(VARCHAR(18), @TLLong) + ' ' + CONVERT(VARCHAR(18), @BRLat) + ',
    16        ' + CONVERT(VARCHAR(18), @BRLong) + ' ' + CONVERT(VARCHAR(18), @BRLat) + ', 
    17        ' + CONVERT(VARCHAR(18), @BRLong) + ' ' + CONVERT(VARCHAR(18), @TLLat) + ',
    18        ' + CONVERT(VARCHAR(18), @TLLong) + ' ' + CONVERT(VARCHAR(18), @TLLat) + '))', 4326) 



    So what i've tried to do is create a bounding box that is, more or less, a massive part of the northern hemisphere. To generate this polygon, i've started at the top left and gone anti-clockwise around to make the box, ending back at the top again.

    Polygon doesn't generate -> it's the standard sql error...

    Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation.

    Could anyone shed some light on what i've done horribly wrong? I'm taking a guess it might have something to do with the curvature of the earth and the projected box i'm trying to construct?

    cheers.


    -Pure Krome-
    Thursday, February 5, 2009 2:02 AM

All replies

  • Pure Krome said:

    I'm taking a guess it might have something to do with the curvature of the earth and the projected box i'm trying to construct?


    Sounds like a pretty good guess to me!
    To help explain the "Within a hemisphere" limitation, you might want to check out the EnvelopeAngle() method. Basically, the result of EnvelopeAngle() gives you the angle (in degrees) between the center of a geography instance and the furthest most outlying point of that instance. So it's one way of defining the geographic 'spread' of an instance.

    EnvelopeAngle() can come in useful in your case because, in order to fit within a single hemisphere, the result of the EnvelopeAngle() method must therefore be less than 90 degrees. (Otherwise, at least one point of the instance cannot be contained in the same hemisphere).

    With this in mind, keeping the same latitude bounds as you are using currently, you can work out the left and right longitudes of the maximum possible extent of your bounding box as follows:
    DECLARE @BoundingBox geography    
         
    DECLARE @TLLong DECIMAL(18, 10),    
    @TLLat DECIMAL(18, 10),    
    @BRLong DECIMAL(18, 10),    
    @BRLat DECIMAL(18, 10)    
            
    -- Hardocode some values for the purpose of this test.    
    SET @TLLong = -89.962186901  
    SET @TLLat = 82.11838360691267    
    SET @BRLong = 89.962186901  
    SET @BRLat = 0    
            
    SET @BoundingBox = geography::STGeomFromText('POLYGON((' + CONVERT(VARCHAR(18), @TLLong) + ' ' + CONVERT(VARCHAR(18), @TLLat) + ', 
      ' + CONVERT(VARCHAR(18), @TLLong) + ' ' + CONVERT(VARCHAR(18), @BRLat) + ', 
      ' + CONVERT(VARCHAR(18), @BRLong) + ' ' + CONVERT(VARCHAR(18), @BRLat) + ',  
      ' + CONVERT(VARCHAR(18), @BRLong) + ' ' + CONVERT(VARCHAR(18), @TLLat) + ', 
      ' + CONVERT(VARCHAR(18), @TLLong) + ' ' + CONVERT(VARCHAR(18), @TLLat) + '))', 4326)    
     
    SELECT @BoundingBox,  
    @BoundingBox.EnvelopeAngle() 

    The result of EnvelopeAngle() in this case is 89.9999713521094.
    Attempting to expand the bounding box further will take this result over 90, leading to the geography instance being invalid, so this is your largest bounding box (with these latitudes).
    Thursday, February 5, 2009 7:30 AM
    Answerer
  • Are you creating your bounding box in order to define an area in which to search for POIs?

    If so, rather than creating a large rectangular polygon and trying to do SELECT * WHERE location.STIntersects(Polygon), have you considered taking an alternative approach, something like:
    SELECT *   
    WHERE   
    POI.Lat BETWEEN TLLat AND BRLat  
    AND POI.Long BETWEEN BLLong AND TRLong 

    That way, you never need to worry about creating the polygon in the first place...
    Thursday, February 5, 2009 7:38 AM
    Answerer
  • yes i am. all my spatial calculations are point vs poly or point vs points or (my hardest problems) pointS (multiple) vs polys.

    as such, i was always trying do to STIntersects(..). I will definitely give your BETWEEN code a go in a few mins :)

    One question...

    Quote: Tanoshimi :: With this in mind, keeping the same latitude bounds as you are using currently, you can work out the left and right longitudes of the maximum possible extent of your bounding box as follows  (bolded added by myself)

    Why longitude? i thought the hemisphere problem was latitude and we can't have a bounding box that goes over the equator .. meaning .. i don't care about the longitude (or vertical) sides of the box?

    Now, you're suggesting I use this EnvelopeAngle() method (i must admit, i've never heard of that .. oops!) which gives the angle (in degress) between the centre and the furthest point. Ok.. so if i have a simply polygon rectangle (eg. the viewport of a VirtualEarth/GoogleMap map), then this method. Er.. I think i need to see a picture of this.. Time to use google... umm..yeah. gotcha :: http://blogs.msdn.com/davidlean/archive/2008/10/27/sql-2008-spatial-samples-part-n-3-of-n-performance-improvement-methods.aspx (hint: also one of my top spatial blogs i keep tabs on .. and a fellow Aussie I think!).

    ok, Dave has a good quote about the STEnvelopeAngle() ..

    Dave :: You can think of EnvelopeAngle as a distance. Your distance is measuring "Degrees of Longitude" or "Degrees of Latitude" so it is an Angle & not a linear measurement. It is the Radius of the bounding circle.

    OK -interesting. the Radius. Ok .. but how does having the radius help us? In your example above, you said:

    Tanoshimi: The result of EnvelopeAngle() in this case is 89.9999713521094.
    Attempting to expand the bounding box further will take this result over 90, leading to the geography instance being invalid, so this is your largest bounding box (with these latitudes).


    Now, the reason why it did that, was because the longest distance was the two sides, not the top and bottom. The shape provided was a rectangle, with the East<->West being longer than the North<->South (think widescreen, cause the viewport was generated on a widescreen monitor).

    But what i don't get is how this can help? you see, i don't want to modify the horizontal length of the bounding box, just the height (so it doesn't go over the equator). I can't see how the EnvelopeAngle() can help with that.


    -Pure Krome-
    Friday, February 6, 2009 12:14 AM
  • Pure Krome said:

    i thought the hemisphere problem was latitude and we can't have a bounding box that goes over the equator .. meaning .. i don't care about the longitude (or vertical) sides of the box?


    Nope - the hemisphere problem is that you can't have a geography instance that spans more than one-half of the earth's surface.
    It's got nothing to with the equator (nor the international dateline) and it doesn't mean that you can't have a polygon that spans the traditional division of 'northern'/'southern' hemisphere - in this case a 'hemisphere' means any half of the earth's surface.

    So, for instance, this is ok:

    geography::STPolyFromText('POLYGON((0 85, 0 -85, 179 -85, 179 85, 0 85))', 4326)



    and so is this:

    geography::STPolyFromText('POLYGON((60 -45, -135 -45, -135 45, 60 45, 60 -45))', 4326)



    So long as the points of the instance can be contained in one half of the earth's surface, you're ok.

    As you say, envelopecenter() gives a bounding circle (because it operates on the curved surface of the geography datatype). So think of the earth as a football, or something like that, and put a rubber band on the surface of the football representing the outer ring of a polygon.
    Now, make the polygon larger by pulling the rubber band down over the football. The largest polygon you can make is when it covers one half of the earth's surface - i.e. when the rubber band goes around the middle of the football. At this point, the angle between the point in the middle of the rubber band, and the band itself is 90 degrees.

    Make sense?

    Friday, February 6, 2009 8:35 AM
    Answerer
  • Hi Folks,

    First, I just put up a post on my blog that discusses exactly what and how the hemisphere limitation works.  There's nothing wrong with what Tanoshimi says, but there's a little more to the story.  It may be of interest to people on this thread.

    Second, let me say a few words about bounding boxes on the earth: they stink.

    Let me expand on this a bit---although it's probably worth another blog entry.

    One problem is that when people say they want a bounding box, they generally want a lower and upper bound on the latitude and longitude.  This is generally a reasonable request, but we cannot represent this cleanly as a polygon.  The two north-south edges (representing the minimum and maximum longitudes) are easy.  The east-west edges (representing the minimum and maximum latitudes) are not: they aren't great circles, but small circles or loxodromes

    Until we add small circle support to SQL there is no way to simply represent this polygon.

    A second problem—that the concept of a bounding box is really a planar concept—becomes evident when we cross over the line where longitude = 180 or -180, or at the poles.  What should the bounding box for LINESTRING(-140 30, 140 40) be?  Worse, what about LINESTRING(-90 80, 90 80)?

    Finally, let me try to be a little positive.  :)  If you want to query a box that ranges from MinLat to MaxLat, and from MinLong to MaxLong, you can create a polygon that will at least closely match the desired box.  As we've discussed, the edges of the box that run from (MinLong MaxLat) to (MinLong MinLat) and from (MaxLong MinLat) to (MaxLong MaxLat) are easy—they're great circles.

    The other two edges will have to be approximated by densifying them.  I.e., instead of going directly, place a number of points along the way, each at the same latitude.  You'll get a great circle between these points, but if you get the points reasonably close together you should not deviate very much.  You should be able to build this fairly easily using the sink/builder API.

    The polygon you generate won't be simple—it will have many points—but it should give you the query region you're looking for.

    Cheers,
    -Isaac

    Isaac Kunen, Microsoft SQL Server
    Friday, February 6, 2009 10:01 PM