Thread: 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