[postgis-users] Optimization - point density calculation

[Paul Ramsey]

(a) The ST_Transform() function is making your spatial index
un-usable, because your spatial index is against the raw coordinates
of your data.
(b) You are using ST_Buffer() instead of just calculating a distance.

So, first transform your data to your working projection.
Then, use ST_DWithin() to test closeness.

Approximately:

DROP INDEX dmti_2k7_gix;
UPDATE dmti_2k7 SET epoi_sherby = ST_Transform(epoi_sherby, 2037);
CREATE INDEX dmti_2k7_gix ON dmti_2k7 USING GIST (epoi_sherby);
VACUUM FULL;

SELECT count(*) from dmti_2k7 WHERE ST_DWithin(dmti_2k7.epoi_sherby,
ST_SetSRID(MakePoint(x, y), 2037), 250.0)


On Tue, May 27, 2008 at 1:45 PM, Francis Dupont
<Francis.X.Dupont at usherbrooke.ca> wrote:
> 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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>