Query an SDE Feature Class Containing ESRI Binary Spatial Type Geometry

Question

Hello all,

I've been bumbling through some SDE Geodatabases, trying to learn how to query geometry. In particular, AREA and LENGTH.

Now, I have 2 different SDE geodatabases. One is running SDO_Geometry, the other is ESRI Binary Spatial Type. In each geodatabase, there is an identical polygon feature class (i.e one is a copy of the other). I wanted to query the length and area of one of them. I started with the feature class with the SDO_geometry:

select OBJECTID,GEOMETRY,SDO_GEOM.SDO_Area(Geometry, 0.0005) AREA,SDO_GEOM.SDO_LENGTH(Geometry, 0.005) PERIMETRE  
from 
MySDEFeatureClass

This query works out fine. So in my bumbling, I tried to use the query on the feature class containing the ESRI Binary Spatial Type Geometry and of course, that failed because it isn't using SDO. Then I started googling around and found how to query ST_Geometry. Again, fail. It is not using ST_Goemetry. I tried googling for querying on ESRI Binary Spatial Type and I couldn't really find anything. Again, I'm bumbling because I'm learning as I go, but can I query on this spatial data type? Is there an example on how to do this or is there something I am missing?

VinceAngelo · Answer

The ArcSDE API actually hides the fact that SDELOB (and SDEBINARY, though one shouldn't be using LONG RAW so long after it was deprecated by Oracle) uses a joined table, by aliasing the joined Fn table with the geometry column name, so that the API equivalent of

SELECT shape.numofpts,shape.area,shape.length FROM tablename

returns the expected values.

You can emulate this in SQL by using the same trick:

SELECT shape.numofpts,shape.area,shape.len length

FROM tablename a, Fn shape

WHERE shape.fid = a.shape

or

SELECT shape.numofpts,shape.area,shape.len length

FROM tablename a

JOIN Fn shape on  Fn.fid = a.shape

For most uses, SDE.ST_GEOMETRY will be a better storage choice than SDELOB, and this gives

you access to a larger set a accessor functions and operators.

- V

LitingCui · Answer

Hi,

In the Oracle database end, you cannot query the geometry information directly on SDE Binary base table. Those geometry information is stored in F table corresponding to your feature class and not the base table. Below is a link which explains the relationship between feature class base table and F table:

http://resources.arcgis.com/en/help/main/10.2/index.html#/ArcSDE_compressed_binary_storage/002n0000006p000000/