Re: How do I create a box from fields in a table?
Re: How do I create a box from fields in a table?
От:
"D'Arcy J.M. Cain" <darcy@druid.net>
Дата:
On Fri, 18 Oct 2013 17:05:07 +0200
Tom Lane 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 | 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
How do I create a box from fields in a table?
От:
Rob Richardson <RDRichardson@rad-con.com>
Дата:
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? Thanks very much! RobR
Re: How do I create a box from fields in a table?
От:
Rob Richardson <RDRichardson@rad-con.com>
Дата:
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
Re: How do I create a box from fields in a table?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Rob Richardson 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
Re: How do I create a box from fields in a table?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"D'Arcy J.M. Cain" 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
Re: How do I create a box from fields in a table?
От:
Merlin Moncure <mmoncure@gmail.com>
Дата:
On Fri, Oct 18, 2013 at 10:05 AM, Tom Lane wrote: > Rob Richardson 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
Re: How do I create a box from fields in a table?
От:
Merlin Moncure <mmoncure@gmail.com>
Дата:
On Thu, Oct 17, 2013 at 4:04 PM, Rob Richardson
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
