[postgis-devel] More Cascade Union Adventures
Obe, Regina
robe.dnd at cityofboston.gov
Thu Aug 14 11:00:55 PDT 2008
Martin,
Haven't looked at the code yet, but just realized that yes that I think you are describing can be expressed in just SQL with what I will
call a CROSS JOIN boolean_or (g would be the geometries all dumped out into their constituent geometries)
So looks something like this
SELECT ST_Collect(s.geom) As newgeom
FROM (SELECT g1.id, g1.geom, boolean_or(g1.geom && g2.geom) As has_intersect
FROM g As g1 CROSS JOIN g As g2
WHERE g1.id <> g2.id
GROUP BY g1.id, g1.geom) As s
GROUP BY
CASE WHEN s.has_intersect
THEN s.id ELSE -1 END
Would collect together all parts that have no intersections and leave the others as separate individual geometries, then I perform my union operation against the above new set.
Not sure how efficient the above would be to do, but I suspect if I do in batches which are ordered, may be quite efficient.
Anyrate I'll give it a go.
Thanks,
Regina
-----Original Message-----
From: postgis-devel-bounces at postgis.refractions.net on behalf of Martin Davis
Sent: Thu 8/14/2008 11:55 AM
To: PostGIS Development Discussion
Subject: Re: [postgis-devel] More Cascade Union Adventures
You can download JTS 1.9 (with src) from Sourceforge:
http://sourceforge.net/projects/jts-topo-suite/
Obe, Regina wrote:
>
> Martin,
> Well there is plpgsql too which is very procedural so doesn't have to
> be expressed in sql per se. I use sql because it is generally
> speedwise faster for the planner to integrate and easier to prototype
> with (at least for me since I can get my thoughts down more succinctly
> granted probably harder to read for others) than plpgsql.
>
> If we replace the garray accum with a custom append - it can also look
> at the geometries as they are coming in and perhaps do a collect
> instead of an append and do some other conditional stuff.
>
> Where would I download this code to see what it looks like (JTS site
> only seems to go to JTS 1.8). I'm having a hard time picturing what
> you are saying. But if I see it in Java I can probably figure out the
> most efficient translation in sql/plpgsql.
>
> Thanks,
> Regina
>
> -----Original Message-----
> From: postgis-devel-bounces at postgis.refractions.net on behalf of
> Martin Davis
> Sent: Wed 8/13/2008 8:08 PM
> To: PostGIS Development Discussion
> Subject: Re: [postgis-devel] More Cascade Union Adventures
>
> While pondering OJ's failure to complete the union on this dataset, I
> realized that one trick that CascadedUnion uses is that when unioning
> two MultiPolygons, it *avoids* unioning polygon components which lie
> outside the intersection of the envelopes of the two input geometries.
> Those polygon components will be left unchanged by the union, and so can
> just be added in afterwards. This can make a big difference when large
> far-flung polygons are being unioned - as is the case with this dataset.
>
> Not sure if you can use this technique in your algorithm - seems like it
> might be tricky to express in SQL.
>
> Obe, Regina wrote:
> >
> > Try this set. - this has 2895 records
> >
> > My timings are
> > ---time 161,047 ms = SELECT 161047/1000.0/60 = 2.68 minutes
> > SELECT ST_CascadeUnion(the_geom)
> > from usstatebounds;
> >
> > --time 121719 ms = SELECT 121719/1000.0/60 = 2.02 minutes
> > SELECT st_unitecascade_garray_sort(ARRAY(SELECT the_geom FROM
> > usstatebounds));
> >
> > In OJ - it gets to the last round and then fails with a Java out of
> > Heap space error.
> >
> >
> > ------------------------------------------------------------------------
> >
> > *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-devel mailing list
> > postgis-devel at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-devel
> >
>
> --
> Martin Davis
> Senior Technical Architect
> Refractions Research, Inc.
> (250) 383-3022
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
--
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022
_______________________________________________
postgis-devel mailing list
postgis-devel at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-devel
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20080814/cf25b471/attachment.html>
More information about the postgis-devel
mailing list