<HTML dir=ltr><HEAD><TITLE>Re: [postgis-users] ST_Difference</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.6000.16544" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText96398 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2></FONT> </DIV></DIV>
<DIV dir=ltr>That doesn't quite look right. What about</DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr><FONT size=2>select pd.* from pandatestdata pd LEFT JOIN<BR>(select p.id<BR>from pandatestdata p, world2_12nm w<BR>where st_contains(w.the_geom, p.the_geom)) as wd ON pd.id = wd.id</FONT></DIV>
<DIV dir=ltr><FONT size=2>WHERE wd.id IS NULL</FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>Should give you all the pandatestdata points that is not in any of your world polygons. Also I think using st_within(w.th</FONT></DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>Hope that helps,</DIV>
<DIV dir=ltr>Regina<BR><BR></DIV>
<DIV dir=ltr>
<HR tabIndex=-1>
</DIV>
<DIV dir=ltr><FONT face=Tahoma size=2><B>From:</B> postgis-users-bounces@postgis.refractions.net on behalf of Paul Ramsey<BR><B>Sent:</B> Wed 10/24/2007 5:01 PM<BR><B>To:</B> Richard.Heimann@nrl.navy.mil<BR><B>Cc:</B> 'PostGIS Users Discussion'<BR><B>Subject:</B> Re: [postgis-users] ST_Difference<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>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><BR></FONT></P></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></HTML>