[postgis-devel] Lame attempt at cascade union in sql

[Kevin Neufeld]

Hmmm.  Still, I give you an applause for the speed an simplicity of your 
attempt.  Even my crazy looking GROUPBY/ORDERBY query that runs in 1min 
is still 3 times slower than Martin Davis's Java code in JTS.  Ideally, 
it would be best for someone to put the thing through GEOS so PostGIS 
can benefit from it as well.  Anyone feel like brushing up on their C?  :)

-- Kevin

Obe, Regina wrote:
>
> Curiosity got the better of me this time so I ran thru the full set 
> once and got this.  So still better, but I guess there is still room 
> for improvement and my cascading seems to not be growing linearly as 
> Kevin's is.
>
> --277,656 ms = 4.6276 minutes
> SELECT ST_CascadeUnion(the_geom)
> FROM (SELECT * FROM sample_poly) As foo;
>
> --69,953 ms = 1.16 minutes
> SELECT ST_Union(the_geom) AS the_geom
> FROM (
>    SELECT min(id) AS id, ST_Union(the_geom) AS the_geom
>    FROM (
>      SELECT min(id) AS id, ST_Union(the_geom) AS the_geom
>      FROM (
>        SELECT min(id) AS id, ST_Union(the_geom) AS the_geom
>        FROM (
>          SELECT min(id) AS id, ST_Union(the_geom) AS the_geom
>          FROM (SELECT * FROM sample_poly) As foo
>          GROUP BY round(id/10)
>          ORDER BY id) AS tmp1
>        GROUP BY round(id/100)
>        ORDER BY id) AS tmp2
>      GROUP BY round(id/1000)
>      ORDER BY id) AS tmp3
>    GROUP BY round(id/10000)
>    ORDER BY id) AS tmp4
> GROUP BY round(id/100000);
>