Spatial Problem -- Polygons
-
I've scratched my head over this for hours. Can someone tell me why @G1 works and @G3 doesn't work?
(@G2 is just @G3 shown as a LINESTRING instead of a POLYGON)
---------------------------------------------------------------------------
declare @g1 geography
declare @G2 geography
declare @g3 geography
-- works lovely, and connects back to starting point
set @G2 = 'POLYGON ((-118.624 34.2197, -118.643 34.2085, -118.61 34.2, -118.605 34.2156, -118.59 34.2255, -118.624 34.2197))'
select @G2 as [@g2]
-- also works lovely, and connects back to starting point
set @G2 = 'LINESTRING (-118.62 34.22, -118.61 34.22, -118.61 34.21, -118.62 34.2, -118.62 34.22)'
select @G2 as [@g2]
-- doesn't work when cast as a polygon
-- throws an error saying input string isn't a valid geography instance
set @g3 = 'POLYGON ((-118.62 34.22, -118.61 34.22, -118.61 34.21, -118.62 34.2, -118.62 34.22))'
select @g3 as [@g3]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Got an answer from another site. The problem is ring orientation. Reversing coordinates in positions 2 and 4 will make @g3 a valid polygon string. You would think that it wouldn't matter if you listed them in clockwise or counterclockwise order... but it does.
Note to self and those who may come after. The easiest solution seems to be a user defined function or try... catch... block that attempts to load the polygon into a geography variable and then, if it fails, reverses the order of the lat/long pairs and tries again.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Ring orientation in polystring/polygon spherical mapping is usually used to indicate which side of the polygon string is the area "contained" by the polygon.
For instance, consider a polygon string that trace the earth's equator: starting at the international date line, moving westward past Asia, then through Africa, South America, and connecting back to itself in the Pacific. Now, which hemisphere is the "inside" of our polygon, the Northern or Southern Hemisphere? Or a polygon that circles Australia, does it include Australia or does it include everything on earth except Australia?
One way to distinguish these two possibilities is by using ring orientation, usually the "right-hand rule". That is, as we follow the path of the polygon, the "inside" is always on our right. Thus for our Equator polygon above the inside would be the Northern Hemisphere. If we instead wanted to contain the Southern Hemisphere, then we would go East around the Equator instead of West.
And, IIRC, SS2008 has a rule that the "inside" of a polygon cannot be larger than a hemisphere; thus your error. It is my understanding that this restriction well be removed in the next release of SQL Server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Good explanation and most informative. At first I didn't see that the lines of a polygon on a globe could be seen as either defining the smaller area "inside" the lines or the larger area "outside" the lines.
The problem is, I can't count on all the users who might define points through a user interface to keep that concept in mind. Reversing the order of the coordinate pairs effectively turns an invalid polygon "inside out" (clockwise/counterclockwise or left hand/right hand). That's the reason for the note to self in my second post.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Thanks for the feedback, Bob. And yeah, the "cure" is to reverse it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Heh... it's the only way he could get the eyeholes on the helmet right. 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Heh, are you kidding? I've been wearing this thing backwards for years! :hehe:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Shouldn't your avatar look like this? 😀
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
But I am standing backwards also!
(Nice pic though! I'm sure I'll use it later... 🙂 )
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
But I am standing backwards also!
Like Bud in "Meet the Robinsons" ??
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply