Add column data to a table based on geometry intersection with another table – PostgreSQL

my city has defined some Urban Revitalization Perimeters using PostGIS I’ve separated the city Blocks within Perimeters and the Buildings within Perimeters. What I want to do now is to update the buildings table with two new columns, that relates them with the blocks they are on.

The following is a sample of the Blocks table, the two columns qd_setor and qd_fiscal are the information I need to pass to the Buildings table

 qd_setor | qd_fiscal |  wkb_geometry 
----------+-----------+---------------
 041      | 038       | 010600...
 086      | 302       | 010600...
 041      | 076       | 010600...
 075      | 054       | 010600...
 019      | 055       | 010600...

This is a sample of the Buildings table:

  ed_id  | ed_area | ed_altura |  ed_distrito  | wkb_geometry
---------+---------+-----------+---------------+--------------
 2739627 | 187.211 |     6.922 | SANTA_CECILIA | 010600...
 2739628 |  240.39 |    43.382 | SANTA_CECILIA | 010600...
 2739781 |  51.758 |     4.668 | SANTA_CECILIA | 010600...
 2739629 | 388.264 |    52.907 | SANTA_CECILIA | 010600...

This is the query I used to create the blocks and buildings tables:

CREATE TABLE quadras_opurb AS
SELECT q.qd_setor, q.qd_fiscal, q.wkb_geometry
FROM quadra_fiscal q
WHERE EXISTS (
SELECT ou_nome
FROM op_urbana_valid o
WHERE
ST_Intersects(q.wkb_geometry, o.wkb_geometry)
);

And this is the expected result, so once it is done I don’t have to test for intersection between the desired block and buildings, I can just filter by qd_setor and qd_fiscal:

  ed_id  | ed_area | ed_altura |  ed_distrito  | wkb_geometry | qd_setor | qd_fiscal 
---------+---------+-----------+---------------+--------------+----------+------------
 2739627 | 187.211 |     6.922 | SANTA_CECILIA | 010600...    | 041      | 038       
 2739628 |  240.39 |    43.382 | SANTA_CECILIA | 010600...    | 041      | 038       
 2739781 |  51.758 |     4.668 | SANTA_CECILIA | 010600...    | 041      | 038       
 2739629 | 388.264 |    52.907 | SANTA_CECILIA | 010600...    | 086      | 302
 2739631 | 187.244 |    22.865 | SANTA_CECILIA | 010600...    | 086      | 302

Note that qd_setor and qd_fiscal will repeat several times, since a block has multiple buildings in it.

Thanks for the help!

Answer

So I figured this out, I used the following query:

CREATE TABLE ed_quadra AS
SELECT
q.qd_setor AS setor,
q.qd_fiscal AS quadra,
e.*
FROM ed_opurb AS e
JOIN quadras_opurb AS q
ON ST_Intersects(e.wkb_geometry, q.wkb_geometry)
ORDER BY q.qd_setor ASC, q.qd_fiscal ASC;

Advertisement

Answer