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-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Stephen V. Mather Sent: Monday, October 17, 2011 5:03 PM To: postgis-users@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 s...@clevelandmetroparks.com <http://www.clemetparks.com/> clevelandmetroparks.com
<<image001.png>>
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
