[postgis-users] ST_Difference

Wed Oct 24 16:47:54 PDT 2007

Oh, ignore the gist_join_sel warning, it's just a warning, the join  
selectivity is calculated for standard inner joins, so it warns you  
when you use some other join type.  It just means that maybe you'll  
get a bad selectivity estimate and perhaps a bad plan, but never a  
bad answer, just potentially a slow one, in the worst case scenario.

P

On 24-Oct-07, at 4:22 PM, Paul Ramsey wrote:

> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users