I am using pgAdmin (v.1.22) for my postgres(9.5). I am still fairly new to postgres. I am trying to perform a union(intersection) of multiple layers where there is both complete and no overlap between layers. I need all linework and attributes to come through to the final product. I am playing around with st_union and st_intersection but can't quite get to the final layer I need.
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST AS
SELECT
(st_dump(st_union(a.shape, b.shape))).geom geom, a.status, b.code
FROM
p760_passive_lb a, p760_fmu b
WHERE
st_intersects(a.shape, b.shape)
;
ALTER TABLE TEST ADD COLUMN id SERIAL;
ALTER TABLE TEST ADD PRIMARY KEY (id);
CREATE INDEX idx_TEST_geom ON TEST USING gist(geom);
Where am I going wrong?
This is similar to: Is there a union function for multiple layers comparable to ArcGIS in open source GIS like QGIS?
So, to help illustrate, I have attached an image of what is going on. The top two layers are my inputs. The bottom left is the output of the above code. Two things of note: (1) the features in the western portion are not visible, and (2) when I use the identify tool (bottom right) in QGIS on any ONE area in the result, the whole area gets chosen and multiple features are selected. Strangely, the linework in the western section is viewable with the identify tool. This leads me to think that the layers are just stacked on top of each other and not forming one geometry.