Formel

Die Berechnung der Distanz geht nach folgender Formel:

arccos(sin(B_lat)*sin(A_lat)+cos(B_lat)*cos(A_lat)*cos(B_lon - A_lon)) * Erdradius

Mit dem Erdradius von etwa 6380 km. Die Längengrade und Breitengrade müssen in das Bogenmaß konvertiert werden, wenn die sin() und cos() Funktionen es so erwarten. Das geschieht durch

• die Multiplikation der Werte aus der Datenbank mit PI/180. In PHP ist PI als Konstante M_PI definiert
• die SQL Funktion RADIANS()

SQL

Die SQL sieht folgendermassen aus (für Suche in der Extension WEC Connector):

SELECT WEC.*,
( 6380  *
  acos(
    sin(RADIANS(49.4478)) *
    sin(RADIANS(coord.lat)) +
    cos(RADIANS(49.4478)) *
    cos(RADIANS(coord.lat)) *
    cos(RADIANS(11.0683) - RADIANS(coord.lon))
  )
)  distance
FROM tx_wecconnector_entries WEC
LEFT JOIN tx_wecconnector_cat ON (WEC.category = tx_wecconnector_cat.uid)
LEFT JOIN (
    SELECT DISTINCT loc_id , text_val, text_type 
    FROM geodb_textdata
    WHERE text_type = 500300000
    GROUP BY text_val
) AS code ON (
    (WEC.zipcode = code.text_val) AND
    (code.text_type = 500300000) /* Postleitzahl */
)
LEFT JOIN  geodb_coordinates coord 
ON (coord.loc_id = code.loc_id)
WHERE
  WEC.pid IN(84) AND WEC.deleted=0 AND hidden=0 AND moderationQueue=0
  AND tx_wecconnector_cat.pid IN(84) 
  AND WEC.post_date >= 1114034400  
  AND post_date >=1114034400
  AND ( 6380  *
    acos(
      sin(RADIANS(49.4478)) *
      sin(RADIANS(coord.lat)) +
      cos(RADIANS(49.4478)) *
      cos(RADIANS(coord.lat)) *
      cos(RADIANS(11.0683) - RADIANS(coord.lon)))
) <= 100
ORDER BY tx_wecconnector_cat.sort_order , distance, post_date DESC

Fettgedruckt sind die Werte des Ausgangspunktes - Berechnung siehe weiter unten bei Ausgangspunkt (Hierarchie, Locations).

Der LEFT JOIN erzeugt mehrfache Ergebnis Einträge, weil für eine PLZ teilweise mehrere Einträge existieren. Das oben verwendete LEFT JOIN (SELECT DISTINCT...) AS code verhindert dies - jedoch mit langen Antwortzeiten (1..2sec).

Beschreibung

Hier wird der Ausgangspunkt bestimmet der für die Distanzsuche relevant ist. Wenn mehrere Ergebnisse zurückgegeben werden muss eines davon gewählt werden. Wenn nur noch ein Ergebnis zurückkommt (Eindeutige PLZ oder Stadt oder Eintrag aus Liste gewählt und die Query Single ID ausgeführt). Dann wird die location (einfach die gesamte SQL Ergebniss Zeile) gespeichert (im PHP Objekt) um später in der SQL zur Distanzberechnung eingesetzt werden zu können als $location['lat'], $location['lon'], $location['searchRadius']

Orte

Bei der Suche nach Ortsnamen gibt diese SQL eine Liste der möglichen Orte zurück, mit einer Komma separierten Liste der Hirarchie im Feld parent:

SELECT stadt.*, coord.lat, coord.lon,
CONCAT(
    IF(parent1.text_val AND parent1.loc_id <> 105, CONCAT(parent1name.text_val,", "),""),
    IF(parent2.text_val AND parent2.loc_id <> 105, CONCAT(parent2name.text_val,", "),""),
    IF(parent3.text_val AND parent3.loc_id <> 105, CONCAT(parent3name.text_val,", "),""),
    IF(parent4.text_val AND parent4.loc_id <> 105, CONCAT(parent4name.text_val,", "),""),
    IF(parent5.text_val AND parent5.loc_id <> 105, CONCAT(parent5name.text_val,", "),"")
) parent
FROM geodb_textdata AS stadt
LEFT JOIN  geodb_coordinates coord  ON (coord.loc_id = stadt.loc_id)

LEFT JOIN geodb_textdata parent0
  ON  (parent0.loc_id = stadt.loc_id)
  AND (parent0.text_type=400100000)

LEFT JOIN geodb_textdata parent1 
  ON  (parent1.loc_id = parent0.text_val)
  AND (parent1.text_type=400100000)
LEFT JOIN geodb_textdata parent1name 
  ON  (parent1name.loc_id = parent1.loc_id )
  AND (parent1name.text_type= 500100000)
  AND parent1name.text_locale = 'de'

LEFT JOIN geodb_textdata parent2 
  ON  (parent2.loc_id = parent1.text_val)
  AND (parent2.text_type=400100000)
LEFT JOIN geodb_textdata parent2name 
  ON  (parent2name.loc_id = parent2.loc_id )
  AND (parent2name.text_type= 500100000)
  AND parent2name.text_locale = 'de'

LEFT JOIN geodb_textdata parent3 
  ON  (parent3.loc_id = parent2.text_val)
  AND (parent3.text_type=400100000)
LEFT JOIN geodb_textdata parent3name 
  ON  (parent3name.loc_id = parent3.loc_id )
  AND (parent3name.text_type= 500100000)
  AND parent3name.text_locale = 'de'

LEFT JOIN geodb_textdata parent4 
  ON  (parent4.loc_id = parent3.text_val)
  AND (parent4.text_type=400100000)
LEFT JOIN geodb_textdata parent4name 
  ON  (parent4name.loc_id = parent4.loc_id )
  AND (parent4name.text_type= 500100000)
  AND parent4name.text_locale = 'de'

LEFT JOIN geodb_textdata parent5
  ON  (parent5.loc_id = parent4.text_val)
  AND (parent5.text_type=400100000)
LEFT JOIN geodb_textdata parent5name 
  ON  (parent5name.loc_id = parent5.loc_id )
  AND (parent5name.text_type= 500100000)
  AND parent5name.text_locale = 'de'
           
WHERE  (stadt.text_val LIKE 'Nürnberg%')
AND      (stadt.text_type = 500100000) /* Stadt */
AND (
    (parent5.text_val = 105) OR
    (parent4.text_val = 105) OR
    (parent3.text_val = 105) OR
    (parent2.text_val = 105) OR
    (parent1.text_val = 105)
)

PLZ

Bei der Suche nach PLZ sieht es ähnlich aus. Die Unterschiede hier:

SELECT  code.*, stadt.text_val AS city, coord.lat, coord.lon,
CONCAT(
 ...
) parent
FROM geodb_textdata AS code
LEFT JOIN geodb_textdata AS stadt
  ON (( stadt.loc_id = code.loc_id)
  AND (stadt.text_type =500100000))
LEFT JOIN  geodb_coordinates coord  ON (coord.loc_id = stadt.loc_id)

LEFT JOIN ...

WHERE
  (code.text_val LIKE '90480%')
  AND (stadt.text_type <> 500100002) /* Sortiername */
  AND (code.text_type = 500300000) /* Postleitzahl */
  AND (
    (parent5.text_val = 105) OR
    ...
)

Single ID

SELECT  stadt.*,  coord.lat, coord.lon
FROM geodb_textdata AS stadt
LEFT JOIN  geodb_coordinates coord  ON (coord.loc_id = stadt.loc_id)
WHERE
  (stadt.loc_id = '616')
  AND (stadt.text_type = 500100000) /* Stadt */'