Distance between two latitude longitude Postgresql

Anonymous
2023-09-01T11:50:25.26+00:00

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

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


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;



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

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

 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" 
)

OUTPUT
Chennai Mysore 250 miles 404 km
Chennai Kochi
Kochi Mysore 

Can someone give me a query or sp in Postgresql
Azure Database for PostgreSQL
0 comments No comments
{count} votes

Accepted answer
  1. GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator
    2023-09-01T16:13:53.0966667+00:00

    Hi @Anonymous Thanks for posting your question in the Microsoft Q&A forum

    You can try this if you are expecting the below output
    User's image

    Regards

    Geetha

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.