Geo Targetting With PHP And MySQL

By Kevin Waterson 1724216 12 Comments

Geo Targetting

By Kevin Waterson

Abstract
The Database
Distance Between Two Points
Spatial Proximity
Spatial Proximity of a Zip/Post Code
Just Google it

Abstract

After publishing a database schema and data for US Cities Zip Codes PHPRO was swamped by requests on how to use this information for distance calculations and for geo targetting. Geo targetting allows developers to target end users by thier geographic location, given thier zip/post code or latitude and longitude. For example, if you know a users zip/post code you could retrieve a list of pizza shops with a 20 mile radius, then supply them with a google map on the location of each.

The Database

The database used for this tutorial is the database at US Cities Zip Codes. Other databases can be found containing the same information, some with more cities, some with cities from other countries. For the purpose of this tutorial, the schema will be the one at the give page.

Distance Between Two Points

On the surface of the Earth, there are no straight lines. Due to the spherical shape of the Earth, all distance calculations must take into account the arc described by a distance between two points. Simple trigonometry does not apply and we need to look more into spherical and hyperbolic geometry to achieve this.

The Earth is not perfectly circular either, with the shape being more ellipsoid making calculations even more complex. added to this, the arc is not the same at various meridians. To simplify this a median radius of the Earth will be used for calculations. Unless you need to caluculate to within a meter of a given point, this method will suffice.

Bernhard Riemann, in the nineteenth centuary, gave rise to spherical geometry from which the Great Circle Distance formulae evolved. It is this Great Circle Distance formulae that will be used in this tutorial.

Armed with this information, here is the way to calculate the distance between two give points, give the latitude and longitude.



<?php

/**
 *
 * @get distance from latitude and longitute
 *
 * @param float $lat_from
 *
 * @param float $long_from
 *
 * @param float $lat_to
 *
 * @param float *long_to
 *
 * @param $unit options k, m, n, Default k
 *
 * @return float
 *
 */
 function getRiemannDistance($lat_from, $long_from, $lat_to, $long_to, $unit='k'){
 /*** distance unit ***/
 switch ($unit):
 /*** miles ***/
 case 'm':
    $unit = 3963;
    break;
 /*** nautical miles ***/
 case 'n':
    $unit = 3444;
    break;
 default:
    /*** kilometers ***/
    $unit = 6371;
 endswitch;

 /*** 1 degree = 0.017453292519943 radius ***/
 $degreeRadius = deg2rad(1);
 
 /*** convert longitude and latitude to radians ***/
 $lat_from  *= $degreeRadius;
 $long_from *= $degreeRadius;
 $lat_to    *= $degreeRadius;
 $long_to   *= $degreeRadius;
 
 /*** apply the Great Circle Distance Formula ***/
 $dist = sin($lat_from) * sin($lat_to) + cos($lat_from)
 * cos($lat_to) * cos($long_from - $long_to);
 
 /*** radius of earth * arc cosine ***/
 return ($unit * acos($dist));
}
?>

If we were to gain the latitude and longitude from the database of two cities, the distance between them could be easily calculated with the function above. But.. why not have the database do the work for us? The same formulae can be used within a MySQL query to do the work for us.

Picking two cities at random, Grand Junction, MI and and Discovery Bay CA, which have latitude and longitude of:

	Grand Junction	Discovery Bay
latitude	42.376081	37.9085357
longitude	-86.054052	-121.6002291

Using the same formulae as the PHP in SQL the query to calculate the miles between two distances would be:

 SELECT 
 3963 * ACOS(
    SIN(RADIANS(42.376081)) * SIN(RADIANS(42.376081)) + COS(RADIANS(42.376081))  * COS(RADIANS(37.9085357)) * COS(RADIANS(-86.054052) - RADIANS(-121.6002291)))
 AS
 distance;

It is now a simple matter of constructing our function around the query



<?php

 function getRiemannDistance( $lat_from, $long_from, $lat_to, $long_to, $unit='k')
 {
    /*** distance unit ***/
    switch ($unit):
    /*** miles ***/
    case 'm':
        $unit = 3963;
        break;
    /*** nautical miles ***/
    case 'n':
        $unit = 3444;
        break;
    default:
        /*** kilometers ***/
        $unit = 6371;
    endswitch;


    /*** the sql ***/
    $sql = "SELECT :unit * ACOS( SIN(RADIANS(:lat_from)) * SIN(RADIANS(:lat_to)) + COS(RADIANS(:lat_from))  * COS(RADIANS(:lat_to)) * COS(RADIANS(:long_from) - RADIANS(:long_to))) AS distance";

    try
    {
        /*** an instance of PDO singleton ***/
        $stmt = db::getInstance()->prepare($sql);
        /*** bind the paramaters ***/
        $stmt->bindParam(':lat_from', $lat_from, PDO::PARAM_INT);
        $stmt->bindParam(':lat_to', $lat_to, PDO::PARAM_INT);
        $stmt->bindParam(':long_from', $long_from, PDO::PARAM_INT);
        $stmt->bindParam(':long_to', $long_to, PDO::PARAM_INT);
        $stmt->bindParam(':unit', $unit, PDO::PARAM_INT);

        /*** execute the query ***/
        $stmt->execute();

        /*** return the distance ***/
        return $stmt->fetch(PDO::FETCH_COLUMN);
    }
    catch( Exception $e )
    {
        return FALSE;
    }
}
?>

The above function does the same as the previous version, however, by moving the load to the database their is a gain in speed and memory usage over using a SELECT query to gain the latitude and longitude and then have PHP do the math.

It is important that we know this, because their must be a database call also to get information from the database, not only for distances between points, but for distances that fall within a given radius of a point.

Spacial Proximity

In the above scripts, methods of gaining the distance between two points is given. However, when geo targetting for advertising a single point is needed to specify targets with a radius around the central point..



<?php
/*
 *
 * @get cities within $distance 
 *
 * @param int $latitude
 *
 * @param int $longitude
 *
 * @param int $distance, default 25
 *
 * @param int $unit, default kilomenters
 *
 * @return int
 *
 */
 function getSpacialProximity( $latitude, $longitude, $distance=25, $unit='k')
 {
    /*** distance unit ***/
    switch ($unit):
    /*** miles ***/
    case 'm':
        $unit = 3963;
        break;
    /*** nautical miles ***/
    case 'n':
        $unit = 3444;
        break;
    default:
        /*** kilometers ***/
        $unit = 6371;
    endswitch;


    /*** the sql ***/
    $sql = "SELECT city_name, ( :unit * ACOS( COS( RADIANS(:latitude) ) * COS( RADIANS( city_latitude ) ) * COS( RADIANS( city_longitude ) - RADIANS(:longitude) ) + SIN( RADIANS(:latitude) ) * SIN( RADIANS( city_latitude ) ) ) ) AS distance FROM cities HAVING distance < :distance ORDER BY distance";

    try
    {
        /*** an instance of PDO singleton ***/
        $stmt = db::getInstance()->prepare($sql);
        /*** bind the paramaters ***/
        $stmt->bindParam(':latitude', $latitude, PDO::PARAM_INT);
        $stmt->bindParam(':longitude', $longitude, PDO::PARAM_INT);
        $stmt->bindParam('distance', $distance, PDO::PARAM_INT);
        $stmt->bindParam(':unit', $unit, PDO::PARAM_INT);

        /*** execute the query ***/
        $stmt->execute();
        /*** return the distance ***/
        return $stmt->fetchAll(PDO::FETCH_ASSOC); 
    }
    catch( Exception $e )
    {
        return FALSE;
    }
}
?>

Using the function above, now makes it simple to get a list of cities around a given town if the latitude and longitude are given. This time, the randomly chosen city is Cupertino, CA. The data fetched will be the cities within a ten mile radius.




/*** Cupertino lat and long ***/
$latitude = 37.317363;
$longitude = -122.038604;

/*** calculate the distance in miles ***/
$cities = getSpacialProximity($latitude, $longitude, 10, 'm');
?>
<table>
<tr><td>City Name</td><td>Distance</td></tr>
<?php

foreach( $cities as $city )
{
        echo '<tr><td>'.$city['city_name'].'</td><td>'.$city['distance'].'</td></tr>';
}
?>
</table>

Spatial Proximity of a Zip/Post Code

In the above sections spatial proximity has been based on latitude and longitude co-ordinates supplied from an external source. But if only a zip/post code is supplied, the latitude and longitude need to be based on those stored within the database. There are two methods to deal with this, using PHP or using MySQL

As the US cities table is only a small representation of the cities in the world, PHP could deal with the data searching required with little issue. However, when dealing with larger data sets, such as the all countries database which has over six million records, MySQL will be able to crunch the numbers many times faster than PHP. It is this method, using MySQL that will be shown here.

To accomplish this with MySQL stored procedures will be used to do that work for us. Two functions will be created to help with data converson of degrees to radians etc.

DELIMITER $

DROP FUNCTION IF EXISTS zip_deg2rad$
DROP FUNCTION IF EXISTS zip_gcd$
DROP FUNCTION IF EXISTS zip_distance$
DROP PROCEDURE IF EXISTS zip_radius$

CREATE FUNCTION zip_deg2rad(DEGREES DOUBLE) RETURNS DOUBLE
BEGIN
    RETURN DEGREES / (180 / PI()+0.000000000000000);
END$

CREATE FUNCTION zip_gcd(type ENUM('M', 'N', 'K'), src_lat DOUBLE, src_long DOUBLE, dst_lat DOUBLE, dst_long DOUBLE) RETURNS DOUBLE
BEGIN
    DECLARE temp DOUBLE;
   
    DECLARE STATUTE_MILES DECIMAL(5,1);
    DECLARE NAUTICAL_MILES DECIMAL(9,5);
    DECLARE KILOMETERS DECIMAL(5,1);
   
    SET STATUTE_MILES = 3963.0;
    SET NAUTICAL_MILES = 3437.74677;
    SET KILOMETERS = 6378.7;
   
    SET src_lat = zip_deg2rad(src_lat);
    SET src_long = zip_deg2rad(src_long);
    SET dst_lat = zip_deg2rad(dst_lat);
    SET dst_long = zip_deg2rad(dst_long);
   
    SET temp = ACOS(SIN(src_lat) * SIN(dst_lat) + COS(src_lat) * COS(dst_lat) * COS(dst_long - src_long));
   
    IF type = "M" THEN
        SET temp = STATUTE_MILES * temp;
    END IF;
   
    IF type = "N" THEN
        SET temp = NAUTICAL_MILES * temp;
    END IF;
   
    IF type = "K" THEN
        SET temp = KILOMETERS * temp;
    END IF;
   
    RETURN temp;
END$

CREATE FUNCTION zip_distance(type ENUM('M', 'N', 'K'), zip_start VARCHAR(5), zip_finish VARCHAR(5)) RETURNS DOUBLE
BEGIN
    DECLARE distance DOUBLE;
   
    DECLARE start_lat DOUBLE;
    DECLARE start_long DOUBLE;
    DECLARE finish_lat DOUBLE;
    DECLARE finish_long DOUBLE;
   
    SELECT city_latitude, city_longitude INTO start_lat, start_long FROM cities WHERE city_zip = zip_start;
    SELECT city_latitude, city_longitude INTO finish_lat, finish_long FROM cities WHERE city_zip = zip_finish;
   
    SELECT zip_gcd(type, start_lat, start_long, finish_lat, finish_long) INTO distance;
   
    RETURN distance;
END$

CREATE PROCEDURE zip_radius(IN type ENUM('M', 'N', 'K'), IN zip_start VARCHAR(5), IN radius INT, prec INT)
BEGIN
    DECLARE src_lat DOUBLE;
    DECLARE src_long DOUBLE;
   
    SELECT city_latitude, city_longitude INTO src_lat, src_long FROM cities WHERE city_zip = zip_start;

    SELECT city_zip, city_name, city_state, city_county, ROUND(zip_gcd(type, src_lat, src_long, city_latitude, city_longitude), prec) AS `distance`
    FROM cities
    WHERE city_zip != zip_start
    AND (POW((69.1 * (city_longitude - src_long) * COS(src_lat / 57.3)), 2) + POW((69.1 * (city_latitude - src_lat)), 2)) <= (radius * radius)
    ORDER BY `distance` ASC;
END$

DELIMITER ;

To set the gears in motion for the functions and stored procedures to work, only single database call is now needed, while MySQL will do what it does best, number crunching. By simply CALLing the zip_radius stored procedure from the PHP function, the returned results are give as they would be for an PDO result set. The function itself takes four arguements:

$zip: The zip/post code to search
$distance: The radius of the search
$precision: The number of decimal places to return
$unit: Miles, Kilometers or Nautical miles



<?php

/*
 *
 * @get spacial proximity based on zip/post code
 *
 * @param int $zip
 *
 * @param int $distance
 *
 * @param int $precision
 *
 * @param int $unit, default 'K'
 *
 * @return array
 *
 */
function getSpatialProximityByZip($zip, $distance, $precision, $unit='K')
{
    /*** the sql ***/
    $sql = "CALL zip_radius(:unit, :zip, :distance, :precision)";

    try
    {
        /*** an instance of PDO singleton ***/
        $stmt = db::getInstance()->prepare($sql);
        /*** bind the paramaters ***/
        $stmt->bindParam(':zip', $zip, PDO::PARAM_INT);
        $stmt->bindParam(':unit', $unit, PDO::PARAM_STR);
        $stmt->bindParam('distance', $distance, PDO::PARAM_INT);
        $stmt->bindParam(':precision', $precision, PDO::PARAM_INT);

        /*** execute the query ***/
        $stmt->execute();
        /*** return the distance ***/
        return $stmt->fetchAll(PDO::FETCH_ASSOC); 
    }
    catch( Exception $e )
    {
    echo $e->getMessage();
    return false;
    }
}
?>

All that is left now to do is call the function with a zip/post code and other arguements and see the results. For this example, Dallas, NC has been chosen with a radius of ten miles and a precision of two.



<?php

/*** call funciton with zip code ***/
$cities = getSpatialProximityByZip(28034, 10, 2, 'M');

/*** loop over the result set ***/
foreach( $cities as $city )
{
    /*** print the arrays ***/
        print_r($city);
}

?>

Just Google It!

With a small change to the stored procedure decribed above, the result sets can be made to include the latitude and longitude of the location in the result set. Why is this useful? With this information a Google map can be created to pin point each returned value.

DELIMITER $

DROP FUNCTION IF EXISTS zip_deg2rad$
DROP FUNCTION IF EXISTS zip_gcd$
DROP FUNCTION IF EXISTS zip_distance$
DROP PROCEDURE IF EXISTS zip_radius$

CREATE FUNCTION zip_deg2rad(DEGREES DOUBLE) RETURNS DOUBLE
BEGIN
    RETURN DEGREES / (180 / PI()+0.000000000000000);
END$

CREATE FUNCTION zip_gcd(type ENUM('M', 'N', 'K'), src_lat DOUBLE, src_long DOUBLE, dst_lat DOUBLE, dst_long DOUBLE) RETURNS DOUBLE
BEGIN
    DECLARE temp DOUBLE;
   
    DECLARE STATUTE_MILES DECIMAL(5,1);
    DECLARE NAUTICAL_MILES DECIMAL(9,5);
    DECLARE KILOMETERS DECIMAL(5,1);
   
    SET STATUTE_MILES = 3963.0;
    SET NAUTICAL_MILES = 3437.74677;
    SET KILOMETERS = 6378.7;
   
    SET src_lat = zip_deg2rad(src_lat);
    SET src_long = zip_deg2rad(src_long);
    SET dst_lat = zip_deg2rad(dst_lat);
    SET dst_long = zip_deg2rad(dst_long);
   
    SET temp = ACOS(SIN(src_lat) * SIN(dst_lat) + COS(src_lat) * COS(dst_lat) * COS(dst_long - src_long));
   
    IF type = "M" THEN
        SET temp = STATUTE_MILES * temp;
    END IF;
   
    IF type = "N" THEN
        SET temp = NAUTICAL_MILES * temp;
    END IF;
   
    IF type = "K" THEN
        SET temp = KILOMETERS * temp;
    END IF;
   
    RETURN temp;
END$

CREATE FUNCTION zip_distance(type ENUM('M', 'N', 'K'), zip_start VARCHAR(5), zip_finish VARCHAR(5)) RETURNS DOUBLE
BEGIN
    DECLARE distance DOUBLE;
   
    DECLARE start_lat DOUBLE;
    DECLARE start_long DOUBLE;
    DECLARE finish_lat DOUBLE;
    DECLARE finish_long DOUBLE;
   
    SELECT city_latitude, city_longitude INTO start_lat, start_long FROM cities WHERE city_zip = zip_start;
    SELECT city_latitude, city_longitude INTO finish_lat, finish_long FROM cities WHERE city_zip = zip_finish;
   
    SELECT zip_gcd(type, start_lat, start_long, finish_lat, finish_long) INTO distance;
   
    RETURN distance;
END$

CREATE PROCEDURE zip_radius(IN type ENUM('M', 'N', 'K'), IN zip_start VARCHAR(5), IN radius INT, prec INT)
BEGIN
    DECLARE src_lat DOUBLE;
    DECLARE src_long DOUBLE;
   
    SELECT city_latitude, city_longitude INTO src_lat, src_long FROM cities WHERE city_zip = zip_start;

    SELECT city_zip, city_name, city_state, city_county, city_latitude, city_longitude, ROUND(zip_gcd(type, src_lat, src_long, city_latitude, city_longitude), prec) AS `distance`
    FROM cities
    WHERE city_zip != zip_start
    AND (POW((69.1 * (city_longitude - src_long) * COS(src_lat / 57.3)), 2) + POW((69.1 * (city_latitude - src_lat)), 2)) <= (radius * radius)
    ORDER BY `distance` ASC;
END$

DELIMITER ;