Обсуждение: How do I convert four table columns into a box?
I have the following table:
CREATE TABLE operationsplanning.shop_floor
(
id bigserial NOT NULL,
location_id character varying(32),
area_top integer NOT NULL,
area_bottom integer NOT NULL,
area_left integer NOT NULL,
area_right integer NOT NULL,
CONSTRAINT shop_floor_pkey PRIMARY KEY (id)
)
I would like to be able to tell which row in this table describes a rectangle that contains a given point. So, I figured I’d convert the four integers into a box. However, I don’t know how to do it. The documentation only shows how to work with literals, like this:
Box ‘((0, 0), (1, 1))’
But this does not work:
Select box ‘((area_left, area_top), (area_right, area_bottom))’ from operationsplanning.shop_floor
I tried a more normal cast syntax:
select location_id, ((area_left, area_top),(area_right, area_bottom))::box from operationsplanning.shop_floor
But PostgreSQL complained that it could not cast an object of type record to type box.
How do I do this?
Thanks very much!
RobR
This works:
select location_id, box(point(area_left, area_top), point(area_right, area_bottom)) from operationsplanning.shop_floor
RobR
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Rob Richardson
Sent: Tuesday, July 17, 2012 10:29 AM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] How do I convert four table columns into a box?
I have the following table:
CREATE TABLE operationsplanning.shop_floor
(
id bigserial NOT NULL,
location_id character varying(32),
area_top integer NOT NULL,
area_bottom integer NOT NULL,
area_left integer NOT NULL,
area_right integer NOT NULL,
CONSTRAINT shop_floor_pkey PRIMARY KEY (id)
)
I would like to be able to tell which row in this table describes a rectangle that contains a given point. So, I figured I’d convert the four integers into a box. However, I don’t know how to do it. The documentation only shows how to work with literals, like this:
Box ‘((0, 0), (1, 1))’
But this does not work:
Select box ‘((area_left, area_top), (area_right, area_bottom))’ from operationsplanning.shop_floor
I tried a more normal cast syntax:
select location_id, ((area_left, area_top),(area_right, area_bottom))::box from operationsplanning.shop_floor
But PostgreSQL complained that it could not cast an object of type record to type box.
How do I do this?
Thanks very much!
RobR