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.
If anyone has any further Ideas it would be appreciated.
I'm essentially 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)
------======== Update : 22/8/2012 ========------
Thanks everyone for all the answers so far, I'll go away and examine each one, see what happens then come back and document on my findings.
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
I know what I need to know about relatively straight forward stuff in Postgis such as Spatial Querying and Statistics gathering and if the publisher will allow me I do intend to mention gis.stackexchange and all those who frequent here :-)
I'll be back soon....
------======== Update : 25/8/2012 ========------
Well after wandering back off into cyber space and trying the suggestions below, the one that worked the best was 'Paul Ramseys' solution. Thanks Paul.
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.