Calculate geographic distances in SQL

Again I added a new feature to my picture gallery… I wanted to integrate the calculation of the distance between two or more points on the globe.

Some months ago I already integrated the possibility to save longitude and latitude for places in the database. Until now this information was hidden under the surface of the XHTML page in so called RDF and meta header tags. This meant no direct benefit for the users as they were not able to use this information when browsing through the galleries. Unfortunately it is also not clear if any of the larger search engines would integrate support for geo tags.

Inspiration for the new distance calculation function was a PHP script of ZIPCodeWorld. However I wanted the MySQL server to do the calculations as I am sure that the database is much faster when it comes to selecting and calculating larger amounts of data.

Based on the few lines of code shown here, I was able to integrate a function into my picture gallery showing neighbouring places and even calculating the approximate distance.

Abstract SQL query

First of all I translated the PHP code into SQL. This SELECT query is only an example how the calculations generally work. Please remind that latitude and logitude must be given in degrees.

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

Introduction to the database structureAdaptation to my picture gallery

Based on the abstract SQL query I started to adapt the new idea to my existing database structure. To give you can example how a working query could look like, I will explain in brief how my picture gallery works.
The picture gallery is divided into categories. Each category has a unique key (cid), period of validity (date_from, date_to), date of last editing (stamp) and optional geo data information (longitude, latitude)

#
# 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';

 

#
# 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';

You can add name and description for all supported languages (lid) for each category (cid).

Query the database

The following PHP script now calculates the distances between a defined point and all geo-tagged places in the database. Results are in American miles. The query also shows the names of all geo-tagged places in language nr. “2” (lid=2).

// Predefined coordinates
$lat1=48.9000;
$lon1=-9.1833;
$result=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);
// Shows the results as an unformatted list
while($row=mysql_fetch_array($result)) {
   echo "Category: (".$row[cid].") ".$row[name].": ".$row[distance]."\n";
}

Sources for geo data

Usually I am using two sources for coordinates in my picture gallery: