[postgis-users] Optimization - point density calculation

[Francis Dupont]

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/