locked
Invalid Polygon data - Texas RRS feed

  • Question

  • I'm importing US State data from the Census site, all states are imported except for two (TX and NJ). I'm getting this error: 

    System.ArgumentException: 24200: The specified input does not represent a valid geography instance

    From what I have read on these forums, that error typically means that the polygon has intersected itself. I can see on the northern border of TX where there are very tight lead ins.. which might cause some lines to intersect when placed in to geography (curved earth..)

    What I don't understand, is why the souther border of Oklahoma worked fine, which has the same shape lead-ins, but in reverse??

     

    I want to post my point data for TX, but it is 58,000 points.. and these forums keep crashing when I try to paste them in, is there another way for me to post my data so someone can help me? Thanks

     

    KB

    Thursday, August 5, 2010 2:10 PM

Answers

  • Yeah, looks like the precision rules of geography are causing a self-intersection problem somewhere. A workaround for you is to create it first as a geometry, smooth out the edges a bit using Reduce(), and then convert it back into geography:

    First, change the datatype of @TX to geometry:

    DECLARE @TX AS geometry
    
    SET @TX = 
    'POLYGON((-94.27627 33.558254, 
    -94.27826 33.557556,<br/>
    ...<br/>
    ...<br/>
    .. <br/>
    
    

    Then, Reduce() the geometry ever so slightly and convert it back to geography:

    DECLARE @tx_fixed geography
    SET @tx_fixed = geography::STGeomFromWKB(@tx.Reduce(0.000001).STAsBinary(), 4326)
    
    
    Now, you should be able to select @tx_fixed ok.

     


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Kbalz Thursday, August 5, 2010 7:34 PM
    Thursday, August 5, 2010 3:48 PM
    Answerer
  • That one's a simple case of incorrect ring orientation. You can fix it as follows:

    SET @NotWorkingState_Geography = geography::STGeomFromWKB(@NotWorkingState_Geometry.STUnion(@NotWorkingState_Geometry.STStartPoint()).STAsBinary(), 4326)
    SELECT @NotWorkingState_Geography
    
    You might find my blog post helpful: http://www.beginningspatial.com/fixing_invalid_geography_data


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Kbalz Thursday, August 5, 2010 8:12 PM
    Thursday, August 5, 2010 7:53 PM
    Answerer

All replies

  • Try http://pastebin.com/

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, August 5, 2010 3:10 PM
    Answerer
  • Took about 35 minutes, eventually pastebin took the 'paste' action :D

     

    http://pastebin.com/kLUyj7UM

    Thursday, August 5, 2010 3:24 PM
  • Yeah, looks like the precision rules of geography are causing a self-intersection problem somewhere. A workaround for you is to create it first as a geometry, smooth out the edges a bit using Reduce(), and then convert it back into geography:

    First, change the datatype of @TX to geometry:

    DECLARE @TX AS geometry
    
    SET @TX = 
    'POLYGON((-94.27627 33.558254, 
    -94.27826 33.557556,<br/>
    ...<br/>
    ...<br/>
    .. <br/>
    
    

    Then, Reduce() the geometry ever so slightly and convert it back to geography:

    DECLARE @tx_fixed geography
    SET @tx_fixed = geography::STGeomFromWKB(@tx.Reduce(0.000001).STAsBinary(), 4326)
    
    
    Now, you should be able to select @tx_fixed ok.

     


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Kbalz Thursday, August 5, 2010 7:34 PM
    Thursday, August 5, 2010 3:48 PM
    Answerer
  • That worked for Texas, but I can't get it work for some states that worked going directly to Geography - check @NotWorkingState variables.

     

    I get this error: System.ArgumentException: 24200: The specified input does not represent a valid geography instance.

     

    http://pastebin.com/9ME4DgDd

     

    I think mixing the two approaches I can get all my states in, but I'd like to know why this one fails, because I will be moving on to other imports soon. Thanks for everything!

    Thursday, August 5, 2010 7:23 PM
  • That one's a simple case of incorrect ring orientation. You can fix it as follows:

    SET @NotWorkingState_Geography = geography::STGeomFromWKB(@NotWorkingState_Geometry.STUnion(@NotWorkingState_Geometry.STStartPoint()).STAsBinary(), 4326)
    SELECT @NotWorkingState_Geography
    
    You might find my blog post helpful: http://www.beginningspatial.com/fixing_invalid_geography_data


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Kbalz Thursday, August 5, 2010 8:12 PM
    Thursday, August 5, 2010 7:53 PM
    Answerer
  • Ah so there is no one approach to getting every state I see. Your blog helps a lot thanks.

     

    PS - Did you get Alaska to work, and HI?

    Thursday, August 5, 2010 8:12 PM
  • Got all states using each of your methods!
    Thursday, August 5, 2010 8:40 PM