What is the best way to join lots of small polygons to form a larger polygon?

Question

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)

Cheers

Shawty

------======== 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....

Cheers

Shawty

------======== 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.

Answer 1

ST_Union would work, but your line work is almost assuredly not clean. So the boundaries of your little thingies don't all perfectly like up. You can gently snap them to a grid to try and increase the odds that vertexes line up, but I bet that you'll still have a few cases that don't work. Either they will be beyond tolerance or, more likely, there will be places where the vertices aren't paired, so there's a line on one side and a vertex on the other.

 CREATE TABLE merged AS
 SELECT ST_Union(ST_SnapToGrid(the_geom,0.0001)) 
 FROM parishes
 GROUP BY county_name;

If you have PostGIS 2.0, building a topology structure with a tolerance could get you to the answer you are looking for, if you have some luck.

Answer 2

ST_Collect function is an "aggregate" function in the terminology of PostgreSQL

"SELECT ST_Collect(GEOM) FROM GEOMTABLE GROUP BY ATTRCOLUMN" will return a separate GEOMETRYCOLLECTION for each distinct value of ATTRCOLUM

http://postgis.refractions.net/docs/ST_Collect.html

Note: ST_Collect is much faster than ST_Union

Answer 3

You say that need to "... form one large polygon from the single outer ring that contains all the polys...". The ST_ExteriorRing do this,

SELECT ST_MakePolygon(ST_ExteriorRing(ST_Union(GEOM)))
FROM GEOMTABLE GROUP BY ATTRCOLUMN

You can use ST_Union(), as suggested, or test with ST_Collection().

---

NOTES: to avoid little lops or "broken geometries" you can use st_convexhull and/or ST_Simplify for each geom,

SELECT ST_MakePolygon(ST_ExteriorRing(ST_union(ST_Simplify(GEOM,0.5))))
FROM GEOMTABLE GROUP BY ATTRCOLUMN

and check your geometries,

SELECT * FROM (
   SELECT gid, ST_IsValid(geom) as valid, ST_IsSimple(geom) as simple 
   FROM GEOMTABLE) AS t  
WHERE NOT(valid AND simple); 

Answer 4

I'm assuming from your question that you are using Ordnance Survey's Boundary-Line product? If that is the case then it already includes a County level data set so there is no need to attempt to generate it yourself from lower level parish areas.

If you aren't using Boundary-Line then I recommend you do as it is free under the OS OpenData licence and has a County level as a shape file you can load directly into PostGIS.