I have the following layer using SRID 27700 in postgis:
It's every administrative region in the UK, and (as you can see from the colour grouping) each of them has a text field specifying the county they lie in.
What I'd like to do is to make larger county polygons from the smaller ones in a given county, so EG in the picture above all the teal colour polygons would form one large polygon from the single outer ring that contains all the polys in that colour, like wise all purple, brown, pink, grey etc should all form one polygon.
I've already tried the following:
insert into parishesmerged (geometry) select astext(multi(ST_Union(the_geom))) as the_geom from parishes group by county_name
But it keeps generating broken geometries which I then have big problems processing further.
I'm trying to make a simpler county level map with the major output areas in.
Any solutions don't have to be in Postgis either, I have the full OS4Geo stack installed, the latest version of QGis and more utils than I can shake a stick at.
The only things I don't have are the big boys like ArcGis (Although I may have an Old Mapinfo lying around somewhere)
For the record, the dataset I'm trying to create is to accompany a GIS book I'm on writing aimed at .NET programmers wishing to write GIS applications using .NET
After trying the suggestions below, the one that worked the best was 'Paul Ramseys' solution.
I now have a nice simplified counties & boroughs file that's just simple enough for my book, but complex enough to allow me to demonstrate some interesting geo-spatial SQL.
Even though Paul's solution ultimately was the one that worked for me, I also drew on the other answers for things like simplifying the polygon map and reducing the complexity further.
On thing I did observe while doing this however, while ST_Collect is indeed faster than ST_Union, run for run it was also the one mostly responsible for broken geometries. My guess is the speed increase is obtained at the expense of less accuracy in the core function.