[postgis-users] Landtype minus parcels ?

[Birgit Laggner]

Hi Brian,

you could try this query:

SELECT st_difference(l.wkb_geometry,
          case when st_union(p.wkb_geometry) is null
            then st_collect(p.wkb_geometry)
            else st_union(p.wkb_geometry)
          end)
        as tgeom
from
   big_landtypes l
   inner join
   small_parcels p
   on st_intersects(l.wkb_geometry, p.wkb_geometry)
where l.pkey = 1
group by l.pkey, l.wkb_geometry;

You are getting more than one result per landtype geometry because they 
intersect with more then one parcel geometry. For each intersect case 
there is a result row. You are unioning all parcel geometries which are 
anywhere intersecting any landtype geometry, which is probably resulting 
in a big geometry. I would assume, the proposed query would do better, 
but you would have to try :-) I also added a CASE WHEN with st_collect 
as an alternative for st_union, because, sometimes st_union fails and 
results in a NULL geometry. In this case st_collect could take over and 
as for the st_difference, st_union or st_collect have the same impact.

Hope that helps,

Birgit.



Am 14.02.2012 20:42, schrieb Brian Hamlin:
> SELECT distinct on (tgeom)
>      st_difference(
>        l.wkb_geometry,
>
>        (select st_union(p.wkb_geometry )
>        from
>          big_landtypes l,
>          small_parcels p
>        where st_intersects(
>          l.wkb_geometry, p.wkb_geometry) AND
>          l.pkey = 1)
>        ) tgeom
>      FROM
>        big_landtypes l,
>        small_parcels p
>      WHERE
>        l.pkey = 1
>      ORDER BY
>        tgeom;