PostgreSQL SQL - Using calculated columns as arguments in same SELECT

Author

Using calculated columns as arguments in same SELECT

Michael Burke

2006-02-16, 11:23 am

Hello,

I am looking to simplify this query (uses PostGIS, but I have encountered this
scenario with other chains of functions):

gtest=# SELECT X(
SetSRID(
Transform(
GeomFromText(
'POINT(142512 1020225)', 26910
), 4326
),
-1)
) as xcoord, Y(
SetSRID(
Transform(
GeomFromText(
'POINT(142512 1020225)', 26910
), 4326
),
-1)
) AS ycoord;

This SELECT works, but the rather long arguments to X(geom) and Y(geom) are
the same. Is it possible and feasible to pre-calculate the argument, such
as:

gtest=# SELECT
SetSRID(
Transform(
GeomFromText(
'POINT(142512 1020225)', 26910
), 4326
),
-1) AS transformed_geom,
X(transformed_geom) AS xcoord,
Y(transformed_geom) AS ycoord

Where I don't really care about transformed_geom being returned, but it saves
double-calling the inside functions This doesn't work -- it complains that
transformed_geom is not a column.

SELECT version(); gives:
PostgreSQL 8.0.6 on i386-portbld-freebsd5.4, compiled by GCC cc (GCC) 3.4.2
[FreeBSD] 20040728

Thanks in advance!
Mike.

--
Michael Burke
Engineering Technologies Canada Ltd.
michael@engtech.ca 1 (902) 628-1705

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Michael Burke

2006-02-16, 11:23 am

On February 16, 2006 11:07 am, Michael Burke wrote:
> Hello,
>
> I am looking to simplify this query (uses PostGIS, but I have encountered
> this scenario with other chains of functions):
>
-- snip --

Immediately after sending this one, I realized I could do it with a
sub-select:

gtest=# SELECT X(SubSel.transformed_geom), Y(SubSel.transformed_geom) FROM (
SELECT SetSRID(
Transform(
GeomFromText(
'POINT(142512 1020225)', 26910
), 4326
),
-1) AS transformed_geom) SubSel;

This works fine.
Mike.

--
Michael Burke
Engineering Technologies Canada Ltd.
michael@engtech.ca 1 (902) 628-1705

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links