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

[Josh Livni]

Kevin,

Thanks for the followup - I guess I knew the ST_ functions wrapped the 
spatial queries and wasn't thinking straight - however I never did the 
actual tests to see that I've probably been slowing myself down where 
keep my old habits and include the && in there.  Interesting.  Thanks!

  -Josh

Kevin Neufeld wrote:
> Correct.  This JOIN query is the same thing that I posted yesterday, 
> but using a WHERE clause instead.
>
> Josh,
> A quick note on your comment about gist indexes.  I totally agree with 
> you that you need the && operator, but now in postgis, most of the 
> st_* methods are simple wrappers of previous methods and include the 
> && operator.
> i.e.
> test=# \df+ st_within
>                                                            List of 
> functions
> Schema |   Name    | Result data type | Argument data types |  Owner   
> | Language |              Source code              | Description
> --------+-----------+------------------+---------------------+----------+----------+---------------------------------------+------------- 
>
> public | st_within | boolean          | geometry, geometry  | postgres 
> | sql      | SELECT $1 && $2 AND _ST_Within($1,$2) |
> (1 row)
>
> In fact, I've noticed that sometimes adding an extra && operator can 
> make a query take longer.  Here is an example two tiny tables I have:
>
> test=# explain analyze
> select a.* from neighbourhood_poly a, cityarea_poly b
> where st_within(a.the_Geom, b.the_Geom);
>                                                        QUERY 
> PLAN                                                       
> -------------------------------------------------------------------------------------------------------------------------- 
>
> Nested Loop  (cost=1.03..4.24 rows=19 width=30271) (actual 
> time=2.391..121.782 rows=15 loops=1)
>   Join Filter: ((a.the_geom && b.the_geom) AND _st_within(a.the_geom, 
> b.the_geom))
>   ->  Seq Scan on neighbourhood_poly a  (cost=0.00..1.26 rows=26 
> width=30271) (actual time=0.049..0.233 rows=26 loops=1)
>   ->  Materialize  (cost=1.03..1.06 rows=3 width=15506) (actual 
> time=0.006..0.192 rows=3 loops=26)
>         ->  Seq Scan on cityarea_poly b  (cost=0.00..1.03 rows=3 
> width=15506) (actual time=0.015..0.028 rows=3 loops=1)
> Total runtime: 122.979 ms
> (6 rows)
>
> test=# explain analyze
> select a.* from neighbourhood_poly a, cityarea_poly b
> where st_within(a.the_Geom, b.the_Geom)
> and b.the_geom && a.the_geom;
>                                                        QUERY 
> PLAN                                                       
> -------------------------------------------------------------------------------------------------------------------------- 
>
> Nested Loop  (cost=1.03..4.44 rows=14 width=30271) (actual 
> time=4.176..190.813 rows=15 loops=1)
>   Join Filter: ((a.the_geom && b.the_geom) AND _st_within(a.the_geom, 
> b.the_geom) AND (b.the_geom && a.the_geom))
>   ->  Seq Scan on neighbourhood_poly a  (cost=0.00..1.26 rows=26 
> width=30271) (actual time=0.048..0.658 rows=26 loops=1)
>   ->  Materialize  (cost=1.03..1.06 rows=3 width=15506) (actual 
> time=0.012..0.110 rows=3 loops=26)
>         ->  Seq Scan on cityarea_poly b  (cost=0.00..1.03 rows=3 
> width=15506) (actual time=0.125..0.140 rows=3 loops=1)
> Total runtime: 191.687 ms
> (6 rows)
>
> This looks like it's due to the fact that the && operator is not 
> truely symmetric and the query planner does not disregard the extra 
> index lookup.
>
> Cheers,
> Kevin
>
> Obe, Regina wrote:
>> 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.
>>
>> _______________________________________________
>> 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