Re: [postgis-users] Calculate Average Distance (w/ LIMIT)
On 6 December 2010 13:19, Andreas Forø Tollefsen andrea...@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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Calculate Average Distance (w/ LIMIT)
Andreas, I can't improve on Nicolas' suggestion, but at a guess you might also want to look at the spatstat package in R, which has defined a number of functions for this sort of analysis, and can include windows (ie country polygons) to incorporate edge correction - ie F, G and K functions. cheers Ben On 03/01/2011, at 9:05 PM, Nicolas Ribot wrote: On 6 December 2010 13:19, Andreas Forø Tollefsen andrea...@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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Calculate Average Distance (w/ LIMIT)
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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users