Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE IF EXISTS gtfs_ttc_agency;
- CREATE TABLE gtfs_ttc_agency(
- agency_id int,
- agency_name text,
- agency_url text,
- agency_timezone text,
- agency_lang text,
- agency_phone text,
- agency_fare_url text
- );
- COPY gtfs_ttc_agency FROM
- '/home/nate/gtfs/agency.txt' CSV HEADER;
- DROP TABLE IF EXISTS gtfs_ttc_calendar;
- CREATE TABLE gtfs_ttc_calendar(
- service_id smallint,
- monday boolean,
- tuesday boolean,
- wednesday boolean,
- thursday boolean,
- friday boolean,
- saturday boolean,
- sunday boolean,
- start_date text, -- todo
- end_date text -- todo
- );
- COPY gtfs_ttc_calendar FROM
- '/home/nate/gtfs/calendar.txt' CSV HEADER;
- DROP TABLE IF EXISTS gtfs_ttc_calendar_dates;
- CREATE TABLE gtfs_ttc_calendar_dates(
- service_id smallint,
- "date" text,
- exception_type smallint
- );
- COPY gtfs_ttc_calendar_dates FROM
- '/home/nate/gtfs/calendar_dates.txt' CSV HEADER;
- DROP TABLE IF EXISTS gtfs_ttc_routes;
- CREATE TABLE gtfs_ttc_routes (
- route_id int,
- agency_id int,
- route_short_name text,
- route_long_name text,
- route_desc text,
- route_type smallint,
- route_url text,
- route_color text,
- route_text_color text
- );
- COPY gtfs_ttc_routes FROM
- '/home/nate/gtfs/routes.txt' CSV HEADER;
- DROP TABLE IF EXISTS gtfs_ttc_shapes;
- CREATE TABLE gtfs_ttc_shapes (
- shape_id int,
- shape_pt_lat numeric,
- shape_pt_lon numeric,
- shape_pt_sequence smallint,
- shape_dist_traveled real
- );
- COPY gtfs_ttc_shapes FROM
- '/home/nate/gtfs/shapes.txt' CSV HEADER;
- -- add geometry field
- ALTER TABLE gtfs_ttc_shapes ADD COLUMN geom geography(POINT,4326);
- UPDATE gtfs_ttc_shapes SET geom = ST_MakePoint(shape_pt_lon,shape_pt_lat);
- DROP TABLE IF EXISTS gtfs_ttc_stops;
- CREATE TABLE gtfs_ttc_stops (
- stop_id integer,
- stop_code text,
- stop_name text,
- stop_desc text,
- stop_lat numeric,
- stop_lon numeric,
- zone_id smallint,
- stop_url text,
- location_type smallint,
- parent_station smallint,
- wheelchair_boarding smallint
- );
- COPY gtfs_ttc_stops FROM
- '/home/nate/gtfs/stops.txt' CSV HEADER;
- -- add geometry field
- ALTER TABLE gtfs_ttc_stops ADD COLUMN geom geography(POINT,4326);
- UPDATE gtfs_ttc_stops SET geom = ST_MakePoint(stop_lon,stop_lat);
- DROP TABLE IF EXISTS gtfs_ttc_stop_times;
- CREATE TABLE gtfs_ttc_stop_times (
- trip_id int,
- arrival_time interval,
- departure_time interval,
- stop_id int,
- stop_sequence smallint,
- stop_headsign text,
- pickup_type smallint,
- drop_off_type smallint,
- shape_dist_traveled real
- );
- COPY gtfs_ttc_stop_times FROM
- '/home/nate/gtfs/stop_times.txt' CSV HEADER;
- DROP TABLE IF EXISTS gtfs_ttc_trips;
- CREATE TABLE gtfs_ttc_trips (
- route_id int,
- service_id smallint,
- trip_id integer,
- trip_headsign text,
- trip_short_name text,
- direction_id text,
- block_id int,
- shape_id int,
- wheelchair_accessible smallint
- );
- COPY gtfs_ttc_trips FROM
- '/home/nate/gtfs/trips.txt' CSV HEADER;
- -- add geometry field
- ALTER TABLE gtfs_ttc_trips ADD COLUMN geom geometry(LINESTRING,4326);
- UPDATE gtfs_ttc_trips AS trip SET geom = shape
- FROM (
- SELECT
- shape_id,
- ST_MakeLine(geom::geometry ORDER BY shape_pt_sequence ASC) AS shape
- FROM gtfs_ttc_shapes
- GROUP BY shape_id
- ) AS sub
- WHERE sub.shape_id = trip.shape_id;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement