locked
Error in saving geometry shape into sql server geometry data type. RRS feed

  • Question

  • Hi,

    I have a problem saving data (which is initially east and north points from shapfile, and transformed to lat/lon eventually) into SQL Server geometry data type. The error is shown below:

     


    Please refer to the error below that I added after the original post. Any idea and advice????????????????????

    They are polygons: polygon ((, ... ))

    I used open source to convert them, but unable to store them in geography data type.

    Please note that I have to do all these (reading from shapfile, transform them, store in db) programmatically.

    Msg 6522, Level 16, State 1, Line 1
    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.
    Microsoft.SqlServer.Types.GLArgumentException:
       at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
       at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData g)
       at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive()
       at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
       at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
       at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
    .


    Problem below is caused by Lat/lon

    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)
       at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
    .

     

     

    It works fine when it is saved into geometry data type instead of geography type. However, because the converted points is for Lat/lon, the shape is narrower than actual shape.

     


    Any solution or workaround?

     

    Thanks in advance!!

     

    • Edited by Pingpong689 Wednesday, November 24, 2010 2:29 PM
    Tuesday, November 23, 2010 11:31 PM

Answers

  • > hemisphere... looks like Bob won the bet!

    @Kingofwebguru - did you try looking at the link I posted above? The points in your polygon rings are listed in reverse order, which means that they're "inside-out" - they include everything on the outside of the polygon and exclude everything on the inside. But seeing as you've been able to import them into the geometry type, it's a relatively easy fix to correct the ring orientation.

    UPDATE table
    SET geom = geom.MakeValid();
    GO

    UPDATE
    table
    SET geog = geography::STGeomFromWKB( geom.STUnion ( geom.STStartPoint ( ) ) .STAsBinary ( ) , geom.STSrid )
    GO

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Pingpong689 Thursday, November 25, 2010 12:22 AM
    Wednesday, November 24, 2010 1:14 PM
    Answerer
  • If they're points, are you sure you're saving them as Long/Lat, not Lat/Long. SQL Server spatial mandates Long/Lat to be compliant with OGC.

    If you're converting polygons, remember the "not more than half the earth" rule and the polygon ring orientation rule. But these rules normally give more specific error messages, so I'm betting you're using Lat/Long when you should be using Long/Lat.

    If this doesn't resolve the issue, could you post WKB or WKT for points that cause the exception?

    Cheers, Bob Beauchemin, SQLskills

    • Marked as answer by Pingpong689 Thursday, November 25, 2010 12:22 AM
    Wednesday, November 24, 2010 2:39 AM
  • The code above works for most geometry, I have to add the code below to make the rest works:

     

    update dbo.greater_london_const_region2 set
    geog = geography::STGeomFromWKB(geom.MakeValid().Reduce(.00000001).STUnion(geom.STStartPoint()).MakeValid().STAsBinary(), 4326)
    where id = 5

     

     

    • Marked as answer by Pingpong689 Thursday, November 25, 2010 12:22 AM
    Wednesday, November 24, 2010 5:10 PM

All replies

  • If they're points, are you sure you're saving them as Long/Lat, not Lat/Long. SQL Server spatial mandates Long/Lat to be compliant with OGC.

    If you're converting polygons, remember the "not more than half the earth" rule and the polygon ring orientation rule. But these rules normally give more specific error messages, so I'm betting you're using Lat/Long when you should be using Long/Lat.

    If this doesn't resolve the issue, could you post WKB or WKT for points that cause the exception?

    Cheers, Bob Beauchemin, SQLskills

    • Marked as answer by Pingpong689 Thursday, November 25, 2010 12:22 AM
    Wednesday, November 24, 2010 2:39 AM
  • Like Bob said,

    could you post WKB or WKT for points that cause the exception?

    if there's too many, u can upload the file of points, somewhere .. and link to it, from here.


    -Pure Krome-
    Wednesday, November 24, 2010 4:01 AM
  • Seeing as Bob's put his money on "Lat/Long wrong way round", I'll place my bet onto "Self-intersecting polygon ring".

    Either way, seeing as you have been able to import your data into a column of the geometry datatype ok, you might find one of the suggestions here helps: http://www.beginningspatial.com/fixing_invalid_geography_data


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, November 24, 2010 7:53 AM
    Answerer
  • Please refer to the error below that I added after the original post. Any idea and advice????????????????????

    They are polygons: polygon ((, ... ))

    I used open source to convert them, but unable to store them in geography data type.

    Msg 6522, Level 16, State 1, Line 1
    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.
    Microsoft.SqlServer.Types.GLArgumentException:
       at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
       at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData g)
       at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive()
       at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
       at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
       at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)

    Wednesday, November 24, 2010 1:04 PM
  • > hemisphere... looks like Bob won the bet!

    @Kingofwebguru - did you try looking at the link I posted above? The points in your polygon rings are listed in reverse order, which means that they're "inside-out" - they include everything on the outside of the polygon and exclude everything on the inside. But seeing as you've been able to import them into the geometry type, it's a relatively easy fix to correct the ring orientation.

    UPDATE table
    SET geom = geom.MakeValid();
    GO

    UPDATE
    table
    SET geog = geography::STGeomFromWKB( geom.STUnion ( geom.STStartPoint ( ) ) .STAsBinary ( ) , geom.STSrid )
    GO

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Pingpong689 Thursday, November 25, 2010 12:22 AM
    Wednesday, November 24, 2010 1:14 PM
    Answerer
  • Hi


    Please find the link below to download the file, called LongLat.txt:

    http://www.sendspace.com/file/ama0e3

    It generates the error below:

    Msg 6522, Level 16, State 1, Line 1
    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.
    Microsoft.SqlServer.Types.GLArgumentException:
       at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
       at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData g)
       at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive()
       at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
       at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
       at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
    .

    Thanks in advance.

    Wednesday, November 24, 2010 2:44 PM
  •  

    The code below cannot work for all polygons

                       C# ADO.NET, same as DB version

                       SqlGeometry geom = SqlGeometry.STGeomFromText(new SqlChars(new SqlString("Polygon(()...)"))), 4326);
                      
                       geom = geom.MakeValid();

                       SqlGeography geog = SqlGeography.STGeomFromWKB(geom.STUnion(geom.STStartPoint()).STAsBinary(), 4326);

      It works for some points not for other points.

    System.ArgumentException was unhandled
      Message=24200: The specified input does not represent a valid geography instance.
      Source=Microsoft.SqlServer.Types
      StackTrace:
           at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
           at Microsoft.SqlServer.Types.SqlGeography.GeographyFromBinary(OpenGisType type, SqlBytes binary, Int32 srid)
           at Microsoft.SqlServer.Types.SqlGeography.STGeomFromWKB(SqlBytes wkbGeometry, Int32 srid)
         ....

     

    DB version:

    Msg 6522, Level 16, State 1, Line 10
    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.GeographyFromBinary(OpenGisType type, SqlBytes binary, Int32 srid)

     

    Is there a direct way to fix it (Polygon((....))) to a valid geography type, rather than using geometry in the between????

     

    The offending polygon:

    http://www.sendspace.com/file/3aa7oq
    Wednesday, November 24, 2010 2:59 PM
  • The code above works for most geometry, I have to add the code below to make the rest works:

     

    update dbo.greater_london_const_region2 set
    geog = geography::STGeomFromWKB(geom.MakeValid().Reduce(.00000001).STUnion(geom.STStartPoint()).MakeValid().STAsBinary(), 4326)
    where id = 5

     

     

    • Marked as answer by Pingpong689 Thursday, November 25, 2010 12:22 AM
    Wednesday, November 24, 2010 5:10 PM