1

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?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.