The steps you need to perform to complete this recipe are as follows:
- First, check the imported table by running the following commands:
postgis_cookbook=# SELECT county, fips, state_fips
FROM chp03.counties ORDER BY county;
(6138 rows)
- Now perform the merging operation based on the state_fips field, using the ST_Union PostGIS function:
postgis_cookbook=# CREATE TABLE chp03.states_from_counties
AS SELECT ST_Multi(ST_Union(the_geom)) as the_geom, state_fips
FROM chp03.counties GROUP BY state_fips;
- The following screenshot shows how the output PostGIS layer looks in a desktop GIS; the aggregate counties have successfully been composed by their respective state (indicated by the thick blue border):
