Postgresql: Merge multiple geometries into single geometry using Join

Question

Say I have two tables, geom_levels and taz_geoms where taz_geoms has the columns as follows:

taz_geoms

id(int)   state(int)   county(int)  taz(int)  geom(geometry(MultiPolygon,4326))

and geom_levels looks like this:

geom_levels

TAZ         COUNTY  STATE   DISTRICT
1           1       29      1
2           1       29      1
3           1       29      1
4           2       29      2
5           2       29      2
6           2       29      2
7           2       29      3
8           3       29      3
9           3       29      3
10          3       29      4
11          3       29      4
12          3       29      4
13          4       29      5
14          4       29      5
15          4       29      5
16          4       29      6
17          4       29      6

How would I go about combining these taz geometries into county, state, and district geometries? I would like to have a county_geoms, state_geoms, and district_geoms table. I have see that you can use ST_UNION with a geom array, but how would I generate such an array for counties or districts?

I was thinking something like this for counties:

SELECT ST_UNION(SELECT geom from taz_geoms GROUP BY county);

and for districts:

SELECT ST_UNION(SELECT geom from taz_geoms t LEFT JOIN geom_levels gl ON gl.taz = t.taz GROUP BY district);

But those options do not see possible.

Ideas?

Answer 1

try with:

SELECT ST_UNION( ARRAY( 'YOUR SELECT geoms QUERY' ) );

in your case:

SELECT ST_UNION(ARRAY( (SELECT geom from taz_geoms t LEFT JOIN geom_levels gl ON gl.taz = t.taz GROUP BY district) ));

I had the same problem and got it to work with postgre usin the ARRAY() function ;)