[postgis-users] Indices for ST_distance_sphere

[Stephen Baillie]

Thanks to some helpful input from this list, I've got PostgreSQL/PostGIS 
to find me all the entities within a given distance of an arbitrary 
point.  However, it takes about 5 seconds to search the database for 
this data, which is too long for the application in question.  I'm not 
overly concerned about that at this point, as it isn't using any indices 
in that search, so it can presumably be made much faster.  However, I'm 
not sure how best to index this.

I can work out how to get PostgreSQL to build an index based on distance 
from a constant point, but not how to extend that to an index based on 
distance from an arbitrary point.  Is there some clever way in PostGIS 
to do this kind of distance based index?

If that's not possible, the next best approximation seems to be putting 
indices on my lat and long fields and restricting my search to the 
bounding box of my circle of interest, then using ST_distance_sphere to 
do the more precise check for each entity inside the bounding box.

What have others done when faced with this choice?

Thanks,

Steve.

-- 
Stephen Baillie
Developer
Alliance Software

1/234 Whitehorse Road
Nunawading, VIC 3131
Australia

Ph:  03 9877 9921
Fax: 03 9894 2106