Skip to content

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);
dsamojlenko /
Last active Aug 29, 2015
In place upgrade script for drupal/wetkit distribution
# Environment-specific variables
# sourced by
# Database settings
dsamojlenko / gist:5562174
Created May 12, 2013
MySQL - import GTFS data
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;
dsamojlenko / gtfs_db
Last active Dec 10, 2015
GTFS MySQL db creation script. This script only contains a subset of the GTFS tables that I needed, but you can probably figure out the rest. The indices, particularly the ones on the stop_times table, are important - queries will run really slow without them.
View gtfs_db
-- Database: `ocdata`
-- --------------------------------------------------------
-- Table structure for table `agency`
dsamojlenko / getNextTripsForStop.sql
Last active Feb 13, 2017
MySQL Stored Procedure to get upcoming bus times for a stop from GTFS data. This query includes buses that started running before midnight on the night before but continue through the current day. It also includes service additions and removals from calendar_dates. WARNING: without the proper indices, this query can be VERY slow. See my other Gi…
View getNextTripsForStop.sql
DROP PROCEDURE IF EXISTS `ocdata`.`getNextTripsForStop`$$
CREATE PROCEDURE `ocdata`.`getNextTripsForStop` (
IN SelectedStop VARCHAR(6),
IN SearchDateTime DATETIME
dsamojlenko / ImportGTFSData.sql
Last active Mar 6, 2016
Import GTFS data to SQLite (need to remove header row after import). These commands should be entered in a SQLite command-line shell.
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);
dsamojlenko /
Last active Nov 8, 2020
Import GTFS data to MongoDB
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
dsamojlenko / getNextBusTimesForStop.sql
Last active Dec 10, 2015
SQLite query to get upcoming bus times for the next hour for a bus stop from GTFS data.
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