locked
geometry or geography? converting between the two types? performing spatial intersections? RRS feed

  • Question

  • Well, problem number one is that I had difficulty importing polygon data with geographic coordinates into a geography data type column, probably because of the ring orientation.  And even though I've read a post on dealing with ring orientation by Spatial Ed, it doesn't seem to address polygons with hundreds, maybe thousands, of vertices.  Is there an easy way to reverse the ring order of something like this...?

    POLYGON((-92.15028 36.49866,-92.13271 36.49878,-92.12029 36.49878,-92.11518 36.49882,-92.09698 36.49875,-92.07882 36.49871,-92.06084 36.49859,-92.04281 36.49855,-92.02463 36.49853,-92.00652 36.49848,-92.00078 36.49846,-91.99991 36.49841,-91.98876 36.49844,-91.97255 36.49849,-91.95457 36.49855,-91.93641 36.49869,-91.91832 36.49885,-91.90028 36.49885,-91.88211 36.49887,-91.87332 36.49891,-91.855 36.49889,-91.83662 36.49891,-91.81835 36.49896,-91.79996 36.49911,-91.78152 36.49919,-91.77191 36.49916,-91.76381 36.4992,-91.75925 36.4992,-91.75489 36.49927,-91.7455 36.49934,-91.74073 36.49938,-91.72695 36.49941,-91.70846 36.49941,-91.6901 36.49941,-91.67228 36.49939,-91.65419 36.49938,-91.6366 36.49935,-91.6251 36.49928,-91.61768 36.49933,-91.60953 36.49936,-91.59976 36.49935,-91.5824 36.49933,-91.56486 36.49932,-91.55604 36.49928,-91.54606 36.49924,-91.53607 36.49911,-91.52604 36.49902,-91.5054 36.49877,-91.5002 36.49869,-91.49728 36.49867,-91.49119 36.49861,-91.4853 36.49852,-91.46811 36.49811,-91.459 36.49789,-91.45002 36.49767,-91.45007 36.49604,-91.45009 36.49532,-91.45057 36.48146,-91.45059 36.48093,-91.45072 36.47871,-91.45144 36.46631,-91.4524 36.45205,-91.45241 36.45195,-91.45273 36.43746,-91.45276 36.43736,-91.45273 36.43331,-91.45271 36.4227,-91.4532 36.40806,-91.45323 36.4078,-91.4537 36.39355,-91.45372 36.3933,-91.45392 36.37905,-91.45393 36.37879,-91.45393 36.37507,-91.45401 36.36464,-91.45401 36.36442,-91.45419 36.35029,-91.45418 36.35004,-91.45436 36.33554,-91.46368 36.33576,-91.4637 36.33576,-91.47238 36.33598,-91.47265 36.33599,-91.47742 36.33612,-91.49015 36.33649,-91.49039 36.33651,-91.50014 36.33657,-91.50867 36.3366,-91.50904 36.33662,-91.52763 36.3371,-91.52978 36.33716,-91.54141 36.3372,-91.5459 36.33721,-91.54789 36.33728,-91.55058 36.3373,-91.5659 36.33741,-91.56593 36.32278,-91.56595 36.32241,-91.56585 36.30886,-91.56587 36.30824,-91.56586 36.30804,-91.56592 36.29381,-91.56593 36.29358,-91.56583 36.27909,-91.56593 36.26467,-91.56593 36.26457,-91.56606 36.25007,-91.56606 36.24996,-91.56902 36.25006,-91.58398 36.25065,-91.58433 36.25068,-91.60186 36.25149,-91.60204 36.2515,-91.62049 36.25214,-91.62514 36.2523,-91.63785 36.25264,-91.63801 36.25265,-91.65577 36.25295,-91.65602 36.25295,-91.67382 36.25324,-91.69149 36.25316,-91.70935 36.25335,-91.72772 36.25359,-91.74589 36.25399,-91.76404 36.25412,-91.78355 36.25424,-91.8015 36.25454,-91.8195 36.25472,-91.83713 36.25516,-91.85447 36.25554,-91.87222 36.25603,-91.88855 36.25615,-91.90698 36.25626,-91.9252 36.25649,-91.94333 36.25654,-91.96133 36.25677,-91.97846 36.25719,-91.99643 36.25745,-92.01431 36.25798,-92.03231 36.2583,-92.05003 36.25863,-92.06758 36.25927,-92.08562 36.25944,-92.10487 36.25985,-92.1212 36.26009,-92.13909 36.26072,-92.1412 36.26077,-92.15693 36.26122,-92.1592 36.26128,-92.15905 36.27575,-92.15838 36.29038,-92.15713 36.30507,-92.1567 36.31983,-92.15621 36.33428,-92.15511 36.34888,-92.15448 36.34887,-92.15392 36.36342,-92.15328 36.37775,-92.15273 36.39235,-92.15185 36.40686,-92.15147 36.42149,-92.15063 36.43592,-92.15112 36.43593,-92.15147 36.45048,-92.15082 36.46486,-92.15101 36.4795,-92.15038 36.49424,-92.15028 36.49866))

     

    I have had no luck, whatsoever, using Morton's Shape2Sql.  It appears to work and I see the status bar running, but nothing is placed in the database when I check afterwards.  So I'm forced to load polygons like this....

    INSERT INTO @myBULLSHIT VALUES(
     geometry::STPolyFromText('POLYGON((-92.15028 36.49866,-92.13271 36.49878,....etc.

    Furthermore, if you notice, I'm loading it into a geometry data type, not a geography type, because I get errors..

    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

     

    Since I have a geometry data type storing lat/lng coordinates, I thought I could convert it to geography like so....

    UPDATE

     

    tblTownship

    SET

     

    twn_geog = geography::STGeomFromWKB(twn_geom_latlng.STAsBinary(), twn_geom_latlng.STSrid)

    WHERE

     

    twn_geom_latlng is not null

    Yet I still get errors....

    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)

     

    Now here is my other mystery.  When I try to run the following query...

    SELECT

     

    LOCATIONID, CountyName

    FROM

     

    tblLocation, tblCounty

    WHERE

     

    tblCounty.cty_geom_latlng.STIntersects(tblLocation.geom_gcs) = 1

    and

     

    tblLocation.geom is not null

    It will run for a few minutes and come up with no results.  Please note that cty_geom_latlng and geom_gcs are storing lat/lng coordinates in a geometry data type.  However, a similar query using geometry columns storing UTM coordinates will run perfectly.  What gives?

    Thursday, August 5, 2010 6:22 PM

Answers

  • My question now is whether the amount of data gives it a problem in sql server express, because although we recently purchased SQL server we don't have it running yet?  Also would multipart polygons be a culprit?

    SQL Server Express has a maximum database size limit of 10Gb, but otherwise has all the same spatial features as other editions of SQL Server. Multipolygons are fully supported, so I doubt that's the culprit either.

    Your first error message is quite descriptive: geography::STGeomFromWKB' failed because parameter 2 is not allowed to be null.

    The second parameter to the STGeomFromWKB needs to be the SRID in which your coordinates are defined... so what coordinate system are you using? 4326? 4269? Something else?

     

    The "not a valid geography instance" is a less helpful error message, but the most likely explanation is that you've probably got self-intersecting polygons. Try reading my post to see if any of the solutions there help: http://www.beginningspatial.com/fixing_invalid_geography_data


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Proposed as answer by Tom Li - MSFT Monday, August 9, 2010 12:08 PM
    • Marked as answer by razor_nate Monday, August 9, 2010 11:22 PM
    Friday, August 6, 2010 2:21 PM
    Answerer
  • Thanks for the help!  Yes self-intersecting polygons is the culprit.
    • Marked as answer by razor_nate Monday, August 9, 2010 11:22 PM
    Friday, August 6, 2010 6:08 PM

All replies

  • Try converting your geometry back to geography like this instead:
    UPDATE tblTownship
    SET twn_geog = geography::STGeomFromWKB(twn_geom_latlng.STUnion(twn_geom_latlng.STStartPoint()).STAsBinary(), twn_geom_latlng.STSrid)
    WHERE
    twn_geom_latlng is not null
    

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, August 5, 2010 6:46 PM
    Answerer
  • I'm still recieving the same error.....

    Msg 6522, Level 16, State 1, Line 2

    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)

    .

    Thursday, August 5, 2010 8:47 PM
  • It's a bit hard to tell without seeing your actual data, but that fix works for the POLYGON example you gave at the beginning of your post, which had incorrect ring orientation.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, August 5, 2010 9:34 PM
    Answerer
  • I was able to convert tblCounty to cty_geom to cty_geog.  However when I attempted it on tblQuads I received an error....

    UPDATE

     

    tblQuad

    SET

     

    quad_geog = geography::STGeomFromWKB(quads_GCS.STUnion(quads_GCS.STStartPoint()).STAsBinary(), quads_GCS.STSrid)

    WHERE

    tblQuad

    .quads_GCS is not null

    Msg 6569, Level 16, State 1, Line 2

    'geography::STGeomFromWKB' failed because parameter 2 is not allowed to be null.

     

    When I attempted to convert tblTownship I receive this error....

    UPDATE

     

    tblTownship

    SET

     

    twn_geog = geography::STGeomFromWKB(twn_geom_latlng.STUnion(twn_geom_latlng.STStartPoint()).STAsBinary(), twn_geom_latlng.STSrid)

    WHERE

    twn_geom_latlng

    is not null

    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)

     

    My question now is whether the amount of data gives it a problem in sql server express, because although we recently purchased SQL server we don't have it running yet?  Also would multipart polygons be a culprit?

    Friday, August 6, 2010 1:36 PM
  • My question now is whether the amount of data gives it a problem in sql server express, because although we recently purchased SQL server we don't have it running yet?  Also would multipart polygons be a culprit?

    SQL Server Express has a maximum database size limit of 10Gb, but otherwise has all the same spatial features as other editions of SQL Server. Multipolygons are fully supported, so I doubt that's the culprit either.

    Your first error message is quite descriptive: geography::STGeomFromWKB' failed because parameter 2 is not allowed to be null.

    The second parameter to the STGeomFromWKB needs to be the SRID in which your coordinates are defined... so what coordinate system are you using? 4326? 4269? Something else?

     

    The "not a valid geography instance" is a less helpful error message, but the most likely explanation is that you've probably got self-intersecting polygons. Try reading my post to see if any of the solutions there help: http://www.beginningspatial.com/fixing_invalid_geography_data


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Proposed as answer by Tom Li - MSFT Monday, August 9, 2010 12:08 PM
    • Marked as answer by razor_nate Monday, August 9, 2010 11:22 PM
    Friday, August 6, 2010 2:21 PM
    Answerer
  • Thanks for the help!  Yes self-intersecting polygons is the culprit.
    • Marked as answer by razor_nate Monday, August 9, 2010 11:22 PM
    Friday, August 6, 2010 6:08 PM