[postgis-users] newbie questions

[Benedetto Porfidia]

Stephen Woodbridge wrote:

> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
WOW
thanks Steve.
it takes 158 seconds returning all records!!

Benedetto