Skip to content
Advertisement

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

This is a sample of the Buildings table:

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

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:

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

Thanks for the help!

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement