Using the MaxMind geoip database - Part 3 - Getting the longitude and latitudes

I thought this was going to take me ages and I was going to have to spend year finding a database of coorditates for countries. I was wrong, my search led me to the MaxMind site again. Their data comes from the CIA World Fact Book, but they’ve compiled it into a nice list for us.

I’m over simplifying the situation again here, but I’m created a simple table to which we’ll match up country codes from the geo_ip data to the country codes in the latlon list. Later I think I’ll be delving into a proper country database with foreign keys and stuff. But not now. Firstly this program will get the codes into the database. Place those codes into a file and upload to your site, then run this php code.

<?php
/*
 * install_country_coords.php - For installing the country coordinates from
 *                              from www.maxmind.com
 * - Chris Andrews (http://www.toxicbyte.com/)
 *
 * History:
 * 02/09/2006
 *  - First edition
 */

$db_host = "xxxxxx";
$db_user = "xxxxxx";
$db_pass = "xxxxxx";
$db_name = "xxxxxx";

$file_location = "/path/to/CountryCoords.csv";

// Drop the table and it’s contents from the database
$sql_drop_table = "DROP TABLE IF EXISTS country_coords;";

/*
 Create the table that will contain the following:
    - ISO 3166 Country Code
    - Latitude
    - Longtitude
*/
$sql_create_table = <<<SQL
CREATE TABLE country_coords
(
    country_code    CHAR(2)      NOT NULL,
    latitude        NUMERIC(3,5) NOT NULL,
    longitude       NUMERIC(3,5) NOT NULL
);
SQL;

// Insert the row into the database using bound parameters
$sql_insert_row = <<<SQL
INSERT INTO country_coords
(
    country_code,
    latitude,
    longitude
)
VALUES
(
    ‘%s’,
    ‘%s’,
    ‘%s’
);
SQL;

$dbh = mysql_connect($db_host, $db_user, $db_pass)
    or die(‘I cannot connect to the database because: ‘ . mysql_error());

mysql_select_db($db_name);
?>
Dropping old table<br />
<?php
$result = mysql_query($sql_drop_table)
    or die(‘Query failed: ‘ . mysql_error());
?>

Creating new table<br />
<?php
$result = mysql_query($sql_create_table)
    or die(‘Query failed: ‘ . mysql_error());
?>

Open file for reading<br />
<?php
    if (! $input_file = fopen($file_location, "r"))
    {
        print "No openzee file!";
        exit;
    }
?>

Reading file, line by line into database<br />
<?php
$break_at_counter = 0;
$break_at_value = -1;
$counter = 0;
while (($data = fgetcsv($input_file, 1000, ",")) !== FALSE)
{
    if ($break_at_value > 0 and $break_at_counter++ >= $break_at_value)
    {
        break;
    }    

    $row = array_map(‘mysql_real_escape_string’, $data);
    $result = mysql_query(vsprintf($sql_insert_row, $row))
        or die(‘Query failed: ‘ . mysql_error() . "<br />" . vsprintf($sql_insert_row, $row));

    #vprintf("Done: %s, %s, %s, %s, %s, %s<br />", $row);
    if ($counter++ % 100 == 0)
    {
        print ". ";
    }
}
print "<br />nDone!<br />n";
?>

Closing the database<br />
<?php
    mysql_close($dbh);
?>

Technorati Tags: maxmind, geoip, latitude, longtitude

Toxic Byte

Using the MaxMind geoip database - Part 3 - Getting the longitude and latitudes

Home > About This Post

Post a Comment