Error in calculating distance using latitude & longitude (PostgreSQL)

Question

I am trying to calculate the distance between two locations given their latitude and longitude information on postgres sql.

On doing a google search, I found that I can use the following function:

CREATE OR REPLACE FUNCTION gc_dist (_lat1 FLOAT8,_lon1 FLOAT8,_lat2 FLOAT8,_lon2 FLOAT8) RETURNS FLOAT8 AS $$ SELECT ACOS(SIN($1)*SIN($3) + COS($1)*COS($3)*COS($4 - $2))*6371;$$ LANGUAGE sql IMMUTABLE;

I used this function on a couple of lat/long combinations and result seems to be throwing odd values e.g.

1) select gc_dist(34.313004, 133.86454, 34.312454, 133.86337) as dist;

Result: 8.04 KM

2) select gc_dist(34.313004, 133.86454, 34.313983, 133.83002) as dist;

Result: 213.50 KM

You can see change result from 1) & 2) are too far whereas long/lat is pretty close (checked on https://www.movable-type.co.uk/scripts/latlong.html, Result1 should be 0.1236 and Result2: 3.172 >> which seems ok to me)

what am I doing wrong here?

Please help. I am new to this.

lsowen lsowen · Accepted Answer · 2018-03-24T02:42:53

Could you use built in PostGIS functionality? For example, your first distance could be calculated using:

select st_distancesphere(st_makepoint(34.313004, 133.86454), st_makepoint(34.312454, 133.86337));

Which gives the result of 136.82660446 (meters).

Error in calculating distance using latitude & longitude (PostgreSQL)

2 Answers