topic Re: Spatial SQL - identify arcs (curves) in polygon/line and layers in Data Management Questions

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

NickHarrison — Thu, 16 Jun 2011 15:32:24 GMT

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.

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

VinceAngelo — Fri, 17 Jun 2011 21:54:18 GMT

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

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

NickHarrison — Mon, 20 Jun 2011 14:02:14 GMT

No that column doesn't exist. I gues that information cannot be extracted with SQL.

Thanks,

Nick

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

PaulDziemiela — Tue, 21 Jun 2011 11:53:14 GMT

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

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

NickHarrison — Tue, 21 Jun 2011 14:13:07 GMT

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.

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

PaulDziemiela — Tue, 21 Jun 2011 19:38:54 GMT

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

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

Bud — Thu, 03 Feb 2022 20:40:31 GMT

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.