[postgis-users] ST_Difference Perplexes Me!

[Paragon Corporation]

Had a typo in my last statement and also to make it so you can remove the
where later - revise to
 
SELECT ST_Difference(A.the_geom, C.the_sum_geom), A.country_name
FROM A INNER JOIN (SELECT ST_Collect(B.the_geom) As the_sum_geom,
A.country_name 
    FROM A INNER JOIN B ON ST_Intersects(B.the_geom, A.the_geom) 
                WHERE A.country_name = 'China' 
                GROUP BY A.country_name ) As C
ON  A.country_name = C.country_name
 
 
Hope that helps,
Regina

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paragon
Corporation
Sent: Monday, May 05, 2008 3:36 AM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] ST_Difference Perplexes Me!


Dylan,
 
I hope you don't think that LEFT JOIN is the only trick I have in my bag of
tricks although I have to admit it is my favorite.  For the below I would
think this query wouldn't work at all
 
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;
 
You can't group by without also grouping by the ST_Difference since
ST_Difference is not an aggregate function.
 
If you want to return China minus [all the polygons that lie within it or
overlap it], then you should do
 
SELECT ST_Difference(A.the_geom, C.the_sum_geom), A.country_name
FROM A CROSS JOIN (SELECT ST_Collect(B.the_geom) As the_sum_geom FROM A, B
WHERE A.country_name = 'China' and ST_Intersects(B.the_geom, A.the_geom) )
As C
ON A.country_name = 'China'
 
ST_Collect may not work and if you have intersecting polygons in B, I
suspect replacing ST_Collect with ST_Union will return some sort of error
too.  But give each a try.
 
ST_Overlaps will not subtract the polygons that lie completely inside China,
but ST_Intersects will (ST_Intersects will include those that are completely
within as well as overlap).  If you only want to consider those completely
within China then do ST_Within(B.the_geom, A.the_geom)
 
Hope that helps,
Regina

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Dylan
Lorimer
Sent: Sunday, May 04, 2008 11:50 PM
To: PostGIS Users Discussion
Subject: [postgis-users] ST_Difference Perplexes Me!


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/20080505/87da9502/attachment.html>