Question 422095 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – PostGIS update query multiple rows

I am a student and working on a query to update all records in a table with the distance from a specific point (a town). I am using Pgadmin4 with a postgreSQL/postgis database.

I created a select query that calculates the distance for the point of origin (Stockholm) to all other cities in the database (points). Next I want to update the table for all records with this calculated value in km’s.

I tried this query. If I run the Select part the query gives the names and distance to Stockholm from all other cities in the database. I however haven’t been able to create a query to update all records at once.

UPDATE cities2 
SET dist = distance 
Where distance = (SELECT b.name, ST_Distancesphere(a.geom, b.geom)/1000 
    From cities2 a, cities2 b 
    Where a.name='Stockholm' and b.name in (select name from cities2));

I get the error that column distance does not excist, which is correct because I tried to make it a variable. dist is the field I wish to update for each record (city) in the table with the distance in km’s from Stockholm

Tags:

2

Answers


  1. 0 
    update cities2 as b
set dist = (select st_distancesphere(a.geom, b.geom)/1000 
            from cities2 as a
            where a.name='Stockholm');
    1. That’s a correlated scalar subquery.
    2. The condition and b.name in (select name from cities2) doesn’t make much sense beyond filtering out rows where name is null – if that’s the intention, use that instead.
    3. There’s no built-in = operator that can compare distance = (name, distance).
    4. Might be a good idea to alter table cities2 alter column dist rename to distance_from_stockholm; to make it clearer if not for someone else, then at least for you a week from now.
    Login or Signup to reply.
  2. 0

    There is no such thing as a variable in the PostgreSQL SQL dialect, outside of its procedural languages.

    UPDATE logic might be somewhat counter-intuitive, but the most important quality is that an UPDATE can be inherently correlated, meaning that it exposes a row to the call; think of it as a sequential traversal of the table – excluding any row that match the WHERE filter statement – where the following statement blocks can refer to column values in the current row.

    The SET clause can itself be a (correlated) sub-query, or resembling a SELECT set of a FROM item (or a CROSS JOINed combination thereof), even with a self-join.

    With that in mind, your requirement can be expressed in a variety of ways; some examples:

    • UPDATE
  cities2 AS c1
SET
  dist = (
    SELECT
      ST_Distance(c2.geom::GEOGRAPHY, c1.geom::GEOGRAPHY) / 1000
    FROM
      cities2 AS c2
    WHERE
      c2.name = 'Stockholm'
  )
;

    • UPDATE
  cities2 AS c1
SET
  dist = ST_Distance(c2.geom::GEOGRAPHY, c1.geom::GEOGRAPHY) / 1000
FROM
  cities2 AS c2
WHERE
  c2.name = 'Stockholm'
;

    • WITH
  stockholm AS (
    SELECT
      geom
    FROM
      cities2
    WHERE
      name = 'Stockholm'
  )
UPDATE
  cities2 AS c1
SET
  dist = ST_Distance(c1.geom::GEOGRAPHY, s.geom::GEOGRAPHY) / 1000
FROM
  stockholm AS s
;

    In this case they should all resolve in a similar query plan, at least in terms of performance. For other scenarios, one way may vastly outperform the other, but that is outside the scope of this.

    In cases of a self-join it is good practice – or even required – to add a filter condition to avoid updating self-referenced rows; maybe you don’t want to update the dist value of the row WHERE name = 'Stockholm'!? Again, in this case, adding WHERE c1.name <> 'Stockholm' does the trick, but usually you would try to target an indexed or Primary Key column.

    I prefer the more idiomatic ST_Distance on the (casted) GEOGRAPHY type; strictly speaking, the results between this call and ST_DistanceSphere are not equal, as the two-parameter call of ST_Distance with GEOGRAPHY-typed values defaults to spheroidal distance calculations – which are, in fact, more precise.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search