The Upcoming Geography Coordinate Order Swap---A FAQ

The Upcoming Geography Coordinate Order Swap---A FAQ

  • Comments 14

Hi Folks,

I just thought I'd take a few moments clarify the upcoming coordinate order swap for the geography type.  Here's a quick FAQ on the issue:

  • What exactly is the change?

    We are swapping the coordinate order for well-known text (WKT) and well-known binary (WKB) formats from latitude-longitude to longitude-latitude.  E.g., a point in the Seattle area might be represented in WKT as "POINT(44 -122)" in the current ordering; in the new order it will be "POINT(-122 44)".
  • Why are we making this change?

    Because of customer feedback.  While common practice almost universally uses latitude-longitude ordering, the de facto standard for WKT and WKB is to use a longitude-latitude order.  Making this change will make it much easier for customers to load WKT and WKB data into the geography type.
     
  • What about GML? 

    The standard paractice for GML is to use latitude-longitude ordering, and so no change will be made for GML.
  • Is the on-disk format changing?

    Nope.  Ths is just a matter of changing our routines that import and export data.  Nothing beyond those routines will change.
  • What about geometry?

    There is no change to the geometry type.  Geometry deals with objects on the plane, and is pretty agnostic about the coordinate order outsidfe of the STX and STY properties.
  • When is the swap happening?

    It is not the current Februrary CTP, but the team is working on it now.  Expect to see it in our next public release.

If there are more questions, I'll be happy to answer them here.

Cheers,
-Isaac

Minor Update: I originally got the WKT examples at the top wrong, placing commas between coordinates.  The examples have been updated to their correct, comma-free state.  (Thanks, Steven!)

  • No questions. Thanks for the clarification.

  • For those of you who have programs which work with November CTP (CTP-5) and February CTP (CTP-6) and

  • "The standard paractice for GML is to use latitude-longitude ordering"

    That's not exactly accurate.  GML uses the coordinate ordering specified in the coordinate reference system.  It's close-enough to true for most users though.

    I applaud this change.  Requiring knowledge of CRS for the use cases of WKT/WKB is overkill.

  • Hi Folks, We have one more upcoming pre-release before we're done with SQL Server 2008, and while I've

  • Hi Folks, As we continue to shut down SQL Server 2008, our first release candidate has been released

  • Hi,

    I'm lead author in the new MS Press book "Programming Microsoft SQL Server 2008". Naturally, I had to update the manuscript for the chapter on geospatial to reflect the geography coordinate swap introduced with RC0.

    However, I find it very strange that the swap was made only for POINT, and that the other WKT keywords (LINESTRING, POLYGON, etc.) continue to express their coordinates using lat-long.

    I'm also confused as to how MS can change WKT, which I thought was a standard whose syntax is governed by the OGC.

    Can you please clarify?

    Thanks,

    ~ Lenni

  • Hi Lenni,

    The swap is for all WKT and WKB, not just for POINT elements.  Perhaps the confusions is that we are swapping latitude and longitude for every ("little-p") point in WKT, not just for ("big-P") POINT elements.

    Regarding standards, there simply is no OGC standard that specifies the coordinate ordering for geographic coordinates in WKB and WKT because there is no OGC standard that covers round-Earth systems.

    That said, the new, swapped coordinates comply better with usual industry practice, which is why we made the swap: it's better for interoperability, not worse.

    Perhaps the background here would be useful: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2431933&SiteID=1

    Cheers,

    -Isaac

  • Isaac,

    Thanks for the response. It does clarify matters somewhat, but there is no doubt that the swap in RC0 is only for POINT elements.

    That is, when revising my code that worked in CTP6 to work in RC0, it was necessary to swap the values in all my POINT elements but *not* any of the other elements (LINESTRING, POLYGON, etc.)

    So this is still confusing to me in RC0.

    And in another change from CTP to RC0, polygons must be loaded in clockwise order; whereas in CTP6 they had to be loaded in counter-clockwise order.

    So these WKT strings worked in CTP6:

     POINT(45.01188 -111.06687)

     POLYGON(39.95601 -75.17031,

             39.95778 -75.16786,

             39.96874 -75.17921,

             39.96512 -75.18441,

             39.95601 -75.17031)

    But in RC0, lat and long needs to be swapped *only* for the POINT (not the POLYGON), and the POLYGON points need to be specified in reverse order:

     POINT(-111.06687 45.01188)

     POLYGON(39.95601 -75.17031

             39.96512 -75.18441,

             39.96874 -75.17921,

             39.95778 -75.16786,

             39.95601 -75.17031)

    So...

    1) Is lat/long also going to be swapped for WKT elements besides POINT in RTM?

    2) Is the clockwise/counter-clockwise swap from CTP6 to RC0 going to stick in RTM?

    Thanks again,

    ~ Lenni

  • I can definitively say that:

    1) All latitudes and longitudes have been swapped in WKT and WKB.

    2) There has been no clockwise/counter-clockwise swap.

    The coordinate order swap exactly explains the behavior you're seeing.  If we consider a small object away from the poles---like yours---we can squint and imagine it's on the plane.  If we swap our interpretation of your points while you keep the string the same, then your instance essentially get reflected along the line x=y.  This reflection inverts the orientation of every ring, making it seem like we've changed how we interpret orientation.

    So, your second polygon is a different polygon than your first, but it happens to be legal because your longitude values are in range for a latitude.  You'll get an error interpreting an your un-swapped point because -111.06687 less than -90, and therefore out of range for a latitude value.

    A few examples might illustrate:

    ----------

    declare @g geography = geography::Parse('

     POLYGON ((39.95601 -75.17031

            39.96512 -75.18441,

            39.96874 -75.17921,

            39.95778 -75.16786,

            39.95601 -75.17031))')

    -- In RC0 each point is interpreted

    -- as long-lat, not lat-long.  

    -- We can test this:

    select @g.STPointN(1).Long, @g.STPointN(1).Lat

    -- returns: 39.95601 -75.17031

    select @g.STIntersects(geography::Parse('POINT (39.95602 -75.1703)'))

    -- returns: 1

    select @g.STIntersects(geography::Parse('POINT (-75.1703 39.95602)'))

    -- returns: 0

    -----------

    To get a new polygon for RC0 that matches your original CTP6, you need to invert each of the points.  I.e.:

     POLYGON ((-75.17031 39.95601,

            -75.16786 39.95778,

            -75.17921 39.96874,

            -75.18441 39.96512,

            -75.17031 39.95601))

    Does this help?

    Cheers,

    -Isaac

  • Isaac...

    Thanks so much for the clarification and also for such quick responsiveness (so rare these days!).

    I see now how I got caught in this trap. When my CTP6 code failed to run in RC0, the first thing I did was fix the POINT elements by swapping lat and long (which I guessed was the problem, based on the error message).

    Then, when I tried to run the POLYGON code, I got the "wrong orientation" error. So rather than just swapping lat and long like I did for POINT, I reversed the order of the POLYGON coordinates themselves. When this worked, I was naturally led to believe that the swap was for POINT only, *plus*, POLYGON point plotting was also reversed--and that I had now recreated the very same POLYGON. In fact, of course, because lat was now being treated as long in RC0, I was actually drawing a completely different polygon from a completely different part of the earth entirely, and the orientation had not in fact changed from CTP6 to RC0 (That also explains why the area calculation was different!).

    Thanks to your help, I'm able to understand this clearly so I can revise the manuscript accordingly. Much appreciated!

    ~ Lenni

  • Hi Lenni,

    No sweat---this stuff can get confusing.  :)  (Incidentally, the map viewer should help out mightily in this regard, but you will have to wait for RTM to get it.)

    Cheers,

    -Isaac

  • Cool. Can you elaborate a little bit on the map viewer? In the chapter, I talk about how to get your hands on lat/long values. I mention that (1) coordinates for all major cities in the world can be easily found with a quick Web search, (2) Microsoft Streets and Trips has a location sensor tool to give you any coordinates you point to on the map with the mouse, and (3) GPS devices for DSLR cameras can stamp digital photos with location info.

    If indeed the product itself (RTM) will include a "map viewer" that can also be used to obtain coordinates, I'd of course want to include that information in the book as well. This does present a challenge, because MS Press is pushing for a complete manuscript before RTM. So any info you can get to me on this "map viewer" that will be shipped with the final product would be very much appreciated (once again :)).

    ~ Lenni

  • Hi Lenni,

    Have you looked at my more recent post here: http://blogs.msdn.com/isaac/archive/2008/06/02/eye-candy-of-the-highest-order.aspx

    Beyond that, perhaps you should contact me over email: isaack@...

    -Isaac

  • Se foste capitati su mio vecchio post   sul tipo geografico di SQL Server 2008, e aveste provato

Page 1 of 1 (14 items)