[postgis-users] ST_Difference

Mon Oct 29 04:13:01 PDT 2007

Rich,

I figured since people ask me this question a lot, I would blog about
it.  Hope my explanation below helps a bit.

http://www.bostongis.com/blog/index.php?/archives/37-Explain-Analyze-Geo
metry-Relation-Operators-and-Joins-Except-Where.html

Hope that helps,
Regina

________________________________

From: Richard Heimann [C] [mailto:heimann at ait.nrl.navy.mil] 
Sent: Wednesday, October 24, 2007 7:01 PM
To: Obe, Regina; 'PostGIS Users Discussion'
Subject: RE: [postgis-users] ST_Difference

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. 

-----------------------------------------
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071029/043adda1/attachment.html>