topic Re: Why did a polygon lose 76 sqft (Shape.Area) when exporting from Oracle ST_Geometery to File GDB? in Geodatabase Questions

Why did a polygon lose 76 sqft (Shape.Area) when exporting from Oracle ST_Geometery to File GDB?

AZendel — Fri, 17 Mar 2023 13:53:21 GMT

I exported some simple polygons stored in the ST_Geometry format from an Enterprise Oracle database to File GDB (FGDB) and the shape_area field for one polygon lost 76 sqft. That 'loss' is based on the Shape.Area field in Oracle and the SHAPE_Area field in the FGDB. This happens when I use the Copy Features tool in a geoprocessing model and using Data --> Export Features in Pro. I've looked at the feature class properties of both and they have the exact same:

Coordinate system: SRID 2915 (TN State Plane NAD83 HARN). The "Geographic Coordinate System" that the layer properties dialog box that Pro displays lists the same WKID: 4152
Spatial resolution: 0.000328083333333333 US Survey Feet
No Z value storage, no M Value storage
Simple Feature type
Vertical units (Foot_US) -- this is probably moot since neither store Z values
No Errors or Warnings are shown in the geoprocessing history for the copy features

More notes/questions

Does the FGDB use also ST_Geometry? Or something slightly different? If the latter, I suppose this could account for the difference
I ran the Check Geometry tool on the FGDB feature class and none were reported. It is not possible to run the tool on Oracle feature classes because, as the tool document states, the geometry is reportedly validate when inserted
Does the shape_area or Shape.Area sometimes fail to update?
This discrepancy was only detected in 2 of 190 exported polygons (one discrepancy was ~76 sqft, the other 18 sqft)
This is fairly concerning because we often list polygon areas on documents that have legal ramifications
The calculate geometry tool doesn't match the SHAPE_Area field of the output FGDB feature class, which is shown below (calculated area with us survey feet as the input). The Shape.Area field of the Oracle feature is 651063.678772

Thanks in advance for any insight you can provide.

Re: Why did a polygon lose 76 sqft (Shape.Area) when exporting from Oracle ST_Geometery to File GDB?

DanPatterson — Fri, 17 Mar 2023 14:37:34 GMT

For reference

ST_Geometry in Oracle—ArcGIS Pro | Documentation

Is there anything special about your geometries that are causing issues? curves? etc?

Re: Why did a polygon lose 76 sqft (Shape.Area) when exporting from Oracle ST_Geometery to File GDB?

AZendel — Fri, 17 Mar 2023 14:55:41 GMT

Thanks for the quick response. I believe I ran across that page in the documentation, but it's hard to remember all that I read before I decided to post here. That page starts with "The Esri ST_Geometry spatial data type.....", which implies that ESRI created it. That makes me think that ST_Geometry is used in File GDB's, but I could not confirm that after extensive web searches .

Good suggestion on looking for curves in the source geometry. There are some (below). But shouldn't those transfer from Oracle to FGDB the same?

Re: Why did a polygon lose 76 sqft (Shape.Area) when exporting from Oracle ST_Geometery to File GDB?

AZendel — Fri, 17 Mar 2023 15:28:11 GMT

After coming across this post, I think the discrepancy comes down to how Oracle and ArcGIS are handling curves when calculating area. That post is for SQL Server, but I imagine that something similar is happening with Oracle.

Re: Why did a polygon lose 76 sqft (Shape.Area) when exporting from Oracle ST_Geometery to File GDB?

ShannonShields — Fri, 17 Mar 2023 18:42:11 GMT

If you are working with curve geometry features, I wouldn't recommend using ST_Area & ST_Length functions, which are used to display those values dynamically when you view attributes.

Features with curve geometry/circular arcs have two representations in enterprise geodatabases. The ST_Geometry shape stores a densified arc version of the geometry, and the true curve is stored in a blob format read by ArcGIS, but not by the DBMS. If you use the Measure tool in Pro you'll see the measurement based on the curve geometry. You can calculate your own area/length fields using the Calculate Geometry Attribute tool, and there are Arcade Geometry functions for area & length if you want to have these values automatically generated using Attribute Rules.

This applies to all spatial types in enterprise geodatabases including Oracle Spatial, SQL Server Geometry/Geography, PostGIS as well as ST_Geometry on Oracle & PostgreSQL.

We understand that this is confusing and not a great user experience. We are investigating ways to improve this behavior so that it is easier to get to the consistent measurement value.

Re: Why did a polygon lose 76 sqft (Shape.Area) when exporting from Oracle ST_Geometery to File GDB?

AZendel — Tue, 04 Apr 2023 12:23:56 GMT

Thanks for the excellent explanation, Shannon. And for confirming that the SQL Server documentation that I quoted applies to Oracle and the other Enterprise GDBs (though I imagine each DBMS may have some of its own particular details/idiosyncrasies regarding area and length calculations). I can stop scratching my head now...... Thanks again.

While we're on the topic of SHAPE.AREA, it be really nice to be able to specify the units that are displayed in these fields. As you saw above, I almost exclusively use TN State Plan HARN NAD83. The X/Y units are in feet so the SHAPE.AREA fields display square feet. Square feet is a easy to digest when you're at the building or lot level, but once you get up into dozens of acres, it's almost meaningless, like 4,535,588.22548 sq ft. I'd much rather see acres in feature classes with larger polygons. You mentioned attribute rules, but it sounds like you'd need to create a new field to store the calculate area? That could get inefficient on larger datasets. I also realize that Query Layers allow you to specify SQL that can handle this conversion, but then you can't edit the FC. It'd also be nice to have dynamic field that makes calculations beyond geometry (summing several columns of different races reported in census data). QGIS has 'virtual fields' that do just that.

Re: Why did a polygon lose 76 sqft (Shape.Area) when exporting from Oracle ST_Geometery to File GDB?

Bud — Tue, 21 Mar 2023 13:21:31 GMT

Regarding “Features with curve geometry/circular arcs have two representations in enterprise geodatabases. The ST_Geometry shape stores a densified arc version of the geometry, and the true curve is stored in a blob format read by ArcGIS, but not by the DBMS.”

I think some folks call that blob the “CAD blob”.
https://gis.stackexchange.com/search?q=%5Bst-geometry%5D+cad
Other things to search for in GIS Stack Exchange: [st-geometry] tag and one of these words: blob, densify, densified, arc, curve, vertices, etc.