Ge, Instead of an ST_Union on the full Tbl_b table, merging all polygons into one big polygon, establish first which polygons in Tbl_b intersect the same polygon in Tbl_a. Your recipe then would be:
1. Append the id of the polygon in Tbl_a that intersects with a polygon in Tbl_b. 2. Perform an ST_Union only on those polygons in Tbl_b that have the same id from Tbl_a. 3. Perform the ST_Difference. Thus you will have more iterations in the ST_Difference step, but with smaller geometries. Hope that will allow you to deal with 7M+ records. Regards, Edward Date: Wed, 7 Sep 2011 23:48:14 -0700 From: [email protected] To: [email protected] Subject: Re: [postgis-users] Question about st_difference Hi Nicolas and Chris, Thanks for pointing me in the right direction. On a small scale it works great. Now the job is to implement it on tables with 7million records. Thanks again and kind regards, Ge From: Chris Hermansen <[email protected]> To: PostGIS Users Discussion <[email protected]> Sent: Wednesday, September 7, 2011 8:39 PM Subject: Re: [postgis-users] Question about st_difference In addition to Nicolas' suggestion, one thing that can work, for example when the goal is to "make holes" with one theme in another, is to st_union the two themes, then re-attribute, then delete the unwanted polygons. This has worked for me in relatively small datasets but I've never truly stress tested it. 2011/9/7 Nicolas Ribot <[email protected]> > Hello Group, > > About st_difference I've seen several topics but unfortunately with none of > them I could solve my problem. > > What I want to do is subtract one layer from the other. I could simplify this > down to the following; > > Tbl_a contains 1 record > "POLYGON(((168119.443682473 451093.811197312,197555.469699649 > 451093.811197312,197555.469699649 437539.54805452,168119.443682473 > 437539.54805452,168119.443682473 437539.54805452,168119.443682473 > 451093.811197312)))" > > Tbl_b contains 2 records of which both intersects with the object in tbl_a > > "POLYGON(((171424.484910418 453680.34544058,171424.484910418 > 435697.56759188,174568.140889894 435697.56759188,174568.140889894 > 453680.34544058,174568.140889894 453680.34544058,171424.484910418 > 453680.34544058)))" > > "POLYGON(((184904.90800885 453627.063135843,184904.90800885 > 435644.285287143,188048.563988327 435644.285287143,188048.563988327 > 453627.063135843,188048.563988327 453627.063135843,184904.90800885 > 453627.063135843)))" > > The result I'm looking for is the object of tbl_a abstracted with both > objects from tbl_b, since they are both intersecting. In this example the > result should be 3 squares since it is cut twice by tbl_b. > > > Does anyone know a solution for this? > > Many thanks in advance, > > Ge Hi Ge, You may want to union objects in table B to create a single polygon, then perfom the difference: select st_difference(a.geom, b.geom) from (select 'POLYGON((168119.443682473 451093.811197312,197555.469699649 451093.811197312,197555.469699649 437539.54805452,168119.443682473 437539.54805452,168119.443682473 437539.54805452,168119.443682473 451093.811197312))'::geometry as geom) as a, ( select st_union(geom) as geom from ( select 'POLYGON((171424.484910418 453680.34544058,171424.484910418 435697.56759188,174568.140889894 435697.56759188,174568.140889894 453680.34544058,174568.140889894 453680.34544058,171424.484910418 453680.34544058))'::geometry as geom UNION select 'POLYGON((184904.90800885 453627.063135843,184904.90800885 435644.285287143,188048.563988327 435644.285287143,188048.563988327 453627.063135843,188048.563988327 453627.063135843,184904.90800885 453627.063135843))'::geometry as geom ) as foo ) as b; (see attached pictures) Nicolas _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users -- Chris Hermansen Vice President TECO Natural Resource Group Limited301 · 958 West 8th Avenue Vancouver BC CANADA · V5Z 1E5Tel +1.604.714.2878 · Cel +1.778.840.4625 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
<<attachment: teco_sig.jpg>>
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
