Hi Gabriel,
My comments to your comments, kind of inline:
ok, I assume SE_SPATIAL_FIRST is giving the same result then SE_OPTIMIZE
then?
Precisely.
(and was my understanding) that both should return the same
results but may be more or less convenient depending on the
attribute/spatial filters mix up (attribute first and spatial first,
that is), and optimize should let arcsde decide which of the two to use
based on the filter predicate complexity...
That's my reading also.
Question: that
does happen _always_? or it does for some datasets and for others the
same results are returned?
I've not tried it on other datasets, due to a lack of time, but I suspect it
would, given that the subset layer exhibits the same problem and various
subs of this sub do too, so the spatial index grid values seem to have no
bearing. Tracing _seems_ (to my uneducated eyes) to indicate that Oracle
provides 23 results from the initial table query to ArcSDE, but I only fetch
1 back. The underlayng feature shapes are in no way wierd.
1. With SE_OPTIMIZE, the spatial index is being used, but I cannot fetch
more than one feature.
sad that's not my common sense, but to use the spatial filter first and
still return the correct number of features...
Yeah, sorry, you'd need more info from my to apply common sense - here I'm
saying that the query is lightning fast, so an index is being used, but
sadly I only end up with one result.
my understanding was that it should execute the alphanumeric part of the
query on the database first, but that doesn't imply a full table scan
should be _needed_.
Yes, again, if I'd given you more info, to whit, that my query is purely
spatial, so there's no attribute to use, you'd get the full table scan bit.
I'm sure Oracle would use an index if I'd specified a where clause.
With all the respect I have for arcsde... I still think the indexes
should be used instead of a full table scan whenever possible, but I
certainly remember a former customer having lots of issues trying to
make sde actually use the indexes... unfortunately I don't know/remember
what the resolution was.
From what I'm seeing, all of the query planning is being done by Oracle -
ArcSDE just issues a query like 'select blah from blee where bloh' and
Oracle takes it from there. I take it from that that indexes should be used
whereever appropriate. The 'spatial grid index' is a separate table that
ArcSDE creates and indexes and then queries like any other table, somewhat
like 'select rowid.of.feature.in.layer from index where
index.minXofLayerShape >= givenshapeMinx and index.minYofLayerShape (etc)'.
It then takes the returned rowids and fishes them out from the layer before
reviewing candidate shapes for a final match. Again, Oracle should be
making the best use of indexes on the spatial 'index'.
I will certainly post any resolution I get from ESRI. I'm hoping it's a
setup problem, but we'll see. The fact that ArcMap doesn't show the issue
is rather worrying from that point of view...
All the best,
Frank
Gabriel Roldan wrote:
humm... first off, thanks for sharing this information Frank..
some comments inline
FrankMcLean wrote:
Hi Gabriel,
I am not a GeoServer user, my apologies :-),
no need to apologize, though I know what you'll chose when you need a
geospatial server 
but seem to be having some
issues like this and am in contact with ESRI support right now, so I can
post any resolution I have.
please!!
To outline my issue, I use the Java API to spatially query a layer of
600K+
features to overlay a shape and pull back an attribute for intersected
features. My issue is with SE_OPTIMIZE. Using this setting only ever
gives
me back only one feature, in a normal response time. If I switch to
SE_ATTRIBUTE_FIRST, I get back 23 features, which is correct, but works
EXTREMELY slowly - we're talking a slowdown factor of about 200 times.
ok, I assume SE_SPATIAL_FIRST is giving the same result then SE_OPTIMIZE
then?
yet, it really sux that they return different results... I can
understand (and was my understanding) that both should return the same
results but may be more or less convenient depending on the
attribute/spatial filters mix up (attribute first and spatial first,
that is), and optimize should let arcsde decide which of the two to use
based on the filter predicate complexity...
now this opens a new horizon of uncertainty for me too... Question: that
does happen _always_? or it does for some datasets and for others the
same results are returned?
also, are you mixing attribute and spatial filters at all?
and the _correct_ result for the spatial (and attribute?) filter you're
using is the 23 features or the single one? (you said the 23, I'm just
double checking)
It's obvious from tracing (and common sense) that:
1. With SE_OPTIMIZE, the spatial index is being used, but I cannot fetch
more than one feature.
sad that's not my common sense, but to use the spatial filter first and
still return the correct number of features...
2. With SE_ATTRIBUTE_FIRST, the spatial index is NOT being used and a
full
table scan is occurring. NOTE that this also happens if
SeQuery.prepareQuery() is placed AFTER SeQuery.setSpatialConstraints() -
which is another 'fix' I've used.
my understanding was that it should execute the alphanumeric part of the
query on the database first, but that doesn't imply a full table scan
should be _needed_. It should leverage the indexes as long as they
exist? do you have indexes created on the db table for the non spatial
attributes you're querying? sorry it may be a very dumb question but I'm
still trying to make my head around this weirdness...
I've used a layer containing only the 23 target features as my query
layer
and gotten the same results. Obviously, the full table scan here is
imperceptible.
I suppose all this is to say that you are right - the SE_ATTRIBUTE_FIRST
is
a cover up for an underlying issue. It's a horrible kludge and would
severely degrade performance on larger layers. Thank heavens it's there,
but it definitely isn't the 'solution'.
With all the respect I have for arcsde... I still think the indexes
should be used instead of a full table scan whenever possible, but I
certainly remember a former customer having lots of issues trying to
make sde actually use the indexes... unfortunately I don't know/remember
what the resolution was.
All the above may only apply to my setup, of course - ArcSDE 9.3 Server
on
Oracle 10g 64 bit, using SDE geometry, but I thought I'd pass it on.
thanks again for sharing, and please if you get to any conclusion tell
us back.
If you get any answers to this issue, could you let me know?
sure, I wish I get some more time to research/experiment with this. I'm
perhaps going to have some in the short term.
Best regards,
Gabriel
Thanks,
Frank
Gabriel Roldan wrote:
Hi GeoUser...
it seems tough, I don't think changing that flag should affect the
result
set,
but the speed of the query, if I remember well, as for what the esri
documentation says (ie, execute the alphanumeric filters first, the
geometry
ones firts, or let the arcsde engine decide)...
Now, whether one or the other is better depends on the query, either the
attribute or spatial parts of a query may be faster and lead to less
features
to filter in the next step..
The fact that it's affecting the result count for you makes me wonder if
changing that flag may solve the problem for your specific request and
mess up
others... The only way I can think of to be sure is being able to
reproduce in
a unit test. Could you give more information about the arcsde version,
database flavor and version, and the structure of the layer and the
query
you're performing?
If you can even provide a data snapshot (shapefile is ok) I could try
and
see
what's going on.
Cheers,
Gabriel
On Tuesday 13 January 2009 11:08:09 GeoUser wrote:
It seems i have found the solution for the problem. In ArcSDEQuery.java
in
getSeQuery i replaced this
this.query.setSpatialConstraints(SeQuery.SE_OPTIMIZE,
setReturnGeometryMasks,spatialConstraints);
with this
this.query.setSpatialConstraints(SeQuery.SE_ATTRIBUTE_FIRST,setReturnGeomet
ryMasks,spatialConstraints);
and all seems to be working.
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
------------------------------------------------------------------------------
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
--
View this message in context: http://www.nabble.com/ArcSde-problem-tp21410159p22847434.html
Sent from the GeoServer - User mailing list archive at Nabble.com.