<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16640" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=609041907-05052008><FONT face=Arial
color=#0000ff size=2>Dylan,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008><FONT face=Arial
color=#0000ff size=2>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</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008>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;</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008><FONT face=Arial
color=#0000ff size=2>You can't group by without also grouping by the
ST_Difference since ST_Difference is not an aggregate
function.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008><FONT face=Arial
color=#0000ff size=2>If you want to return China minus [all the polygons that
lie within it or overlap it], then you should do</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008><FONT face=Arial
color=#0000ff size=2>SELECT ST_Difference(A.the_geom, C.the_sum_geom),
A.country_name</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008><FONT face=Arial
color=#0000ff size=2>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</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008><FONT face=Arial
color=#0000ff size=2>ON A.country_name = 'China'</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=609041907-05052008><FONT face=Arial
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=609041907-05052008><FONT face=Arial color=#0000ff
size=2>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)</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=609041907-05052008></SPAN><FONT face=Arial><FONT
color=#0000ff><FONT size=2>H<SPAN class=609041907-05052008>ope that
helps,</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT><FONT color=#0000ff><FONT size=2><SPAN
class=609041907-05052008></SPAN></FONT></FONT></FONT><SPAN
class=609041907-05052008></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=2>R<SPAN
class=609041907-05052008>egina</SPAN></FONT></FONT></FONT><BR></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Dylan
Lorimer<BR><B>Sent:</B> Sunday, May 04, 2008 11:50 PM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> [postgis-users] ST_Difference Perplexes
Me!<BR></FONT><BR></DIV>
<DIV></DIV>Hi Folks,<BR><BR>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.<BR><BR>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.<BR><BR>- I have VMAP country borders stored as
polygons in table A, along with the country name.<BR>- 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.<BR><BR>What I want is to generate
the geometry that represents China minus the MULTIPOLYGONS that lie within it.
Easy enough, right?<BR><BR>I swear the query would look just like
this:<BR><BR>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;<BR><BR>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.<BR><BR>The result I get is simply all
of China as a single polygon.<BR><BR>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.<BR><BR>Many Thanks.<BR>-dylan<BR><BR
clear=all><BR></BODY></HTML>