[postgis-users] newbie questions

[Stephen Woodbridge]

Benedetto Porfidia wrote:
> Hi all,
> I have 300000 points table and I want to calculate for each point the 
> average of a field of other points within 4 km radius.
> I tried:
> SELECT a.gid AS id, avg(b.anom267) AS avg, stddev(b.anom267) AS stddev
>   FROM provino a, provino b
>  WHERE distance(a.the_geom, b.the_geom) < 4000::double precision
>  GROUP BY a.gid;
> 
> First problem is about performance (it takes 15 hours) and the second is 
> that it returns results only for points that have neighbours within the 
> search radius. how can I tell postgis to put avg=anom267 and stddev = 0 
> for all isolated points?
> 
> which is the right sintax to perform the query on 3dboxes to speed up (I 
> hope)?
> 
> thanx in advance
> 
> Benedetto
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 

Benedetto,

Try this query

  SELECT a.gid AS id, avg(b.anom267) AS avg, stddev(b.anom267) AS stddev
    FROM provino a, provino b
   WHERE expand(a.the_geom, 4000) && b.the_geom and distance(a.the_geom, 
b.the_geom) < 4000::double precision
   GROUP BY a.gid;

The additional where cause filters points that are only within 4000 of 
other points before doing the distance() calculation.

-Steve