[postgis-users] Precision of the bounding box query

[Alexander Ulanov]

Thanks, Mike. I've changed my second query and it returns what I expect:

select name from places where location::geometry && ST_setSRID(ST_MakeBox2d
(ST_Point(57,35),
ST_Point(-28,69)),4326)::geometry and name like 'Sagrada%';

      name
-----------------
 Sagrada Família

Does this mean that all bounding box queries have to be written with
casting to geometry type? Bounding box originates from the browser with
google maps.

Related question. Do I really need geography type for my locations if my
queries are only bounding box and st_dwithin ?


2013/9/2 Mike Toews <mwtoews at gmail.com>

> This behaviour can be explained if your places.location column was a
> geography type, where they don't intersect on a spheroid:
>
> postgis=# SELECT ST_MakeBox2d(ST_Point(57, 35), ST_Point(-28,
> 69))::geometry && 'POINT (2 41)'::geography;
>  ?column?
> ----------
>  f
> (1 row)
>
> If you want a Cartesian plane intersection, cast places.location to a
> geometry
>
> postgis=# SELECT ST_MakeBox2d(ST_Point(57, 35), ST_Point(-28,
> 69))::geometry && 'POINT (2 41)'::geography::geometry;
>  ?column?
> ----------
>  t
> (1 row)
>
> -Mike
>
> On 2 September 2013 08:21, Alexander Ulanov <avulanov at gmail.com> wrote:
> > Hi!
> >
> > I have a spatial db of cities and places inside Postgis v1.5. I've built
> a
> > gist index on location column and run a bounding box query. However, I
> > noticed a strange behavior: results of queries with huge bounding boxes
> are
> > very imprecise.
> >
> > For example, the first query returns Sagrada Familia (longitude ~2 and
> > latitude ~41) and second doesn't, although 41 is inside both 34..68 and
> > 35..69.
> > 1)select name from places where location && ST_setSRID(ST_MakeBox2d
> > (ST_Point(57,34), ST_Point(-28,68)),4326) and name like 'Sagrada%';
> >
> >       name       |                st_astext
> > -----------------+------------------------------------------
> >  Sagrada Família | POINT(2.17434883117676 41.4035152693628)
> >
> > 2)select name from places where location && ST_setSRID(ST_MakeBox2d
> > (ST_Point(57,35), ST_Point(-28 69)),4326) and name like 'Sagrada%';
> >
> > name | st_astext
> > ------+-----------
> > (0 rows)
> >
> > Could you explain what is the reason for this and how to fix it?
> >
> >
> > Best regards, Alexander
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at lists.osgeo.org
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130902/6e263ca1/attachment.html>