Paul, Mike, Nick, Thanks for your reply. This has helped me to build a successful query.
Ge --- On Wed, 6/9/10, Nick Bower <[email protected]> wrote: > From: Nick Bower <[email protected]> > Subject: Re: [postgis-users] newbie query question > To: "PostGIS Users Discussion" <[email protected]> > Date: Wednesday, June 9, 2010, 4:27 PM > 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 <[email protected]> > 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 <[email protected]> > 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 <[email protected]> > 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 > >>> [email protected] > >>> http://postgis.refractions.net/mailman/listinfo/postgis-users > >>> > >> _______________________________________________ > >> postgis-users mailing list > >> [email protected] > >> http://postgis.refractions.net/mailman/listinfo/postgis-users > >> > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
