Обсуждение: How do I create a box from fields in a table?

I need to determine whether a given pair of coordinates is inside a given rectangle.  According to the documentation,
PostgreSQLprovides the box and point types and a "contains" operator that will be perfect for this.  However, the
exampleprovided in the documentation only shows the creation of boxes and points from constant values.  So, the
followingquery works: 

select tran_car_identification, box '((0, 1), (2, 3))' from transfer_car

But if I try to use a field from the transfer_car table, it doesn't work:

select tran_car_identification, box '((location, 1), (2, 3))' from transfer_car

That gives me an "invalid input syntax for type box" error.

How do I create a box object using data from the transfer_car table?

Thanks very much!

RobR

On Thu, Oct 17, 2013 at 4:04 PM, Rob Richardson
<RDRichardson@rad-con.com> wrote:
> I need to determine whether a given pair of coordinates is inside a given rectangle.  According to the documentation,
PostgreSQLprovides the box and point types and a "contains" operator that will be perfect for this.  However, the
exampleprovided in the documentation only shows the creation of boxes and points from constant values.  So, the
followingquery works: 
>
> select tran_car_identification, box '((0, 1), (2, 3))' from transfer_car
>
> But if I try to use a field from the transfer_car table, it doesn't work:
>
> select tran_car_identification, box '((location, 1), (2, 3))' from transfer_car
>
> That gives me an "invalid input syntax for type box" error.
>
> How do I create a box object using data from the transfer_car table?

you have to construct the string.  this is somewhat baroque by modern
postgres standards but should work:

select tran_car_identification, format('((%s, 1), (2, 3))',
location)::box from transfer_car;

merlin

Thank you for your reply.

In my opinion, that is ugly to the point of uselessness.  I think I would rather just use simple integer arithmetic.
Itwill be easier for others to understand. 

RobR

-----Original Message-----
From: Merlin Moncure [mailto:mmoncure@gmail.com]
Sent: Thursday, October 17, 2013 5:58 PM
To: Rob Richardson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do I create a box from fields in a table?

you have to construct the string.  this is somewhat baroque by modern postgres standards but should work:

select tran_car_identification, format('((%s, 1), (2, 3))', location)::box from transfer_car;

merlin

Rob Richardson <RDRichardson@rad-con.com> writes:
> In my opinion, that is ugly to the point of uselessness.

Indeed :-(

For some reason, there's no constructor function to make a box from four
floats.  But there is a box constructor that takes two points, as well
as a point constructor that takes two floats; so you could do something
like

    box(point(location, 1), point(2, 3))

            regards, tom lane

On Fri, Oct 18, 2013 at 10:05 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Rob Richardson <RDRichardson@rad-con.com> writes:
>> In my opinion, that is ugly to the point of uselessness.
>
> Indeed :-(
>
> For some reason, there's no constructor function to make a box from four
> floats.  But there is a box constructor that takes two points, as well
> as a point constructor that takes two floats; so you could do something
> like
>
>         box(point(location, 1), point(2, 3))


shoot -- I thought so -- I looked for that, and didn't find it.  hm,
perhaps the geographical type documentation needs some TLC.

merlin

On Fri, 18 Oct 2013 17:05:07 +0200
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> For some reason, there's no constructor function to make a box from
> four floats.  But there is a box constructor that takes two points,
> as well as a point constructor that takes two floats; so you could do
> something like
>
>     box(point(location, 1), point(2, 3))

Kind of makes sense anyway.  The only improvement I would make is to
allow a shortcut like this;

    box((0, 1), (2, 3))

In fact, casting an array of two floats in a point context to a point
would be a good idea anyway.  In fact, this seems to work already if
quotes are added:

    box('(0, 1), (2, 3)')

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 788 2246     (DoD#0082)    (eNTP)   |  what's for dinner.
IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net

"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> ...  In fact, this seems to work already if
> quotes are added:

>     box('(0, 1), (2, 3)')

Well, that's just another spelling for a box literal, which is exactly
what the OP *doesn't* want, since he's trying to construct a box value
from non-constant values.

            regards, tom lane