Instantly share code, notes, and snippets.

View HasJsonFields.php
namespace App\Traits;
trait HasJsonFields
public function newFromBuilder($attributes = [], $connection = null)
$model = parent::newFromBuilder($attributes, $connection);
# Environment-specific variables
# sourced by
# Database settings
View gist:5562174
truncate table agency;
LOAD DATA LOCAL INFILE 'agency.txt' INTO TABLE agency FIELDS TERMINATED BY ',' lines terminated by '\n' IGNORE 1 LINES;
truncate table calendar;
LOAD DATA LOCAL INFILE 'calendar.txt' INTO TABLE calendar FIELDS TERMINATED BY ',' lines terminated by '\n' IGNORE 1 LINES;
truncate table calendar_dates;
LOAD DATA LOCAL INFILE 'calendar_dates.txt' INTO TABLE calendar_dates FIELDS TERMINATED BY ',' lines terminated by '\n' IGNORE 1 LINES;
truncate table routes;
View gtfs_db
-- Database: `ocdata`
-- --------------------------------------------------------
-- Table structure for table `agency`
View getNextTripsForStop.sql
DROP PROCEDURE IF EXISTS `ocdata`.`getNextTripsForStop`$$
CREATE PROCEDURE `ocdata`.`getNextTripsForStop` (
IN SelectedStop VARCHAR(6),
IN SearchDateTime DATETIME
View ImportGTFSData.sql
Create a database in the same directory as the GTFS files by typing:
shell> sqlite3 gtfs_db
The following commands should be executed in the SQLite shell:
CREATE TABLE agency (agency_name TEXT, agency_url TEXT, agency_timezone TEXT, agency_lang TEXT);
CREATE TABLE stops (stop_id TEXT, stop_code INTEGER, stop_name TEXT, stop_desc TEXT, stop_lat REAL, stop_lon REAL, stop_street TEXT, stop_city TEXT, stop_region TEXT, stop_postcode TEXT, stop_country TEXT, zone_id INTEGER);
CREATE TABLE routes (route_id TEXT,route_short_name TEXT,route_long_name TEXT,route_desc TEXT,route_type INTEGER);
CREATE TABLE trips (route_id TEXT,service_id STRING,trip_id STRING,trip_headsign TEXT,block_id INTEGER);
CREATE TABLE stop_times (trip_id STRING,arrival_time TEXT,departure_time TEXT,stop_id TEXT,stop_sequence INTEGER,pickup_type INTEGER,drop_off_type INTEGER);
mongoimport --db <dbname> --collection agencies --type csv --headerline --file agency.txt
mongoimport --db <dbname> --collection calendardates --type csv --headerline --file calendar_dates.txt
mongoimport --db <dbname> --collection calendars --type csv --headerline --file calendar.txt
mongoimport --db <dbname> --collection routes --type csv --headerline --file routes.txt
mongoimport --db <dbname> --collection stoptimes --type csv --headerline --file stop_times.txt
mongoimport --db <dbname> --collection stops --type csv --headerline --file stops.txt
mongoimport --db <dbname> --collection trips --type csv --headerline --file trips.txt
View getNextBusTimesForStop.sql
SELECT s.stop_name, r.route_short_name, t.trip_headsign, st.arrival_time, strftime('%H:%M:%S',datetime('now','localtime')) AS CurrentTime, strftime('%Y%m%d',date('now')) AS CurrentDate, strftime('%H:%M:%S',datetime('now', '+60 Minute','localtime')) AS TimeMax
FROM stops s
INNER JOIN stop_times st ON s.stop_id = st.stop_id
INNER JOIN trips t ON st.trip_id = t.trip_id
INNER JOIN routes r ON t.route_id = r.route_id
INNER JOIN calendar c ON t.service_id = c.service_id
WHERE c.service_id IN (
SELECT service_id
FROM calendar c
WHERE Strftime('%Y%m%d',date('now')) BETWEEN c.start_date AND c.end_date