Umkreissuche mit Oracle Datenbank realisieren

On 9. Dezember 2011

Wer kennt es nicht, eine Suche im Internet mit einer Umkreiseinschränkung. In diesem Artikel beschreibe ich im Detail, wie eine solche Suche mit Hilfe einer Oracle Datenbank gelöst werden kann.

1. Am Anfang benötigen wir eine Datenbank mit allen Orten und deren PLZ sowie Längen-/Breitengraden. Eine solche Datenbank findet sich z.B. hier: Download-Link
Dieser Export stammt aus einer MySQL Datenbank, aber lässt sich mit relativ wenig Aufwand umwandeln in eine Importscript für Oracle.

2. Als nächstes benötigen wir zwei Oracle Objekte für die spätere Nutzung:

Funktion zur Berechnung

create or replace
FUNCTION radians(pin_Degree IN NUMBER)
RETURN NUMBER DETERMINISTIC IS
BEGIN
RETURN pin_Degree / 57.2957795;
END radians;

Typendefinition für späteren return-Wert unserer Funktion:

create or replace type my_tab as table of varchar2(8);
/

3. Jetzt erstellen wir die Oracle Funktion, welche später mit den beiden Parametern Postleitzahl und Entfernung (Radius) aufgerufen wird.

create or replace
FUNCTION UMKREIS ( plz_in number, umkreis_in number)
return my_tab is

rad_l       DOUBLE PRECISION;
rad_b        DOUBLE PRECISION;
plz_out   number;
string_out varchar2(5000);
plz_array my_tab;
mycount number := 1;

–PI wird mit 3,142 gerechnet
–Erdradius (geozentrischer Mittelwert) in Km: 6368

cursor myCursor01 is
SELECT plz
FROM K21_plz WHERE
6368  * SQRT(2*(1-cos(radians(geo_b)) *
cos(rad_b) * (sin(radians(geo_l)) *
sin(rad_l) + cos(radians(geo_l)) *
cos(rad_l)) – sin(radians(geo_b)) * sin(rad_b))) <=30
;

begin

SELECT geo_l/180*3.142 into rad_l FROM k21_plz WHERE plz = plz_in ;
SELECT geo_b/180*3.142 into rad_b FROM k21_plz WHERE plz = plz_in ;

open myCursor01;

plz_array := my_tab();

loop
fetch myCursor01 into
plz_out;
exit when myCursor01%notfound;
plz_array.extend;
plz_array(mycount) := plz_out;
mycount := mycount +1;
end loop;

close myCursor01;

return plz_array;
end;

4. Mit folgendem Select Statement kann nun eine Liste der Orte ausgegeben wereden, welche in dem angegebenen Radius liegen:

select * from table(UMKREIS(88069,30));

5. Die eigene Adressdatenbank kann nun über folgendes Statement abgefragt werden

Adressedatenbank ADRESSEN enthält eine Spalte PLZ

select * from ADRESSEN where PLZ in (select * from table(UMKREIS(88069,30)));

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.