I'm trying to union US State level upper- and lower-level government boundaries in such a way that generates one PostGIS table with both the upper and lower level names for each shape.
In my example below, U
is a dataset and has a column with rows UA
and UB
. L
is another dataset and has LA
, LB
, and LC
. When I union the shapes together I should get the same shapes as L
but the new, unioned dataset has both data columns.
Edit: My example image above is pretty crummy. The answers so far have been related to querying the data assuming the geometry is available. This is important but is skipping the more confusing part of my question. A better example:
The yellow shape is from U
, the orange shape is from L
. Notice that they overlap but some pieces of the polygons aren't contained completely inside another shape (like in my example where LB
and LC
are completely within UB
).
The question I was trying to ask is about correctly chopping the two layers so the resulting data set has no overlaps. See for example this mailing list post that gets close but doesn't quite work.
Answer
Without knowing your column names, this is my best guess. (I have not had a chance to test, so it may not be exactly right.) Hopefully you can suss out the column names I used. Assuming L is a subset of U:
SELECT l.id AS l_id, u.ab AS uab, l.abc AS labc, ST_Intersection(u.geom, l.geom) AS geom
FROM u, l
WHERE ST_Intersects(ST_PointOnSurface(l.geom), u.geom);
No comments:
Post a Comment