Re: [postgis-users] Question about st_difference

[G. van Es]

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 <chris.herman...@tecogroup.ca>
To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
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 <nicolas.ri...@gmail.com>

> 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
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

--
Chris Hermansen
Vice President

TECO Natural Resource Group Limited
301 · 958 West 8th Avenue
Vancouver BC CANADA · V5Z 1E5
Tel +1.604.714.2878 · Cel +1.778.840.4625

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users