[postgis-users] ST_Difference

[Paul Ramsey]

OK, ugly query in a different way:

select pd.* from pandatestdata pd,
where pd.id not in (select p.id
from pandatestdata p, world2_12nm w
where st_contains(w.the_geom, p.the_geom));

I like Regina's best though, elegance personified.

P

On 24-Oct-07, at 4:01 PM, Richard Heimann [C] wrote:

> Again, thank you both Paul and Regina…
>
>
>
> 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?
>
>
>
> “NOTICE:  LWGEOM_gist_joinsel called with incorrect join type
>
>
>
> Query returned successfully with no result in 812 ms.”
>
>
>
> 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.
>
>
>
> “row number -1 is out of range 0..-1
>
> Total query runtime: 703 ms.
>
> 0 rows retrieved.”
>
>
>
> Rich
>
>
>
>
>
>
>
> From: Obe, Regina [mailto:robe.dnd at cityofboston.gov]
> Sent: Wednesday, October 24, 2007 5:23 PM
> To: PostGIS Users Discussion; Richard.Heimann at nrl.navy.mil
> Cc: PostGIS Users Discussion
> Subject: RE: [postgis-users] ST_Difference
>
>
> Oops even easier
>
>
> SELECT pd.*
>
> FROM pandatestdata p LEFT JOIN world2_12nm w On  st_contains 
> (w.the_geom, p.the_geom)
>
> WHERE w.id IS NULL
>
>
> Here I am assuing w.id is the id of world2_12nm
>
>
> From: postgis-users-bounces at postgis.refractions.net on behalf of  
> Paul Ramsey
> Sent: Wed 10/24/2007 5:01 PM
> To: Richard.Heimann at nrl.navy.mil
> Cc: 'PostGIS Users Discussion'
> Subject: Re: [postgis-users] ST_Difference
>
> Oh, yeah, oops, I know. It's the join logic, its finding the things
> not contained for each world feature, not for the full world set.
>
> select pd.* from pandatestdata pd,
> (select p.id
> from pandatestdata p, world2_12nm w
> where st_contains(w.the_geom, p.the_geom)) as wd where pd.id <> wd.id;
>
> Ugly, but probably effective: find all the things contained, then
> just strip those out.
>
> The trouble is that disjointness is not an easily spatially indexable
> operation. If you're going to take this into operation with large
> data volumes, a more effective data set would be ocean polygons, cut
> up into smallish regular grid squares, then you can test containment
> with relatively good index selectivity.
>
> P.
>
> On 24-Oct-07, at 1:53 PM, Richard Heimann [C] wrote:
>
> > Thanks Paul for the response. I hadn’t thought of this logic, it
> > didn’t work
> > however. Strangely, it increased the number of features by eight
> > times. Any
> > thoughts?
> >
> > Vr
> > Rich
> >
> >
> >
> > -----Original Message-----
> > From: Paul Ramsey [mailto:pramsey at refractions.net]
> > Sent: Wednesday, October 24, 2007 4:07 PM
> > To: Richard.Heimann at nrl.navy.mil; PostGIS Users Discussion
> > Subject: Re: [postgis-users] ST_Difference
> >
> > select p.*
> > from pandatestdata p, world2_12nm w
> > where not st_contains(w.the_geom, p.the_geom);
> >
> > On 24-Oct-07, at 11:45 AM, Richard Heimann [C] wrote:
> >
> >> Thanks for your response W. I should have posted the sql query
> >> earlier.
> >> Anyway...its below.
> >>
> >> My goal is to drop all features (points) that fall within my
> >> polygon. In
> >> this case, I have ship track data and want to filter it with world
> >> country
> >> buffers.
> >>
> >> World2_12nm - my world country 12nm buffered polygon
> >> Pandatestdata - track data (point)
> >> Panda_diff2 - new table
> >>
> >>
> >> CREATE TABLE panda_diff2 AS
> >> SELECT AsText(Difference(world2_12nm.the_geom,
> >> pandatestdata.the_geom)) FROM
> >> world2_12nm, pandatestdata
> >>
> >> Also attached are the astext versions of my data as well as a csv
> >> of the
> >> output from the above sql. Please debunk the mystery...
> >>
> >> Thanks again
> >> Rich
> >>
> >> -----Original Message-----
> >> From: Webb Sprague [mailto:webb.sprague at gmail.com]
> >> Sent: Wednesday, October 24, 2007 12:00 PM
> >> To: Richard.Heimann at nrl.navy.mil; PostGIS Users Discussion
> >> Subject: Re: [postgis-users] ST_Difference
> >>
> >> You might get more useful help if you post an "astext()" version of
> >> your data, the query, the result, and  a *desired* result.
> >>
> >> I know the difference functions can be tricky and are sometiimes
> >> defined in ways you might not expect.
> >>
> >> Thx
> >> W
> >>
> >> On 10/24/07, Richard Heimann [C] <heimann at ait.nrl.navy.mil> wrote:
> >>>
> >>>
> >>>
> >>>
> >>> All,
> >>>
> >>>
> >>>
> >>> I would appreciate help in constructing an SQL statement that
> >>> performs a
> >>> difference function on two datasets. My intersect function  
> works but
> >> cannot
> >>> work out the kinks with st_difference. Thanks for your help.Im
> >>> new to
> >>> Postgis.
> >>>
> >>>
> >>>
> >>> Rich
> >>>
> >>>
> >>>
> >>>
> >>> _______________________________________________
> >>> postgis-users mailing list
> >>> postgis-users at postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>>
> >>>
> >>
> >>
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > <panda_diff2.csv>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> 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.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users