[postgis-users] Indices for ST_distance_sphere

[Paragon Corporation]

Steve,
It really depends your area of interest, how far out you span out when
looking for objects and how accurate you want your distance search to be
what the best solution is.

If your area of interest is small like a state or a UTM zone and even US,
its best to create a functional index  on ST_Transform(the_geom,
someplanarsrid) or to just keep your data in that spatial ref and used the
transformed coordinates for doing distance searches.  If your area is wider
and your buffer area is also wide, that gets a bit more complicated.

Any rate if you tell us the area you are dealing with - is it all of the
world, US, Europe etc. and how far you look e.g. do you need to look out 10
meters from a point, 10 miles or 100 miles or more, then I'm sure we'd be
more able to give you some direction.

Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Stephen
Baillie
Sent: Sunday, October 12, 2008 7:56 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Indices for ST_distance_sphere

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

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users