[postgis-users] && BBOX Question

[Mark Cave-Ayland]

On Tue, 2007-04-10 at 23:33 -0700, Dylan Lorimer wrote:
> Hi,
> 
> I'm curious if anyone can quickly see what I may be doing wrong here.
> I'm trying to restrict a query such that the geometry falls within a
> specified BBOX. Basic PostGIS stuff....right?
> 
> However, for the following query, I'm not expecting to get any
> results, as the bbox specified has no points lying within it. And yet
> I get the result below. 
> 
> BOX3D is specified as (xmin ymin, xmax ymax) right?
> 
> So here's the query:
> 
>  SELECT AsText(the_geom) AS the_geom, FACILITY_N, OBJECTID,
> FACILITY_I, DEPTNAME FROM poi_2 WHERE lower(FACILITY_N) LIKE '%lafa%'
> OR lower(OBJECTID) LIKE '%lafa%' OR lower(FACILITY_I) LIKE '%lafa%' OR
> lower(DEPTNAME) LIKE '%lafa%' AND the_geom && SetSRID('BOX3D(- 121.00
> 38.000, -119.00 40.00)'::box3d,4326);
>         the_geom         |        facility_n        | objectid |
> facility_i |              deptname              
> -------------------------+--------------------------+----------+------------+------------------------------------ 
>  POINT(-122.497 37.7771) | LAFAYETTE SCHOOL (ELEM.) | 1969     |
> 0000003158 | SF Unified School District (SFUSD)
> 
> Here's the table description:
> 
> \d poi_2;
>            Table "public.poi_2"
>    Column   |       Type        | Modifiers 
> ------------+-------------------+-----------
>  facility_n | character varying | 
>  objectid   | character varying | 
>  facility_i | character varying | 
>  deptname   | character varying | 
>  the_geom   | geometry          | 
> Indexes:
>     "deptname_gepoi_2_idx" btree (lower(deptname::text))
>     "facility_i_gepoi_2_idx" btree (lower(facility_i::text))
>     "facility_n_gepoi_2_idx" btree (lower(facility_n::text)) 
>     "objectid_gepoi_2_idx" btree (lower(objectid::text))
>     "the_geom_gepoi_2_idx" gist (the_geom)
> Check constraints:
>     "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
>     "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
> 'POINT'::text OR the_geom IS NULL)
>     "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
> 
> Any ideas?
> Cheers,
> Dylan 


Hi Dylan,

I think that what you're seeing here is a result of operator precedence
- see the table in the PostgreSQL documentation here:
http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html#SQL-PRECEDENCE.

AFAICT your query is actually being parsed like this (brackets added for
emphasis):


SELECT AsText(the_geom) AS the_geom, FACILITY_N, OBJECTID, FACILITY_I,
DEPTNAME FROM poi_2
WHERE lower(FACILITY_N) LIKE '%lafa%'
OR lower(OBJECTID) LIKE '%lafa%' 
OR lower(FACILITY_I) LIKE '%lafa%' 
OR 
  (lower(DEPTNAME) LIKE '%lafa%' AND the_geom && SetSRID('BOX3D(-121.00
38.000, -119.00 40.00)'::box3d,4326));


Hence your single row is being returned as a match on "lower(FACILITY_N)
LIKE '%lafa%'". I think that what you are probably looking for is this
(again note the position of the extra brackets):


SELECT AsText(the_geom) AS the_geom, FACILITY_N, OBJECTID, FACILITY_I,
DEPTNAME FROM poi_2 
WHERE 
  (lower(FACILITY_N) LIKE '%lafa%' OR lower(OBJECTID) LIKE '%lafa%' OR 
  lower(FACILITY_I) LIKE '%lafa%' OR lower(DEPTNAME) LIKE '%lafa%')
AND 
  the_geom && SetSRID('BOX3D(- 121.00 38.000, -119.00
40.00)'::box3d,4326);


This should cause your row to match only if *any* of the fields contain
a reference to "lafa" AND the geometry lies within the specified
bounding box.


Kind regards,

Mark.