[postgis-users] geomunion revisited....

[Kevin Neufeld]

Also, I think you may be after the SQL syntax UNION ALL, not UNION, if 
you are simply after concatenating the resultset from table 2 to table 1.

select simplify(...
UNION ALL
select simplify(...

Also, I too would be curious how buffer(collect(...), 0) fares for you 
instead of geomunion() for your final 5 hour query.
Since you're doing this all at once anyway, it may work.  Geomunion() is 
an aggregate for the two geometry method.  Which means that it will 
slowly add one geometry at a time to an every growing resulting 
geometry.  Buffer(collect(...), 0) will decontruct the collection once, 
and rebuild it once to a union-ed geometry.
-- Kevin

Josh Livni wrote:
> I think running buffer(bunch_of_geoms,0) rather than 
> geomunion(bunch_of_geoms) might be faster.
>
> That said, still seems it takes a bit long.  If it were me I'd try 
> playing around by first creating a table with your results, eg:
>
> '''create table simple_buffers as (
> select simplify(buffer(simplify(the_geom, 5), 400), 150) as the_geom 
> from table1
> union
> select simplify(buffer(simplify(the_geom, 5), 400), 150) as the_geom 
> from table2); '''
>
> then you could create a GiST index on the_geom of your new table, run 
> vacuum analyze on it, and then try the geomunion and buffer to test.  
> With 32k+ rows, depending on how much they overlap, that might help a 
> little, or at least help potentially isolate the issue if it does take 
> as long.
> Also I assume you've modified your postgres config away from the 
> defaults to take advantage of more memory on your machine, etc.
>
>
>
>  -Josh
>
>
>
> Lee Keel wrote:
>>
>> Well the good news is that I finally got a configuration that wasn't 
>> crying about memory nor getting the other error....
>>
>> However, I am back to a much more general question.  Have there been 
>> any improvements to the geomunion() algorithm in versions after 
>> 1.2.1?  I have gotten my buffered polygons for all my linestrings and 
>> that part of the query returns 32,278 rows in about 22 seconds.  But 
>> the geomunion of these rows has been running for over 5 hours.  My 
>> problem is that a person can do this in ArcMap in about 20 minutes.
>>
>> So the query that takes 22 seconds is:
>>
>> select simplify(buffer(simplify(the_geom, 5), 400), 150) as the_geom 
>> from table1
>>
>> union
>>
>> select simplify(buffer(simplify(the_geom, 5), 400), 150) as the_geom 
>> from table2
>>
>>
>> by adding geomunion() to the resulting geometry, it is still running:
>>
>> select geomunion(the_geom) as the_geom from (
>>
>> select simplify(buffer(simplify(the_geom, 5), 400), 150) as the_geom 
>> from table1
>>
>> union
>>
>> select simplify(buffer(simplify(the_geom, 5), 400), 150) as the_geom 
>> from table2) foo
>>
>>
>> Yes, I know that I must be insane to asking for a single multipolygon 
>> like this.  And I am sure it is outside the normal realm of 
>> geomunion(), but it would sure be nice if it would work. :)
>>
>> Suggestions welcome.
>>
>> Thanks in advance,
>>
>> Lee Keel
>>
>> This email and any files transmitted with it are confidential and 
>> intended solely for the use of the individual or entity to whom they 
>> are addressed. If you have received this email in error please notify 
>> the sender. This message contains confidential information and is 
>> intended only for the individual named. If you are not the named 
>> addressee you should not disseminate, distribute or copy this e-mail.
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> 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