[postgis-users] [SOLVED] Re: [?] Creating a polygon from a group of polygons

[Max Pyziur]

On Mon, 12 Aug 2013, Peter wrote:

> Well thats an easy one.
>
> If they *exactly* join,
> http://postgis.refractions.net/documentation/manual-1.4/ST_Union.html
>
> if they are a bit squishy, then one simple way is to:
> - st_buffer the multiple polygons all individually outwards by say 10km,
> - st_union them into a single polygon
> - st_buffer that one polygon inwards (negative) 10km
>
> Hopefully helpful contribution from one noob to another.

Much thanks.

I'm an occasional PostGIS user; two weeks of intense usage followed by 
several months of forgetting. And then becoming impatient in trying to 
find a solution once already used.

You're correct that the ST_Union function is key.

Minutes after sending my request I was able to cobble the following code 
to get my results (the TEMP TABLE is only for one US region):
DROP TABLE padd_tmp;

CREATE TEMP TABLE padd_tmp (
 	ctry_code	text,
 	market_region1	text,
 	market_region2	text,
 	prv_code	text
);

COPY padd_tmp FROM stdin WITH NULL AS '';
US	PADD1	PADD1	FL
US	PADD1	PADD1	GA
US	PADD1	PADD1	SC
US	PADD1	PADD1	NC
US	PADD1	PADD1	VA
US	PADD1	PADD1	WV
US	PADD1	PADD1	PA
US	PADD1	PADD1	NY
US	PADD1	PADD1	MD
US	PADD1	PADD1	DE
US	PADD1	PADD1	NJ
US	PADD1	PADD1	CT
US	PADD1	PADD1	RI
US	PADD1	PADD1	MA
US	PADD1	PADD1	NH
US	PADD1	PADD1	VT
US	PADD1	PADD1	ME
\.

-- ST_Multi(ST_Union(ST_Transform(somegeom,4326))) is key here; ST_Multi
-- took me all the way home
SELECT bb.market_region2, st_multi(st_union(st_transform(aa.geom,4326))) 
AS geom
INTO TEMP aa_zz
FROM us_states aa, padd_tmp bb, provinces cc
WHERE aa.state_fips = cc.prv_code
AND bb.prv_code = cc.alt_prv_code
AND cc.ctry_code = 'US'
GROUP BY 1;

SELECT market_region2 FROM aa_zz ORDER BY 1;

UPDATE market_regions set geom =
(SELECT geom FROM aa_zz
WHERE aa_zz.market_region2 = market_regions.market_region2)
WHERE market_regions.market_region2 IN (SELECT market_region2 FROM aa_zz);


> P.

MP

>
>
> Max Pyziur wrote:
>> 
>> Greetings,
>> 
>> Could someone please recommend how to create a polygon from a set of 
>> polygons?
>> 
>> Specifically, a group of states (say, a set of contiguous northeastern 
>> United States) and from that form the polygon containing this region
>> 
>> Much thanks,
>> 
>> Max Pyziur
>> pyz at brama.com
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> 
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>