Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

enter image description here

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:

image

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.

share|improve this question
1  
Side comment: calling this a "union", which is the ESRI term for the operation will be confusing to PostGIS people, since the ST_Union function does the opposite of this (which ESRI calls "dissolve") and melts features together. A vendor-neutral term might be "overlay". –  Paul Ramsey Mar 8 '13 at 17:40
    
I think my question is very similar to this one that was already asked and fairly well answered. –  magneticMonster Mar 8 '13 at 19:47
add comment

2 Answers

up vote 4 down vote accepted

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);
share|improve this answer
1  
This is correct, see also gis.stackexchange.com/a/31562/457 for a performance tweak on this approach. –  Paul Ramsey Mar 8 '13 at 17:39
    
This is perfect once I have the shapes generated, but first I need to build the unioned shapes. My example drawing isn't the greatest at demonstrating this, but I need to cut the geometries so there are no overlaps, first. –  magneticMonster Mar 8 '13 at 19:23
    
I think you didn't read the SQL closely enough, it's generating new shapes that consist of just the shared areas. The output of this query has no overlapping features. –  Paul Ramsey Mar 8 '13 at 22:00
    
You're absolutely right Paul -- I think I meant to comment on Nicklas's answer below. –  magneticMonster Mar 8 '13 at 22:22
add comment

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

share|improve this answer
    
Note that Centroid function does not guarantee a point that lies within the polygon. ST_PointOnSurface does. –  Scro Mar 8 '13 at 17:06
    
@Scro you are absolutely right. Miss from me. –  Nicklas Avén Mar 8 '13 at 17:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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