postgres tips: stored procedure for calculating distance on the earth

CREATE FUNCTION GET_DISTANCE
    (alat FLOAT, alon FLOAT, lat FLOAT, lon FLOAT)
RETURNS FLOAT AS
$$
DECLARE
    radius_earth FLOAT;
    radian_lat FLOAT;
    radian_lon FLOAT;
    distance_v FLOAT;
    distance_h FLOAT;
    distance FLOAT;
BEGIN
    -- Insert earth radius
    SELECT INTO radius_earth 6378.137;

    -- Calculate difference between lat and alat
    SELECT INTO radian_lat radians(lat - alat);

    -- Calculate difference between lon and alon
    SELECT INTO radian_lon radians(lon - alon);

    -- Calculate vertical distance
    SELECT INTO distance_v (radius_earth * radian_lat);

    -- Calculate horizontal distance
    SELECT INTO distance_h (cos(radians(alat)) * radius_earth * radian_lon);

    -- Calculate distance(km) and convert it to distance(meter)
    SELECT INTO distance sqrt(pow(distance_h,2) + pow(distance_v,2)) * 1000;

    -- Returns distance
    RETURN DISTANCE;
END;
$$ language plpgsql;

> createlang plpgsql mytestdb
> psql -d mytestdb
mytestdb=# CREATE FUNCTION GET_DISTANCE
mytestdb-#     (alat FLOAT, alon FLOAT, lat FLOAT, lon FLOAT)
mytestdb-# RETURNS FLOAT AS
mytestdb-# $$
mytestdb$# DECLARE
mytestdb$#     radius_earth FLOAT;
mytestdb$#     radian_lat FLOAT;
mytestdb$#     radian_lon FLOAT;
mytestdb$#     distance_v FLOAT;
mytestdb$#     distance_h FLOAT;
mytestdb$#     distance FLOAT;
mytestdb$# BEGIN
mytestdb$#     -- Insert earth radius
mytestdb$#     SELECT INTO radius_earth 6378.137;
mytestdb$#
mytestdb$#     -- Calculate difference between lat and alat
mytestdb$#     SELECT INTO radian_lat radians(lat - alat);
mytestdb$#
mytestdb$#     -- Calculate difference between lon and alon
mytestdb$#     SELECT INTO radian_lon radians(lon - alon);
mytestdb$#
mytestdb$#     -- Calculate vertical distance
mytestdb$#     SELECT INTO distance_v (radius_earth * radian_lat);
mytestdb$#
mytestdb$#     -- Calculate horizontal distance
mytestdb$#     SELECT INTO distance_h (cos(radians(alat)) * radius_earth * radian_lon);
mytestdb$#
mytestdb$#     -- Calculate distance(km) and convert it to distance(meter)
mytestdb$#     SELECT INTO distance sqrt(pow(distance_h,2) + pow(distance_v,2)) * 1000;
mytestdb$#
mytestdb$#     -- Returns distance
mytestdb$#     RETURN DISTANCE;
mytestdb$# END;
mytestdb$# $$ language plpgsql;
CREATE FUNCTION
mytestdb=# select GET_DISTANCE(34.701909, 135.4949770, 35.681382, 139.766084);
   get_distance
------------------
 405807.810663345
(1 行)