<html>
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered)">
<title>Re: [postgis-users] ST_Difference</title>
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Book Antiqua";
panose-1:2 4 6 2 5 3 5 3 3 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0pt;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:blue;
text-decoration:underline;}
p
{margin-right:0pt;
margin-left:0pt;
font-size:12.0pt;
font-family:"Times New Roman";}
span.EmailStyle18
{font-family:Arial;
color:navy;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=blue>
<div class=Section1>
<p class=MsoNormal><b><font size=2 face="Book Antiqua"><span style='font-size:
10.0pt;font-family:"Book Antiqua";font-weight:bold'>Again, thank you both Paul
and Regina…</span></font></b></p>
<p class=MsoNormal><b><font size=2 face="Book Antiqua"><span style='font-size:
10.0pt;font-family:"Book Antiqua";font-weight:bold'> </span></font></b></p>
<p class=MsoNormal><b><font size=2 face="Book Antiqua"><span style='font-size:
10.0pt;font-family:"Book Antiqua";font-weight:bold'>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 size=2 face="Book Antiqua"><span style='font-size:
10.0pt;font-family:"Book Antiqua";font-weight:bold'> </span></font></b></p>
<p class=MsoNormal style='text-autospace:none'><b><i><font size=2
face="Book Antiqua"><span style='font-size:10.0pt;font-family:"Book Antiqua";
font-weight:bold;font-style:italic'>“NOTICE: LWGEOM_gist_joinsel
called with incorrect join type</span></font></i></b></p>
<p class=MsoNormal style='text-autospace:none'><b><i><font size=2
face="Book Antiqua"><span style='font-size:10.0pt;font-family:"Book Antiqua";
font-weight:bold;font-style:italic'> </span></font></i></b></p>
<p class=MsoNormal><b><i><font size=2 face="Book Antiqua"><span
style='font-size:10.0pt;font-family:"Book Antiqua";font-weight:bold;font-style:
italic'>Query returned successfully with no result in 812 ms.”</span></font></i></b></p>
<p class=MsoNormal><b><font size=2 face="Book Antiqua"><span style='font-size:
10.0pt;font-family:"Book Antiqua";font-weight:bold'> </span></font></b></p>
<p class=MsoNormal><b><font size=2 face="Book Antiqua"><span style='font-size:
10.0pt;font-family:"Book Antiqua";font-weight:bold'>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 size=2 face="Book Antiqua"><span style='font-size:
10.0pt;font-family:"Book Antiqua";font-weight:bold'> </span></font></b></p>
<p class=MsoNormal style='text-autospace:none'><b><i><font size=2
face="Book Antiqua"><span style='font-size:10.0pt;font-family:"Book Antiqua";
font-weight:bold;font-style:italic'>“row number -1 is out of range 0..-1</span></font></i></b></p>
<p class=MsoNormal style='text-autospace:none'><b><i><font size=2
face="Book Antiqua"><span style='font-size:10.0pt;font-family:"Book Antiqua";
font-weight:bold;font-style:italic'>Total query runtime: 703 ms.</span></font></i></b></p>
<p class=MsoNormal><b><i><font size=2 face="Book Antiqua"><span
style='font-size:10.0pt;font-family:"Book Antiqua";font-weight:bold;font-style:
italic'>0 rows retrieved.” </span></font></i></b></p>
<p class=MsoNormal><b><font size=2 face="Book Antiqua"><span style='font-size:
10.0pt;font-family:"Book Antiqua";font-weight:bold'> </span></font></b></p>
<p class=MsoNormal><b><font size=2 face="Book Antiqua"><span style='font-size:
10.0pt;font-family:"Book Antiqua";font-weight:bold'>Rich</span></font></b></p>
<p class=MsoNormal><b><font size=2 face="Book Antiqua"><span style='font-size:
10.0pt;font-family:"Book Antiqua";font-weight:bold'> </span></font></b></p>
<div>
<p class=MsoNormal><b><font size=2 face="Book Antiqua"><span style='font-size:
10.0pt;font-family:"Book Antiqua";font-weight:bold'> </span></font></b></p>
<p class=MsoNormal><font size=3 color=navy face="Times New Roman"><span
style='font-size:12.0pt;color:navy'> </span></font></p>
</div>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=3 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>From:</span></font></b><font size=2
face=Tahoma><span style='font-size:10.0pt;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 size=3 face="Times New Roman"><span style='font-size:
12.0pt'> </span></font></p>
<div id=idOWAReplyText70829>
<div>
<p class=MsoNormal><font size=2 color=black face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:black'>Oops even easier</span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>SELECT pd.*</span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>FROM pandatestdata p LEFT JOIN </span></font><font size=2><span
style='font-size:10.0pt'>world2_12nm w On st_contains(w.the_geom,
p.the_geom)</span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 face="Times New Roman"><span style='font-size:
10.0pt'>WHERE w.id IS NULL</span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 face="Times New Roman"><span style='font-size:
10.0pt'>Here I am assuing w.id is the id of world2_12nm</span></font></p>
</div>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> </span></font></p>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=3 width="100%" align=center tabIndex=-1>
</span></font></div>
<p class=MsoNormal style='margin-bottom:12.0pt'><b><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma;font-weight:bold'>From:</span></font></b><font
size=2 face=Tahoma><span style='font-size:10.0pt;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 size=2 face="Times New Roman"><span style='font-size:10.0pt'>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>
</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>