Geographical

The below is only a demo. The Geographical behavior can be used with any data record for determining the number of miles or kilometers between 2 records.

// models/Zipcode.php

class Zipcode extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('zipcode', 'string', 255);
        $this->hasColumn('city', 'string', 255);
        $this->hasColumn('state', 'string', 2);
        $this->hasColumn('county', 'string', 255);
        $this->hasColumn('zip_class', 'string', 255);
    }

    public function setUp()
    {
        $this->actAs('Geographical');
    }
}

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
# schema.yml

# ...
Zipcode:
  actAs: [Geographical]
  columns:
    zipcode: string(255)
    city: string(255)
    state: string(2)
    county: string(255)
    zip_class: string(255)

Lets check the SQL that is generated by the above models:

// test.php

// ...
$sql = Doctrine_Core::generateSqlFromArray(array('Zipcode'));
echo $sql[0];

The above code would output the following SQL query:

CREATE TABLE zipcode (id BIGINT AUTO_INCREMENT, 
zipcode VARCHAR(255), 
city VARCHAR(255), 
state VARCHAR(2), 
county VARCHAR(255), 
zip_class VARCHAR(255), 
latitude DOUBLE, 
longitude DOUBLE, 
PRIMARY KEY(id)) ENGINE = INNODB

Notice how the Geographical behavior automatically adds the latitude and longitude columns to the records used for calculating distance between two records. Below you will find some example usage.

First lets retrieve two different zipcode records:

// test.php

// ...
$zipcode1 = Doctrine_Core::getTable('Zipcode')->findOneByZipcode('37209');
$zipcode2 = Doctrine_Core::getTable('Zipcode')->findOneByZipcode('37388');

Now we can get the distance between those two records by using the getDistance() method that the behavior provides:

// test.php

// ...
echo $zipcode1->getDistance($zipcode2, $kilometers = false);

The 2nd argument of the getDistance() method is whether or not to return the distance in kilometers. The default is false.

Now lets get the 50 closest zipcodes that are not in the same city:

// test.php

// ...
$q = $zipcode1->getDistanceQuery();

$q->orderby('miles asc')
  ->addWhere($q->getRootAlias() . '.city != ?', $zipcode1->city)
  ->limit(50);

echo $q->getSqlQuery();

The above call to getSql() would output the following SQL query:

SELECT 
z.id AS z__id, 
z.zipcode AS z__zipcode, 
z.city AS z__city, 
z.state AS z__state, 
z.county AS z__county, 
z.zip_class AS z__zip_class, 
z.latitude AS z__latitude, 
z.longitude AS z__longitude, 
((ACOS(SIN(* PI() / 180) * SIN(z.latitude * PI() / 180) + COS(* PI() / 180) * COS(z.latitude * PI() / 180) * COS((- z.longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS z__0, 
((ACOS(SIN(* PI() / 180) * SIN(z.latitude * PI() / 180) + COS(* PI() / 180) * COS(z.latitude * PI() / 180) * COS((- z.longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344) AS z__1 
FROM zipcode z 
WHERE z.city != ? 
ORDER BY z__0 asc 
LIMIT 50

Notice how the above SQL query includes a bunch of SQL that we did not write. This was automatically added by the behavior to calculate the number of miles between records.

Now we can execute the query and use the calculated number of miles values:

// test.php

// ...
$result = $q->execute();

foreach ($result as $zipcode) {
  echo $zipcode->city . " - " . $zipcode->miles . "<br/>";
  // You could also access $zipcode->kilometers
}

Get some sample zip code data to test this

http://www.populardata.com/zip_codes.zip

Download and import the csv file with the following function:

// test.php

// ...
function parseCsvFile($file, $columnheadings = false, $delimiter = ',', $enclosure = "\"")
{
    $row = 1;
    $rows = array();
    $handle = fopen($file, 'r');

    while (($data = fgetcsv($handle, 1000, $delimiter, $enclosure)) !== FALSE) {

        if (!($columnheadings == false) && ($row == 1)) {
            $headingTexts = $data;
        } elseif (!($columnheadings == false)) {
            foreach ($data as $key => $value) {
                unset($data[$key]);
                $data[$headingTexts[$key]] = $value;
            }
            $rows[] = $data;
        } else {
            $rows[] = $data;
        }
        $row++;
    }

    fclose($handle);
    return $rows;
}

$array = parseCsvFile('zipcodes.csv', false);

foreach ($array as $key => $value) {
    $zipcode = new Zipcode();
    $zipcode->fromArray($value);
    $zipcode->save();
}