locked
Can not convert geometry polygon to geography RRS feed

  • Question

  • HI,

    I kept getting error doing below and could not find anything wrong. Can anyone take a look?

    declare @Geog geography;
    declare @Geom geometry;


    set @Geom = geometry::STPolyFromText('POLYGON((101.672729 3.106615,101.674123 3.108650,101.674067 3.110243,101.672484 3.114602,101.672489 3.114701,101.676913 3.115032,101.677854 3.115422,101.679595 3.114258,101.682601 3.112739,101.686587 3.112115,101.688313 3.111350,101.694240 3.104536,101.695499 3.102511,101.696886 3.101461,101.695710 3.098697,101.695138 3.096237,101.695293 3.092493,101.698320 3.084698,101.702435 3.080928,101.703026 3.078771,101.702230 3.074790,101.703138 3.063713,101.704564 3.057330,101.705382 3.050862,101.702204 3.050689,101.698317 3.049582,101.690278 3.048998,101.679846 3.048987,101.673437 3.048943,101.673313 3.048761,101.673334 3.047441,101.673511 3.043867,101.672355 3.042247,101.672240 3.038895,101.671049 3.037673,101.664741 3.036156,101.664570 3.035070,101.649260 3.034952,101.636260 3.034944,101.635901 3.037785,101.632650 3.039883,101.631166 3.039528,101.630202 3.040778,101.627679 3.042344,101.628887 3.044759,101.632300 3.045590,101.633048 3.046620,101.632695 3.049429,101.625376 3.050387,101.620154 3.054541,101.617430 3.056373,101.615318 3.058156,101.618245 3.059763,101.619433 3.068790,101.620459 3.071334,101.622002 3.073351,101.621459 3.077336,101.622743 3.077964,101.624341 3.080588,101.626051 3.084464,101.629632 3.084332,101.631734 3.084223,101.646500 3.081966,101.648402 3.081910,101.650478 3.081828,101.651951 3.081659,101.656113 3.081614,101.660317 3.082865,101.664570 3.084622,101.666941 3.086903,101.669592 3.088133,101.670016 3.088801,101.670010 3.089700,101.669793 3.092784,101.669361 3.096330,101.670607 3.098411,101.671364 3.101454,101.672350 3.105324,101.672729 3.106615))'
        , 0);

    select @Geom, @Geom.IsValidDetailed()  -- It is valid.

    set @Geog = geography::STPolyFromText(@Geom.STAsText(), 4326)
    -- go below error.
    -- But I cannot find any point that is in the other hemisphere. And it is valid so the ring orientation should be good too.


    Msg 6522, Level 16, State 1, Line 17
    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
    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. To create a larger than hemisphere geography instance, upgrade the version of SQL Server and change the database compatibility level to at least 110.
    Microsoft.SqlServer.Types.GLArgumentException:
       at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData& g, Double eccentricity, Boolean forceKatmai)
       at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive(Boolean forceKatmai)
       at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid)
       at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)


    • Edited by Q John Chen Tuesday, December 22, 2015 5:04 AM
    Tuesday, December 22, 2015 5:03 AM

Answers

  • Hi Olaf,

    I figured out. It is the ring orientation. I forgot one step.

    set @Geom = @Geom.STUnion(@Geom.STStartPoint());

    Usually I do MakeValid() and STUnion() together. But this time, I checked STIsValid() (and IsValidDetail()) and thought "well, it's already good."

    BTW, I am using SQL 2014 now (upgraded from SQL 2008). From what you mentioned, SQL 2014 could take the wrong polygon I produced before?

    Thanks again,

    John

    Tuesday, December 22, 2015 12:53 PM
  • SQL 2014 could take the wrong polygon I produced before?

    Hello John,

    Yes, I run your SQL statement against SQL Server 2012 as as you can see by the screenshot it works; only the resulting polygon is a "little bit" large.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, December 22, 2015 1:08 PM

All replies

  • because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere.

    Hello John,

    That's a limitation for geography (and not for geometry), see Spatial Data Types Overview => Orientation of spatial data; you have to consider the "left hand" rule.

    That limitation was remove with SQL Server Version 2012.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Bob Beauchemin Tuesday, December 22, 2015 6:38 PM
    Tuesday, December 22, 2015 8:01 AM
  • Thanks Olaf,

    But the polygon is valid. Does that means the ring orientation is already correct? I also did the MakeValid() but the didn't make any difference for an already valid polygon.

    From the spatial view, it seems the polygon is completely inside a single hemisphere. I also checked all the points and could not find outlier.

    It's just strange.

    Thanks,

    John

    Tuesday, December 22, 2015 12:24 PM
  • Hello John,

    it is a valid geometry ploygon, but for SQL Server <= 2008 it's not a valid geography; it goes over 2 hemisphere, see screenshot. You have to reverse the order of Points in you polygon.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, December 22, 2015 12:40 PM
  • Hi Olaf,

    I figured out. It is the ring orientation. I forgot one step.

    set @Geom = @Geom.STUnion(@Geom.STStartPoint());

    Usually I do MakeValid() and STUnion() together. But this time, I checked STIsValid() (and IsValidDetail()) and thought "well, it's already good."

    BTW, I am using SQL 2014 now (upgraded from SQL 2008). From what you mentioned, SQL 2014 could take the wrong polygon I produced before?

    Thanks again,

    John

    Tuesday, December 22, 2015 12:53 PM
  • SQL 2014 could take the wrong polygon I produced before?

    Hello John,

    Yes, I run your SQL statement against SQL Server 2012 as as you can see by the screenshot it works; only the resulting polygon is a "little bit" large.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, December 22, 2015 1:08 PM