How to combine two tables in the database of SQL?-CodePudding

I have two tables facilities and admin.

For facilities I have columns id, name, address, district. For admin I have columns shape_area, district, geom.

I want to combine the geom column from admin with facilities according to the district column, so the facilities table can have id, name, address, district, geom.

How can I have this facilities table in the database? (not just showing in the result)

CodePudding user response：

You can use SQL join condition to connect two tables.

select id, name, address, district, geom from facilities join admin on facilities.district = admin.geom

CodePudding user response：

alter table facilities 
    add column geom geometry, 
    add column shape_area numeric generated always as (st_area(geom)) stored;

with joined_facilities_and_admin as
    (   select  f.id,
                f.name,
                f.address,
                f.district,
                a.district,
                a.geom
        from facilities f
            join admin a using (district))
update  facilities f
set     geom=fa.geom
from    joined_facilities_and_admin fa
where   f.id=fa.id;

DB<>fiddle (type geometry swapped out for text since PostGIS is absent)