[postgis-users] Best way to combine a polygon

[Rob Tester]

Thanks Brent!

This worked great with one exception. In the case that a ring doesn't
contain interior rings the polygon would be removed. The only thing I
changed was to check the difference for NULL before doing the update.

The final test function looked like this (in pgsql)

	FOR geomid IN SELECT id FROM test LOOP
		SELECT geomunion(wkb_geometry) FROM test WHERE wkb_geometry
&& (SELECT wkb_geometry FROM test WHERE id=geomid)
                            and within(wkb_geometry, (SELECT wkb_geometry
FROM test WHERE id=geomid)) and id <> geomid INTO geom;
			
		SELECT difference(wkb_geometry,geom) FROM test WHERE
id=geomid INTO geom;
		
		IF geom IS NOT NULL THEN
			UPDATE test SET wkb_geometry=geom WHERE id=geomid;
		END IF;
	END LOOP;

Still could use some speed enhancements, but at least I have a good
direction to continue. 


-----Original Message-----
From: Brent Wood [mailto:pcreso at pcreso.com] 
Sent: Wednesday, May 16, 2007 5:09 PM
To: PostGIS Users Discussion; robtester at gmail.com
Subject: Re: [postgis-users] Best way to combine a polygon


--- Rob Tester <robtester at gmail.com> wrote:

> I have a table with many single ring polygons. Some of the polygons rings
in
> the table may be contained by other polygons in the same table. What I
would
> like to do is to assemble all the contained rings into a single polygon.
But
> it gets a little more convoluted than that. Some of the rings contained by
> another poly may contain rings of other poly's too, so: 
> 
> Poly1Ring contains poly2ring, poly3ring and poly4ring
> 
> Poly2Ring contains poly3ring.
>  
> What is the best method to combine these polygons to effectively cut holes
> where the rings are contained?

Hi Rob,

I have an idea as to how I'd go about it, but it may not be the best way :-)


What you want to do, if I understand you correctly, is generate the
geomunion
of all the polygons within a polygon, then get the difference between this
polygon & the geomunion.

I think it can be done in a single update statement, but I'd scribble out a
script to iterate through each polygon, coz I'm more comfortable with that
way,
& because I've found such SQL's in one can use very large amounts of memory,
whereas breaking things down into iterated simple steps can be faster. You
can
also check the results from each step before actually committing
anything....

Perl would be faster than bash, but this is quick & easy for me...


So (in pseudo bash/SQL syntax) something along the lines of:



#!/bin/bash

psql -d db -Atc "select id from table;" > file

# now iterate through the id's to fix each one

while read id ; do
  # get the geomunion of all polygons withion the current one
  # but specifically exclude the current one

  UNION=`psql -d db -Atc "select geomunion(geom) from table
                          where geom && (select geom from table
                                         where id=$id)
                            and within(geom, (select geom from table
                                       where id=$id)
                                   and id != $id;"

  # now set the geometry for that polygon to itself less the geomunion

  psql -d db -c "update table set geom=difference(geom, $UNION)
                 where id=$id;"

done < file


Cheers,

  Brent Wood