log.out

Archive:  gtfs.zip
  inflating: agency.txt              
  inflating: calendar.txt            
  inflating: routes.txt              
  inflating: stops.txt               
  inflating: stop_times.txt          
  inflating: transfers.txt           
  inflating: trips.txt               
  inflating: links.txt               
  inflating: feed_info.txt           
DROP TABLE  IF EXISTS ukrail_agency;
DROP TABLE
DROP TABLE  IF EXISTS ukrail_stops;
DROP TABLE
DROP TABLE  IF EXISTS ukrail_routes;
DROP TABLE
DROP TABLE  IF EXISTS ukrail_trips;
DROP TABLE
DROP TABLE  IF EXISTS ukrail_stop_times;
DROP TABLE
DROP TABLE  IF EXISTS ukrail_stopsgeom;
DROP TABLE
DROP TABLE  IF EXISTS ukrail_lines;
DROP TABLE
DROP TABLE  IF EXISTS ukrail_lines_trips;
DROP TABLE
DROP TABLE  IF EXISTS ukrail_lines_exploded;
DROP TABLE
DROP TABLE  IF EXISTS ukrail_lines_segments;
DROP TABLE
DROP TABLE  IF EXISTS ukrail_lines_final_old;
DROP TABLE
DROP TABLE  IF EXISTS ukrail_distinct_agencies;
DROP TABLE
DROP TABLE  IF EXISTS ukrail_distinct_agencies_parent;
DROP TABLE
DROP TABLE  IF EXISTS ukrail_distinct_parent;
DROP TABLE
CREATE TABLE ukrail_agency
(
  agency_id character varying,
  agency_name character varying,
  agency_url character varying,
  agency_timezone character varying,
  agency_lang character varying,
  agency_phone character varying
);
CREATE TABLE

CREATE TABLE ukrail_routes
(
  route_id character varying,
  agency_id character varying,
  route_short_name character varying,
  route_long_name character varying,
  route_type character varying
);
CREATE TABLE

CREATE TABLE ukrail_trips
(
  route_id character varying,
  service_id character varying,
  trip_id integer,
  wheelchair_accessible character varying,
bikes_allowed character varying,
train_uid character varying,
train_status character varying,
train_category character varying,
train_identity character varying,
headcode character varying,
train_service_code character varying,
portion_id character varying,
power_type character varying,
timing_load character varying,
speed character varying,
oper_chars character varying,
train_class character varying,
sleepers character varying,
reservations character varying,
catering character varying,
service_branding character varying,
stp_indicator character varying,
uic_code character varying,
atoc_code character varying,
applicable_timetable character varying
);
CREATE TABLE

CREATE TABLE ukrail_stop_times
(
  trip_id integer,
  arrival_time character varying,
  departure_time character varying,
  stop_id character varying,
  stop_sequence integer,
  pickup_type character varying,
drop_off_type character varying,
wtt_arrival_time character varying,
wtt_departure_time character varying,
platform character varying,
line character varying,
path character varying,
activity character varying,
engineering_allowance character varying,
pathing_allowance character varying,
performance_allowance character varying
);
CREATE TABLE

\copy ukrail_agency FROM 'agency.txt' DELIMITER ',' CSV HEADER;
\copy ukrail_routes FROM 'routes.txt' DELIMITER ',' CSV HEADER;
\copy ukrail_trips FROM 'trips.txt' DELIMITER ',' CSV HEADER;
\copy ukrail_stop_times FROM 'stop_times.txt' DELIMITER ',' CSV HEADER;
vacuum analyze ukrail_agency;
VACUUM
vacuum analyze ukrail_stops;
VACUUM
vacuum analyze ukrail_routes;
VACUUM
vacuum analyze ukrail_trips;
VACUUM
vacuum analyze ukrail_stop_times;
VACUUM
CREATE TABLE ukrail_distinct_agencies AS 
 SELECT DISTINCT ukrail_agency.agency_name
   FROM ukrail_agency;
SELECT 30

vacuum analyze ukrail_distinct_agencies;
VACUUM

CREATE TABLE ukrail_distinct_agencies_parent AS 
 SELECT ukrail_distinct_agencies.agency_name, ukrail_manual_agency_parent.parent_company
   FROM ukrail_distinct_agencies, ukrail_manual_agency_parent
WHERE ukrail_distinct_agencies.agency_name = ukrail_manual_agency_parent.agency_name;
SELECT 30

vacuum analyze ukrail_distinct_agencies_parent;
VACUUM

CREATE TABLE ukrail_distinct_parent AS 
 SELECT DISTINCT ukrail_manual_agency_parent.parent_company
   FROM ukrail_manual_agency_parent;
SELECT 13

vacuum analyze ukrail_distinct_parent;
VACUUM

CREATE TABLE ukrail_stopsgeom AS
SELECT ukrail_stop_times.trip_id, ukrail_stop_times.stop_id, ukrail_stop_times.stop_sequence, ukrail_stops.geom, ukrail_stops.stop_name
FROM ukrail_stops, ukrail_stop_times
WHERE ukrail_stops.stop_id = ukrail_stop_times.stop_id;
SELECT 2746414

vacuum analyze ukrail_stopsgeom;
VACUUM

CREATE TABLE ukrail_lines AS
SELECT stops.trip_id, ST_MakeLine(stops.geom ORDER BY stop_sequence) As geom
FROM ukrail_stopsgeom As stops
GROUP BY stops.trip_id;
SELECT 244137
CREATE INDEX ukrail_lines_geom_gist ON ukrail_lines USING GIST ( geom );
CREATE INDEX

vacuum analyze ukrail_lines;
VACUUM

CREATE TABLE ukrail_lines_trips AS
SELECT DISTINCT
ukrail_lines.geom, ukrail_routes.route_long_name, ukrail_agency.agency_name
FROM
ukrail_lines,
ukrail_trips,
ukrail_routes,
ukrail_agency
WHERE
ukrail_lines.trip_id = ukrail_trips.trip_id
AND
ukrail_trips.route_id = ukrail_routes.route_id
AND
ukrail_routes.agency_id = ukrail_agency.agency_id
AND
route_type IN ('0','1','2','4','5','6');
SELECT 4808
vacuum analyze ukrail_lines_trips;
VACUUM

CREATE TABLE ukrail_lines_exploded AS
SELECT ukrail_lines_trips.agency_name, 
       (ST_GetVectorSQL(ukrail_lines_trips.geom)).*
  FROM ukrail_lines_trips;
SELECT 50298
vacuum analyze ukrail_lines_exploded;
VACUUM
  
CREATE TABLE ukrail_lines_segments AS
SELECT DISTINCT agency_name, ST_SetSRID(ST_MakeLine(ST_MakePoint((startcoord).x,(startcoord).y)::geometry,ST_MakePoint((endcoord).x, (endcoord).y)::geometry),4326) as geom
From ukrail_lines_exploded;
SELECT 4846
vacuum analyze ukrail_lines_segments;
VACUUM

ALTER TABLE IF EXISTS ukrail_lines_final RENAME  TO ukrail_lines_final_old;
ALTER TABLE

CREATE TABLE ukrail_lines_final AS
SELECT ukrail_lines_segments.agency_name, ukrail_manual_agency_parent.parent_company, ukrail_lines_segments.geom
FROM
ukrail_lines_segments, ukrail_manual_agency_parent
WHERE
ukrail_lines_segments.agency_name = ukrail_manual_agency_parent.agency_name;
SELECT 4846
vacuum analyze ukrail_lines_final;
VACUUM

\copy (SELECT * FROM ukrail_distinct_agencies) TO 'ukrail_distinct_agencies.csv' DELIMITER ',' CSV;
\copy (SELECT * FROM ukrail_distinct_agencies_parent ORDER BY agency_name) TO 'ukrail_distinct_agencies_parent.csv' DELIMITER ',' CSV HEADER;
\copy (SELECT * FROM ukrail_distinct_parent) TO 'ukrail_distinct_parent.csv' DELIMITER ',' CSV;
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
layer names ignored in combination with -sql.
Input file is agencies
Input file is parent
Input file is agencies
Input file is parent
/bin/cp: omitting directory ‘/var/www/ukrailmap.co.uk/html/agencies_js’
/bin/cp: omitting directory ‘/var/www/ukrailmap.co.uk/html/css’
/bin/cp: omitting directory ‘/var/www/ukrailmap.co.uk/html/dynamic_js’
/bin/cp: omitting directory ‘/var/www/ukrailmap.co.uk/html/js’
/bin/cp: omitting directory ‘/var/www/ukrailmap.co.uk/html/parent_js’
/bin/cp: omitting directory ‘/var/www/ukrailmap.co.uk/html/static_js’
/bin/rm: cannot remove ‘/var/www/ukrailmap.co.uk/html/css/images’: Is a directory