<div dir="ltr">Thanks Regina.<br><br>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. <br>
Anyway, my union finished after 4 hours.<br>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)<br>
<br>Regards. <br><br><br><br><div class="gmail_quote">On Mon, Aug 4, 2008 at 12:25 PM, Obe, Regina <span dir="ltr"><<a href="mailto:robe.dnd@cityofboston.gov">robe.dnd@cityofboston.gov</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial">Try the trick documented here</font></span></div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial"><a href="http://postgis.refractions.net/documentation/manual-svn/ST_Collect.html" target="_blank">http://postgis.refractions.net/documentation/manual-svn/ST_Collect.html</a></font></span></div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial">On hindsight - I realized the ST_Multi call here is
probably unnecessary so you can leave that part out.</font></span></div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial">Hope that helps,</font></span></div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial">Regina</font></span></div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial"></font></span> </div><br>
<div dir="ltr" align="left" lang="en-us">
<hr>
<font size="2" face="Tahoma"><div class="Ih2E3d"><b>From:</b>
<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>
[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b>On Behalf Of
</b>danny<br></div><b>Sent:</b> Monday, August 04, 2008 5:04 AM<div><div></div><div class="Wj3C7c"><br><b>To:</b> PostGIS
Users Discussion<br><b>Subject:</b> Re: [postgis-users] Merge all polygons from
a dataset<br></div></div></font><br></div><div><div></div><div class="Wj3C7c">
<div></div>
<div dir="ltr">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)<br>Any other alternative and
quicker ways to accomplish the same operation?<br>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. <br><br><br><br>
<div class="gmail_quote">On Fri, Aug 1, 2008 at 5:31 PM, danny <span dir="ltr"><<a href="mailto:whatevar89@gmail.com" target="_blank">whatevar89@gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div dir="ltr">Thanks Robert.<br><br>I actually exported from ogr2ogr and it
created a shape that worked fine in ArcView.<br><br>Best
<div>
<div></div>
<div><br> <br><br><br>
<div class="gmail_quote">On Fri, Aug 1, 2008 at 5:19 PM, Burgholzer,Robert <span dir="ltr"><<a href="mailto:rwburgholzer@deq.virginia.gov" target="_blank">rwburgholzer@deq.virginia.gov</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div vlink="blue" link="blue" lang="EN-US">
<div>
<p><font size="2" color="navy" face="Arial"><span style="font-size: 10pt; color: navy; font-family: Arial;">Could ArcView
perhaps want some columns other than just the shape? Perhaps you could
put a numeric ID or something else in there.</span></font></p>
<p><font size="2" color="navy" face="Arial"><span style="font-size: 10pt; color: navy; font-family: Arial;"></span></font> </p>
<p><font size="2" color="navy" face="Arial"><span style="font-size: 10pt; color: navy; font-family: Arial;">Just a
thought,</span></font></p>
<p><font size="2" color="navy" face="Arial"><span style="font-size: 10pt; color: navy; font-family: Arial;"></span></font> </p>
<div>
<p><font size="2" color="navy" face="Arial"><span style="font-size: 10pt; color: navy; font-family: Arial;">Robert W.
Burgholzer</span></font></p>
<p><font size="2" color="navy" face="Arial"><span style="font-size: 10pt; color: navy; font-family: Arial;">Surface Water
Modeler</span></font></p>
<p><font size="2" color="navy" face="Arial"><span style="font-size: 10pt; color: navy; font-family: Arial;">Office of Water
Supply and Planning</span></font></p>
<p><font size="2" color="navy" face="Arial"><span style="font-size: 10pt; color: navy; font-family: Arial;">Virginia Department
of Environmental Quality</span></font></p>
<p><font size="2" color="navy" face="Arial"><span style="font-size: 10pt; color: navy; font-family: Arial;"><a href="mailto:rwburgholzer@deq.virginia.gov" target="_blank">rwburgholzer@deq.virginia.gov</a></span></font></p>
<p><font size="2" color="navy" face="Arial"><span style="font-size: 10pt; color: navy; font-family: Arial;">804-698-4405</span></font></p>
<p><font size="2" color="navy" face="Arial"><span style="font-size: 10pt; color: navy; font-family: Arial;">Open Source
Modeling Tools:</span></font></p>
<p><font size="2" color="navy" face="Arial"><span style="font-size: 10pt; color: navy; font-family: Arial;"><a href="http://sourceforge.net/projects/npsource/" target="_blank">http://sourceforge.net/projects/npsource/</a></span></font></p>
</div>
<p style="margin-left: 0.5in;"><font size="2" face="Tahoma"><span style="font-size: 10pt; font-family: Tahoma;">
<div>-----Original Message-----<br><b><span style="font-weight: bold;">From:</span></b> <a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b><span style="font-weight: bold;">On Behalf Of </span></b>danny<br>
</div>
<div>
<div></div>
<div><b><span style="font-weight: bold;">Sent:</span></b> Friday, August 01,
2008 9:30 AM<br><b><span style="font-weight: bold;">To:</span></b> PostGIS
Users Discussion<br><b><span style="font-weight: bold;">Subject:</span></b>
Re: [postgis-users] Merge all polygons from a
dataset</div></div></span></font>
</p><p></p>
<div>
<div></div>
<div>
<p style="margin-left: 0.5in;"><font size="3" face="Times New Roman"><span style="font-size: 12pt;"></span></font> </p>
<div>
<p style="margin-bottom: 12pt; margin-left: 0.5in; margin-right: 0in;"><font size="3" face="Times New Roman"><span style="font-size: 12pt;">Many thanks,
Regina! Indeed ST_Union generated the polygon, invisible in PgAdmin!!
<br><br>It creates a nice layer when I open it in uDig. <br><br>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.<br><br>Here's how I created the layer:<br><br>create table
europe_contour();<br>SELECT addgeometrycolumn('db, 'europe_contour',
'the_geom', 32767,'MULTIPOLYGON', 2 );<br>insert into
europe_contour(the_geom) values( (select st_union(the_geom) from
europe));<br><br>Then I generate the shapefile: <br><br>"c:\Program
Files\PostgreSQL\8.3\bin\pgsql2shp.exe" -u postgres -P password -f
europe_contour db europe_contour<br><br>Any suggestions perhaps on anything
I've wrong?<br><br></span></font></p>
<div>
<p style="margin-left: 0.5in;"><font size="3" face="Times New Roman"><span style="font-size: 12pt;">On Fri, Aug 1, 2008 at 1:15 PM, Obe, Regina <<a href="mailto:robe.dnd@cityofboston.gov" target="_blank">robe.dnd@cityofboston.gov</a>> wrote:</span></font></p>
<div>
<p style="margin-left: 0.5in;"><font size="2" color="blue" face="Arial"><span style="font-size: 10pt; color: blue; font-family: Arial;">Erik,</span></font></p>
<p style="margin-left: 0.5in;"><font size="2" color="blue" face="Arial"><span style="font-size: 10pt; color: blue; font-family: Arial;">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.</span></font></p>
<p style="margin-left: 0.5in;"><font size="3" face="Times New Roman"><span style="font-size: 12pt;"></span></font> </p>
<p style="margin-left: 0.5in;"><font size="2" color="blue" face="Arial"><span style="font-size: 10pt; color: blue; font-family: Arial;">SELECT the_geom is
null </span></font></p>
<p style="margin-left: 0.5in;"><font size="2" color="blue" face="Arial"><span style="font-size: 10pt; color: blue; font-family: Arial;">FROM (somequery or
table) as foo</span></font></p>
<div>
<p style="margin-left: 0.5in;"><font size="2" color="blue" face="Arial"><span style="font-size: 10pt; color: blue; font-family: Arial;">LIMIT
1</span></font></p></div>
<div>
<p style="margin-left: 0.5in;"><font size="3" face="Times New Roman"><span style="font-size: 12pt;"></span></font> </p></div>
<div>
<p style="margin-left: 0.5in;"><font size="2" color="blue" face="Arial"><span style="font-size: 10pt; color: blue; font-family: Arial;">will tell you if
its actually returning no geometry</span></font></p></div>
<div>
<p style="margin-left: 0.5in;"><font size="3" face="Times New Roman"><span style="font-size: 12pt;"></span></font> </p></div>
<div>
<p style="margin-left: 0.5in;"><font size="2" color="blue" face="Arial"><span style="font-size: 10pt; color: blue; font-family: Arial;">Hope that
helps,</span></font></p></div>
<div>
<p style="margin-left: 0.5in;"><font size="2" color="blue" face="Arial"><span style="font-size: 10pt; color: blue; font-family: Arial;">Regina</span></font></p></div>
<p style="margin-left: 0.5in;"><font size="3" face="Times New Roman"><span style="font-size: 12pt;"></span></font> </p>
<div style="margin-left: 0.5in; text-align: center;" align="center"><font size="3" face="Times New Roman"><span style="font-size: 12pt;">
<hr size="2" width="100%" align="center">
</span></font></div>
<p style="margin-bottom: 12pt; margin-left: 0.5in; margin-right: 0in;"><b><font size="2" face="Tahoma"><span style="font-weight: bold; font-size: 10pt; font-family: Tahoma;">From:</span></font></b><font size="2" face="Tahoma"><span style="font-size: 10pt; font-family: Tahoma;"> <a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b><span style="font-weight: bold;">On Behalf Of </span></b>danny<br>
<b><span style="font-weight: bold;">Sent:</span></b> Friday, August 01, 2008 6:12
AM<br><b><span style="font-weight: bold;">To:</span></b> <a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><b><span style="font-weight: bold;">Subject:</span></b> [postgis-users] Merge all
polygons from a dataset</span></font></p>
<div>
<div>
<div>
<p style="margin-left: 0.5in;"><font size="3" face="Times New Roman"><span style="font-size: 12pt;">Hello everybody,<br><br>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?<br><br>Many
thanks,<br><br>Erik</span></font></p></div></div></div></div>
<div>
<div style="margin-left: 0.5in; text-align: center;" align="center"><font size="3" face="Times New Roman"><span style="font-size: 12pt;">
<hr size="1" width="100%" align="center">
</span></font></div>
<p style="margin-left: 0.5in;"><b><font size="3" face="Times New Roman"><span style="font-weight: bold; font-size: 12pt;">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. </span></font></b></p></div>
<div style="margin-left: 0.5in; text-align: center;" align="center"><font size="3" face="Times New Roman"><span style="font-size: 12pt;">
<hr size="1" width="100%" align="center">
</span></font></div>
<p style="margin-left: 0.5in;"><b><font size="2" color="#339900" face="Times New Roman"><span style="font-weight: bold; font-size: 10pt; color: rgb(51, 153, 0);">Help make
the earth a greener place. If at all possible resist printing this email and
join us in saving paper. </span></font></b></p>
<p style="margin-bottom: 12pt; margin-left: 0.5in; margin-right: 0in;"><font size="3" face="Times New Roman"><span style="font-size: 12pt;"><br>_______________________________________________<br>postgis-users
mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a></span></font></p>
</div>
<p style="margin-left: 0.5in;"><font size="3" face="Times New Roman"><span style="font-size: 12pt;"></span></font> </p></div></div></div></div></div><br>_______________________________________________<br>postgis-users
mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div></div></div></blockquote></div><br></div></div></div></div><div><div></div><div class="Wj3C7c">
<div><p></p><hr size="1">
<p><b>
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.
</b></p></div>
<p></p><hr size="1">
<p><b><font size="2" color="#339900"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </font></b></p> <p> </p>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>