[postgis-users] Merge all polygons from a dataset

Mon Aug 4 05:37:58 PDT 2008

For what it's worth, you may check your memory settings.  Doing a union
on the entirety of Europe is quite an undertaking, and besides the
iteration through all of the pieces of the shapes (which is processor
intensive), you may be running into a situation where the machine is
going to disk, which incurs a performance impact.  Then again, if you
are only doing this once, maybe it is not a really big deal.

r.b.

Robert W. Burgholzer

Surface Water Modeler

Office of Water Supply and Planning

Virginia Department of Environmental Quality

rwburgholzer at deq.virginia.gov

804-698-4405

Open Source Modeling Tools:

http://sourceforge.net/projects/npsource/

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
danny
Sent: Monday, August 04, 2008 8:24 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Merge all polygons from a dataset

Thanks Regina.

For some reason ST_Collect doesn't give exactly the same result. For
example I'm trying to merge european countries in one shape and with
ST_Collects it omits England and other islands while ST_Union includes
them. 
Anyway, my union finished after 4 hours.
I'm joining two tables on an ST_Intersect, one of which has ~15000
features (polygons) and the other just one (a complex polygon). The
query's been running for two hours... I hope I'll see the end of it
before the end of the day! (I've set the indexes where I could and tuned
PostgreSQL)

Regards. 

On Mon, Aug 4, 2008 at 12:25 PM, Obe, Regina <robe.dnd at cityofboston.gov>
wrote:

Try the trick documented here

http://postgis.refractions.net/documentation/manual-svn/ST_Collect.html

On hindsight - I realized the ST_Multi call here is probably unnecessary
so you can leave that part out.

Hope that helps,

Regina

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
danny

Sent: Monday, August 04, 2008 5:04 AM

To: PostGIS Users Discussion
Subject: Re: [postgis-users] Merge all polygons from a dataset

I'm getting very long processing time from ST_Union (on a shape of
europe at 1/100000 it's been running since 2 hours and hasn't finished
yet; on my previous one of 1/1000000 it took ~1 hour)
Any other alternative and quicker ways to accomplish the same operation?
I just executed st_buffer(st_collect(the_geom)) but still waiting.
Should it take as long? ST_Collect by itself executes fast but it's
result violates the geometry check constraint. 

On Fri, Aug 1, 2008 at 5:31 PM, danny <whatevar89 at gmail.com> wrote:

Thanks Robert.

I actually exported from ogr2ogr and it created a shape that worked fine
in ArcView.

Best 

On Fri, Aug 1, 2008 at 5:19 PM, Burgholzer,Robert
<rwburgholzer at deq.virginia.gov> wrote:

Could ArcView perhaps want some columns other than just the shape?
Perhaps you could put a numeric ID or something else in there.

Just a thought,

Robert W. Burgholzer

Surface Water Modeler

Office of Water Supply and Planning

Virginia Department of Environmental Quality

rwburgholzer at deq.virginia.gov

804-698-4405

Open Source Modeling Tools:

http://sourceforge.net/projects/npsource/

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
danny

Sent: Friday, August 01, 2008 9:30 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Merge all polygons from a dataset

Many thanks, Regina! Indeed ST_Union generated the polygon, invisible in
PgAdmin!! 

It creates a nice layer when I open it in uDig. 

However when I export it with pgsql2shp, the shape is unreadable by
ArcView with the errmsg: Number of shapes does not match the number of
table records.

Here's how I created the layer:

create table europe_contour();
SELECT addgeometrycolumn('db, 'europe_contour', 'the_geom',
32767,'MULTIPOLYGON', 2 );
insert into europe_contour(the_geom) values( (select st_union(the_geom)
from europe));

Then I generate the shapefile: 

"c:\Program Files\PostgreSQL\8.3\bin\pgsql2shp.exe" -u postgres -P
password -f europe_contour db europe_contour

Any suggestions perhaps on anything I've wrong?

On Fri, Aug 1, 2008 at 1:15 PM, Obe, Regina <robe.dnd at cityofboston.gov>
wrote:

Erik,

How were you using ST_Union and what exactly do you mean by no result.
no result as it looks blank?  If you are using something like PgAdmin
III - it will look blank because it exceeds the buffer supported by
PgAdmin III.

SELECT the_geom is null 

FROM (somequery or table) as foo

LIMIT 1

will tell you if its actually returning no geometry

Hope that helps,

Regina

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
danny
Sent: Friday, August 01, 2008 6:12 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Merge all polygons from a dataset

Hello everybody,

I've converted a shapefile of Europe to PostGIS. I would like to merge
all the countries together so that I only have one polygon left, the
whole contour of Europe. I have tried ST_Union that gives me no result.
I've tried several other functions to no avail. Does anybody know how I
should proceed?

Many thanks,

Erik

  _____  

The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure pursuant
to Massachusetts law. It is intended solely for the addressee. If you
received this in error, please contact the sender and delete the
material from any computer. 

  _____  

Help make the earth a greener place. If at all possible resist printing
this email and join us in saving paper. 

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

  _____  

The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure pursuant
to Massachusetts law. It is intended solely for the addressee. If you
received this in error, please contact the sender and delete the
material from any computer. 

  _____  

Help make the earth a greener place. If at all possible resist printing
this email and join us in saving paper. 

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080804/58063de3/attachment.html>