[postgis-users] ST_SymDifference

[Paragon Corporation]

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/20111017/35411311/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/20111017/35411311/attachment.png>