<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Re: [postgis-users] ST_Difference</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR>
<STYLE>@font-face {
font-family: Tahoma;
}
@font-face {
font-family: Book Antiqua;
}
@page Section1 {size: 612.0pt 792.0pt; margin: 72.0pt 90.0pt 72.0pt 90.0pt; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: blue; TEXT-DECORATION: underline
}
P {
FONT-SIZE: 12pt; MARGIN-LEFT: 0pt; MARGIN-RIGHT: 0pt; FONT-FAMILY: "Times New Roman"
}
SPAN.EmailStyle18 {
COLOR: navy; FONT-FAMILY: Arial
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=blue link=blue>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=398210811-29102007>Rich,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=398210811-29102007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=398210811-29102007>I figured since people ask me this question a lot, I
would blog about it. Hope my explanation below helps a
bit.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=398210811-29102007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=398210811-29102007><A
href="http://www.bostongis.com/blog/index.php?/archives/37-Explain-Analyze-Geometry-Relation-Operators-and-Joins-Except-Where.html">http://www.bostongis.com/blog/index.php?/archives/37-Explain-Analyze-Geometry-Relation-Operators-and-Joins-Except-Where.html</A></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=398210811-29102007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=398210811-29102007>Hope that helps,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=398210811-29102007>Regina</SPAN></FONT></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> Richard Heimann [C]
[mailto:heimann@ait.nrl.navy.mil] <BR><B>Sent:</B> Wednesday, October 24, 2007
7:01 PM<BR><B>To:</B> Obe, Regina; 'PostGIS Users Discussion'<BR><B>Subject:</B>
RE: [postgis-users] ST_Difference<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV class=Section1>
<P class=MsoNormal><B><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: 'Book Antiqua'">Again,
thank you both Paul and Regina…</SPAN></FONT></B></P>
<P class=MsoNormal><B><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: 'Book Antiqua'"></SPAN></FONT></B> </P>
<P class=MsoNormal><B><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: 'Book Antiqua'">Regina,
perhaps you can explain the LEFT JOIN. Nevertheless, it works with one
disclaimer. I am left with the following message, perhaps you can demystify it?
</SPAN></FONT></B></P>
<P class=MsoNormal><B><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: 'Book Antiqua'"></SPAN></FONT></B> </P>
<P class=MsoNormal><B><I><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-STYLE: italic; FONT-FAMILY: 'Book Antiqua'">“NOTICE:
LWGEOM_gist_joinsel called with incorrect join type</SPAN></FONT></I></B></P>
<P class=MsoNormal><B><I><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-STYLE: italic; FONT-FAMILY: 'Book Antiqua'"></SPAN></FONT></I></B> </P>
<P class=MsoNormal><B><I><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-STYLE: italic; FONT-FAMILY: 'Book Antiqua'">Query
returned successfully with no result in 812 ms.”</SPAN></FONT></I></B></P>
<P class=MsoNormal><B><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: 'Book Antiqua'"></SPAN></FONT></B> </P>
<P class=MsoNormal><B><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: 'Book Antiqua'">Paul, I
also received a message from your sql though that was abit more inauspicious. I
attempted to hack your sql though unsuccessfully. The first step is obvious; you
are selecting all points that are not completely contained within country
boundaries. And although the first query provides a list of all IDs it will not
tell if the rest have the correct ids assigned to them. So, maybe the second
query performs a data integrity check? Message below. Also, strangely, the
output was a polygon feature. </SPAN></FONT></B></P>
<P class=MsoNormal><B><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: 'Book Antiqua'"></SPAN></FONT></B> </P>
<P class=MsoNormal><B><I><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-STYLE: italic; FONT-FAMILY: 'Book Antiqua'">“row
number -1 is out of range 0..-1</SPAN></FONT></I></B></P>
<P class=MsoNormal><B><I><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-STYLE: italic; FONT-FAMILY: 'Book Antiqua'">Total
query runtime: 703 ms.</SPAN></FONT></I></B></P>
<P class=MsoNormal><B><I><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-STYLE: italic; FONT-FAMILY: 'Book Antiqua'">0
rows retrieved.” </SPAN></FONT></I></B></P>
<P class=MsoNormal><B><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: 'Book Antiqua'"></SPAN></FONT></B> </P>
<P class=MsoNormal><B><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: 'Book Antiqua'">Rich</SPAN></FONT></B></P>
<P class=MsoNormal><B><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: 'Book Antiqua'"></SPAN></FONT></B> </P>
<DIV>
<P class=MsoNormal><B><FONT face="Book Antiqua" size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: 'Book Antiqua'"></SPAN></FONT></B> </P>
<P class=MsoNormal><FONT face="Times New Roman" color=navy size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: navy"></SPAN></FONT> </P></DIV>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<DIV>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR tabIndex=-1 align=center width="100%" SIZE=3>
</SPAN></FONT></DIV>
<P class=MsoNormal><B><FONT face=Tahoma size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN></FONT></B><FONT
face=Tahoma size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> Obe,
Regina [mailto:robe.dnd@cityofboston.gov] <BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> Wednesday, October 24, 2007 5:23
PM<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> PostGIS Users
Discussion; Richard.Heimann@nrl.navy.mil<BR><B><SPAN
style="FONT-WEIGHT: bold">Cc:</SPAN></B> PostGIS Users Discussion<BR><B><SPAN
style="FONT-WEIGHT: bold">Subject:</SPAN></B> RE: [postgis-users]
ST_Difference</SPAN></FONT></P></DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV id=idOWAReplyText70829>
<DIV>
<P class=MsoNormal><FONT face=Arial color=black size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial">Oops even
easier</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SELECT pd.*</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">FROM pandatestdata p LEFT JOIN
</SPAN></FONT><FONT size=2><SPAN style="FONT-SIZE: 10pt">world2_12nm w On
st_contains(w.the_geom, p.the_geom)</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=2><SPAN
style="FONT-SIZE: 10pt">WHERE w.id IS NULL</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=2><SPAN
style="FONT-SIZE: 10pt">Here I am assuing w.id is the id of
world2_12nm</SPAN></FONT></P></DIV></DIV>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR tabIndex=-1 align=center width="100%" SIZE=3>
</SPAN></FONT></DIV>
<P class=MsoNormal style="MARGIN-BOTTOM: 12pt"><B><FONT face=Tahoma size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN></FONT></B><FONT
face=Tahoma size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">
postgis-users-bounces@postgis.refractions.net on behalf of Paul
Ramsey<BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B> Wed 10/24/2007
5:01 PM<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B>
Richard.Heimann@nrl.navy.mil<BR><B><SPAN
style="FONT-WEIGHT: bold">Cc:</SPAN></B> 'PostGIS Users Discussion'<BR><B><SPAN
style="FONT-WEIGHT: bold">Subject:</SPAN></B> Re: [postgis-users]
ST_Difference</SPAN></FONT></P></DIV>
<DIV>
<P><FONT face="Times New Roman" size=2><SPAN style="FONT-SIZE: 10pt">Oh, yeah,
oops, I know. It's the join logic, its finding the things <BR>not contained
for each world feature, not for the full world set.<BR><BR>select pd.* from
pandatestdata pd,<BR>(select p.id<BR>from pandatestdata p, world2_12nm
w<BR>where st_contains(w.the_geom, p.the_geom)) as wd where pd.id <>
wd.id;<BR><BR>Ugly, but probably effective: find all the things contained,
then <BR>just strip those out.<BR><BR>The trouble is that disjointness is
not an easily spatially indexable <BR>operation. If you're going to take
this into operation with large <BR>data volumes, a more effective data set
would be ocean polygons, cut <BR>up into smallish regular grid squares,
then you can test containment <BR>with relatively good index
selectivity.<BR><BR>P.<BR><BR>On 24-Oct-07, at 1:53 PM, Richard Heimann [C]
wrote:<BR><BR>> Thanks Paul for the response. I hadn’t thought of this logic,
it <BR>> didn’t work<BR>> however. Strangely, it increased the number
of features by eight <BR>> times. Any<BR>> thoughts?<BR>><BR>>
Vr<BR>> Rich<BR>><BR>><BR>><BR>> -----Original
Message-----<BR>> From: Paul Ramsey [<A
href="mailto:pramsey@refractions.net">mailto:pramsey@refractions.net</A>]<BR>>
Sent: Wednesday, October 24, 2007 4:07 PM<BR>> To:
Richard.Heimann@nrl.navy.mil; PostGIS Users Discussion<BR>> Subject: Re:
[postgis-users] ST_Difference<BR>><BR>> select p.*<BR>> from
pandatestdata p, world2_12nm w<BR>> where not st_contains(w.the_geom,
p.the_geom);<BR>><BR>> On 24-Oct-07, at 11:45 AM, Richard Heimann [C]
wrote:<BR>><BR>>> Thanks for your response W. I should have posted the
sql query<BR>>> earlier.<BR>>> Anyway...its
below.<BR>>><BR>>> My goal is to drop all features (points) that
fall within my<BR>>> polygon. In<BR>>> this case, I have ship track
data and want to filter it with world<BR>>> country<BR>>>
buffers.<BR>>><BR>>> World2_12nm - my world country 12nm buffered
polygon<BR>>> Pandatestdata - track data (point)<BR>>> Panda_diff2 -
new table<BR>>><BR>>><BR>>> CREATE TABLE panda_diff2
AS<BR>>> SELECT AsText(Difference(world2_12nm.the_geom,<BR>>>
pandatestdata.the_geom)) FROM<BR>>> world2_12nm,
pandatestdata<BR>>><BR>>> Also attached are the astext versions of
my data as well as a csv<BR>>> of the<BR>>> output from the above
sql. Please debunk the mystery...<BR>>><BR>>> Thanks
again<BR>>> Rich<BR>>><BR>>> -----Original
Message-----<BR>>> From: Webb Sprague [<A
href="mailto:webb.sprague@gmail.com">mailto:webb.sprague@gmail.com</A>]<BR>>>
Sent: Wednesday, October 24, 2007 12:00 PM<BR>>> To:
Richard.Heimann@nrl.navy.mil; PostGIS Users Discussion<BR>>> Subject: Re:
[postgis-users] ST_Difference<BR>>><BR>>> You might get more useful
help if you post an "astext()" version of<BR>>> your data, the query, the
result, and a *desired* result.<BR>>><BR>>> I know the
difference functions can be tricky and are sometiimes<BR>>> defined in
ways you might not expect.<BR>>><BR>>> Thx<BR>>>
W<BR>>><BR>>> On 10/24/07, Richard Heimann [C]
<heimann@ait.nrl.navy.mil>
wrote:<BR>>>><BR>>>><BR>>>><BR>>>><BR>>>>
All,<BR>>>><BR>>>><BR>>>><BR>>>> I would
appreciate help in constructing an SQL statement that<BR>>>> performs
a<BR>>>> difference function on two datasets. My intersect function
works but<BR>>> cannot<BR>>>> work out the kinks with
st_difference. Thanks for your help.Im <BR>>>> new
to<BR>>>>
Postgis.<BR>>>><BR>>>><BR>>>><BR>>>>
Rich<BR>>>><BR>>>><BR>>>><BR>>>><BR>>>>
_______________________________________________<BR>>>> postgis-users
mailing list<BR>>>>
postgis-users@postgis.refractions.net<BR>>>> <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>>><BR>>>><BR>>><BR>>><BR>>><BR>>>
_______________________________________________<BR>>> postgis-users
mailing list<BR>>> postgis-users@postgis.refractions.net<BR>>> <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>>
<panda_diff2.csv><BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR>postgis-users@postgis.refractions.net<BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></SPAN></FONT></P></DIV></DIV>
<P>
<HR SIZE=1>
<P></P>
<P><STRONG>The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure pursuant to
Massachusetts law. It is intended solely for the addressee. If you received this
in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>