[postgis-users] geomunion revisited....

[Martin Davis]

buffer may or may not be faster than geomunion.  For "small" cases it 
almost certainly is.  However, for very large inputs it may bog down, 
because it loads and processes all geometries at once (and thus doesn't 
have a chance to eliminate linework which would be eliminated in the 
resulting union).  Geomunion, while inefficient as Kevin points out, at 
least has the chance to simplify the result geometry as it goes along.  
*However*, geomunion is "dumb" in that it doesn't try to union adjacent 
polygons first - so it doesn't simplify anywhere nearly as much as it could.

A better tool would be to use the new cascaded union operation in JTS 
(which hopefully can get ported to GEOS & PostGIS soon). 

@Lee: are you positive about your ArcGIS result?  It's using exactly the 
same dataset?

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
>

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022