[GIS] How to get distance values from nearest neighbor query for 5 closest features to multiple records in PostGIS

knnnearest neighborpostgispostgresql

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?

Best Answer

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;
Related Question