Hi! I have created random point over a territory and I want to calculate the density for each random point. I do it by calculating the number of point of interest (POI) I found in a buffer of 250 meters for each random point on my territory. My problem is that my query take too much time for not so many point in my point of view (around 11000 random point and 9000 POI). It took 8.2 sec for 10 random points.
I've been using Postgis since 1 month and I find it very strong so I'm sure I can improve the speed of that query. I tried using Gist index on both table but speed hasn't improved. So, I'm asking the community if someone know a way to improve this function? Am I doing something wrong? Here's the function: ---------------------------------------------------------- CREATE OR REPLACE FUNCTION test() RETURNS void AS ' DECLARE x_y_random RECORD; BEGIN FOR x_y_random IN SELECT * FROM random_pt WHERE pk_id < 10 LOOP UPDATE random_pt SET densite = (SELECT COUNT(*) FROM dmti_2k7.epoi_sherby WHERE ST_Transform(dmti_2k7.epoi_sherby.the_geom, 2037) && ST_Buffer(x_y_random.the_geom, 250) AND distance(ST_Transform(dmti_2k7.epoi_sherby.the_geom, 2037), ST_Buffer(x_y_random.the_geom, 250)) < 0.001) WHERE pk_id = x_y_random.pk_id; END LOOP; END; ' LANGUAGE 'plpgsql'; ---------------------------------------------------------- Regards, Francis Dupont GIS Analyst Research chair in GeoBusiness http://chaire.geobusiness.usherbrooke.ca/ _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users