st_AsText slow

[ModyBuchbinder]

Hello all

We have a layer with about 500k points in st_geometry (oracle).
We perform a spatial query in SQL that gets about 20k features back.
When we just get the OBJECTID the query takes less than 4 seconds.
When we return the sde.st_AsText(shape) it takes about 45 seconds.
Is it takes so much time for that function to work (on a simple point layer)?
We need the geographic information in some kind of a text (taking it to a different system), is there any way to improve it?

Thanks
Mody

[VinceAngelo]

Absent any other information (versions of software in use, etc), it doesn't seem
unusual that a query that returns 2-4 bytes per row would take 1/10th the time
of a query that returns 20-40 bytes per row, especially over a large number of
rows.  Accessing a LOB, even an inline LOB, is more expensive then decoding
an integral value, and then a helper function needs to be executed across a
DLL bridge.

If you provide more details on the Oracle configuration (client & server), the table
creation DDL, the nature of the spatial query, and the nature of the data, we might
be able to try to reproduce the behavior.

- V

[BruceHarold]

Mody

if you are moving data to another system then you are doing ETL, we have an extension for that - Data Interoperability.
It can read your data directly with no need to write queries.

Regards

[ModyBuchbinder]

Hi
Some more information:
The database is Oracle11g on Linux.
The query is done in SQL.
The layer is a very simple layer that was exported from a shape file using ArcCatalog.
The query is buffering some center point in some distance and try to get all the points under it as a list of strings.
I am not sure what is the data Interoperability got to do with it. The user wants to use SQL only to "export" the data to some ascii format. Whey and how should I use data Interoperability?

Thanks
Mody

[VinceAngelo]

Does the environment for extracting the WKT coordinates matter?  The 'C' library call
in the ArcSDE API does the job way faster than Oracle can toss around LOBs.

D:\>sdequery -l rnd200k,shape -E -45,-45,45,45 -C shape -d setk.txt

ArcSDE 10.2 Generic Query Tool           Wed Aug 28 09:21:02 2013
------------------------------------------------------------------------
24885 rows found in 1.12 secs

D:\>sdequery -l rnd200k,shape -E -45,-45,45,45 -C shape -d setk.txt:REPLACE

ArcSDE 10.2 Generic Query Tool           Wed Aug 28 09:21:13 2013
------------------------------------------------------------------------
24885 rows found in 421.00 ms

D:\>sdequery -l rnd200k,shape -E -45,-45,45,45 -C "sde.st_astext(shape) wkt" -d stfn.txt -v

ArcSDE 10.2 Generic Query Tool           Wed Aug 28 09:21:53 2013
------------------------------------------------------------------------
24885 rows found in 49.94 secs

D:\>sdequery -l rnd200k,shape -E -45,-45,45,45 -C "sde.st_astext(shape) wkt" -d stfn.txt:REPLACE -v

ArcSDE 10.2 Generic Query Tool           Wed Aug 28 09:22:58 2013
------------------------------------------------------------------------
24885 rows found in 50.66 secs

It also makes a slightly smaller file:

D:\>head setk.txt stfn.txt
==> setk.txt <==
shape
POINT ( -44.95012200 -43.16230800)
POINT ( -44.75976100 -40.27963900)
POINT ( -44.69895400 -41.67931700)
POINT ( -44.68343000 -40.18468300)
POINT ( -44.54161900 -35.92863400)
POINT ( -44.68887400 -32.18295800)
POINT ( -44.14273600 -32.20899500)
POINT ( -44.93447300 -29.87766500)
POINT ( -44.22238600 -28.60796900)
==> stfn.txt <==
wkt
POINT  ( -44.95012200 -43.16230800)
POINT  ( -44.75976100 -40.27963900)
POINT  ( -44.69895400 -41.67931700)
POINT  ( -44.68343000 -40.18468300)
POINT  ( -44.54161900 -35.92863400)
POINT  ( -44.68887400 -32.18295800)
POINT  ( -44.14273600 -32.20899500)
POINT  ( -44.93447300 -29.87766500)
POINT  ( -44.22238600 -28.60796900)

And 'sdequery' will let you search on geodetic buffers from the command line:

D:\>sdequery -l rnd200k,shape -F1 SM_II:geocircle:0,0,600nm -C shape -d buf.txt

ArcSDE 10.2 Generic Query Tool           Wed Aug 28 09:55:02 2013
------------------------------------------------------------------------
934 rows found in 63.00 ms

though it would also accept the equivalent WHERE clause as well.

- V

[ModyBuchbinder]

Hi V

What is sdequery? Can it select on a circle and not on Envelope?

Thanks
Mody

[VinceAngelo]

'sdequery' is a command-line utility, originally written as an exercise for
for the "Accessing SDE with 'C'" training class, back at SDE 3.0.  It's been
adapted over the years to support a number of tasks.  It has always
supported the possibility of multiple spatial constraints, in conjunction
with a WHERE clause.  A circle constraint is the easiest to format.

The main drawback to 'sdequery' is that the ArcSDE API is now deprecated,
and therefore so is the entire se_toolkit suite; there may be some future
equivalent, but I don't know what it might be yet.

Python can of course execute the same query, as could a ArcGIS Server
service.

- V