[postgis-users] && BBOX Question

[Dylan Lorimer]

Many Thanks Mark !

Thought I had included parens grouping my where clause. Clearly I hadn't.
Works brilliantly now.

Cheers,
dylan

On 4/11/07, Mark Cave-Ayland <mark.cave-ayland at ilande.co.uk> wrote:
>
> 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.
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



-- 
E. Dylan Lorimer | Google Earth Enterprise
650.253.2459 (O) | 415.690.3347 (M) | 650.644.0182 (F)

This email and the information it contains are confidential and may be
privileged. If you have received this email in error please notify me
immediately and do not copy it for any purpose, or disclose its contents to
any other person.  Internet communications are not secure and, therefore,
Google does not accept legal responsibility for the contents of this message
as it has been transmitted over a public network. If you suspect the message
may have been intercepted or amended please call me.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070411/07c1d31c/attachment.html>