<?php |
<?php |
include ('include/common.inc.php'); |
include ('include/common.inc.php'); |
//$conn = pg_connect("dbname=transitdata user=postgres password=snmc host=localhost") or die('connection failed'); |
$conn = pg_connect("dbname=transitdata user=postgres password=snmc host=localhost") or die('connection failed'); |
// Unzip cbrfeed.zip, import all csv files to database |
// Unzip cbrfeed.zip, import all csv files to database |
$unzip = false; |
$unzip = true; |
$zip = zip_open(dirname(__FILE__) . "/cbrfeed.zip"); |
$zip = zip_open(dirname(__FILE__) . "/cbrfeed.zip"); |
$tmpdir = "/tmp/cbrfeed/"; |
$tmpdir = "/tmp/cbrfeed/"; |
mkdir($tmpdir); |
mkdir($tmpdir); |
if ($unzip) { |
if ($unzip) { |
if (is_resource($zip)) { |
if (is_resource($zip)) { |
while ($zip_entry = zip_read($zip)) { |
while ($zip_entry = zip_read($zip)) { |
$fp = fopen($tmpdir . zip_entry_name($zip_entry) , "w"); |
$fp = fopen($tmpdir . zip_entry_name($zip_entry) , "w"); |
if (zip_entry_open($zip, $zip_entry, "r")) { |
if (zip_entry_open($zip, $zip_entry, "r")) { |
echo "Extracting " . zip_entry_name($zip_entry) . "\n"; |
echo "Extracting " . zip_entry_name($zip_entry) . "\n"; |
$buf = zip_entry_read($zip_entry, zip_entry_filesize($zip_entry)); |
$buf = zip_entry_read($zip_entry, zip_entry_filesize($zip_entry)); |
fwrite($fp, "$buf"); |
fwrite($fp, "$buf"); |
zip_entry_close($zip_entry); |
zip_entry_close($zip_entry); |
fclose($fp); |
fclose($fp); |
} |
|
} |
} |
zip_close($zip); |
|
} |
} |
|
zip_close($zip); |
} |
} |
|
} |
|
|
foreach (scandir($tmpdir) as $file) { |
foreach (scandir($tmpdir) as $file) { |
if (!strpos($file, ".txt") === false) { |
if (!strpos($file, ".txt") === false) { |
$fieldseparator = ","; |
$fieldseparator = ","; |
$lineseparator = "\n"; |
$lineseparator = "\n"; |
$tablename = str_replace(".txt", "", $file); |
$tablename = str_replace(".txt", "", $file); |
echo "Opening $tmpdir . $file \n"; |
echo "Opening $file \n"; |
$line = 0; |
$line = 0; |
$handle = fopen($tmpdir . $file, "r"); |
$handle = fopen($tmpdir . $file, "r"); |
$stmt = null; |
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { |
$stmt_noarrival = $conn->prepare("insert into stop_times (trip_id,stop_id,stop_sequence) values(? ? ?);"); |
if ($line > 0) { |
while (($columns = fgetcsv($handle, 1000, ",")) !== FALSE) { |
|
if ($line == 0) { |
|
$query = "insert into $tablename values("; |
$query = "insert into $tablename values("; |
$valueCount = 0; |
$valueCount = 0; |
foreach ($columns as $value) { |
foreach ($data as $value) { |
$query.= ($valueCount >0 ? "," :"")." ? "; |
$query.=($valueCount >0 ? "','" :"'").pg_escape_string($value); |
$valueCount++; |
$valueCount++; |
} |
} |
if ($tablename == "stops") { |
if ($tablename == "stops") { |
$query.= ", ST_GeographyFromText('SRID=4326;POINT(? ?)'));"; |
$query.= "', ST_GeographyFromText('SRID=4326;POINT({$data[2]} {$data[0]})'));"; |
} |
} else { |
else { |
$query.= "');"; |
$query.= ");"; |
} |
} |
if ($tablename =="stop_times" && $data[1] == "") { |
$stmt = $conn->prepare($query); |
$query = "insert into $tablename (trip_id,stop_id,stop_sequence) values('{$data[0]}','{$data[3]}','{$data[4]}');"; |
|
} |
|
|
} |
} |
else { |
$result = pg_query($conn, $query); |
$data = $columns; |
|
if ($tablename == "stops") { |
|
$data[] = $data[2]; |
|
$data[] = $data[0]; |
|
} |
|
if ($tablename == "stop_times" && $data[1] == "") { |
|
$stmt_noarrival->execute(Array( |
|
$data[0], |
|
$data[3], |
|
$data[4] |
|
)); |
|
} |
|
else { |
|
$stmt->execute($data); |
|
} |
|
} |
|
$line++; |
$line++; |
if ($line % 10000 == 0) echo "$line records... \n"; |
if ($line % 10000 == 0) echo "$line records... \n"; |
} |
} |
fclose($handle); |
fclose($handle); |
echo "Found a total of $line records in $file.\n"; |
echo "Found a total of $line records in $file.\n"; |
|
|
} |
} |
} |
} |
?> |
?> |
|
|