2

I found this query on this blog https://geeohspatial.blogspot.com/2013/05/k-nearest-neighbor-search-in-postgis.html?showComment=1480796271673#c3250651227513965563 which returns the 5 closest records in one table to each record in another table:

SELECT l.gid, (SELECT ARRAY(SELECT c.city_name 
  FROM bndry.us_cities c 
  ORDER BY l.geom <#> c.the_geom LIMIT 5))
FROM offshore_meta.load_centers l;

This works great for my needs. The only other thing I would like it to be able to do is to return an array of the distance values in a second column for each item in the first array containing the names. I'm assuming I can use ST_Distance but am not sure how to apply it in this case. Can anyone help?

1 Answer 1

0

It looks like I just need to construct another array, like this (I also switched to using ST_Distance for ordering the first array):

SELECT l.gid, (SELECT ARRAY(SELECT c.city_name 
  FROM bndry.us_cities c 
  ORDER BY ST_Distance(l.geom. c.the_geom LIMIT 5)),
(SELECT ARRAY SELECT(ST_Distance(l.geom, c.the_geom)
FROM bndry.us_cities c
ORDER BY ST_Distance(l.geom, c.the_geom) limit 5))
FROM offshore_meta.load_centers l;
2
  • 1
    which versions of postgis and postgreSQL are you using? if they are 2.2+ and 9.5+, then it may be quicker to use the <-> operator rather than ST_Distance in the ORDER BYs
    – MickyT
    Commented Dec 5, 2016 at 20:08
  • I am using 9.5 and 2.2+ will try that, thanks
    – kflaw
    Commented Dec 5, 2016 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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