st_AsText slow

Question

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 · Answer

'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

ModyBuchbinder · Answer

Hi V

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

Thanks
Mody

VinceAngelo · Answer

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