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
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
);
  • Does this solution benefit anything from the use of the <->, <#> operators as per boundlessgeo.com/2011/09/…? – DPSSpatial 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? – ThingumaBob 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. – ThingumaBob Feb 15 '18 at 14:16

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.