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

[Paul Ramsey]

Something is not right here. If you look into the definition of
ST_Within, you'll see this:

SELECT $1 && $2 AND _ST_Within($1,$2)

So using ST_Within includes an index clause. You say you've indexed
your table, what command did you use to do that? Did you use a GiST
index on the geometry column? You shouldn't need to break geometries
out into boxes, since that's what the index is already doing. (Using
smaller delegates does get your around tuple-toasting overhead,
though, which for very large objects can be considerable.)

How big are your geometries?
select npoints(the_geom) from thetable order by npoints limit 25;
What is your PostGIS version?
select postgis_full_version();

P.

(That said, for really really large geometries, you'll find that
breaking them up does make things faster, because the indexing code
memcpy's them out as part of the consistency check,

On Mon, Jul 6, 2009 at 10:06 AM, jds<jds340 at gmail.com> wrote:
> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>