| <?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"; |
| } | } |
| } | } |
| ?> | ?> |