Shape.Area and Shape.len via ODBC

[gismoe]

I need to use some attributes from a sde-feature class in a EXCEL-Query.
All fields are show but Shape.Area and Shape.len are missing.

These field are not shown in the SQL Managment Studio so i think that this attribute are
stored in a different place.

So maybe i can create a Database View.

But i don't now where?!

[VinceAngelo]

The SQL syntax is slightly different depending on which RDBMS is in use, and which
geometry storage is present, but for most combinations with native or ST_GEOMETRY
storage you just need to include appropriate references in the SELECT column list. 

It's unlikely that ODBC will be able to extract geometry properties with SDEBINARY
or SDELOB storage.

- V

[AsrujitSengupta]

Ernst,

The respective F-table maintains the shape.len and shape.area information for a Feature Class. But as Vince mentioned, it's not possible to implement this always. The "F" table does not always exist. Native geometry types do not include a Shape.Len and Shape.Area field. We can't create them using an OLE DB connection.

These queries can be used to query the details though, from my experience (sde.Area3 is the FC name):
Select shape.STLength() as length from sde.Area3
Select shape.STArea() as area from sde.Area3

Regards,

[gismoe]

I 'am working with SQL Server. So ST_Geometry won't work.
I found the f-table. There are 1204 features shown in ArcMAP and 1321 rows in the table. This is a versioned Dataset.
So there might be data in other tables......?

[VinceAngelo]

Several additional tables.  They're all documented, but I'd recommend you search
for another way to accomplish this task (like using ArcObjects or Python to export
the data into a format readable by Excel).

- V

[gismoe]

I don't want to export the data. I have to link the data directly from excel.
Now i found the a- and d-tables and i'am trying to query them with sql.
It seems to work.

BTW: All my feature classes are versioned. Is it correct that it would be helpful to compress the databases everyday.
Are there any aspects against my idea?

[JakeSkinner]

Hi Ernst,

Take a look at creating a versioned view.  There is a similar post here.

[dancheng]

Hi Ernst and Jake,

I have a similar question about this too: I tried to use "select by attribute"  in Arc for a feature class stored in sde. The field I am interested is Shape.STLength() and it does show up in the sql query box, but when I input sql expression like : Shape.STLength() = 2.876, it didn't return any result though I do see such record exists... any ideas?

Thanks,
Dan

[JakeSkinner]

Hi Dan,

What is your current configuration?  i.e.

ArcGIS 10.1 SP1
ArcSDE 10.1 SP1
SQL Server 2008 R2

Do any queries work against this field? i.e. Shape.STLength() > 0

[VinceAngelo]

You always need to be careful when doing exact comparisons on floating-point
values in SQL --  If the length isn't 2.8760000000000000000, then your query
will fail to return any rows.

Instead, you need to use the BETWEEN operator or a pair of greater-equal/less-than
tests:

WHERE Shape.STLength() BETWEEN 2.8755 AND 2.87765

WHERE Shape.STLength() >= 2.8755 AND Shape.STLength() < 2.8765

Note that these are not necessarily equivalent -- See your RDBMS "BETWEEN"
documentation for details.

- V