Spatial SQL - identify arcs (curves) in polygon/line and layers

896
6
06-16-2011 08:32 AM
NickHarrison
New Contributor III
Is it possible use spatial SQL to identify the st_polygon and/or st_linestring shapes that were constructed with arcs? By arcs I mean true arcs or curves and not line strings.

I am using ArcSDE 9.2 with Oracle.
0 Kudos
6 Replies
VinceAngelo
Esri Esteemed Contributor
I'm pretty sure the CAD data isn't carried in a SQL-accesible form (I don't see any accessor
functions in the SQL standard).

Do your tables have a parallel SE_ANNO_CAD_DATA BLOB column? If so, are any of those
rows NULL?

- V
0 Kudos
NickHarrison
New Contributor III
No that column doesn't exist. I gues that information cannot be extracted with SQL.

Thanks,

Nick
0 Kudos
PaulDziemiela
New Contributor
Hello,

I do this all the time.  I would think you've discovered that Oracle's R-Tree Spatial Index does not support curves for geodetic coordinate systems and you need to get rid of them, correct?

While ArcCatalog won't load the layer, you can load it with sdeimport though the indexing step will then fail.  However you will have a layer - the SDO_GEOMETRY will contain the curve features and the SE_ANNO_CAD_DATA field will contain the mysterious ESRI blob data (be not NULL).  At this point you can inspect the SDO_GEOMETRY for the curve features but you can't do much about them as the Oracle Spatial utility SDO_GEOM.SDO_ARC_DENSIFY does not work in a geodetic context and Oracle will not allow you to transform them into a projected system.  But you could identify them here if you just want to delete them or complain to the source about them.

So to densify them you need to backup a step and import your curves into a projected coordinate system that works for you in Oracle.  Maybe a nice Albers Equal Area or such, your call.  Anyhow once you load them in this manner you will again have the curves in both SDO_GEOMETRY and SE_ANNO_CAD_CATA.  Now you can densify the SDO_GEOMETRY AND remember after you do so to set SE_ANNO_CAD_CATA to NULL as if you don't ArcSDE will keep reading the curves out of the BLOB.  I believe the SDO_GEOMETRY is ignored when the BLOB field is not null. 

Hope that helps.

Cheers,
Paul
0 Kudos
NickHarrison
New Contributor III
Thanks Paul.

But my data is ST_GEOMETRY.

I have curves in my data which don't come accross when I extract it with third party software.

I was looking for an appropriate ST_GEOMETRY function, which apparently doesn't exist.
0 Kudos
PaulDziemiela
New Contributor
Hi Nick,

Sorry, if you are only working with SDE.ST_GEOMETRY I don't think there is a way to do it on the sql side of things.  Converting the data to SDO_GEOMETRY is the only way I know of to expose the innards of a geometry.  What happens when you run SDE.ST_AsText on an ST_GEOMETRY with curves?   I've never tried. 

Cheers,

Paul
0 Kudos
Bud
by
Frequent Contributor II

FYI - I submitted an ArcGIS Idea about this topic:
Determine if line has true curves via SQL (Oracle ST_GEOMETRY)

But I doubt ESRI will ever look at it. They don't seem to be interested in improving ST_GEOMETRY.

0 Kudos