Distance between two latitude longitude Postgresql

Question

 CREATE TABLE IF NOT EXISTS public."Coor"
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    name character varying(50) COLLATE pg_catalog."default",
    lat numeric,
     lng numeric,
    CONSTRAINT pk__coor PRIMARY KEY ( id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Coor"
    OWNER to postgres;






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 AND 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;

Here is sample select to execute calculate_distance.

SELECT calculate_distance(13.08, 80.27, 12.29, 76.63, 'M');
SELECT calculate_distance(13.08, 80.27, 12.29, 76.63, 'K');

The final output should be in the below format

 CREATE TABLE IF NOT EXISTS public."distance"
(
     
   fromname character varying(50) COLLATE pg_catalog."default",
    toname character varying(50) COLLATE pg_catalog."default",
     distancemiles character varying(50) COLLATE pg_catalog."default",
     diskm character varying(50) COLLATE pg_catalog."default" `
)

I want to find distance between three cities Chennai to Kochi , Chennai to Mysore , and Kochi to Mysore

1   "Chennai"   13.08   80.27
2   "Kochi"     9.93    76.26
3   "Mysore"    12.29   76.63

OUTPUT

Chennai Mysore 250 miles 404 km
Chennai Kochi
Kochi Mysore

When working with geo data, consider using [PostGIS](https://postgis.net/) instead. This handles such calculations of distances between lat-long coordinates natively. — Valerij Dobler, Sep 01 '23 at 13:59

nbk · Answer 1 · 2023-09-01T13:55:14.050

I needed torewrite your function and taböe a bit, as you´r design caused lots of problems

as you want a a row you need to specify the type of row you want toreciece, so distance seem like the best chance

It now returns a row of the style distance

You need to expand the hole things, as you need also to pass the name and last name.

Your function is built that it only returns only Km or miles, like you can see in the output.

This gives you a start, on that you can adept your function

 CREATE TABLE IF NOT EXISTS public."distance"
(
     
   fromname TEXT COLLATE pg_catalog."default",
    toname TEXT COLLATE pg_catalog."default",
     distancemiles float,
     diskm  float 
)

CREATE OR REPLACE FUNCTION calculate_distance(lat1 float, lon1 float, lat2 float, lon2 float, units varchar)
RETURNS SETOF "distance" AS $$
    DECLARE
        dist float = 0;
  distkm float = 0;
  distMiles float = 0;
        radlat1 float;
        radlat2 float;
        theta float;
        radtheta float;
    BEGIN
        IF lat1 = lat2 AND lon1 = lon2
            THEN RETURN QUERY SELECT 'userA','userlastA',distMiles,distkm ;
        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 distkm = dist * 1.609344; END IF;
            IF units = 'M' THEN distMiles = dist * 0.8684; END IF;

            RETURN QUERY SELECT 'userA','userlastA' ,distMiles,distkm  ;
        END IF;
    END;
$$ LANGUAGE plpgsql;

SELECT * FROM  calculate_distance(13.08, 80.27, 12.29, 76.63, 'M');
SELECT * FROM calculate_distance(13.08, 80.27, 12.29, 76.63, 'K');

fromname	toname	distancemiles	diskm
userA	userlastA	218.26626129516504	0

fromname	toname	distancemiles	diskm
userA	userlastA	0	404.49734916836263

fiddle

But an approach with loops which would be neede is verslow, so that maybe another approach is necessary

 CREATE TABLE IF NOT EXISTS public."Coor"
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    name Text COLLATE pg_catalog."default",
    lat numeric,
     lng numeric,
    CONSTRAINT pk__coor PRIMARY KEY ( id)
);
   
INSERT INTO "Coor"
    ( "name", "lat", "lng")
VALUES
    ( '"Chennai"', 13.08, 80.27),
    ( '"Kochi"', 9.93, 76.26),
    ( '"Mysore"', 12.29, 76.63)
;

CREATE TABLE

INSERT 0 3

 CREATE TABLE IF NOT EXISTS public."distance"
(
     
   fromname TEXT COLLATE pg_catalog."default",
    toname TEXT COLLATE pg_catalog."default",
     distancemiles float,
     diskm  float 
)

CREATE TABLE

CREATE OR REPLACE FUNCTION calculate_distance
  (fromname  Text ,lat1 float, lon1 float, toname text,lat2 float, lon2 float, units varchar)
RETURNS SETOF "distance" AS $$
    DECLARE
        dist float = 0;
  distkm float = 0;
  distMiles float = 0;
        radlat1 float;
        radlat2 float;
        theta float;
        radtheta float;
    BEGIN
        IF lat1 = lat2 AND lon1 = lon2
            THEN RETURN QUERY SELECT fromname,toname,distMiles,distkm ;
        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;

            distkm = dist * 1.609344; 
            distMiles = dist * 0.8684;

            RETURN QUERY SELECT fromname,toname ,distMiles,distkm  ;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

create or replace function fn_list() 
  returns table(fromname Text,toname Text,distMiles Float,distkm Float) 
as $$
begin
  -- do some work....
  return query
WITH CTE As ( 
  SELECT c1.name as Fromname, c1.lat lat1, c1.lng lon1,
  c2.name toname, c2. lat lat2, c2.lng lon2
  FROM "Coor" c1 JOIN "Coor" c2 ON c1.id < c2.id)
SELECT CTE.Fromname, CTE.toname, 
  acos(sin(pi() * lat1 / 180) * sin(pi() * lat2 / 180) 
  + cos(pi() * lat1 / 180) * cos(pi() * lat2 / 180) * cos(pi() * (lon1 - lon2) / 180)) * 180 / pi() * 60 * 1.1515 * 0.8684 as distancemiles
  ,  acos(sin(pi() * lat1 / 180) * sin(pi() * lat2 / 180) 
  + cos(pi() * lat1 / 180) * cos(pi() * lat2 / 180) * cos(pi() * (lon1 - lon2) / 180)) * 180 / pi() * 60 * 1.1515 * 1.609344 as distanceKm 
FROM CTE;
end;
$$ language plpgsql;

CREATE FUNCTION

SELECT * FROM fn_list()

fromname	toname	distmiles	distkm
"Chennai"	"Kochi"	302.13074782483	559.9174415331682
"Chennai"	"Mysore"	218.26626129516504	404.49734916836263
"Kochi"	"Mysore"	143.26021902027512	265.49398194261363

SELECT 3

fiddle

I need a query for n rows here are three rows ,If I have three locations a,b,c I need a to b , a to c and b to c. 1 "Chennai" 13.08 80.27 2 "Kochi" 9.93 76.26 3 "Mysore" 12.29 76.63 OUTPUT Chennai Mysore 250 miles 404 km Chennai Kochi Kochi Mysore — KALYANA ALLAM, Sep 01 '23 at 12:29
and you need something liek https://stackoverflow.com/questions/27841910/input-table-for-pl-pgsql-function to get back a table — nbk, Sep 01 '23 at 12:45
I created this but I am getting errorSELECT a.id ,a.Name ,a.lat ,a.lng , b.id ,b.Name ,b.lat ,b.lng , calculate_distance(a.lat, a.lng, b.lat, b.lng, 'M') 'MILES' , calculate_distance(a.lat, a.lng, b.lat, b.lng, 'k') 'KMS' FROM public."Coor" a cross join public."Coor" b where a.id <> b.id — KALYANA ALLAM, Sep 01 '23 at 12:55
I got this SELECT a.id ,a.Name ,a.lat ,a.lng , b.id ,b.Name ,b.lat ,b.lng , calculate_distance(a.lat, a.lng, b.lat, b.lng, 'M') miles , calculate_distance(a.lat, a.lng, b.lat, b.lng, 'K') kms FROM public."Coor" a cross join public."Coor" b where a.id <> b.id — KALYANA ALLAM, Sep 01 '23 at 13:01
you would make this like https://dbfiddle.uk/y9boxf2x now you can call the function like described in the last link and add your distance code to it — nbk, Sep 01 '23 at 13:08

Distance between two latitude longitude Postgresql

1 Answers1