[postgis-users] Calculate Average Distance (w/ LIMIT)

[Nicolas Ribot]

On 6 December 2010 13:19, Andreas Forø Tollefsen <andreasft at gmail.com> wrote:
> Hi all.
> I have two point data sets. One point set is health observations, the other
> one is conflicts.
> What i want to do is to calculate the average distance from each health
> observation to the 10 nearest conflict points.
> I have managed to calculate the average distance, but only to all conflict
> points.
> My query:
> select "LBGE51FL".the_geom, "LBGE51FL".gid,
> AVG(ST_Distance(ST_Transform("LBGE51FL".the_geom, 954010),
> ST_Transform(acled.the_geom, 954010)))/1000 AS dist INTO LB_dist FROM
> "LBGE51FL", acled WHERE acled.gwno = 450 GROUP BY "LBGE51FL".the_geom,
> "LBGE51FL".gid;
> SRID 954010 is Eckert VI.
> Any idea on how to limit this query to the 10 nearest points?
> Thanks.
> Andreas

Hi,

Using a subquery to compute the distance, order by distance, limit 10,
before computing the avg distance ?

Nicolas