topic Re: Shape.Area and Shape.len via ODBC in Data Management Questions

Shape.Area and Shape.len via ODBC

gismoe — Fri, 25 Jan 2013 08:00:21 GMT

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

Re: Shape.Area and Shape.len via ODBC

VinceAngelo — Fri, 25 Jan 2013 09:34:32 GMT

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

Re: Shape.Area and Shape.len via ODBC

AsrujitSengupta — Fri, 25 Jan 2013 17:10:03 GMT

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,

Re: Shape.Area and Shape.len via ODBC

gismoe — Mon, 28 Jan 2013 07:15:01 GMT

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

Re: Shape.Area and Shape.len via ODBC

VinceAngelo — Mon, 28 Jan 2013 09:58:27 GMT

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

Re: Shape.Area and Shape.len via ODBC

gismoe — Mon, 28 Jan 2013 10:39:34 GMT

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?

Re: Shape.Area and Shape.len via ODBC

JakeSkinner — Mon, 28 Jan 2013 13:06:05 GMT

Hi Ernst,

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

Re: Shape.Area and Shape.len via ODBC

dancheng — Fri, 14 Jun 2013 14:30:58 GMT

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

Re: Shape.Area and Shape.len via ODBC

JakeSkinner — Fri, 14 Jun 2013 14:53:42 GMT

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

Re: Shape.Area and Shape.len via ODBC

VinceAngelo — Fri, 14 Jun 2013 15:31:42 GMT

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

Re: Shape.Area and Shape.len via ODBC

dancheng — Fri, 14 Jun 2013 16:17:43 GMT

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

Re: Shape.Area and Shape.len via ODBC

VinceAngelo — Fri, 14 Jun 2013 16:29:19 GMT

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

Re: Shape.Area and Shape.len via ODBC

dancheng — Fri, 14 Jun 2013 18:23:51 GMT

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