[postgis-users] newbie query question

[Mike Toews]

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
>