SQL Berechnung der Entfernung zweier Postleitzahlen

Aufgrund der großen Nachfrage nehme ich mir hier nochmal Zeit, um mein SQL zur Berechnung von Entfernungen anhand von PLZ zu veröffentlichen:

SELECT	ROUND(SQRT(POW((u2.utm_lon-u.utm_lon),2)+POW((u2.utm_lat-u.utm_lat),2))/1000) as entfernung

FROM	umkreissuche u INNER JOIN umkreissuche u2
ON		SQRT(POW((u2.utm_lon-u.utm_lon),2)+POW((u2.utm_lat-u.utm_lat),2)) < ".intval($this->km)."*1000
AND		u.zipdisplay_postcode = '".mysql_escape_string($this->zip)."'
INNER JOIN fe_users a
ON		a.zip = u2.zipdisplay_postcode

Kurze Erklärung: die Tabelle umkreissuche beinhaltet alle Postleitzahlen und deren Koordinaten in Längengrad und Breitengrad in der UTM Codierung. Die Daten findet Ihr hier: Download MySQL Create der Tabelle Umkreissuche

Die Tabelle fe_users beinhaltet Benutzer, von welchen aus die Berechnung gestartet werden soll, der Parameter $this->plz hat die Formulareingabe der PLZ gespeichert.

Getagged mit:
Veröffentlicht unter Database
10 Kommentare auf “SQL Berechnung der Entfernung zweier Postleitzahlen
  1. caro sagt:

    Hallo!

    ersteinmal vielen lieben dank für diesen netten einblick!
    funktioniert wunderbar und das ist eine tolle bereicherung (falls meine seite mal endlich online geht :D)

    ich habe mit meinem sql programm (heidisql) ein bisschen rumgespielt und wollte jetzt versuchen die umkreissuche zum laufen zu bringen, mittels des ortes…
    aber das klappt irgendwie nicht, mittlerweile bin ich so verzweifelt, das ich mich extra hier registriert habe 🙂
    hast du eventuell einen tipp für mich?

    und, ich habe schon einiges über umkreissuche gelesen und immer wieder stand was von der opengeodb, hatte diese auch testweise installiert, allerdings ist die datenbank ca. 55 mb groß, deine schlanke db find ich super, es ist nur das nötigste drinne und sehr übersichtlich und da wollte ich dich fragen, ob es sowas auch eventuell für österreich und schweiz gibt.
    hatte jetzt viel gesucht und mich das komplette wochenende damit aussernander gesetzt aber nichts passendes gefunden, ausser 50 mb große datenbanken die mir zuviel sind. 🙂

    das wäre ganz toll, wenn du mir da eventuell helfen könntest! 🙂

    nochmal vielen lieben dank für dein kleines script und der tollen erklärung, bis jetzt die beste lösung die ich im internet finden konnte!

    ganz liebe grüße
    caro

  2. Michael sagt:

    Achja, und hier für alle, die noch die Bundesländer zu der PLZ brauchen:

    http://blog.marit.ag/2008/11/13/plz-bundesland-berechnung/

  3. Robmaster sagt:

    Hallo,

    hat jemand Erfahrung, wie kann man diese SQL-Anfrage mit DB_DATAOBJECT von PEAR realisieren?
    Danke.

    Grüße!
    Rob

  4. Michael sagt:

    ich schätze, mit DB_DATAOBJECT geht das nicht. aber ich würde mir mal den complex join ansehen..

  5. Robmaster sagt:

    Das wäre natürlich super, da viele Webmaster auch DB_DATAOBJECT benutzen, wie z.B. ich… Vielleicht benutzen Sie andere Datenbanken-Frameworks, die das realisieren können?

  6. Michael sagt:

    Jedes SQL Framework sollte die Hintertür haben, um lupenreines SQL abzufeuern. Ich beschäftige mich auch nicht großartig damit.

  7. Joseph sagt:

    Danke für den Code und den Download.
    Mit 2 Änderungen und 10 Minuten Aufwand hab ich jetzt endlich eine funktionierende Umkreissuche.

  8. Michael sagt:

    Hier bin ich auch nochmal auf eine schöne Lösung gestoßen.

    SELECT *,
    111.045*DEGREES(ACOS(COS(RADIANS(52))*COS(RADIANS(entries.latitude)) *COS(RADIANS(14) – RADIANS(entries.longitude)) + SIN(RADIANS(52))*SIN(RADIANS(entries.latitude)))) AS distance_km
    from `entries`

    order by distance_km asc

  9. Steve sagt:

    Hallo,

    erstmal vielen dank für die Vorarbeit. In der zum Download bereitgestellten Daten, ist in UTM Format somit ist die Umrechnung von COS nicht mehr brauchbar. Eine kleine Kritik ist noch die Datenbank ist leider nicht vollständig.

  10. elias sagt:

    Hallo ,
    vielen dank für tolle Tutorial ,
    kann jemand vllt mir bitte sagen , wo kann ich Die Tabelle fe_users Herunterladen ? weil ich die Struktur von dieser Tabelle nicht genau weiß ,da ich Anfänger bin und es ist meine Praktikums Aufgabe, ich freue mich über jedes Antwort
    bin sehr dankbar

Schreibe einen Kommentar

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

*