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

[Kevin Neufeld]

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
>