Postgresql 自左联接的POSTGIS更新

你可以在标准的update..from..where号中依靠implicit joins号:

当存在FROM子句时，实质上发生的是目标表连接到101列表中提到的表，并且连接的每个输出行表示对目标表的更新操作.

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);

如果您还从ptp_links中获取一些唯一标识符，则可以将select包装在with CTE中，并将其用作update语句的源: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);

包装基本查询的另一种方法是将其直接馈送到update...set geom=赋值: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 );

请注意，在后两种情况下，您需要通过比较唯一标识符将传入的值定向到目标行.就像reminded乘以@Bergi一样，如果你没有，总会有"hidden" ctid column可用.请记住，ctid在单个查询中是稳定的，但不能保证在单独的、甚至是连续的查询之间保持一致.