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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
