I would like to mimic the ArcGIS near tool behavior is PostGIS.
Two tables, A and B. For each entry in A I want the distance of its nearest neighbor in B written to the field 'A.neighbordistance' which already exists in A. So far I have been able to come up with the below code which basically returns what I want. But because of my newness to postgis I have been unable to write the results to table A.
select distinct on(A.id) A.id as A_id, B.id as B_id, ST_Distance(A.geom, B.geom) as dist from A, B order by A.id, ST_Distance(A.geom,B.geom)
Other related posts/links:
- Nearest Neighbor calculation in PostGIS? does not write the results back to table A
- Nearest Neighbor in PostGIS is about php, and also does not write the results back to table A
How can I adjust my query (or a different one that might be better) to write the nearest neighbor distance back to table A?
- pgadmin 4
- postgis 2.4.3 (note, no tag exists yet for this version?)
- pgsql 100