[postgis-users] newbie query question

[Nick Bower]

For ensuring there are no cloud occurences in a given roi I used  
except at first then realised "having" combined with count/groupby was  
the key.

Having 0 occurence or in our case having maximum pixels clear - same  
either way.

These queries get thorny - sqlalchemy orm made things alot easier for  
us becauses chances are that your having subquery needs to be joined  
to something for additional criteria.


On 10/06/2010, at 5:02 AM, Mike Toews <mwtoews at gmail.com> wrote:

> You could also use the EXCEPT set operator:
>
> SELECT * FROM tbl_nla
> EXCEPT
> SELECT * FROM tbl_nla
> JOIN tbl_rvb ON ST_DWithin(tbl_nla.the_geom, tbl_rvb.the_geom, 100);
>
> -Mike
>
>
> On 9 June 2010 11:19, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
>> That's actually a surprisingly tricky question (to solve  
>> efficiently).
>> The approach I have usually used is the counterintuitive one: do a
>> left join on the positive constraint (*is* within 100 meters) and the
>> return the rows that did *not* match the join (and therefore have  
>> null
>> unique id values in the resultant).
>>
>> SELECT tbl_nla.gid FROM
>> tbl_nla LEFT JOIN tbl_rvb
>> ON ST_DWithin(tbl_nla.the_geom, tbl_rvb.the_geom, 100)
>> WHERE tbl_rvb.gid IS NULL;
>>
>> P.
>>
>> On Wed, Jun 9, 2010 at 2:06 PM, G. van Es <gves2000 at yahoo.com> wrote:
>>> I have two tables. tbl_nla has points as geometry and tbl_rvb has  
>>> multipolygons.
>>>
>>> We want to list all the points of tbl_nla with no objects of  
>>> tbl_rvb within 100 metres.
>>>
>>> Can anyone point me in the right direction?
>>>
>>> Thanks,
>>> Ge
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> 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
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users