[postgis-users] Nearest Neighbour Index calculation

[Matthias Ludwig]

Hi,

I want to calculate the Nearest Neighbour Index for statistical interpretation of point data. It simply sums the distances to the first neighbours and devides it through the count.

create or replace function 
  nn()
returns double precision as $$
declare 
	result  	double precision := 0;
	id	  	integer;
	count	  	integer;
begin
	count := (SELECT count(*) FROM t_harrispoints_reduced);
	for id in (SELECT gid FROM t_harrispoints_reduced) loop
		result := result + (
				SELECT ST_Distance(start.the_geom, ende.the_geom) as distance
				FROM 	t_harrispoints_reduced AS start,
					t_harrispoints_reduced AS ende
				WHERE 	start.gid = id AND
					start.gid <> ende.gid
				ORDER BY ende.the_geom <-> start.the_geom
				LIMIT 1
			);
	end loop;
	result := result / count;
	return result;
end
$$ language 'plpgsql' stable;

It does the job...but not really fast. For 8000 points it take about 90 seconds. QGIS those the same work in 2-3 seconds. Is it possible to speed this up? Is it possible to prevent the loop?
-- 
Mit freundlichen Grüßen

Matthias Ludwig
B.Eng. Geoinformation

Tel.:   0177/4913288
         030/68329587
ICQ:  163168410

Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir
belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de