PostgreSQL calculate distance between two points without using PostGIS
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
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');