PostgreSQL calculate distance between two points without using PostGIS

Question

How to calculate distance between two points when latitude and longitude are in two separate columns in a table?
I cannot use PostGIS because I use heroku Postgres free version.

Reeva

Can you use the earthdistance extension? – Cylindrical

Answer 1

You can using, something like this:

select SQRT(POW(69.1 * (latitude::float -  p_lat::float), 2) + 
    POW(69.1 * (p_lon::float - longitude::float) * COS(latitude::float / 57.3), 2)
)

In this:

(latitude, Longitude) point 1.

(p_lat, p_lon) point 2

Agonizing

its working but I think its giving distance in miles can we get distance in km? – Fronnia

Yeah, you could if you calculate at the end, but I posted a function that already do it. – Dunker

Answer 2

I found a function that could help you.

Font: geodatasource

Passed to function:
lat1, lon1 = Latitude and Longitude of point 1 (in decimal degrees)
lat2, lon2 = Latitude and Longitude of point 2 (in decimal degrees)
unit = the unit you desire for results
where: 'M' is statute miles (default)
'K' is kilometers
'N' is nautical miles

CREATE OR REPLACE FUNCTION calculate_distance(lat1 float, lon1 float, lat2 float, lon2 float, units varchar)
RETURNS float AS $dist$
    DECLARE
        dist float = 0;
        radlat1 float;
        radlat2 float;
        theta float;
        radtheta float;
    BEGIN
        IF lat1 = lat2 OR lon1 = lon2
            THEN RETURN dist;
        ELSE
            radlat1 = pi() * lat1 / 180;
            radlat2 = pi() * lat2 / 180;
            theta = lon1 - lon2;
            radtheta = pi() * theta / 180;
            dist = sin(radlat1) * sin(radlat2) + cos(radlat1) * cos(radlat2) * cos(radtheta);

            IF dist > 1 THEN dist = 1; END IF;

            dist = acos(dist);
            dist = dist * 180 / pi();
            dist = dist * 60 * 1.1515;

            IF units = 'K' THEN dist = dist * 1.609344; END IF;
            IF units = 'N' THEN dist = dist * 0.8684; END IF;

            RETURN dist;
        END IF;
    END;
$dist$ LANGUAGE plpgsql;

And you can use like below:

SELECT calculate_distance(32.9697, -96.80322, 29.46786, -98.53506, 'M');
SELECT calculate_distance(32.9697, -96.80322, 29.46786, -98.53506, 'K');
SELECT calculate_distance(32.9697, -96.80322, 29.46786, -98.53506, 'N');

Dunker

The OR must be AND in this part: IF lat1 = lat2 OR lon1 = lon2 THEN RETURN dist; – Painter

This has an error, stated in the previous comment, please correct the answer – Diactinic