function in PL/SQL to calculate all distances from one city to to all other cities

[niki viki]

I have a table with cities and their longitudes and latitudes. I need to write a function in PL/SQL to calculate all distances from one city to to all other cities in this table and return those distances. I have already made a function which computes the distance between 2 cities. I know that I need to work with a cursor. Any tips?

`CREATE TABLE Rheinland_Staedte
(Stadtname VARCHAR2(25),
Noerdlicher_Grad NUMBER,
Noerdliche_Minute NUMBER,
oestlicher_Grad NUMBER,
oestliche_Minute NUMBER,
CONSTRAINT rhein_St UNIQUE (Stadtname,Noerdlicher_Grad,Noerdliche_Minute,oestlicher_Grad,oestliche_Minute)
);


INSERT INTO Rheinland_Staedte
VALUES ('Aachen',50,47,6,5);
INSERT INTO Rheinland_Staedte
VALUES ('Bonn',50,44,7,6);
INSERT INTO Rheinland_Staedte
VALUES ('Düsseldorf',51,14,6,47);
INSERT INTO Rheinland_Staedte
VALUES ('Duisburg',51,25,6,4);
INSERT INTO Rheinland_Staedte
VALUES ('Essen',51,27,7,1);
INSERT INTO Rheinland_Staedte
VALUES ('Köln',50,56,6,57);
INSERT INTO Rheinland_Staedte
VALUES ('Krefeld',51,20,6,34);
INSERT INTO Rheinland_Staedte
VALUES ('Leverkusen',51,2,6,59);
INSERT INTO Rheinland_Staedte
VALUES ('Mönchengladbach',51,11,6,27);
INSERT INTO Rheinland_Staedte
VALUES ('Mülheim an der Ruhr',51,26,6,53);
INSERT INTO Rheinland_Staedte
VALUES ('Oberhausen',51,28,6,52);
INSERT INTO Rheinland_Staedte
VALUES ('Remscheid',51,11,7,12);
INSERT INTO Rheinland_Staedte
VALUES ('Solingen',51,10,7,5);
INSERT INTO Rheinland_Staedte
VALUES ('Wuppertal',51,16,7,13);

create or replace PACKAGE GEOGRAPHICAL_PACKAGE IS


FUNCTION DISTANCE(stadt1 VARCHAR2
,stadt2 VARCHAR2) RETURN NUMBER ;

END GEOGRAPHICAL_PACKAGE;
/
create or replace PACKAGE BODY GEOGRAPHICAL_PACKAGE IS

FUNCTION DISTANCE(stadt1 VARCHAR2
,stadt2 VARCHAR2) RETURN NUMBER IS
v_norgr Rheinland_Staedte.Noerdlicher_Grad%TYPE;
v_nordmin Rheinland_Staedte.Noerdliche_Minute%TYPE;
v_ostgr Rheinland_Staedte.oestlicher_Grad%TYPE;
v_ostmin Rheinland_Staedte.oestliche_Minute%TYPE;
v_norgr1 Rheinland_Staedte.Noerdlicher_Grad%TYPE;
v_nordmin1 Rheinland_Staedte.Noerdliche_Minute%TYPE;
v_ostgr1 Rheinland_Staedte.oestlicher_Grad%TYPE;
v_ostmin1 Rheinland_Staedte.oestliche_Minute%TYPE;
latitude_min NUMBER;
longitude_min NUMBER;
latitude_min1 NUMBER;
longitude_min1 NUMBER;
distance NUMBER;
BEGIN
SELECT Noerdlicher_Grad,Noerdliche_Minute,oestlicher_Grad,oestliche_Minute INTO v_norgr,v_nordmin,v_ostgr,v_ostmin FROM Rheinland_Staedte WHERE STADTNAME=stadt1;

SELECT Noerdlicher_Grad,Noerdliche_Minute,oestlicher_Grad,oestliche_Minute INTO v_norgr1,v_nordmin1,v_ostgr1,v_ostmin1 FROM Rheinland_Staedte WHERE STADTNAME=stadt2;
latitude_min:=v_norgr+v_nordmin/60;
longitude_min:=v_ostgr+v_ostmin/60;
latitude_min1:=v_norgr1+v_nordmin1/60;
longitude_min1:=v_ostgr1+v_ostmin1/60;
distance:= SQRT((latitude_min - latitude_min1)*(latitude_min - latitude_min1) + (longitude_min -longitude_min1)*(longitude_min -longitude_min1));
return distance*60;
END;
END GEOGRAPHICAL_PACKAGE;
/
Here is my table and Function what return Distance between two cities

[Paul Clapham]

Would it not be easier to just join Rheinland_Staedte to Rheinland_Staedte without any matching columns (a Cartesian join)?

And by the way your distance formula isn't correct. At least, I assume you want to calculate the distance in kilometers, don't you? There's a good web page here explaining the calculation in detail and you could adapt the JavaScript version of the calculation which the page gives.

EDIT: Looks like I forgot to say what "here" meant. The page I was talking about is here. Sorry for the confusion.

[Brian Tkatch]

Paul Clapham wrote:Would it not be easier to just join Rheinland_Staedte to Rheinland_Staedte without any matching columns (a Cartesian join)?

Yeah, a VIEW would be better at that anyway. Using a PROCEDURE is an unnecessary switch to the PL/SQL engine.

[niki viki]

my task is to write a funktion.

[niki viki]

By the way i need to calculate distance in minutes not in km.

[Brian Tkatch]

niki viki wrote:my task is to write a funktion.

A function can only return one record. But you want many values. That is, one value per city, but there are many cities. Unless you are returning a REFCURSOR, TABLE, or the like. So, i am not sure i understand what you want.

As an aside, the variables use %TYPE. That's good. The parameters and return type ought to also use %TYPE.