[postgis-users] Re: ST_Difference Perplexes Me!

[Dylan Lorimer]

Wanted to follow-up on this with the SQL that worked in the end.
Firstly...thanks so much Regina! I've no confidence that I ever would have
figured out this sql on my own, and yet looking at it now it makes perfect
sense!

So here it is in case anyone else is interested:

- vmap_countries contains country boundaries
- published_both_prototype_union contains dissolved polygons all over the
world (millions of them.)

CREATE TABLE not_meter_or_less_prototype AS
SELECT ST_Difference(vmap_countries.the_geom, C.the_sum_geom),
vmap_countries.country_na
FROM vmap_countries INNER JOIN (SELECT
ST_MemUnion(published_both_prototype_union.the_geom) As the_sum_geom,
vmap_countries.country_na
   FROM vmap_countries INNER JOIN published_both_prototype_union ON
ST_Intersects(vmap_countries.the_geom,
published_both_prototype_union.the_geom)
               WHERE vmap_countries.country_na IN ('China', 'India',
'Japan')
               GROUP BY vmap_countries.country_na ) As C
ON  vmap_countries.country_na = C.country_na;

Many many (many) thanks to those on the list that have helped me out!
Cheers,
dylan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080505/5a9b50ba/attachment.html>