[postgis-users] geos based spatial queries

[Peter Aberline]

Hi,

Congratulations on the release of GEOS guys - it's looking pretty good!
Eager to put it to good use, I'm trying to get my head around using some of
the new functions for spatial queries. I want to do a fairly simple query:
select the number of the points that fall within the boundary of a state
polygon.

My first attempt at the query was:

select count(*)
from points
where within(points.the_geom, (select the_geom
                                                              from states
                                                              where
state_name = 'BigState')) = true;

Obviously, this does a full table scan on points table, which is bad
because there is 2 million of them, and eventually tens of millions, and
for each point also does a scan of the states table.

Aggregate  (cost=238486.78..238486.78 rows=1 width=0)
  InitPlan
    ->  Seq Scan on states  (cost=0.00..3.64 rows=1 width=32)
          Filter: (state_name = 'BigState'::character varying)
  ->  Seq Scan on states  (cost=0.00..238466.38 rows=8163 width=0)
        Filter: (within(the_geom, $0) = true)

Can anyone suggest a query that would force PostGIS to use the gist indexes
on both tables' geometry fields? I though of using the MBR of the state as
a search window to reduce the number of times "within" gets called, much
like oracle spatial's primary /secondary filter concept but I'm unsure how.

thanks,
Peter.

_____________________________________________________________

The information in this email and in any attachments is confidential and
may be privileged.  If you are not the intended recipient, please destroy
this message, delete any copies held on your systems and notify the sender
immediately.  You should not retain, copy or use this email for any
purpose, nor disclose all or any part of its content to any other person.