locked
Single emisphere problem with Antarctica RRS feed

  • Question

  • Hello !

    I have recently donwloaded shape files from Natural Earth Data (110m scale ranks)

    These are the shapes for all countries. All are importing OK except one : Antarctica.

    Sql Server says (in french): Microsoft.SqlServer.Types.GLArgumentException: 24205 : l'entrée spécifiée ne représente pas d'instance géographique valide, car elle dépasse un seul hémisphère. Chaque instance géographique doit s'ajuster à un seul hémisphère. Cette erreur a souvent pour origine une mauvaise orientation de l'anneau du polygone.

    This means that I broke the single emisphere rule. OK.

    Now what is the good way for having it as a geography in my table ?

    • Break it into single emisphere parts ?
    • Do something I didn't think about ?
    • Use geometry instead ?

    Other case is Russia that is crossing International Date Line...

    I'm looking for best pratices for that cases.

    Thank you

    Xavier

    Thursday, April 19, 2012 2:48 PM

Answers

  • Hi Alastair and thank you for answering,

    I found what the problem was (and you're right, the problem was in the data) :
    In Natural Earth's data, although data is in WGS84 coordinates, the features are projected onto a plane projection.

    I wrote that code to plot every point in the feature :

    declare @i int declare @max int declare @p geometry set @i = 1 set @max = @geomAntarctica.STNumPoints() CREATE TABLE #union ( geom geometry NULL, label char(10) null) WHILE @i<@max BEGIN SET @p = @geomAntarctica.STPointN(@i).STBuffer(0.8) INSERT INTO #union SELECT @p, cast(@i as char(10)) SET @i = @i + 1 END SELECT * FROM #union UNION ALL SELECT @geomAntarctica, 'Antarctica'

    DROP TABLE #union

    Here is the result (stretched for visibility) :

    Now the problem is clear : the feature has two points : (-180 -90) and (180 -90).
    Those points ensure that the geometry is a closed polygon. It loads and displays ok in geometry data type.
    But for geography data type, there are two issues :

    1) The resulting segment for the two points is the South Pole itself : the resulting geography would self intersect...
    -> solution : remove the two points from the coordinate sequence
    2) (minor) the points are sequenced clockwise, which means that the feature actually represents everything BUT Antarctica.
    -> solution : reverse the coordinate sequence

    And then, the feature is OK in geography.

    Now, is this really a problem with Natural Earth data: although I can say the data is SQL-SERVER-geographically invalid, can I say it is invalid in an absolute way ?

    Thank you.

    PS : I cannot install SQL Server 2012 on my office dev computer because it is still in WinXP...


    • Edited by Xavier Fischer Tuesday, April 24, 2012 11:24 AM Code incorrect
    • Marked as answer by Xavier Fischer Tuesday, April 24, 2012 11:24 AM
    Tuesday, April 24, 2012 11:21 AM

All replies

  • I have more news : By searching in this forum I have found tanomishi post with the simplified world borders.

    I have imported the file and everything is OK.

    So I presume that the polygon from Natural Earth is self intersecting.

    When in geometry data type, STIsValid() returns 1. I have tried the buffer / debuffer trick but it only works with big buffers not preserving the shape.

    So how can I fix this ? I Will continue diving...

    Thursday, April 19, 2012 3:48 PM
  • Hi there,

    I've not tried the Natural Earth dataset, so I can't say for certain what the problem is.

    However, I can say that there the "single hemisphere" rule doesn't mean that a geography polygon can't cross the international date line, or that it can't sit over the poles. In fact, that's exactly the sort of situation where the geography datatype is better than the geometry datatype, which treats spatial data as if it were on a map with edges.

    The single hemisphere rule means that a geography polygon can't encompass more than one-half of the surface of the earth. However, this shouldn't be an issue either for Antarctica or Russia (as you found out from the simplified world borders dataset I posted). So, it's probably something else wrong with the data instead.

    The fact that Buffer/Debuffer solves the issue suggests that it's probably self-intersection/spikes. Is using SQL Server 2012 an option? The increased precision means SQL Server 2012 is more tolerant of erroneous data than SQL Server 2008/R2, and it also has a useful IsValidDetailed() method that might help narrow down the problem.


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Proposed as answer by amber zhang Friday, April 20, 2012 7:03 AM
    Thursday, April 19, 2012 4:33 PM
    Answerer
  • Hi Alastair and thank you for answering,

    I found what the problem was (and you're right, the problem was in the data) :
    In Natural Earth's data, although data is in WGS84 coordinates, the features are projected onto a plane projection.

    I wrote that code to plot every point in the feature :

    declare @i int declare @max int declare @p geometry set @i = 1 set @max = @geomAntarctica.STNumPoints() CREATE TABLE #union ( geom geometry NULL, label char(10) null) WHILE @i<@max BEGIN SET @p = @geomAntarctica.STPointN(@i).STBuffer(0.8) INSERT INTO #union SELECT @p, cast(@i as char(10)) SET @i = @i + 1 END SELECT * FROM #union UNION ALL SELECT @geomAntarctica, 'Antarctica'

    DROP TABLE #union

    Here is the result (stretched for visibility) :

    Now the problem is clear : the feature has two points : (-180 -90) and (180 -90).
    Those points ensure that the geometry is a closed polygon. It loads and displays ok in geometry data type.
    But for geography data type, there are two issues :

    1) The resulting segment for the two points is the South Pole itself : the resulting geography would self intersect...
    -> solution : remove the two points from the coordinate sequence
    2) (minor) the points are sequenced clockwise, which means that the feature actually represents everything BUT Antarctica.
    -> solution : reverse the coordinate sequence

    And then, the feature is OK in geography.

    Now, is this really a problem with Natural Earth data: although I can say the data is SQL-SERVER-geographically invalid, can I say it is invalid in an absolute way ?

    Thank you.

    PS : I cannot install SQL Server 2012 on my office dev computer because it is still in WinXP...


    • Edited by Xavier Fischer Tuesday, April 24, 2012 11:24 AM Code incorrect
    • Marked as answer by Xavier Fischer Tuesday, April 24, 2012 11:24 AM
    Tuesday, April 24, 2012 11:21 AM