Joining lots of small polygons to form larger polygon using PostGIS?

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.


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


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);