[postgis-users] ST_Difference Perplexes Me!

[Dylan Lorimer]

Hi Folks,

I few weeks ago I asked this list how to generate, given a country border
and some arbitrary polygons lying within it, the geometry within the country
border where the arbitrary polygons do NOT lie. Regina, as usual, provided a
thoughtful reply. However, I'm now revisiting this topic and am seeing some
odd behavior from ST_Difference.

Hoping for some clarification from those in the know. I also noticed in the
archives some others were confused by the results of ST_Difference. So
perhaps I just don't understand the results correctly.

- I have VMAP country borders stored as polygons in table A, along with the
country name.
- I have a number of MULTIPOLYGONS stored in table B that exist,
geographically, in various countries over the world. I've tripled checked
that the MULTIPOLYGONS are all valid and clean and have no self
intersections etc etc.

What I want is to generate the geometry that represents China minus the
MULTIPOLYGONS that lie within it. Easy enough, right?

I swear the query would look just like this:

SELECT ST_Difference(A.the_geom, B.the_geom), A.country_name FROM A, B WHERE
A.country_name='China' AND ST_Overlaps(b.the_geom, a.the_geom) GROUP BY
A.country_name;

Logically, this subtracts the MULTIPOLYGONS from the Geometry represented by
China and for the sake of query speed restricts the operation to only where
the two overlap.

The result I get is simply all of China as a single polygon.

Any ideas or thoughts as to why this isn't working? I know Regina will come
back with a clever LEFT JOIN, and I swear I've tried a bunch of them to no
avail as well but I keep coming back to this simple query that I think
should work.

Many Thanks.
-dylan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080504/3bca6cd8/attachment.html>