[postgis-users] Merge all polygons from a dataset

[danny]

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/4139536e/attachment.html>