SHAPE.area / length empty for sdo_geom data

[HaniuHokkaido]

hi,

I create a feature class from arccatalog using sdo_geom keyword. the feature class is created successfully and i can insert new data (via ArcMap by drawing it manually) and also i can view the data normally. But when I look  into shape.area and shape .length they are empty...

Why ?

Database: oracle 10.2.0.3, arcsde 10.0

[ArkadiuszMatoszka]

Shape.Area and Schape.Length are parts of st_geometry definition, if you're using sdo_geometry type geometry is stored in other tables in oracle, so this fields will stay empty.
Cheers
Arek

[HaniuHokkaido]

hi..

thanks. you are exactly right.. ! i had the very same problem a couple of years ago.i forgot the reason why. but your answer refreshes my memory.

thank you... 😄

[WesKing]

If using sdo_geom, is there any way to get the SHAPE.AREA and SHAPE.LEN to populate correctly?  Yes, other fields could be added to store this geometry info., but I'd rather not do that if possible.

Thanks in advance,
Wes

[MarcoBoeringa]

If using sdo_geom, is there any way to get the SHAPE.AREA and SHAPE.LEN to populate correctly?  Yes, other fields could be added to store this geometry info., but I'd rather not do that if possible.

Thanks in advance,
Wes

Calculating area, length, and other geometric properties (10.1 Help)

[WesKing]

Thanks for the reply Marco.

I think I did a poor job writing up my question.  I understand using "Calculate Geometry", but that can't be used on the SHAPE.AREA/SHAPE.LEN fields.  I would have to add additional fields to store the geometry, and then run "Calculate Geometry" on them at regular intervals.  I would like to take advantage of having the SHAPE.AREA/SHAPE.LEN fields and having them update automatically.  The problem is we are using SDO_GEOMETRY so this does not happen.

I was hoping someone knows of a way to make the SHAPE.AREA/SHAPE.LEN fields popolate and update automatically even if using SDO_GEOMETRY.  Maybe someone has found a way to "trick" Oracle to use these fields???

I hope that clarifies my question.

Thanks in advance,
Wes

[MarcoBoeringa]

I was hoping someone knows of a way to make the SHAPE.AREA/SHAPE.LEN fields popolate and update automatically even if using SDO_GEOMETRY.  Maybe someone has found a way to "trick" Oracle to use these fields???

I hope that clarifies my question.

Thanks in advance,
Wes

It does.

The "trick" is DBA work. Your DBA needs to set a so called "trigger" on these fields, and possibly create a database function and / or stored procedure to update the fields automatically each time a record changes. How that may interfere with ArcSDE, and if there are any risks involved (I guess limited to none considering these fields aren't updated as you noted), ESRI is in a better position to answer.

Using Triggers (Oracle documentation)

[WesKing]

Thanks again Marco!

We do use triggers on many fields and in the past used them on our own geometry fields.  We've changed things a lot lately to be in compliance with SDSFIE 3.0 standards and it has caused HAVOC!  Basically, now all the fields we used to have in spatial tables are now in stand-alone ancillary tables.  So our geometry fields are now in stand-alone tables...doesn't really make sense does it?  We're trying to work on these and many other issues too.  I've made a request to see if triggers will work on these fields and I'll post the results when we learn more.  I believe we hadn't tried this already because we may have thought since these are Arc generated fields and not "true" fields in the tables that triggers couldn't be used...that's just a guess though.

Until later,
Wes

[VinceAngelo]

This is my understanding of the situation:  When ArcGIS Developers, who worked exclusively
with ArcInfo coverages previously, encountered SDE (for then is was so called), then were
disappointed that the LENGTH and AREA fields were unavailable as columns.  SDE has
always had the ability to return these properties, through a clever slight of hand during the
query, so they started making use of it.  And when ArcSDE stated allowing SDO_GEOMETRY
storage, ran into incompatibilty.  SDO_GEOMETRY, while it allowed an accessor function
to calculate it, didn't store these properties, and the run-time cost of calculation was fearsome.
So the decision was made to alter the SQL query with SDO_GEOMETRY storage so that
empty values were returned.

Since you cannot alter ArcGIS source code, or intercept the SQL inside Oracle, there is no
way to generate these values without calculating them and carrying them as attributes on
the business table (and updating as necessary).

- V

[MarcoBoeringa]

So essentially you are saying Wesley has no other option than to:

- Create two new fields in the business table of the Feature Classes to store the Area and Length (the ArcMap displayed Area and Length fields can not be used since they only exist as "virtual fields" in the returned query result *** for SDO_GEOMETRY ***).

- Populate these using for example the Calculate Geometry tool, or an equivalent Oracle Spatial SQL statement.

- From then on, possibly use database triggers to update the fields using Oracle Spatial SQL statements to cope with any changes in the Feature Classes geometries, to get the desired automation.