update all rows of a table in postgresql

Question

I am comparing every row of a table I have with every other row to find the minimum distance. I also want to add two columns one called "closest_distance", and the other "id_of_the_closest_distance". I know that I need to do this in two steps: one- update the first row, and two- evaluate the second row based on the first. I have a query that gives me the closest distance as below:

SELECT DISTINCT ON (a.id)
  a.id,
  (SELECT
     MIN(ST_HausdorffDistance(a.the_geom, b.the_geom))
   FROM
     gridareas AS b
   WHERE
     b.id != a.id
  )
FROM gridareas AS a;

However I cannot use a simple

UPDATE tablename SET colname = expression;

and use the query above as the expression, because

ERROR:  subquery must return only one column
LINE 5: (SELECT DISTINCT ON (a.id)

On the other hand, the subquery cannot return both a.id and the MIN(ST_HausdorffDistance()).

How should I proceed to update this column?

id is the primary key of the table! and the_geom is the geometry of the area (as this is a postGIS) but it's also irrelevant what the geom is, it could be replaces by any int and the ST_HausdorffDistance() with any other function e.g. LEAST — Acalypha
– Acalypha, Commented Oct 10, 2018 at 15:37
I really think that you should not persist that information in table columns. It is redundant and hence will "rot" as the table changes. You should use a view that calculates the information "on the fly". If the performance of such a view is too slow (which doesn't seem unlikely if the table is large), use a materialized view and refresh that regularly. — Laurenz Albe
– Laurenz Albe, Commented Oct 10, 2018 at 15:44

Gordon Linoff · Accepted Answer · 2018-10-10 15:40:41Z

2

Is this what you want?

update gridareas ga
    set (closest_id, closest_dist) =
            (select gs2.id, ST_HausdorffDistance(ga.the_geom, ga2.the_geom) as dist, ga2.id
             from gridareas ga2
             where ga2.id <> ga.id
             order by ST_HausdorffDistance(ga.the_geom, ga2.the_geom)
             limit 1
            );

answered Oct 10, 2018 at 15:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Acalypha · Accepted Answer · 2018-10-10 15:54:37Z

1

Not exactly, but it helped a lot, thanks! This is what fixed my problem:

UPDATE 
  gridareas AS a
SET 
  (hausdorffdistance_to_closest_geom) =
  (SELECT
     MIN(ST_HausdorffDistance(a.the_geom, b.the_geom))
   FROM
     gridareas AS b
   WHERE
     b.id != a.id
  );

answered Oct 10, 2018 at 15:54

Acalypha

1812 silver badges16 bronze badges

Collectives™ on Stack Overflow

update all rows of a table in postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related