--- a/updatedb.php +++ b/updatedb.php @@ -1,61 +1,140 @@ <?php -include ('include/common.inc.php'); -$conn = pg_connect("dbname=transitdata user=postgres password=snmc host=localhost") or die('connection failed'); + +/* + * Copyright 2010,2011 Alexander Sadleir + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + */ +if (php_sapi_name() == "cli") { + include ('include/common.inc.php'); + $pdconn = new PDO("pgsql:dbname=transitdata;user=postgres;password=snmc;host=localhost"); + + /* + delete from agency; + delete from calendar; + delete from calendar_dates; + delete from routes; + delete from shapes; + delete from stop_times; + delete from stops; + delete from trips; + */ + // Unzip cbrfeed.zip, import all csv files to database -$unzip = true; -$zip = zip_open(dirname(__FILE__) . "/cbrfeed.zip"); -$tmpdir = "/tmp/cbrfeed/"; -mkdir($tmpdir); -if ($unzip) { -if (is_resource($zip)) { - while ($zip_entry = zip_read($zip)) { - $fp = fopen($tmpdir . zip_entry_name($zip_entry) , "w"); - if (zip_entry_open($zip, $zip_entry, "r")) { - echo "Extracting " . zip_entry_name($zip_entry) . "\n"; - $buf = zip_entry_read($zip_entry, zip_entry_filesize($zip_entry)); - fwrite($fp, "$buf"); - zip_entry_close($zip_entry); - fclose($fp); - } - } - zip_close($zip); -} -} - -foreach (scandir($tmpdir) as $file) { - if (!strpos($file, ".txt") === false) { - $fieldseparator = ","; - $lineseparator = "\n"; - $tablename = str_replace(".txt", "", $file); - echo "Opening $file \n"; - $line = 0; - $handle = fopen($tmpdir . $file, "r"); - while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { - if ($line > 0) { - $query = "insert into $tablename values("; - $valueCount = 0; - foreach ($data as $value) { - $query.=($valueCount >0 ? "','" :"'").pg_escape_string($value); - $valueCount++; - } - if ($tablename == "stops") { - $query.= "', ST_GeographyFromText('SRID=4326;POINT({$data[2]} {$data[0]})'));"; - } else { - $query.= "');"; - } - if ($tablename =="stop_times" && $data[1] == "") { - $query = "insert into $tablename (trip_id,stop_id,stop_sequence) values('{$data[0]}','{$data[3]}','{$data[4]}');"; - } - - } - $result = pg_query($conn, $query); - $line++; - if ($line % 10000 == 0) echo "$line records... \n"; - } - fclose($handle); - echo "Found a total of $line records in $file.\n"; + $unzip = false; + $zip = zip_open(dirname(__FILE__) . "/cbrfeed.zip"); + $tmpdir = "c:/tmp/cbrfeed/"; + mkdir($tmpdir); + if ($unzip) { + if (is_resource($zip)) { + while ($zip_entry = zip_read($zip)) { + $fp = fopen($tmpdir . zip_entry_name($zip_entry), "w"); + if (zip_entry_open($zip, $zip_entry, "r")) { + echo "Extracting " . zip_entry_name($zip_entry) . PHP_EOL; + $buf = zip_entry_read($zip_entry, zip_entry_filesize($zip_entry)); + fwrite($fp, "$buf"); + zip_entry_close($zip_entry); + fclose($fp); + } + } + zip_close($zip); + } + } - } + foreach (scandir($tmpdir) as $file) { + $headers = Array(); + if (!strpos($file, ".txt") === false) { + $fieldseparator = ","; + $lineseparator = PHP_EOL; + $tablename = str_replace(".txt", "", $file); + echo "Opening $file \n"; + $line = 0; + $handle = fopen($tmpdir . $file, "r"); + + $distance = 0; + $lastshape = 0; + $lastlat = 0; + $lastlon = 0; + $stmt = null; + while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { + if ($line == 0) { + $headers = array_values($data); + if ($tablename == "stops") { + $headers[] = "position"; + } + if ($tablename == "shapes") { + $headers[] = "shape_pt"; + } + $query = "insert into $tablename ("; + $valueCount = 0; + foreach ($headers as $value) { + $query.=($valueCount > 0 ? "," : "") . pg_escape_string($value); + $valueCount++; + } + $query.= ") values( "; + $valueCount = 0; + foreach ($data as $value) { + $query.=($valueCount > 0 ? "," : "") . '?'; + $valueCount++; + } + if ($tablename == "stops") { + $query.= ", ST_GeographyFromText(?));"; + } else if ($tablename == "shapes") { + $query.= ", ST_GeographyFromText(?));"; + } else { + $query.= ");"; + } + + echo $query; + $stmt = $pdconn->prepare($query); + } else { + $values = array_values($data); + if ($tablename == "stops") { + // Coordinate values are out of range [-180 -90, 180 90] + $values[] = 'SRID=4326;POINT('.$values[5].' '.$values[4].')'; + } + if ($tablename == "shapes") { + if ($data[0] != $lastshape) { + $distance = 0; + $lastshape = $data[0]; + } else { + $distance += distance($lastlat, $lastlon, $data[1], $data[2]); + } + $lastlat = $data[1]; + $lastlon = $data[2]; + + $values[4] = $distance; + $values[] = 'SRID=4326;POINT('.$values[2].' '.$values[1].')'; + } +if (substr($values[1],0,2) == '24') $values[1] = "23:59:59"; +if (substr($values[2],0,2) == '24') $values[2] = "23:59:59"; + $stmt->execute($values); + $err = $pdconn->errorInfo(); + if ($err[2] != "" && strpos($err[2], "duplicate key") === false) { + print_r($values); + print_r($err); + die("terminated import due to db error above"); + } + } + $line++; + if ($line % 10000 == 0) + echo "$line records... " . date('c') . PHP_EOL; + } + fclose($handle); + $stmt->closeCursor(); + echo "Found a total of $line records in $file.\n"; + } + } } ?>