[postgis-users] intersection/within queries - newbie basic spatial queries

[Craigie Dirk]

Thanks,

1st case worked perfectly - thanks.

2nd case (more what I am after) gave a syntax error at or near "where" 
Any ideas on the error ?
---------------------------
select sec_catch.secondary 
from sec_catch join prim_catch
where st_within(sec_catch.the_geom, prim_catch.the_geom)
---------------------------

Regards,

Dirk Craigie

-----Original Message-----
From: dylan [mailto:debeaudette at ucdavis.edu] 
Sent: 07 December 2007 08:27 AM
To: postgis-users at postgis.refractions.net
Cc: Craigie Dirk
Subject: Re: [postgis-users] intersection/within queries - newbie basic
spatial queries

On Thursday 06 December 2007 10:02:57 pm Craigie Dirk wrote:
> I am a new user and trying to perform some basic spatial queries using
> within/intersect/union etc.
>
>
>
> I have 2 x polygon files :
>
>
>
> Prim_catch (19 x records)
>
> Sec_catch (attribute secondary = text field name of catchment)
>
>
>
> All the polys in sec_catch fall within a single record (polygon) of
> prim_catch
>
> The query I am using (see below) needs some kind of loop or temporary
> table as it returns a true/false and not a list of the records = true.
>
> ------------
>
> SELECT secondary FROM sec_catch
>
> WHERE WITHIN((select the_geom from sec_catch),(select the_geom from
> prim_catch))
>
> -----------
>
>

Using a join can save typing:

select st_intersection(a.geom, b.geom) from a join b on
st_intersects(a.geom, 
b.geom) ;

if you want the cartesian product (all possible permutations leave off
the ON 
clause).

or if you just want matching records:

select a.id from a join b where st_within(a.geom, b,geom);

does that help ?

Dylan









DISCLAIMER:
This message and any attachments are confidential and intended solely for the addressee. If you have received this message in error, please notify the system manager/sender.  Any unauthorized use, alteration or dissemination is prohibited.  The Department of Water Affairs and Forestry further accepts no liability whatsoever for any loss, whether it be direct, indirect or consequential, arising from this e-mail, nor for any consequence of its use or storage.