Wayne's Docs Site

Appearance

PostgreSQL basics ​

Intro: Why PostgreSQL? ​

QGIS did satisfy most of our need in processing GIS data and altering the attribute tables, but it lacks utility in editing in-place (processing toolbox usually creates a new layer per run), and it lacks the ability in spatial join (field calculator does some sort of spatial analysis, but it does not fill in all shoes).

PostgreSQL, meanwhile, provides joining utility like every other databases, its PostGIS extension also enables the ability to join attributes with spatial joining functions, bridging the gaps between data layers.

How to do an update + join in PostgreSQL? ​

stackoverflow The UPDATE syntax is:

sql
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

In basic join case I think you want this:

sql
UPDATE vehicles_vehicle AS v 
SET price = s.price_per_vehicle
FROM shipments_shipment AS s
WHERE v.shipment_id = s.id

Or if you need to join on two or more tables:

sql
UPDATE table_1 t1
SET foo = 'new_value'
FROM table_2 t2
    JOIN table_3 t3 ON t3.id = t2.t3_id
WHERE
    t2.id = t1.t2_id
    AND t3.bar = True;

Spatial join with buffer ​

stackoverflowpostgis-intro What you're looking for is a spatial join. Join the table with itself and check which records lie inside each individual buffer, and then exclude the duplicates:

sql
SELECT * FROM point_table p
JOIN point_table p2 ON 
  ST_Contains(ST_Buffer(p.geom, p.buffer_distance), p2.geom) AND
  p2.id <> p.id;
sql
SELECT t1.*, t2.* FROM "table1" t1 LEFT JOIN "tabl2" t2 ON st_contains(st_buffer(st_startpoint(t1.geom), 0.0001), t2.geom)

UPDATE "public"."path_split" t1 SET start_station = t2."name" FROM "public"."railway_subway_Shangshai_station_filtered_merged" t2 WHERE st_contains(st_buffer(st_startpoint(t1.geom), 0.0001), t2.geom) AND right(t2."name_line", CHAR_LENGTH(t2."name_line")-position('Line ' IN t2."name_line")-4)=right(t1."name:en", CHAR_LENGTH(t1."name:en")-position('Line ' IN t1."name:en")-4) AND t1."name" = '轨交12号线'

Joining lines ​

postgis docs

sql
SELECT (ST_LineMerge(st_union(geom))) FROM (SELECT geom FROM "public"."line_11" WHERE "id" IN ('2149', '2150') UNION SELECT geom FROM "railway_subway_Shanghai_path_merged" WHERE fid = 42)

UPDATE "railway_subway_Shanghai_path_merged" set geom = (SELECT (ST_LineMerge(st_union(geom))) FROM (SELECT geom FROM "public"."line_11" WHERE "id" IN ('2149', '2150') UNION SELECT geom FROM "railway_subway_Shanghai_path_merged" WHERE fid = 42)) WHERE fid = 42

Reversing lines ​

sql
SELECT * FROM "public"."path_split" WHERE "name:en" = 'Shanghai Metro Line 12' AND "position" = '下行'
UPDATE "public"."path_split" SET geom = st_reverse(geom) WHERE "name:en" = 'Shanghai Metro Line 12' AND "position" = '下行'

Credit @Wayne19980