locked
Spatial Results is mirrored RRS feed

  • Question

  • Hi there...

    I'm in my initial stage of obtaining knowledge regarding Spatial Features in SQL Server 2008, so far it has been a great journey :)

    A long the path there is however one thing I'm getting confused by..., I have imported a lot of polygons that represents all the parishes in Denmark. But when looking at the Spatial Results pane it looks like the map is mirrored and turned clockwise, please take a look here:

    http://cid-c54c30033f515f8a.skydrive.live.com/self.aspx/.Public/dk%5E_wrongway.png


    I've declared a column as geography and inserted polygons like this format:
    POLYGON ((55.0697880535906 12.2686701528047, 55.0751532964085 12.2590473886913, 55.0793953982605 12.2581074809919, 55.0799380289411 12.260674916252, 55.0821838982713 12.2607480858186, 55.0853066343666 12.2512370875849, 55.0846215710444 12.2583603311118, 55.0798512936605 12.2649151202313, 55.0785417964604 12.2621127650909, 55.0750982043364 12.2724883869907, 55.0681271789614 12.275929950915, 55.0654753998429 12.2735667499275, 55.0649248824169 12.269965413754, 55.0697880535906 12.2686701528047))


    Please notice when I pick a random point in Denmark at Bing Maps I get a coordinate like this:
    <Point>     <coordinates>12.482694625384851,54.98492896611987</coordinates>
    </Point>

    What is the correct latitude and longitude format?

    I've tried to reverse lat long in the polygons, but this throws an error:
    Msg 6522, Level 16, State 1, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
    System.ArgumentException: 24200: The specified input does not represent a valid geography instance.
    System.ArgumentException:
       at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
       at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)



    However when I declare the datatype as geometry and reloads the polygons it suddenly works fine, take a look here:
    http://cid-c54c30033f515f8a.skydrive.live.com/self.aspx/.Public/dk^_rightway.png


    Why is this happening?
    Tuesday, December 15, 2009 4:01 PM

Answers

  • There are several differences between the behaviour of geometry and geography, and one of them is that geography can't be builded from invalid data.

    In general, you can check this with the method .STIsValid, and fix the geometries with the method .MakeValid(). But there are special cases, Ed has treated the problem in several posts

    http://blogs.msdn.com/edkatibah/archive/2008/07/24/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1.aspx
    http://blogs.msdn.com/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx
    http://blogs.msdn.com/edkatibah/archive/2009/06/05/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-2.aspx

    • Marked as answer by Janus007 Wednesday, December 16, 2009 10:08 AM
    Wednesday, December 16, 2009 7:51 AM
  • SQL Server stores two types of spatial data:
    - the geometry datatype is used for storing X,Y coordinate data on a flat plane. This could be data from a CAD application, a floor plan, or a flat map such as a National Grid system (I believe there is a Danish National Grid?).
    - the geography datatype is used for storing angular coordinates of longitude, latitude. The most common sorts of long/lat coordinates are those obtained from GPS devices, which are measured using SRID 4326, but there are other systems.

    The thing to note is the coordinate ordering I've listed above. geography expects longitude/latitude, which is consistent with the X/Y order of the geometry datatype (since most maps have longitude extending along the X axis and latitude along the Y).

    So, to answer your first question, the correct latitude/longitude format is longitude then latitude, which is the opposite of what you currently have.

    When you reverse the order but use the geometry format, the data appears correctly because what you are then doing is treating each longitude value as the X coordinate value, and each latitude value as a Y coordinate value (in other words, you have created an equirectangular projection of your data). However, while this may "look" correct, you're not going to be able to use any of the spatial methods like STArea(), STDistance() etc. because your data is not truly projected - it's just longitude/latitude data stored using the geometry datatype.

    For longitude/laittude data, you should be using the geography datatype but, judging from the error message, some of your data is currently invalid. The geography datatype is more strict than the geometry datatype in several respects and it is common to experience these kind of errors, especially if you are using free data. The most common reason is that your polygon rings have the incorrect ring orientation - for geography it matters what order your points are listed in. Spatial Ed wrote a blog post to describe a trick to fix invalid data, here: http://blogs.msdn.com/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx

    And lastly, to explain why your coordinate order seems to swap round when you select a point - it appears that you are selecting the point as GML, using the AsGml() method, which (rather confusingly) always uses lat/long ordering. It therefore swaps the order of your original coordinates (although these were in the incorrect order anyway).

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Janus007 Wednesday, December 16, 2009 10:08 AM
    Wednesday, December 16, 2009 8:15 AM
    Answerer

All replies

  • There are several differences between the behaviour of geometry and geography, and one of them is that geography can't be builded from invalid data.

    In general, you can check this with the method .STIsValid, and fix the geometries with the method .MakeValid(). But there are special cases, Ed has treated the problem in several posts

    http://blogs.msdn.com/edkatibah/archive/2008/07/24/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1.aspx
    http://blogs.msdn.com/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx
    http://blogs.msdn.com/edkatibah/archive/2009/06/05/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-2.aspx

    • Marked as answer by Janus007 Wednesday, December 16, 2009 10:08 AM
    Wednesday, December 16, 2009 7:51 AM
  • SQL Server stores two types of spatial data:
    - the geometry datatype is used for storing X,Y coordinate data on a flat plane. This could be data from a CAD application, a floor plan, or a flat map such as a National Grid system (I believe there is a Danish National Grid?).
    - the geography datatype is used for storing angular coordinates of longitude, latitude. The most common sorts of long/lat coordinates are those obtained from GPS devices, which are measured using SRID 4326, but there are other systems.

    The thing to note is the coordinate ordering I've listed above. geography expects longitude/latitude, which is consistent with the X/Y order of the geometry datatype (since most maps have longitude extending along the X axis and latitude along the Y).

    So, to answer your first question, the correct latitude/longitude format is longitude then latitude, which is the opposite of what you currently have.

    When you reverse the order but use the geometry format, the data appears correctly because what you are then doing is treating each longitude value as the X coordinate value, and each latitude value as a Y coordinate value (in other words, you have created an equirectangular projection of your data). However, while this may "look" correct, you're not going to be able to use any of the spatial methods like STArea(), STDistance() etc. because your data is not truly projected - it's just longitude/latitude data stored using the geometry datatype.

    For longitude/laittude data, you should be using the geography datatype but, judging from the error message, some of your data is currently invalid. The geography datatype is more strict than the geometry datatype in several respects and it is common to experience these kind of errors, especially if you are using free data. The most common reason is that your polygon rings have the incorrect ring orientation - for geography it matters what order your points are listed in. Spatial Ed wrote a blog post to describe a trick to fix invalid data, here: http://blogs.msdn.com/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx

    And lastly, to explain why your coordinate order seems to swap round when you select a point - it appears that you are selecting the point as GML, using the AsGml() method, which (rather confusingly) always uses lat/long ordering. It therefore swaps the order of your original coordinates (although these were in the incorrect order anyway).

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Janus007 Wednesday, December 16, 2009 10:08 AM
    Wednesday, December 16, 2009 8:15 AM
    Answerer
  • Thank you both for this valuable information.

    After a lot of thinking and reading based at your input I took each coordinate and switched them, furthermore I reversed the order and ended up with, to what I understand/ think is correct

    POLYGON ((12.4079766873832 54.9902897119586, 12.4149161473538 54.9828833907892, 12.4070402856653 54.9788783035892, 12.4062938844105 54.968225864874, 12.4187125836535 54.9637317359534, 12.4452146569631 54.9636933463805, 12.4586197109044 54.974912162407, 12.4759615289657 54.9773216730289, 12.4750683078861 54.9917615553007, 12.4708162690976 54.9919640098738, 12.4767372349474 55.0180875925484, 12.4694138751627 55.0200862528894, 12.463196337723 55.0225744987751, 12.4621914276388 55.0228998600548, 12.4607816114068 55.0228860521453, 12.4571856570392 55.0223859077145, 12.4516796743487 55.0226053137435, 12.444203737221 55.0237615104338, 12.4392823380241 55.0238648754238, 12.435282385037 55.0243106746683, 12.4346166136354 55.0242125912714, 12.4289639961793 55.0230863181791, 12.4262057359465 55.0232270309795, 12.4234825117744 55.0224944806657, 12.4209214448894 55.0223777475804, 12.4258829941888 55.0163303452819, 12.4119385994459 55.0157604159994, 12.4044999891345 55.0066534317438, 12.4193574743599 54.9994294849658, 12.4079766873832 54.9902897119586))


    Which should be somewhere in Denmark - now the Spatial Results is showing the right picture, but still I don't understand why the order should be longitude, latitude i.e. (12, 55 ) as in my sample data.

    Acoording to this: http://blogs.msdn.com/isaac/archive/2007/12/27/latitude-longitude-ordering.aspx  it should be the other way around!

    Tanoshimi: You said it should be longitude/latitude , it works - thank you :) , but the blog posting above tells something different - is it outdated and the ordering finally settled?

    Wednesday, December 16, 2009 10:22 AM
  • Isaac wrote that post in December 2007, at which point SQL Server 2008 had not been released. The latest available version at that time was I think CTP6, which as he states used latitude-longitude ordering.

    After much protesting from the developer community (most of it coming from those here on this forum), the decision was made to swap the ordering to the more industry-standard longitude-latitude (well done MS!), and this is the version used in all released versions of SQL Server 2008.

    So, yes, that post is sort of "out-of-date", but if you read it carefully, he is actually describing that the change to longitude-latitude will happen.
    Be careful if you follow any other examples posted on the internet before about August 2008 (or possibly later) - many of them will incorrectly still use latitude-longitude.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, December 16, 2009 11:21 AM
    Answerer