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?

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;
  • 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 Dec 5 '16 at 20:08
  • I am using 9.5 and 2.2+ will try that, thanks – kflaw Dec 5 '16 at 20:22

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.