Generate the map of federal units from the municipalities map in PostgreSQL with PostGIS.

Example below: example

The table has only the geometry information and the "id".

I tried:

SELECT st_union(m.geom)
FROM uf, municipios as m
WHERE ST_Intersects(uf.geom, m.geom)
AND st_contains(uf.geom, m.geom)
GROUP BY uf.gid

But he returns geometries that do not belong to the state.

Note: The operation has the purpose of studies

  • 1
    you should both select a FEDERAL_UNIT column, and also group by FEDERAL_UNIT (i'm making up that column name, but the grouping shouldn't occur on GID - unless that is equivalent to the federal_unit, but I would recommend changing the name of that as GID is generic and often the same as OBJECTID – DPSSpatial Jul 24 '19 at 23:54

From what I understand, your table uf has the geom of the federal units and municipios the geom of the municipalities, and you don't hvae any problem with the polygons (usually when you do this kind of things, the geom are not perfectly similar and there is approximation that make the correspondance between 2 tables difficult, but here I assume they come from the same source or have been cleaned before with something like a snaptogrid and other).

In this case, you are sure that your municipalities are completely inside your units, so I don't see any reason the request you did doesn't work. The st_intersects is not necesseray however. To make it clearer, I would write it like that:

SELECT uf.gid, ST_Union(geom) 
    FROM (
        SELECT uf.gid, m.geom FROM municipios as m LEFT JOIN uf ON st_contains(uf.geom, m.geom)
    ) as req
    GROUP BY uf.gid

If you have problem with geom that shouldn't be here, maybe you can give us an exemple?

And in case your geom are not from the same sources and not perfectly aligned, you will have cases where the municipality can be slightly outside the uf, so instead of st_contain you would need to use st_intersection with all the uf (where st_intersect), order them by st_area and take the first, to get the uf where the municipality is the most, so it's a different story...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.