locked
Making valid Geography from WKB RRS feed

  • Question

  • Hi all,

    In the interests of finding an interoperable way of getting data from Oracle Spatial to SDE/SQL Server 2008
    my colleage has created a view that runs a function to convert SDO Geometry to WKB.

    I have a DB Link and can pull all records as they are. In order to register the data with ESRI SDE I need to use SQL Geography datatype

    SELECT MI_PRINX, LOCALITY,
    
    geography::STGeomFromWKB(WKB,4283) as Geography 
    
    from SISTDB..GIPS.LOCALITY_WKB_V
    this pulls back some records just fine but as soon as invalid Geography is encountered I receive:

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

    Is there a way that I can make these polygons valid 'on the fly' using that WKB column within my view?
    Friday, February 5, 2010 5:09 AM

Answers

All replies

  • Without knowing why your data is invalid, it's a bit tricky... the error message is unfortunately quite generic, but you could see if any of the techniques here work:
    http://www.beginningspatial.com/fixing_invalid_geography_data

    How complex are your polygons? Is it possible to paste an example here of one of the records that fails to import?
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, February 5, 2010 7:37 AM
    Answerer
  • Hi, try this:

    WITH A AS (SELECT MI_PRINX, LOCALITY,geometry::STGeomFromWKB(WKB,4283).MakeValid() as geom from SISTDB.GIPS.LOCALITY_WKB_V)
    
    SELECT MI_PRINX,LOCALITY,geography::STGeomFromWKB(geom.STUnion(geom.STPointN(1)).STAsBinary(),4283) as geog from A;
    
    

    This way first you construct a geometry and make valid, and after that you adjust the orientation of the rings by executing the STUnion. Anyway, be careful because the output of MakeValid can be anything, usually geometrycollections.

    If you still have problems, I recomend you to read this post from Ed:


    • Marked as answer by Richie_Aussie Monday, February 8, 2010 5:56 AM
    Friday, February 5, 2010 8:27 AM