SDE MSSQL SqlSpatial Geometry Self Intersects problem

[ChrisSmith]

Hi

I have created a SqlGeometry using the Microsoft.SqlServer.Types SqlGeometryBuilder, the geometry IsValid and IsSimple but when trying to view it in ArcMap it wont draw, when trying to export to a non SqlSpatial SDE table it complains that the polygon is self intersecting. The geometry uses srid 27700 (BNG) and is viewable in SQL management Studio, I have tried various methods that are suggested to correct the geometry including MakeValid(), STUnion(STStartPoint()) on the SqlSpatial side but since it is valid and doesnt self intersect then it doesnt fix the issue.

Any help is appreciated, Geometry details below



Regards

chris

Geometry as Text...(after trying MakeValid()/StUnion(STStartPoint))

POLYGON ((531405.73999977112 104148.89000034332, 531414.71999979019 104179.65999984741, 531407.15999984741 104182.27999973297, 531406.94000005722 104182.35000038147, 531404.44000005722 104177.02000045776, 531401.59000015259 104168.47000026703, 531401.59999990463 104168.5, 531401.48999977112 104168.19999980927, 531395.61999988556 104152.19999980927, 531405.73999977112 104148.89000034332))

Geometry as Gml...

<Polygon xmlns="http://www.opengis.net/gml">
  <exterior>
    <LinearRing>
      <posList>531405.73999977112 104148.89000034332 531414.71999979019 104179.65999984741 531407.15999984741 104182.27999973297 531406.94000005722 104182.35000038147 531404.44000005722 104177.02000045776 531401.59000015259 104168.47000026703 531401.59999990463 104168.5 531401.48999977112 104168.19999980927 531395.61999988556 104152.19999980927 531405.73999977112 104148.89000034332</posList>
    </LinearRing>
  </exterior>
</Polygon>



Original gml entry from file I am importing...

<gml:Polygon srsName='osgb:BNG'>
<gml:outerBoundaryIs>
<gml:LinearRing>
<gml:coordinates>531406.94,104182.35 531404.44,104177.02 531401.59,104168.47 531401.60,104168.50 531401.49,104168.20 531395.62,104152.20 531405.74,104148.89 531414.72,104179.66 531407.16,104182.28 531406.94,104182.35</gml:coordinates>
</gml:LinearRing>
</gml:outerBoundaryIs>
</gml:Polygon>

[ChrisSmith]

I should add that this table is registered with SDE and if I delete this polygon then all my other polygons in the same layer can display without problem in ArcMap.

[VinceAngelo]

I made a line out of polygon shell, and generated a shapefile from it, then inspected
the line.  That shape does self-intersect, in the vicinity of 531401.6,104168.5. 

- V

[ChrisSmith]

Hi Vince

Thanks for the post

I posted the same on the SqlSpatial forums and Tanoshimi points out it doesn't self intersect,
so have drawn it on a piece of squared paper and it doesn't appear to self intersect.

I also tried the same process as you have done i.e. created a polyline from the points loaded as an XY Event layer and it doesn't look like it crosses to me.

Appreciate the help and I will have another look at it and see if I find anything different.

The post on the SqlSpatial forum if your interested 🙂

http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/d844071c-68b6-4ce8-bbda-05f19ac4b9e0

Cheers

chris

[VinceAngelo]

The ArcSDE topology engine is quite mature. It uses Clementini's topology rules,
which includes the definition of a simple line. All polygon rings must adhere to
simple line restrictions, and this line overlaps itself (linear intersection, not point).

C:\>sdequery -l testshape2,shape -F1 SM_II:line:531404.44,104177.02,531401.59,104168.47 -v
ArcSDE 9.3 Generic Query Tool            Thu Dec 30 08:53:42 2010
------------------------------------------------------------------------
         1
            objectid: 5
               shape: Line shape (1 part, 2 vertices)
                    1   531404.440000, 104177.020000
                    2   531401.590000, 104168.470000
         2
            objectid: 6
               shape: Line shape (1 part, 2 vertices)
                    1   531401.590000, 104168.470000
                    2   531401.600000, 104168.500000
2 rows found in 31.00 ms
 
C:\>sdequery -l testshape2,shape -F1 SM_II:line:531401.59,104168.47,531401.60,104168.50 -v
ArcSDE 9.3 Generic Query Tool            Thu Dec 30 08:53:42 2010
------------------------------------------------------------------------
         1
            objectid: 5
               shape: Line shape (1 part, 2 vertices)
                    1   531404.440000, 104177.020000
                    2   531401.590000, 104168.470000
         2
            objectid: 6
               shape: Line shape (1 part, 2 vertices)
                    1   531401.590000, 104168.470000
                    2   531401.600000, 104168.500000
2 rows found in 47.00 ms

If you do the math, you'll see that segments 5 & 6 have reciprocal slopes;
since they share a vertex, they overlap.

In order for vertex #6 to be included, several points would need to be added,
including a point to make a polygon of the deformed interior polygon ring, a
vertex for the interior ring to touch, and vertices to open and close the interior
ring.

- V

[ChrisSmith]

Hi Vince

Thanks for the help, will post your response back to the SqlSpatial forum, see what reply I get there.

cheers

chris

[VinceAngelo]

SQL-Server isn't handling the geometry right, but the root of the problem is your
GML source, which also has a topology bug.

I must admit that I also find the representation errors (two places in, twelve out)
disturbing as well -- it looks as though the values have been stored in single precision.

- V

[ChrisSmith]

Hi Vince

Is there any way of checking geometries to see if they are considered valid, the loaded dataset will have millions of records. If I remember correctly there was such an app called Checkgeometries that could be used to find invalid geometries in Oracle Spatial.

thanks

chris

[VinceAngelo]

'sdelayer -o feature_info -r invalid' should scan a table for invalid shapes.

- V

[ChrisSmith]

unfortunately the cmd ends when it hits the first self intersecting feature, it reports the following..

Total rows examined:       99460
Total invalid shapes:          0
Error: Linestring or poly boundary is self-intersecting (-155).
Error: Error in fetching row.

I have examined the features with fids of 99459,99460,99461,99462 but they all are simple polygons definitely not self intersecting so they appear to be fine, so not sure what order the data is examined in.

Interestingly I have located another dodgy geometry, this time the interior ring is self intersecting, gml below if you are interested...

<Polygon xmlns="http://www.opengis.net/gml">
  <exterior>
    <LinearRing>
      <posList>529785.17001342773 180867.58999633789 529850.04000854492 180907.64001464844 529827.799987793 180940.95001220703 529767.10000610352 180902.35998535156 529785.17001342773 180867.58999633789</posList>
    </LinearRing>
  </exterior>
  <interior>
    <LinearRing>
      <posList>529786.55999755859 180893.41000366211 529790.07778930664 180895.52319335938 529786.32998657227 180902.80999755859 529807.55999755859 180915.42001342773 529811.73001098633 180908.5299987793 529786.55999755859 180893.41000366211</posList>
    </LinearRing>
  </interior>
</Polygon>

Any other suggestions on how to detect these problem geometries?

thanks

chris