[postgis-users] Nearest Neighbour Index calculation

[Stephen Woodbridge]

Matthias,

Try using the function for the nearest neighbor search:
http://bostongis.com/?content_name=postgis_nearest_neighbor_generic#130

and is should speed things up significantly. So your query in the loop 
would look like:

select dist from pgis_fn_nn(start.the_geom, 1.0, 1, 100, 
't_harrispoints_reduced', 'start.gid <> gid', 'gid', 'the_geom');

1.0 assumes degrees and should be the maximum distance you want to 
search for a nearest neighbor.

100 is the number of increments that you want to divide the search into.

Adjust these values to fit you data.

-Steve

On 1/18/2012 10:21 AM, Matthias Ludwig wrote:
> 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?