[postgis-users] PostGIS spatial query performance

[Dave Blasby]

> select park.*
> from park,roads
> where xmin(box3d(park.the_geom)) <= xmax(box3d(roads.the_geom)) + 2
> and   xmax(box3d(park.the_geom)) >= xmin(box3d(roads.the_geom)) - 2
> and   ymin(box3d(park.the_geom)) <= ymax(box3d(roads.the_geom)) + 2
> and   ymax(box3d(park.the_geom)) >= ymin(box3d(roads.the_geom)) - 2
> and distance(park.the_geom,roads.the_geom) <= 2

I anticipated people doing a query based on a 'bigger' bounding boxes and added
the
expand(box3d,float) function that make the box3d 'bigger' on each side.

select park.* from park,roads
where
    expand(box3d(park.the_geom),2) && box3d(roads.the_geom)
    and distance(park.the_geom, roads.the_geom) <=2;

The base problem is that you are still asking the database to do something like:

    For each feature in park
        see if there are any features in roads that are < 2km away

And thats going to take a long time.

dave