[postgis-users] newbie query question

[G. van Es]

Paul, Mike, Nick,

Thanks for your reply. This has helped me to build a successful query.

Ge 

--- On Wed, 6/9/10, Nick Bower <nick at petangent.net> wrote:

> From: Nick Bower <nick at petangent.net>
> Subject: Re: [postgis-users] newbie query question
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> 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 <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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>