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