Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have the following layer using SRID 27700 in postgis:

enter image description here

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.

share|improve this question
    
This process is known as a "dissolve". I'm not experienced with PostGIS, but I believe you can use the ST_Union command do perform the dissolve. –  dmahr Aug 21 '12 at 20:07
    
Hi dmahr, thanks for the clarification, wasn't sure what it was called, however if you read my question you'll see I already tried that :-) –  shawty Aug 21 '12 at 20:13
    
Oops, sorry...didn't see that. Have you tried the select statement without the astext(multi()) part? I'm just going off of what I see in other PostGIS dissolve examples. –  dmahr Aug 21 '12 at 20:20
    
Not yet, will try that now. Tks. Do you have link for dissolve examples? –  shawty Aug 21 '12 at 20:25
    
Please edit for express if you want "the single outer ring" or not. (see my answer) –  Peter Krauss Aug 22 '12 at 17:01

4 Answers 4

up vote 13 down vote accepted

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.

share|improve this answer
    
Good clue for geometries correction, but about "... one large polygon from the single outer ring that contains all the polys..."? –  Peter Krauss Aug 22 '12 at 16:58
    
I didn't know about 'SnapTo' I'll give that a try :-) Tks. Unfortunately, no, not using PG 2 just yet, upgrade is in the pipeline though. –  shawty Aug 22 '12 at 19:02
    
Not sure your syntax is correct. Per postgis.net/docs/ST_Union.html, there is no signature that accepts a number in the 2nd parameter. –  Aren Cambre May 11 '13 at 3:21
    
You're right, the parenthesis was in the wrong place. Edited. –  Paul Ramsey May 11 '13 at 21:53

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.net/docs/ST_Collect.html

Note: ST_Collect is much faster than ST_Union

share|improve this answer
    
I tried that and got slightly different results, however is a geometry collection what I need? I'm essentially trying to make one big polygon, optionally with holes in it (Specifically in Derbyshire & nottinghamshire where both derby & Nottingham form separate districts right in the centre. I did observe the speed difference though, so thats kewl. –  shawty Aug 21 '12 at 22:11

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); 
share|improve this answer
    
Sorry for the confusion: What I meant by my description was one larger polygon created from the smaller ones, I realise that depending on context 'Outer Ring' may mean different things to different folks, my intention was to describe a single polygon created from the boundary present around each polygon group. –  shawty Aug 22 '12 at 19:01

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.

share|improve this answer
2  
How about providing a link for those who don't know it? thanks. –  jonatr Aug 22 '12 at 5:02
    
Hi CHEnderson you are in fact correct, yes I am using the boundary layer data set from OS Opendata, unfortunately the county boundaries are not complete, the actual County's shape file only includes those that are named as counties, the London boroughs contains the areas around London, and other files all have some parts in, some lower and smaller levels than the others. The only file that has the entire outline of the UK in, and subsequently any chance of extracting all upper level counties and municipal boundaries in one layer is the parishes layer, hence why I'm attempting to do so. –  shawty Aug 22 '12 at 18:52
    
For those that are interested you can download the county boundaries and more, right here: ordnancesurvey.co.uk/oswebsite/products/os-opendata.html –  shawty Aug 22 '12 at 18:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.