Обсуждение: Detecting whether a point is in a box.

Hello

I am trying to write a query to find all points that fall within a
given box. However, I cannot seem to find the functionality for
determining whether a point is within a box.

e.g.  select box '((0,0),(1,1))' @> point '(0.5,0.5)';

operator does not exist: box @> point

Is this operator for circles only? Is there a similar operator for
boxes? I have not been able to find it in the documentation.

Any help would be much appreciated
H

HST <helenst@gmail.com> writes:
> I am trying to write a query to find all points that fall within a
> given box. However, I cannot seem to find the functionality for
> determining whether a point is within a box.

> e.g.  select box '((0,0),(1,1))' @> point '(0.5,0.5)';

> operator does not exist: box @> point

psql's \do shows that we have box @> box and polygon @> point,
but no box @> point.  So one possibility is to change your
box data to polygons.  Another is to manufacture a zero-area
box from the point, using the box(point,point) function, and
use @> with that.  Also, there is a point <@ box, although
I'm not sure you want to use that --- the lack of a commutator
suggests that it probably hasn't got any index support.

Is this just for a one-shot query, or are you hoping to do
indexed searches in a big table?  If the latter, which object
is being stored in the table?  You want to be sure you can
create an index that will support the operator you use.

            regards, tom lane

Helen <helenst@gmail.com> writes:
> It's the point that's stored in the table - the query is to find all the points within a given box. I'm not sure what
thatmeans for indexing though, would that mean the point <@ box operator could be used with an index? if not, the
polygonsolution will probably do. 

No, a quick look shows that we don't have *any* opclass for points:

regression=# select * from pg_opclass where opcintype = 'point'::regtype;
 opcamid | opcname | opcnamespace | opcowner | opcintype | opcdefault | opckeytype
---------+---------+--------------+----------+-----------+------------+------------
(0 rows)

So if you want to make this indexable, you probably have to build a gist
index on box(pointcol,pointcol), and be careful to express all your
queries using that locution :-(.

You might want to look into PostGIS, which has much more extensive
geometric support than what you can find in core Postgres.

Or, if you're feeling really enterprising, you could build a gist
operator class for points.  (Please submit it if you do.)

            regards, tom lane

Dear Madam,

I had the same issue tonight.

I sorted it out, perhaps it may help you :)

Convert BOX to a polygon & insert some values as per the syntax.

Than run a query

SELECT polygon '((-3,10),(8,18),(-3,30),(-10,20))' @> point '(-8,25)';

let me know if this doesn't work for you ;)

Cheers!
Rao

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Detecting-whether-a-point-is-in-a-box-tp1887472p3422647.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.