locked
SDE polygon --> inserted into SQL 2008 tbl as geom obj; Geog cast fails. RRS feed

  • Question

  • dear Readers,

    thank you for taking the time to reading and understanding the question.  I had inserted records into SQL 2008 as geomobject without an error - i am unable to work with it to compute area etc.  i had explained all my steps below:

    1. i was trying to write a utility to take ESRI ArcSDE records and write them to SQL.   did not know where to start and wanted to control what i was testing.

    2. so i created a polygon with a hole (doughnut) and inner polygon in ArcMap to test out my code.  i merged the polygons because they are of the same location and it is required by the business team to merge polygon with the same unique identifier. 

    2b. i found out that i could use arcobject's inner polygon bags and external polygon bags to read all the coords of the vertices as point collection from IRing.

    3. another thing that i found was ESRI stored polygon filled feature coords in clockwise direction and do nut holes in counter clockwise direction. For some reason, SQL Server 2008 geom obj did it in the opposite direction.   so i did not sweat it out - i read the coordinates of hte vertices from bottom of the pointcollection to the top.

    4. SRID is 4269! is it a crime to be in 4269 and is it holier to be in 4326? 

    5. record got written to sql server 2008 as geom object without any error and i was able to query the records in sql server 2008: as:

    select geomobject.STAsText() from RaviTestDelMe

    POLYGON ((-86.4332016299999 32.7078001200001, -86.4560662189999 32.7075601980001, -86.4560902119999 32.6953961410001, -86.4331536459999 32.695180211, -86.4332016299999 32.7078001200001), (-86.4405988899999 32.705552991, -86.4505503509999 32.7046017490001, -86.4506723049999 32.70057726, -86.4486234749999 32.6985284300001, -86.4451599759999 32.6999430990001, -86.4410867059999 32.7034065970001, -86.4405988899999 32.705552991), (-86.454650678 32.7053529140001, -86.4347131399999 32.7065765170001, -86.4487725829999 32.6969556350001, -86.454650678 32.7053529140001), (-86.4378930859999 32.701927524, -86.4357207219999 32.698382085, -86.4356963309999 32.698357694, -86.4344012419999 32.696067923, -86.4426065819999 32.6969076510001, -86.4378930859999 32.701927524), (-86.4497942349999 32.7006748240001, -86.4498910499999 32.7038213180001, -86.4419891669999 32.7046261390001, -86.44206234 32.703455379, -86.4472819789999 32.700113834, -86.4497942349999 32.7006748240001))

    6. i couild not figure out as to how to attach screen shot documents in this forum.  i had pasted my findings as attachments at the bottom of the posting in this forum: http://forums.esri.com/Thread.asp?c=93&f=1170&t=298310#932196

    7. when i tried casting or taking it to sqlgeography object, it did not like it and gave me a generic 24200 error.  there is nothing wrong with the input because in the forum link above, the users can see the records complete in sql server 2008 geom object.

    8. when i tried the generic make valid with a stunion (starting point), it changed my polygon to Multipolygon - and even after converting it to multipolygon, .STArea and other sql geography functions were not happy with my coordinates.

    any help will be useful.

    regards
    ravi.


    Dallas, TX
    Monday, January 11, 2010 9:27 PM

Answers

  • With regards converting this to a valid geography instance, the following works for me:

    -- Create the invalid polygon
    DECLARE @g geometry;
    SET @g = geometry::STGeomFromText('POLYGON ((-86.4332016299999 32.7078001200001, -86.4560662189999 32.7075601980001, -86.4560902119999 32.6953961410001, -86.4331536459999 32.695180211, -86.4332016299999 32.7078001200001), (-86.4405988899999 32.705552991, -86.4505503509999 32.7046017490001, -86.4506723049999 32.70057726, -86.4486234749999 32.6985284300001, -86.4451599759999 32.6999430990001, -86.4410867059999 32.7034065970001, -86.4405988899999 32.705552991), (-86.454650678 32.7053529140001, -86.4347131399999 32.7065765170001, -86.4487725829999 32.6969556350001, -86.454650678 32.7053529140001), (-86.4378930859999 32.701927524, -86.4357207219999 32.698382085, -86.4356963309999 32.698357694, -86.4344012419999 32.696067923, -86.4426065819999 32.6969076510001, -86.4378930859999 32.701927524), (-86.4497942349999 32.7006748240001, -86.4498910499999 32.7038213180001, -86.4419891669999 32.7046261390001, -86.44206234 32.703455379, -86.4472819789999 32.700113834, -86.4497942349999 32.7006748240001))', 4269);
    
    -- Make the instance a valid multipolygon
    SET @g = @g.MakeValid();
    
    -- Use Spatial Ed's trick to correct ring orientation
    SET @g = @g.STUnion(@g.STStartPoint());
    
    -- Convert to geography
    DECLARE @h geography;
    SET @h = geography::STGeomFromWKB(@g.STAsBinary(), @g.STSrid)
    
    SELECT @h
    The result is as follows:

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Tuesday, January 12, 2010 12:15 AM
    Answerer

All replies

  • With regards converting this to a valid geography instance, the following works for me:

    -- Create the invalid polygon
    DECLARE @g geometry;
    SET @g = geometry::STGeomFromText('POLYGON ((-86.4332016299999 32.7078001200001, -86.4560662189999 32.7075601980001, -86.4560902119999 32.6953961410001, -86.4331536459999 32.695180211, -86.4332016299999 32.7078001200001), (-86.4405988899999 32.705552991, -86.4505503509999 32.7046017490001, -86.4506723049999 32.70057726, -86.4486234749999 32.6985284300001, -86.4451599759999 32.6999430990001, -86.4410867059999 32.7034065970001, -86.4405988899999 32.705552991), (-86.454650678 32.7053529140001, -86.4347131399999 32.7065765170001, -86.4487725829999 32.6969556350001, -86.454650678 32.7053529140001), (-86.4378930859999 32.701927524, -86.4357207219999 32.698382085, -86.4356963309999 32.698357694, -86.4344012419999 32.696067923, -86.4426065819999 32.6969076510001, -86.4378930859999 32.701927524), (-86.4497942349999 32.7006748240001, -86.4498910499999 32.7038213180001, -86.4419891669999 32.7046261390001, -86.44206234 32.703455379, -86.4472819789999 32.700113834, -86.4497942349999 32.7006748240001))', 4269);
    
    -- Make the instance a valid multipolygon
    SET @g = @g.MakeValid();
    
    -- Use Spatial Ed's trick to correct ring orientation
    SET @g = @g.STUnion(@g.STStartPoint());
    
    -- Convert to geography
    DECLARE @h geography;
    SET @h = geography::STGeomFromWKB(@g.STAsBinary(), @g.STSrid)
    
    SELECT @h
    The result is as follows:

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Tuesday, January 12, 2010 12:15 AM
    Answerer
  • Tanoshimi,

    good afternoon.  thank you for the insight into this.  i was having code issue trying to write the validated multipolygon due to extra paranthesis etc while trying to write to sql 2008 table.

    i am pleased with the make valid and stUnion function that took my collection of inner and outer rings and arranged it as polygon feature + any inner hole(s) if present as one polygon,  puts a comma and does the same for remaining polygon features.

    first, i was angry that sql server 2008 took the misarranged polygon coords and then refused to call it valid.  but now, after comparing coords carefully, burning my eyes out, i saw how it had done it.

    further, 8th or 9th decimal places of these decimal degrees were changed by STUnion or MakeValid() function.  so i went to the web to see what the 8th decimal place meant for decimal degrees at the equator and found to be 1.11 millimeters http://en.wikipedia.org/wiki/Decimal_degrees.  and the changes degrees as we move to the poles as a cosine function of the latitude. 1.11 mm is so small, there is nothing i need to loose sleep over.

    thanks for opening my eyes to the 'semi-internal's of this function.

    i do not know whether you or your team members or your colleagues wrote those 're-arranging' functions to make the geometry valid.  but it is a cool function inspite of some bugs people have reported.  congratulations.

    regards
    ravi.
    Sr GIS App Developer Dallas Fort Worth area, TX
    Wednesday, January 13, 2010 9:47 PM