Entfernungen zu anderen Orten mittels SQL auslesen

This page is also available in English.

Erneut habe ich mir für meine Bildergalerie eine kleine zusätzliche Funktion ausgedacht... Ich wollte eine Berechnung zwischen zwei oder mehreren Punkten auf dem Globus mit einbauen.

Schon seit einiger Zeit hinterlege ich in der Datenbank zu allen Orten die Geodaten über Länge und Breite. Diese Informationen wurden bislang nur unter der Oberfläche in so genannten RDF-Tags sowie im Meta-Header eingebunden. Für die Nutzer der Bildergalerie hatten diese Geodaten keinen unmittelbaren Mehrwert.

Grundlage und Ideengeber war ein PHP-Script der Firma ZIPCodeWorld. Allerdings wollte ich die Berechnung lieber der MySQL Datenbank überlassen, da sich diese in meinen Augen etwas flotter durch größere Datenbestände wühlt.

Auf Basis der hier dargestellten Programmzeilen konnte ich für die Bildergalerie eine Abfrage realsieren, die für jeden Ort jeweils den nächsten Ort ermittelt und sogar die Entfernung anzeigt.

Allgemeine SQL-Abfrage

Zunächst habe ich das PHP-Script mit den Berechnungen in SQL übersetzt:

SELECT(
  DEGREES(
     ACOS(
        SIN(RADIANS( latitue1 )) * SIN(RADIANS( latitue2 ))
            +  COS(RADIANS( latitue1 )) * COS(RADIANS( latitue2 ))
            * COS(RADIANS( longitude1 - longitude2 ))
        ) * 60 * 1.1515
     )
  ) AS distance";

Anpassung an meine Bildergalerie

Aufbau der zugrunde liegenden Daten

Anschließend habe ich es auf meine Bedürfnisse angepasst. Hierzu sollte ich vielleicht zunächst erklären, dass meine Bildergalerie in Kategorien unterteilt ist. Jede Kategorie zeichnet sich durch einen eindeutigen Primärschlüssel (cid), Zugehörigkeit zu einem übergeordneten Kanal (ch), Gültigkeitsbereich (date_from, date_to), letzter Bearbeitungszeitraum (stamp) sowie optional die Geodaten (longitude, latitude) aus.

#
# Table structure for table `category`
#

CREATE TABLE `category` (
         `cid` int(10) unsigned NOT NULL auto_increment,
         `ch` int(11) NOT NULL default '0',
         `date_from` date NOT NULL default '0000-00-00',
         `date_to` date NOT NULL default '0000-00-00',
         `stamp` timestamp(14) NOT NULL,
         `latitude` float(7,4) default NULL,
         `longitude` float(7,4) default NULL,
         PRIMARY KEY (`cid`),
         UNIQUE KEY `id` (`cid`),
         KEY `ch` (`ch`),
         KEY `date_from` (`date_from`)
) TYPE=MyISAM COMMENT='The categories for each channel';

Jeder Kategorie lässt sich für jede Sprache ein Name (name) und die Beschreibung (description) hinterlegen.

#
# Table structure for table `category_name`
#

CREATE TABLE `category_name` (
         `id` int(10) unsigned NOT NULL auto_increment,
         `cid` int(10) unsigned NOT NULL default '0',
         `lid` int(10) unsigned NOT NULL default '0',
         `stamp` timestamp(14) NOT NULL,
         `name` varchar(100) NOT NULL default '',
         `description` text,
         PRIMARY KEY (`id`),
         UNIQUE KEY `id` (`id`),
         KEY `lid` (`lid`),
         KEY `cid` (`cid`),
         FULLTEXT KEY `name` (`name`,`description`)
) TYPE=MyISAM COMMENT='Names for the categories in each language';

Abfrage der SQL-Daten

Folgendes Script fragt nun die Entfernung von einem Fixpunkt zu allen in der Datenbank mit Geodaten hinterlegten Orten ab. Das Ergebnis wird in amerikanischen Meilen geliefert. Gleichzeitig wird mit den Entfernungen auch der Name des jeweiligen Ortes in der Sprache "2" abgefragt.

// Beispielwerte für Länge und Breite
$lat1=48.9000;
$lon1=-9.1833;

$ergebnis=mysql_query(("SELECT A.cid, B.name, (
  DEGREES(
     ACOS(
        (SIN(RADIANS(".$lat1.")) * SIN(RADIANS(A.latitude)))
         + (COS(RADIANS(".$lat1.")) * COS(RADIANS(A.latitude))
         * COS(RADIANS((".$lon1." - A.longitude))))
     ) * 60 * 1.1515
  )
) AS distance FROM category AS A, category_name AS B
WHERE A.cid=B.cid AND A.latitude IS NOT NULL AND A.longitude IS NOT NULL
AND A.date_from<=NOW() AND A.date_to>=NOW() AND B.lid=2
ORDER BY distance",$databaselink);

// Zeigt die Daten untereinander an
while($row=mysql_fetch_array($ergebnis)) {
   echo "Kategorie: (".$row[cid].") ".$row[name].": ".$row[distance]."
\n";
}

Quellen für Geodaten

Ich verwende generell zwei Quellen, um die bei mir hinterlegten Orte mit Metadaten zu versehen.

Global Gazetteer Version 2.1
Projekt OpenGeoDB (nur Orte in Deutschland)

Haftungshinweis: Trotz sorgfältiger inhaltlicher Kontrolle übernehme ich keine Haftung für die Inhalte externer Links. Für den Inhalt der verlinkten Seiten sind ausschließlich deren Betreiber verantwortlich.
Besuchen Sie auch meine Bildergalerie unter gallery.plogmann.net.
© Stefan Plogmann, 1996-2008