Is there a way to set multiple column values on a single spatial query?

Question

I am associating attributes with points using PostGIS's ST_DWIthin().

UPDATE scratch.intersections AS i
SET legs = (
    SELECT COUNT(r.geom)
    FROM received.streets r
    WHERE ST_DWithin(i.geom, r.geom, 2));

UPDATE scratch.intersections AS i
SET streets = (
    SELECT ARRAY_AGG(DISTINCT r.NAME ORDER BY r.NAME) 
    filter (WHERE r.NAME IS NOT NULL)
    FROM received.streets r
    WHERE ST_DWithin(i.geom, r.geom, 2));

Seems like it should be possible to update multiple columns with a single spatial query, but I can't think of a way to structure it since I can only update a single column at a time.

Is there a way to turn these two queries into a single query, requiring only one spatial calculation?

Would it be more efficient to do an INNER JOIN creating a new temp table with a record for each line within 2 of a point, and then set values off of that table? Describing it, it sounds less efficient since performance of ST_DWithin() isn't terrible using indexes.

Answer 1

You can update multiple columns in one statement by enclosing them in parentheses

UPDATE myTable
SET (a,b) = (
  select c,d 
  from anotherTable 
  WHERE st_dwithin(mytable.geom, anotherTable.geom,2)
);