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

Postgresql – POSTGIS UPDATE from self with left join

I have a working SELECT statement but as SQL isn’t my strong point I don’t know how to make this into an UPDATE function. I have others that work but it appears to be JOIN’s getting me here.

I’ve tried a few ways I could think of:
UPDATE ptp_links SET geom = ST_MAKELINE (this results in every record getting the same line)
UPDATE FROM SELECT (this one gets me the close but throws an error about column "geom" is of type geometry but expression is of type record)

This is my SELECT Query:

SELECT 
  ST_MAKELINE(
    ST_POINT(out_bs.longitude::DOUBLE PRECISION, out_bs.latitude::DOUBLE PRECISION, 4326),
    ST_POINT(CASE WHEN ptp.link_purpose = 'backhaul'
      THEN in_bs.longitude::DOUBLE PRECISION
      ELSE ptp.in_address_geo_x::DOUBLE PRECISION
    END,
    CASE WHEN ptp.link_purpose = 'backhaul'
      THEN in_bs.latitude::DOUBLE PRECISION
      ELSE ptp.in_address_geo_y::DOUBLE PRECISION
    END, 4326)
  )
FROM ptp_links ptp
LEFT JOIN base_stations in_bs ON ptp.in_base_station = in_bs.id
LEFT JOIN base_stations out_bs ON ptp.out_base_Station = out_bs.id;

What I want to do is write the generated line from ST_MAKELINE back to the ptp_links table into the column called geom if anyone knows how I would do this in PostGIS.

Tags:

2

Answers


  1. 0

    You can lean on implicit joins in a standard update..from..where:

    When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table.

    Demo:

    UPDATE ptp_links AS ptp
SET geom = ST_MAKELINE(
                ST_POINT(out_bs.longitude::DOUBLE PRECISION,
                         out_bs.latitude::DOUBLE PRECISION, 
                         4326),
                ST_POINT(CASE WHEN ptp.link_purpose = 'backhaul'
                              THEN in_bs.longitude::DOUBLE PRECISION
                              ELSE ptp.in_address_geo_x::DOUBLE PRECISION END,
                         CASE WHEN ptp.link_purpose = 'backhaul'
                              THEN in_bs.latitude::DOUBLE PRECISION
                              ELSE ptp.in_address_geo_y::DOUBLE PRECISION END, 
                         4326)
            )
FROM base_stations in_bs,
     base_stations out_bs
WHERE ptp.in_base_station  = in_bs.id
AND   ptp.out_base_Station = out_bs.id
RETURNING ptp.id,in_base_station,out_base_station,link_purpose,st_astext(geom);
    id in_base_station out_base_station link_purpose st_astext
    1 1 2 backhaul LINESTRING(2 1,0 0)
    2 2 3 backhaul LINESTRING(3 2,2 1)
    3 3 4 other LINESTRING(4 3,17 23)

    If you also grab some unique identifier from ptp_links, you can wrap your select in a with CTE and use it as a source for the update statement: demo

    WITH update_batch AS (
    SELECT ptp.id,--something unique to targeted record
      ST_MAKELINE(
        ST_POINT(out_bs.longitude::DOUBLE PRECISION,
                 out_bs.latitude::DOUBLE PRECISION, 
                 4326),
        ST_POINT(CASE WHEN ptp.link_purpose = 'backhaul'
          THEN in_bs.longitude::DOUBLE PRECISION
          ELSE ptp.in_address_geo_x::DOUBLE PRECISION
        END,
        CASE WHEN ptp.link_purpose = 'backhaul'
          THEN in_bs.latitude::DOUBLE PRECISION
          ELSE ptp.in_address_geo_y::DOUBLE PRECISION
        END, 4326)
      ) AS new_geom
    FROM ptp_links ptp
    LEFT JOIN base_stations in_bs ON ptp.in_base_station = in_bs.id
    LEFT JOIN base_stations out_bs ON ptp.out_base_Station = out_bs.id )
UPDATE ptp_links AS t
SET geom = new_geom
FROM update_batch AS s
WHERE s.id = t.id
RETURNING t.id,in_base_station,out_base_station,link_purpose,st_astext(geom);

    Another way to wrap your base query is by making it a scalar subquery fed directly to the update...set geom= assignment: demo

    UPDATE ptp_links AS target
SET geom = (SELECT ST_MAKELINE(
                ST_POINT(out_bs.longitude::DOUBLE PRECISION,
                         out_bs.latitude::DOUBLE PRECISION, 
                         4326),
                ST_POINT(CASE WHEN ptp.link_purpose = 'backhaul'
                  THEN in_bs.longitude::DOUBLE PRECISION
                  ELSE ptp.in_address_geo_x::DOUBLE PRECISION
                END,
                CASE WHEN ptp.link_purpose = 'backhaul'
                  THEN in_bs.latitude::DOUBLE PRECISION
                  ELSE ptp.in_address_geo_y::DOUBLE PRECISION
                END, 4326)
              ) AS new_geom
            FROM ptp_links ptp
            LEFT JOIN base_stations in_bs ON ptp.in_base_station = in_bs.id
            LEFT JOIN base_stations out_bs ON ptp.out_base_Station = out_bs.id 
            WHERE ptp.id=target.id
            LIMIT 1 );

    Note that in the last two cases you need to direct the incoming value to the target row by comparing a unique identifier. As reminded by @Bergi, if you don’t have one, there’s always the "hidden" ctid column you can use. Just keep in mind that ctid is stable throughout a single query but it’s not guaranteed to stay consistent between separate, even consecutive queries.

    Login or Signup to reply.
  2. 0

    Apart from the approach shown by @Zegarek, you can first rewrite your query to use subqueries instead of (left) joins:

    SELECT ST_MAKELINE(
  (
    SELECT ST_POINT(out_bs.longitude::DOUBLE PRECISION, out_bs.latitude::DOUBLE PRECISION, 4326),
    FROM base_stations out_bs 
    WHERE ptp.out_base_Station = out_bs.id
  ),
  CASE WHEN ptp.link_purpose = 'backhaul'
    THEN (
      SELECT ST_POINT(in_bs.longitude::DOUBLE PRECISION, in_bs.latitude::DOUBLE PRECISION, 4326)
      FROM base_stations in_bs
      WHERE ptp.in_base_station = in_bs.id
    )
    ELSE ST_POINT(ptp.in_address_geo_x::DOUBLE PRECISION, ptp.in_address_geo_y::DOUBLE PRECISION, 4326)
  END
)
FROM ptp_links ptp;

    Now it’s easy to convert that into an UPDATE statement:

    UPDATE ptp_links ptp
SET geom = ST_MAKELINE(
  (
    SELECT ST_POINT(out_bs.longitude::DOUBLE PRECISION, out_bs.latitude::DOUBLE PRECISION, 4326),
    FROM base_stations out_bs 
    WHERE ptp.out_base_Station = out_bs.id
  ),
  CASE WHEN ptp.link_purpose = 'backhaul'
    THEN (
      SELECT ST_POINT(in_bs.longitude::DOUBLE PRECISION, in_bs.latitude::DOUBLE PRECISION, 4326)
      FROM base_stations in_bs
      WHERE ptp.in_base_station = in_bs.id
    )
    ELSE ST_POINT(ptp.in_address_geo_x::DOUBLE PRECISION, ptp.in_address_geo_y::DOUBLE PRECISION, 4326)
  END
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search