[postgis-users] Postgis query slowing down with polygon bounds

[Rob Young]

I developed a pL/pgsql function that creates a bounding box for
lat/long data.  Given a latitude, long and distance in metres, it
returns a box that bounds a circle at that point with a radius equal
to the distance parameter.  Here's an example:

select AsText(expand_sphere( 5, 3, 600 * 1000 ));

"POLYGON((8.41054153442383 -0.389891535043716,8.41054153442383
10.3898906707764,-2.41054105758667 10.3898906707764,-2.41054105758667
-0.389891535043716,8.41054153442383 -0.389891535043716))"

However, when I use this in  a query (below) in my testing database,
it runs significantly slower than when I run the query without
bounding the data using expand_sphere.


select *, astext(point) from test
WHERE
point && expand_sphere( 5, 3, 600 * 1000 ) AND
distance_sphere( setsrid( makepoint( 3, 5 ), 4326 ), point ) < 600 * 1000;


This has me puzzled, any suggestions as to what I should change to
speed up this query?  The table test has a one geometry column, point,
which contains point data.  There is a GIST index on point, and I
anazlysed the database after adding the index.

Also, if there's interest I would be happy to share my expand_sphere code.

Regards
Robert Young  :-)