[SQL] PL/SQL - Radius-Suche

Dieses Thema im Forum "Webentwicklung" wurde erstellt von Smokers, 18. Januar 2012 .

Status des Themas:
Es sind keine weiteren Antworten möglich.
  1. 18. Januar 2012
    PL/SQL - Radius-Suche

    !!!! Änderung siehe bitte letzter Post


    Hallo,

    ich hab ein kleines Problem.
    Ich hab bisher folgende Funktion(en):

    DISTANCE(p1x,p1y,p2x,p2y) --> abstand zwischen 2 Punkten in KM
    Spoiler
    Code:
    CREATE OR REPLACE FUNCTION DISTANCE 
    ( 
     Lat1 IN NUMBER,
     Lon1 IN NUMBER,
     Lat2 IN NUMBER,
     Lon2 IN NUMBER
    ) RETURN NUMBER IS
     -- Convert degrees to radians
    DegToRad NUMBER := 57.29577951;
    BEGIN
     RETURN(6387.7 * ACOS((sin(NVL(Lat1,0) / DegToRad) * SIN(NVL(Lat2,0) / DegToRad)) +
     (COS(NVL(Lat1,0) / DegToRad) * COS(NVL(Lat2,0) / DegToRad) *
     COS(NVL(Lon2,0) / DegToRad - NVL(Lon1,0)/ DegToRad))));
    END;
    Den Algo hab ich im Netz gefunden und nur so abgeschrieben aus PHP, sollte eigentlich stimmen.


    Und nun hab ich meine Abfrage :
    Code:
    SELECT 
     m.mid,
     m.lon,
     m.lat,
     distance(m.lat,m.lon,51.342677,12.350285)
     FROM medium m;
    Die Funktioniert auch wunderbar, es gibt nur einen Fehlerpunkt, nämlich wenn
    p1x == p2x ^ p1y == p2y

    Sprich wenn der Abstand zwischen den 2 Punkten eigentlich exakt 0 sein müsste, dann bekomme ich von Oracle eine nette Meldung :

    Code:
    Fehlerbericht:
    SQL-Fehler: ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.STANDARD", line 394
    ORA-06512: at "MMDB22.DISTANCE", line 11
    06502. 00000 - "PL/SQL: numeric or value error%s"
    *Cause: 
    *Action:

    Ich such nun schon in der Distance Formel wo dort vllt durch 0 geteilt wird, oder wo der Fehler sein könnte.
    Finde ihn aber auf anhieb nicht,... :-/ jemand eine Idee oder findet den Fehler eher als ich?


    lg und danke =)
     
  2. 18. Januar 2012
    AW: PL/SQL - Radius-Suche

    könnte das gehen
    IF(expr1,expr2,expr3)

    PHP:
    ...
    IF(
    distance ( m . lat , m . lon , 51.342677 , 12.350285 )= 0 , 0 , distance ( m . lat , m . lon , 51.342677 , 12.350285 ))
    ...
     
  3. 18. Januar 2012
    AW: PL/SQL - Radius-Suche

    habs mal auf oracle umgemokelt

    Code:
    SELECT 
     m.mid,
     m.lon,
     m.lat,
     CASE WHEN distance(m.lat,m.lon,51.342677,12.350285)=0 THEN 0
     ELSE distance(m.lat,m.lon,51.342677,12.350285) END
     FROM medium m;
    Ergebnis ist aber das gleiche :
    Spoiler
    Code:
    Fehler beim Start in Zeile 1 in Befehl:
    SELECT 
     m.mid,
     m.lon,
     m.lat,
     CASE WHEN distance(m.lat,m.lon,51.342677,12.350285)=0 THEN 0
     ELSE distance(m.lat,m.lon,51.342677,12.350285) END
     FROM medium m
    Fehlerbericht:
    SQL-Fehler: ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.STANDARD", line 394
    ORA-06512: at "MMDB22.DISTANCE", line 11
    06502. 00000 - "PL/SQL: numeric or value error%s"
    *Cause: 
    *Action:

    das IF Statement so wie es bei MySQL anscheinend enthalten ist , gibts wohl im normalen Kontext von Oracle SQL nicht, nur innerhalb eines PL/SQL Blocks.

    Da das bei mir aber gerade ne normale Abfrage ist , mag er das nicht
    Spoiler
    Code:
    Fehler beim Start in Zeile 1 in Befehl:
    SELECT 
     m.mid,
     m.lon,
     m.lat,
     IF(distance(m.lat,m.lon,51.342677,12.350285)=0,0,distance(m.lat,m.lon,51.342677,12.350285))
     FROM medium m
    Fehler bei Befehlszeile:5 Spalte:48
    Fehlerbericht:
    SQL-Fehler: ORA-00907: missing right parenthesis
    00907. 00000 - "missing right parenthesis"
    *Cause: 
    *Action:


    hab das mal mathematisch aufgeschrieben und es für den fall
    p1x == p2x sowie p1y == p2y zusammengefasst,.. hilft mir aber null weiter : D
     
  4. 18. Januar 2012
    AW: PL/SQL - Radius-Suche

    Also irgendwie gestaltet sich das doch anders als erst erwartet, sorry

    hier mal die tabelle mit den geodaten
    Code:
    "MID" "LON" "LAT" 
    20 12,350285 51,342677 
    1 12,396807 51,332217 
    2 12,396807 51,332217 
    3 12,375262 51,313092 
    4 12,375262 51,313092 
    5 11,429729 53,916978 
    6 12,411751 51,314882 
    7 10,681179 53,866207 
    8 13,378196 52,516317 
    9 13,410959 52,521404 
    10 13,373886 52,519949 
    11 8,651197 51,606664 
    12 6,966088 50,941191 
    13 8,673234 50,109584 
    14 8,760309 51,718215 
    15 9,684491 47,544129 
    16 12,3968 51,33221 
    17 10,68117 53,8662 
    18 13,446617 52,506053 
    19 13,446611 52,506059 
    

    Ich versuche also den abstand jedes objektes aus der tabelle mit einem referenzpunkt zu vergleichen und den abstand zu ermitteln :

    Code:
    SELECT 
     m.mid,
     m.lon,
     m.lat,
     distance(m.lat,m.lon,52.519949,13.373886)
     FROM medium m;


    Wenn ich als Referenzpunkt den Wert 51.342677 / 12.350285 angebe, bekomme ich den genannten Fehler :
    Spoiler
    Code:
    Fehler beim Start in Zeile 1 in Befehl:
    SELECT 
     m.mid,
     m.lon,
     m.lat,
     distance(m.lat,m.lon,51.342677,12.350285)
     FROM medium m
    Fehlerbericht:
    SQL-Fehler: ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.STANDARD", line 394
    ORA-06512: at "MMDB22.DISTANCE", line 11
    06502. 00000 - "PL/SQL: numeric or value error%s"
    *Cause: 
    *Action:

    Wenn ich einen anderen Punkt nehme wie :
    52.506059 / 13.446611 --> geht
    Spoiler
    Code:
    MID LON LAT DISTANCE(M.LAT,M.LON,52.506059,13.446611)
    --- ---------- ---------- -----------------------------------------
     20 12,350285 51,342677 150,00929419064969599715388999442964555 
     1 12,396807 51,332217 149,452316525798210217790399496601881189 
     2 12,396807 51,332217 149,452316525798210217790399496601881189 
     3 12,375262 51,313092 152,042456649519080521717070768374890367 
     4 12,375262 51,313092 152,042456649519080521717070768374890367 
     5 11,429729 53,916978 207,046552094323742221648724944361563785 
     6 12,411751 51,314882 150,665936968938361029439033296132500138 
     7 10,681179 53,866207 238,976622093017077392300685162278320187 
     8 13,378196 52,516317 4,78084807892205240368747639557415608605 
     9 13,410959 52,521404 2,96272743959348286770687575028917391563 
     10 13,373886 52,519949 5,17157255180533415126228466640005006045 
     11 8,651197 51,606664 343,608450130887069866659370884656490426 
     12 6,966088 50,941191 480,139708577517744221837161121226283921 
     13 8,673234 50,109584 426,526954761318051940840504448080510887 
     14 8,760309 51,718215 332,588387537307181061710561160330317336 
     15 9,684491 47,544129 615,11878613297336013822928694494261114 
     16 12,3968 51,33221 149,453234990926358527969146950110500503 
     17 10,68117 53,8662 238,976603322134631139223023815122309606 
     18 13,446617 52,506053 0,000783088432451472069153762061059101971874 
     19 13,446611 52,506059 0,000000000000000842595499896599257881930467543104895209
    51.313092 / 12.375262 --> geht auch
    53.8662 / 10.68117 --> geht nicht!
    Spoiler
    Code:
    ehler beim Start in Zeile 1 in Befehl:
    SELECT 
     m.mid,
     m.lon,
     m.lat,
     distance(m.lat,m.lon,53.8662,10.68117)
     FROM medium m
    Fehlerbericht:
    SQL-Fehler: ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.STANDARD", line 394
    ORA-06512: at "MMDB22.DISTANCE", line 11
    06502. 00000 - "PL/SQL: numeric or value error%s"
    *Cause: 
    *Action:
    51.314882 / 12.411751 --> geht auch nicht!
    Spoiler
    Code:
    Fehler beim Start in Zeile 1 in Befehl:
    SELECT 
     m.mid,
     m.lon,
     m.lat,
     distance(m.lat,m.lon,51.314882,12.411751)
     FROM medium m
    Fehlerbericht:
    SQL-Fehler: ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.STANDARD", line 394
    ORA-06512: at "MMDB22.DISTANCE", line 11
    06502. 00000 - "PL/SQL: numeric or value error%s"
    *Cause: 
    *Action:

    So zieht sich das durch die ganze Tabelle, keine Ahnung woran es liegt.
    //kann es an der Definition "NUMBER" als return type liegen, das der wert der zurückgegeben wird als abstand einfach viel zu klein ist, und somit den "NUMBER" sprengt?
     
  5. 18. Januar 2012
    AW: PL/SQL - Radius-Suche

    sorry das ich wieder mit mysql komme.
    hab das gefunden.
    vielleicht hilft das weiter?
     
  6. 18. Januar 2012
    AW: PL/SQL - Radius-Suche

    Dank euch,

    hab den Algo neu geschrieben und etwas aus den Quellen die mir du und Murdoc gegeben haben rausgefischt.

    neuer algo :

    Code:
    create or replace
    FUNCTION DISTANCE(
     Lat1 NUMBER,
     Lon1 NUMBER,
     Lat2 NUMBER,
     Lon2 NUMBER)
     RETURN NUMBER
    IS
    
    earth_radius CONSTANT NUMBER := 6367;
    
    res NUMBER;
    Lat NUMBER;
    Lat1Radians NUMBER;
    Lat2Radians NUMBER;
    Lon NUMBER;
    Lon1Radians NUMBER;
    Lon2Radians NUMBER;
    
    BEGIN
    
     Lat1Radians := Lat1 * 0.017453293;
     Lat2Radians := Lat2 * 0.017453293;
     Lon1Radians := Lon1 * 0.017453293;
     Lon2Radians := Lon2 * 0.017453293;
    
     Lon := Lon2Radians - Lon1Radians;
     Lat := Lat2Radians - Lat1Radians;
    
     res := POWER(SIN(Lat/2),2) + COS(Lat1Radians) * COS(Lat2Radians) * POWER(SIN(Lon/2),2);
     RETURN ROUND(earth_radius * 2 * ATAN2(SQRT(res), SQRT(1-res)),2);
     
    EXCEPTION
     WHEN OTHERS THEN
     RETURN 9999;
    END DISTANCE;
    Der fängt das ganze durch ne Esception halt ;-)

    danke euch
     
  7. Video Script

    Videos zum Themenbereich

    * gefundene Videos auf YouTube, anhand der Überschrift.