[postgis-users] Merging two features

[Stephen Woodbridge]

Jeff Dege wrote:
> I have a shapefile that contains a few duplicated features.
> 
> They have identical attribute data, different OGRFeature numbers, and
> different geometries.
> 
> I've imported them into PostGIS, but having two different records for
> the same feature is causing some problems.
> 
> The schema is simple:
> 
>    CREATE TABLE "public"."counties" (gid serial PRIMARY KEY,
>    "state" varchar(2),
>    "name" varchar(18),
>    "wtr" int2,
>    "fips" int4);
>    SELECT
> AddGeometryColumn('public','counties','the_geom','4267','MULTIPOLYGON',2
> );
> 
> The duplicates are easy to find:
> 
>    select state, name, count(*)
>    from counties
>    group by state, name
>    having count(*) > 1
>    ;
> 
> What I would like to do is to merge these duplicate records into a
> single record, containing a union of the separate records' geometries.
> But I'm not sure of exactly how to approach it.

Jeff,

Create a temp table

insert into temp (select state, name, wtr, fips, union(the_geom) from 
countries group by states, name having count(*) > 1 );

check that it did what you expected, then delete the records by state, 
name and insert the temp table records into the original table.

or you can alter you table to add a column which is used to track the 
unioned records when they are added so you can delete the non-unioned 
records that you don't want. You can alter the table to drop the column 
when you are done.

-Steve