[postgis-users] Point to Point Distance Query (optimization possible?)

[Andreas Forø Tollefsen]

Hi,

I am working on a query where i want to measure the distance from a set of
points, to the nearest point having a spi6 value over 0.
The priogrid includes the points and the spi6_cell identifies which points
have a spi6 over 0.

My question is whether i can optimize this for better performance?
As for now it takes 66 minutes for one year. Having 53 years that would
take like 58 hours to complete.
Using my St_DWithin(), does it restrict the distance measures to only
points within 500 km?

My query:
SELECT p.gid, s.gridyear, MIN(ST_Distance(p.centroid::geography,
p2.centroid::geography))/1000 AS distance
FROM priogrid p, priogrid p2, spi6_cell s
WHERE p2.gid = s.gid AND s.spi6 >= 0.5 AND s.gridyear = 1952 AND
ST_DWithin(p.centroid::geography, p2.centroid::geography, 500000)
GROUP BY p.gid, s.gridyear
ORDER BY distance;

"POSTGIS="2.0.0SVN" GEOS="3.4.0dev-CAPI-1.8.0" PROJ="Rel. 4.7.1, 23
September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8"
USE_STATS (procs from 2.0 r need upgrade)"
PostGIS Revision 8622
"PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit"

Any help would be very appreciated.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120208/c8617968/attachment.html>