PostGIS Union Between Two Tables, Preserving Attributes

Question

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 1

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);

Answer 2

The answer depends on how clean and consistent your data is. If you can assume that the centroid of table L can be found inside the matching polygon in table U you can write something like:

SELECT * FROM U INNER JOIN L ON ST_Intersects(L.Centroid(geom),U.geom);

Then you should get what you want. If you use the whole geometry instead of the centroid of L you will get a lot of strange matches.

HTH

Nicklas