0

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:

How can I adjust my query (or a different one that might be better) to write the nearest neighbor distance back to table A?

Using:

  • pgadmin 4
  • postgis 2.4.3 (note, no tag exists yet for this version?)
  • pgsql 100
PolyGeo
  • 65,136
  • 29
  • 109
  • 338
LMB
  • 1,166
  • 8
  • 27

1 Answers1

1

This is not really a question regarding PostGIS per se, but PostgreSQL as a whole. Still, what you first need to do is create a distance column in your A table:

ALTER TABLE "A" ADD COLUMN distance real;

Then, for every row you'll update the value found in your query to the distance column:

UPDATE "A"
SET distance = (SELECT ST_Distance(A.geom, B.geom)
    FROM B
    ORDER BY ST_Distance(A.geom, B.geom)
    LIMIT 1
);
Roberto Ribeiro
  • 3,161
  • 13
  • 28
  • Does this solution benefit anything from the use of the <->, <#> operators as per https://boundlessgeo.com/2011/09/indexed-nearest-neighbour-search-in-postgis/? – DPSSpatial_BoycottingGISSE Feb 14 '18 at 17:34
  • 1
    Not really, it might be slightly faster, but otherwise point-to-point comparison doesn't benefit much from the boost in performance, while giving less accurate figures and not working with geographic coordinate systems. – Roberto Ribeiro Feb 14 '18 at 17:42
  • ORDER BY A.id, ... is useless no? doesn´t it even invoke an unnecessary seq scan by sort key? – geozelot Feb 15 '18 at 13:45
  • @ThingumaBob st_distance() returns a list of all features from A and their matching distance to all features from B. It does that, however, in no particular order. The order by is to thus get the smallest distance first, so that the limit clause will exclude all others. It can probably be done without in a different solution, but in this one I can't see a way not to. – Roberto Ribeiro Feb 15 '18 at 13:55
  • 1
    ah, I just meant the A.id that is obsolete, not the whole ORDER BY block! AFAIK each item in that block will invoke a sort key operation in the table/index scan (if not outsmarted by the planner). a quick test did give me the same results of course, but with a few extra seconds on only 10000 x 10000 points. – geozelot Feb 15 '18 at 14:16
  • @ThingumaBob oh, you are absolutely right. I was testing with a select prior to update and it was glanced over. Fixed it, thank. – Roberto Ribeiro Feb 19 '18 at 12:11