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

[Josh Livni]

Ah - that's probably what he meant - in which case I guessed wrong both 
times :)

Since you mentioned the GiST index (and it may well be of use -- perhaps 
he's got millions of sec_catch polygons), I just figured I'd point out 
to Craigie that if indeed he does have such an index, to take advantage 
of it he'd need to add the spatial && operator to the where clause, eg:
  AND b.the_geom && a.the_geom

  -Josh


Kevin Neufeld wrote:
> You're after a list of tuples from sec_catch where the geometry falls 
> within some polygon from prim_catch?
>
> Try to rework your query to something like this.
>
> SELECT b.secondary
> FROM prim_catch a, sec_catch b
> WHERE ST_Within(b.the_geom, a.the_geom);
>
> With only 19 geometries in your prim_catch table it's not a big deal, 
> but in general, you want to ensure you have a GIST index on both of 
> your geometry columns.
>
> Cheers,
> Kevin
>
> 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))
>>
>> -----------
>>
>>  
>>
>> Any help, sample will be appreciated
>>
>>  
>>
>> Thanks,
>>
>>  
>>
>>  
>>
>> Dirk Craigie
>>
>>  
>>
>> 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
>>   
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>