[postgis-users] intersection/within queries - newbie basicspatial queries

[Obe, Regina]

You are missing an ON. You can fix by moving your where into the ON

SELECT sec_catch.secondary 
FROM sec_catch INNER JOIN prim_catch ON st_within(sec_catch.the_geom,
prim_catch.the_geom)

I tend to spell out the type of JOIN too because I can never remember
what is the default join behavior in PostgreSQL.

Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Craigie Dirk
Sent: Friday, December 07, 2007 1:42 AM
To: postgis-users at postgis.refractions.net
Cc: dylan
Subject: RE: [postgis-users] intersection/within queries - newbie
basicspatial queries

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.
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.