Speeding up queries with simplified geometries.
|
View:
New views
3 Messages
—
Rating Filter:
Alert me
|
 |
Speeding up queries with simplified geometries.
by JDS-6
::
Rate this Message:
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@... http://postgis.refractions.net/mailman/listinfo/postgis-users |
|
|
Re: Speeding up queries with simplified geometries.
by Paul Ramsey-4
::
Rate this Message:
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@...> 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@... > http://postgis.refractions.net/mailman/listinfo/postgis-users > > postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users |
|
|
|
| Free embeddable forum powered by Nabble | Forum Help |
