1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | <?php if ( php_sapi_name() == "cli") { include ('include/common.inc.php'); $conn = pg_connect("dbname=transitdata user=postgres password=snmc host=localhost") or die('connection failed'); $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"); if ($tablename =="stop_times") { $stmt = $pdconn->prepare("insert into stop_times (trip_id,stop_id,stop_sequence,arrival_time,departure_time) values(:trip_id, :stop_id, :stop_sequence,:arrival_time,:departure_time);"); $stmt->bindParam(':trip_id',$trip_id); $stmt->bindParam(':stop_id',$stop_id); $stmt->bindParam(':stop_sequence',$stop_sequence); $stmt->bindParam(':arrival_time',$time); $stmt->bindParam(':departure_time',$time); } while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { if ($line == 0) { } else { $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") { // $query = "insert into $tablename (trip_id,stop_id,stop_sequence) values('{$data[0]}','{$data[3]}','{$data[4]}');"; $trip_id=$data[0]; $stop_id=$data[3]; $stop_sequence=$data[4]; $time=($data[1] == "" ? null : $data[1]); } } if ($tablename =="stop_times") { $stmt->execute(); } else { $result = pg_query($conn, $query); } $line++; if ($line % 10000 == 0) echo "$line records... ".date('c')."\n"; } fclose($handle); echo "Found a total of $line records in $file.\n"; } } } ?> |