p69HL057

1<?php
2$dbhost        = "localhost";
3$dbuser        = "root";
4$dbpass        = "";
5$dbname        = "geoloc";
6$tablename     = "cities";
7$MAX_START_ROW = 5000; //Limitation of geonames.org    
8$ch            = curl_init();
9curl_setopt_array($ch, array(
10    CURLOPT_RETURNTRANSFER => true,
11    CURLOPT_FOLLOWLOCATION => true
12));
13$page        = 0;
14$total_found = 0; //Connect to database   
15try {
16    $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass, array(
17        PDO::ATTR_ERRMODE,
18        PDO::ERRMODE_EXCEPTION
19    ));
20}
21catch (PDOException $e) {
22    die("Aw snap, couldn't connect to database: " . $e->getMessage());
23}
24//Create table if needed    
25try {
26    $stmt = $dbh->prepare("CREATE TABLE IF NOT EXISTS $tablename         (city_id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,        city_name TEXT NOT NULL,        lat DOUBLE NOT NULL,        lon DOUBLE NOT NULL)");
27    $stmt->execute();
28}
29catch (PDOException $e) {
30    die("Could not create table $tablename: " . $e->getMessage());
31}
32//Now pinch that data and store it!   
33while ($page * 50 <= $MAX_START_ROW) {
34    curl_setopt($ch, CURLOPT_URL, "http://www.geonames.org/advanced-search.html?q=&country=GB&featureClass=P&continentCode=EU&startRow=" . ($page * 50));
35    $result = curl_exec($ch);
36    preg_match_all('/<as+href="/maps/google_(-?d*.?d*_-?d*.?d*).html">([^<]+)</a>/sim', $result, $matches);
37    for ($i = 0; $i < count($matches[0]); $i++) {
38        $city_name = $matches[2][$i];
39        list($lat, $lon) = explode("_", $matches[1][$i]);
40        echo "Inserting city <strong>" . $city_name . "</strong> (" . $lat . ", " . $lon . ")<br />";
41        try {
42            $stmt = $dbh->prepare("INSERT INTO $tablename (city_name, lat, lon) VALUES (?, ?, ?)");
43            $stmt->execute(array(
44                $city_name,
45                $lat,
46                $lon
47            ));
48        }
49        catch (PDOException $e) {
50            die("Could not insert row: " . $e->getMessage());
51        }
52    }
53    $total_found += count($matches[0]);
54    $page++;
55}
56echo "<br /><br />-------------- FINISHED<br /><br />TOTAL: " . $total_found . " / " . ($MAX_START_ROW + 50);
57$dbh = null;