[postgis-users] ST_SymDifference

[Stephen V. Mather]

Awesome.  That worked.

 

OK, so

                

           ...FROM boundary neww, boundary_old oldd;

 

is an implicit Cartesian product, and that gets performed before the union,
hence why we put the union into the FROM statement.  I had no idea I had
been using so many implicit Cartesian products. .  Otherwise we are
collecting a pairwise collection of all records.  So, if we wanted to retain
some of the original tabular information, we might skip the Cartesian
product altogether (which is a fine option if we just want the gross
difference between a unioned version of all the geometries in each of the
tables) and do join based on bounding box instead?

 

BTW, thanks too for the tip on ST_Collect.

 

Steve

 

http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather
Geographic Information Systems (GIS) Manager
(216) 635-3243

svm at clevelandmetroparks.com
 <http://www.clemetparks.com/> clevelandmetroparks.com

 

 

 

 

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paragon
Corporation
Sent: Monday, October 17, 2011 9:31 PM
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] ST_SymDifference

 

Stephen, 

 

It takes a brave man to admit he is using antiquated technology. I applaud
you :).

 

I see one major issue here.  You are doing a cartesian product before
unioning / collecting.

 

That means for every new.the_geom you have you are creating n copies of it
where n is the number of boundary_old records you have and then unioning
that permutation.  You'll get the right answer, but it will be really slow
the more geometries you have.  

 

Try this:

 

 SELECT ST_SymDifference(new.geom, oldd.geom)

FROM     

(SELECT ST_Union(the_geom) As  geom FROM boundary) AS neww

 CROSS JOIN (SELECT ST_Union(the_geom) As geom FROM boundary_old) AS oldd;

 

The above should be faster, but depending on how many geometries you have
could still be slow.

In 1.4 and above, aggregation speed and ST_Unionspeed got wicked faster than
1.3  especially when you are talking about a lot of geometries.

 

That would be one reason to upgrade.

 

As far as using ST_Collect vs. ST_Union.  If you are dealing with polygons
you are much safer using ST_Union since ST_Collect will create invalid
multipolygons if any of your geometries intersect.

 

Hope that helps,

Regina

http://www.postgis.us

 

 

 

 

 


  _____  


From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Stephen
V. Mather
Sent: Monday, October 17, 2011 5:03 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] ST_SymDifference

Hi All,

                True confession, I'm running PostGIS 1.3.5, so throw the
tomatoes gently if A) I'm doing a bad query, or B) I'm dredging up old news
on (in)efficiency, and I really should upgrade to 1.5.x, thank you very
much. .  Admittedly, it is time for that, but hopefully that's a separate
issue.

                I'm trying to calculate the symmetrical difference on a pair
of geometries, and my query runs slowly:

 

     SELECT ST_SymDifference(

                ST_Collect(neww.the_geom),

                ST_Collect(oldd.the_geom) 

           ) AS the_geom

           FROM boundary neww, boundary_old oldd;

 

Now, if I were to be entirely honest, I'd confess that this was my cludge of
an original query:

 

     SELECT ST_SymDifference(

                ST_Union(ST_Buffer(neww.the_geom, 0)),

                ST_Union(ST_Buffer(oldd.the_geom, 0)) 

           ) AS the_geom

           FROM boundary neww, boundary_old oldd;

 

but who would admit to running a query like that, should they know better.
Not I.

 

So, why is my query sooooo slooooow?  Is it A), B), or some yet to be known
to me C).  BTW, (now I'm just to being provocative,) when I run a
symmetrical difference in ArcGIS, it runs very quickly. :).

 

Best,

Thanks,

 

 

 http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather
Geographic Information Systems (GIS) Manager
(216) 635-3243

svm at clevelandmetroparks.com
 <http://www.clemetparks.com/> clevelandmetroparks.com

 

 

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111018/67055902/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 3772 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111018/67055902/attachment.png>