How do I create a box from fields in a table?

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, PostgreSQL provides the box and point types and a "contains" operator that will be perfect for this. However, the example provided in the documentation only shows the creation of boxes and points from constant values. So, the following query 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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. It will 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general