nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?!

Find more posts tagged with

Accepted answers

All comments

dancheng

Got it, thanks for the help!

No, there's no standard wildcard operator for numeric values. You'd need to play
with modulus or casting functions, but that's really more appropriate for the RDBMS
vendor's forums.

- V

VinceAngelo

No, there's no standard wildcard operator for numeric values. You'd need to play
with modulus or casting functions, but that's really more appropriate for the RDBMS
vendor's forums.

- V

dancheng

Hi all,

Yes, when I tried something like >0 or between value range, it works, thanks for pointing out the precision issue .

This raised up another question I have:

What if I want to know if the shape.length() column contain certain numbers (e.g., 234). For strings, I can do fieldname Like '%234%',however, for numbers, it seems like no such function is available....

Any thoughts would be appreciated, thanks!

Dan

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

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

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 Ernst,

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

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?

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 '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......?

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,

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.