Help: ST_Contains taking a very long time!

I have a new Feature Class which contains 900,000 Polygons which are all the
same size (528 feet x 14 feet).  I would like to find which Polygon contains a
specific Point.  <br />
<br />
When I run the following query, it is taking over 5 minutes!  I would like to
see if I could get a result in under 1 second.  Using ArcCatalog, I added a
SpatialIndex of 1,6000 and then did an ArcToolbox Analyze on the FeatureClass to
optimize the spatial index.  Does SpatialExtender use the Spatial Index i
created through ArcCatalog?  Does SpatialExtender use a Spatail INdex?<br
/>
<br />
Besides adding additional predicates, what can I do to improve the performace of
this query? <br />
<br />
Here is the query:<br />
<br />
Select<br />
*<br />
from<br />
ggadmin.fgeofences<br />
where<br />
db2gse.st_contains(shape, db2gse.ST_Point (-97.307712, 33.033339, 1))  0<br
/>
<p />
<br />
Thanks,<br />

I'm very surprised that this is taking so long - I would expect it to be 
sub-second.

What is the spatial index definition?  It certainly sounds like the 
index is not being used.

You could modify your predicate to:
db2gse.st_contains(shape, db2gse.st_point(-97.307712, 33.033339, 1)) = 1 
SELECTIVITY 0.001

Is something missing from your predicate as typed below?  I don't see
"= 1"

Regards,
David

tim@browsersoft.com wrote:
> I have a new Feature Class which contains 900,000 Polygons which are all
the same size (528 feet x 14 feet).  I would like to find which Polygon contains
a specific Point.  <br />
> <br />
> When I run the following query, it is taking over 5 minutes!  I would like
to see if I could get a result in under 1 second.  Using ArcCatalog, I added a
SpatialIndex of 1,6000 and then did an ArcToolbox Analyze on the FeatureClass to
optimize the spatial index.  Does SpatialExtender use the Spatial Index i
created through ArcCatalog?  Does SpatialExtender use a Spatail INdex?<br
/>
> <br />
> Besides adding additional predicates, what can I do to improve the
performace of this query? <br />
> <br />
> Here is the query:<br />
> <br />
> Select<br />
> *<br />
> from<br />
> ggadmin.fgeofences<br />
> where<br />
> db2gse.st_contains(shape, db2gse.ST_Point (-97.307712, 33.033339, 1)) 
0<br />
> <p />
> <br />
> Thanks,<br />

Have you tried using any explain tools to check if the spatial index is 
being exploited?

The easiest is to use Visual Explain from the DB2 Control Center. 
Right-click on your database name and select "Explain SQL".

More detailed information can be obtained by using the db2batch and 
db2exfmt utilities.  I run them with a simple Perl script containing the 
following.  You can run the same commands from a DB2 command window, 
substituting in the needed variables.


#!/usr/bin/perl
my $dbname = "sde81";
my $userid = "sde";
my $pw     = "db2admin";
$qryname = shift;
my $outfile = "$qryname.out";
my $expfile = "$qryname.exp";
my $clpfile = "$qryname.clp";
system("db2 connect to $dbname user $userid using $pw");
system("db2 delete from explain_statement");
system("db2batch -d $dbname -f $clpfile -a $userid/$pw -r $outfile -o p 
4 e 2");
system("db2exfmt  -d $dbname -e $userid -gTIC -n % -s % -o $expfile -u 
$userid $pw -w -1 -# 0");

tim@browsersoft.com wrote:
> I have a new Feature Class which contains 900,000 Polygons which are all
the same size (528 feet x 14 feet).  I would like to find which Polygon contains
a specific Point.  <br />
> <br />
> When I run the following query, it is taking over 5 minutes!  I would like
to see if I could get a result in under 1 second.  Using ArcCatalog, I added a
SpatialIndex of 1,6000 and then did an ArcToolbox Analyze on the FeatureClass to
optimize the spatial index.  Does SpatialExtender use the Spatial Index i
created through ArcCatalog?  Does SpatialExtender use a Spatail INdex?<br
/>
> <br />
> Besides adding additional predicates, what can I do to improve the
performace of this query? <br />
> <br />
> Here is the query:<br />
> <br />
> Select<br />
> *<br />
> from<br />
> ggadmin.fgeofences<br />
> where<br />
> db2gse.st_contains(shape, db2gse.ST_Point (-97.307712, 33.033339, 1)) 
0<br />
> <p />
> <br />
> Thanks,<br />

Hi Tim,  I've taken the liberty of including your response below.  It 
may be helpful to others to see this trail in the newsgroup.

There is a reason why the predicate
st_contains(shape, loc) = 0
may not return a result while
st_contains(shape, loc) <> 0
with return an expected result.

In the first case, (= 0), the Spatial Extender will attempt to use the 
spatial index on "shape" to determine the index grid cell(s) that 
contain "loc" and then return candidate rows where the envelope (MBR)
of 
"shape" has generated index entries for the same grid cell(s).

Rows that pass this test will then be processed by a C-language function 
to determine containment.

In the second case, (<> 0), the Spatial Extender will not attempt to use 
the spatial index on "shape" but will most likely perform a table
scan, 
checking if each geometry contains the specified "loc".  This is 
obviously very time-consuming but should always return the correct results.

If the coordinate system of the "shape" column is not using 
latitude/longitude, that would explain this behavior. If your 
coordinates are in UTM and you create a spatial index with grid sizes 
appropriate for UTM coordinates, a spatial index query with the second 
geometry using lat/long coordinates will fail.

I suspect this is your problem.

There is a peculiarity in the Spatial Extender predicate processing in 
that the exploitation of the spatial index requires both geometry values 
to be in the same coordinate representation.

When the spatial index is not used, the coordinates of the second 
geometry are transformed to the spatial reference system of the first 
geometry before processing which will likely cause the evaluation to be 
successful but increases further the computational complexity.

A query like the following might be successful in your case:

WHERE
db2gse.st_contains(shape,
    db2gse.st_transform(
       db2gse.ST_Point (-97.307712, 33.033339, 1),
	      srid_of_shape_column)) = 1

********************************************************************************
***********


David,
Thanks for getting back to us.  We believe that our slow performance may
be due to a "bug" which we are encountering with Spatial Extender. 
For
some reason, the result from st_intersects which appears to be a 1 does
not equal a 1 when the evaluation is within the where clause.  The
queries below will show what I mean.
I will send you the spatial index definition once I get it.
Thanks,
Tim
I created a new feature class which contains only 1 polygon.  This query
returns no results:
Select
*
from
ggadmin.ftest
where
db2gse.st_intersects(shape, db2gse.ST_Point (-97.307712, 33.033339, 1))
= 1
I get the correct results if I change the query to:
Select
*
from
ggadmin.ftest
where
db2gse.st_intersects(shape, db2gse.ST_Point (-97.307712, 33.033339, 1))
<> 0
I also created a query which contains the Polygon definition and this
query works:
with test as (
   select
   *
   from
   (values
    db2gse.st_multipolygon('multipolygon (((-97.307533 33.033883,
-97.307578 33.033892, -97.307984 33.032482, -97.307940 33.032473,
-97.307533 33.033883)))', 1)
   ) as shapes(shape)
)
select
db2gse.st_asText(shape)
from test
where
db2gse.st_contains(shape, db2gse.ST_Point (-97.307712, 33.033339, 1)) =
1
This query returns a 1 as the result:
Select
objectid,
case when (db2gse.st_contains(shape, db2gse.ST_Point (-97.307712,
33.033339, 1)) = 1)
		 then '1' else '0' end as expected_result
from
ggadmin.ftest
where
objectid = 1
This query returns no results:
Select
objectid,
case when (db2gse.st_contains(shape, db2gse.ST_Point (-97.307712,
33.033339, 1)) = 1)
		 then '1' else '0' end as unexpected_result
from
ggadmin.ftest
where
db2gse.st_intersects(shape, db2gse.ST_Point (-97.307712, 33.033339, 1))
= 1


tim@browsersoft.com wrote:
> I have a new Feature Class which contains 900,000 Polygons which are all
the same size (528 feet x 14 feet).  I would like to find which Polygon contains
a specific Point.  <br />
> <br />
> When I run the following query, it is taking over 5 minutes!  I would like
to see if I could get a result in under 1 second.  Using ArcCatalog, I added a
SpatialIndex of 1,6000 and then did an ArcToolbox Analyze on the FeatureClass to
optimize the spatial index.  Does SpatialExtender use the Spatial Index i
created through ArcCatalog?  Does SpatialExtender use a Spatail INdex?<br
/>
> <br />
> Besides adding additional predicates, what can I do to improve the
performace of this query? <br />
> <br />
> Here is the query:<br />
> <br />
> Select<br />
> *<br />
> from<br />
> ggadmin.fgeofences<br />
> where<br />
> db2gse.st_contains(shape, db2gse.ST_Point (-97.307712, 33.033339, 1)) 
0<br />
> <p />
> <br />
> Thanks,<br />

Hi David,<br />
<br />
Adding the transform fixed the problem!  The information you provided was very
useful.  The spatial query is now returning records in under 1 second for our
table which contains 1,000,000 records.  Extreamly fast!  We absolutly love
Spatial Extender!  <br />
<br />