# Nearest Neighbour function

I'm trying to create a function which calculates the nearest neighbors under a certain length in a table s_1 an stores them in an output table.

This is what I've got so far:

``````CREATE OR REPLACE FUNCTION NearestN()
RETURNS TABLE (
a integer,
b integer,
ST_Distance integer
)
AS
\$\$

SELECT ST_Distance(a.the_geom , b.the_geom ) from s_1 a, s_1 b
WHERE a.id < b.id
AND ST_DWithin(a.the_geom,b.the_geom, 50.0)
ORDER BY ST_Distance(a.the_geom, b.the_geom) ASC LIMIT 25;
\$\$
LANGUAGE plpgsql;
``````

Needless to say to function is not working at all (I'm very new to sql) and I think my calculation does not only get me the nearest neighbor but all distances under a certain length.

And is it possible to make the length an argument of the function so I can vary just by calling the function?

## 1 Answer

Try to use Indexed Nearest Neighbour Search like

``````SELECT ST_Distance(a.the_geom , b.the_geom ) from s_1 a, s_1 b
WHERE a.id < b.id
AND ST_DWithin(a.the_geom,b.the_geom, 50.0)
ORDER BY a.the_geom <-> b.the_geom LIMIT 25;
``````

Note: I cannot see why you would need the `WHERE a.id < b.id`, you will have to try which version gives you the best result.

Another resource which helped me to understand the Indexed Nearest Neighbour Search was this blog post.