[postgis-users] Speeding up queries with simplified geometries.

[jds]

I've downloaded the Berkley administrative boundaries shape files and now
I'm trying to formulate queries that will give me the administrative regions
that contain any given lat/long combination. Obviously it's blindingly slow
if I just search through the geometries using ST_Within, even though I've
got it indexed. The problem is related to the number of points in the
boundaries. So for example Canada, which has over a million points, is slow
to test against. So I've created another indexed column which holds the
bounding boxes for every geometry and doing ST_Within using that geometry is
very fast, though it does return strange results, for example the lat/lng of
Toronto airport is inside the bounding box for Alaska! But that's OK because
at least I've cut the number of geometries to a handful. Then I need to
search through that handful testing against the full geometries to eliminate
the false positives.

I was expecting to be able to do something like;-

ST_Within(my_test_geom,table.bounding_box)
AND
ST_Within(my_test_geom,table.the_full_geom)

or

my_test_geom && table.bounding_box AND
ST_Within(my_test_geom,table.the_full_geom)

But it still does a full scan on table.the_full_geom, which takes ages at
sets the CPU to 100% while it's doing it.

I've tried to do sub-selects in the hope that by sub-selecting the entries
where the bounding box contains the point it would then  compare the full
geometries for only those records, but no luck.

If it comes to it I'm going to have to write a stored procedure that gets
the handfull of records which pass the test using the bounding box and then
test each of them individually against the full geom to get just the ones
that pass.

Is there a better way to do this?

Ta

John Small
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090706/7933bfd9/attachment.html>