Azure Database for PostgreSQL
An Azure managed PostgreSQL database service for app development and deployment.
1,437 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
Hi @Anonymous Thanks for posting your question in the Microsoft Q&A forum
You can try this if you are expecting the below output
Regards
Geetha