while converting mysql 5.5 postgresql 9.4, i've had problems query:
select *, gcdist(?, ?, lat, lon) dist ads having dist < radius order date_created desc limit ?; where gcdist computes great-circle distance between 2 points.
without having clause, query works fine on postgres, if want filter out rows dist > radius receive error:
error: column "dist" not exist
line 1: ...*, gcdist(0, 0, lat, lon) dist ads having dist < 100...
is possible use output of function in having clause of query in postgressql 9.4? if so, how?
thank in advance hint.
here's how reproduce error:
create function gcdist ( _lat1 float, -- scaled degrees north 1 point _lon1 float, -- scaled degrees west 1 point _lat2 float, -- other point _lon2 float ) returns float immutable $$ -- hardcoded constant: declare _deg2km float default 0.0111325; _deg2rad float default pi()/1800000; -- scaled 1e4 mediumint _rlat1 float default _deg2rad * _lat1; _rlat2 float default _deg2rad * _lat2; -- compute if earth's radius = 1.0 _rlond float default _deg2rad * (_lon1 - _lon2); _m float default cos(_rlat2); _x float default cos(_rlat1) - _m * cos(_rlond); _y float default _m * sin(_rlond); _z float default sin(_rlat1) - sin(_rlat2); _n float default sqrt(_x * _x + _y * _y + _z * _z); begin return _deg2km * 2 * asin(_n / 2) / _deg2rad; -- again--scaled degrees end; $$ language plpgsql; create table test (id serial primary key, lat integer not null, lon integer not null); insert test (id, lat, lon) values (default, 10000, 10000); insert test (id, lat, lon) values (default, 20000, 20000); insert test (id, lat, lon) values (default, 50000, 50000); select *, gcdist(0, 0, lat, lon) dist test having dist < 200; the output table on mysql similar following:
id | lat | lon | dist ---+-----+-----+------ 1 |10000|10000|157.43
the having useless (and wrong) in first place because not using group by, should use where clause in case.
to access column alias in where clause need wrap query in derived table:
select * ( select *, gcdist(0, 0, lat, lon) dist test ) t dist < 200; or repeat expression
select *, gcdist(0, 0, lat, lon) dist test gcdist(0, 0, lat, lon) < 200; see here:
Comments
Post a Comment