-
Notifications
You must be signed in to change notification settings - Fork 0
/
load.sql
176 lines (146 loc) · 7.61 KB
/
load.sql
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
-- create temp table for orgs
create table organisation_temp (
name character varying (200) not null,
country character varying (200) not null,
code character (9),
website character varying (200),
email character varying (200),
domain character varying (200),
colour character (7),
logo character varying (200)
);
-- import organisations
\copy organisation_temp from 'data/organisations.csv' csv header;
-- import countries
insert into country(name)
select distinct country from organisation_temp order by country;
-- insert into organisation table
insert into organisation(name, country_id, code, website, email, colour, logo)
select t.name, c.id, t.code, t.website, t.email, t.colour, t.logo from organisation_temp t join country c on c.name = t.country order by c.id, t.name;
drop table organisation_temp;
-- load Angus
\copy staging from 'data/angus.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Angus');
\copy trip_staging from 'data/angus_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Angus');
-- load Barnet
\copy staging from 'data/barnet.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Barnet');
\copy trip_staging from 'data/barnet_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Barnet');
-- load Bath and North East Somerset
\copy staging from 'data/bath_and_north_east_somerset.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Bath and North East Somerset');
\copy trip_staging from 'data/bath_and_north_east_somerset_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Bath and North East Somerset');
-- load Birmingham
\copy staging from 'data/birmingham.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Birmingham');
\copy trip_staging from 'data/birmingham_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Birmingham');
-- load Cambridgeshire
\copy staging from 'data/cambridgeshire.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Cambridgeshire');
\copy trip_staging from 'data/cambridgeshire_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Cambridgeshire');
-- Load East Riding of Yorkshire
\copy staging from 'data/east_riding_of_yorkshire.csv' csv header;
select fn_load_stops_staging(organisation_name := 'East Riding of Yorkshire');
\copy trip_staging from 'data/east_riding_of_yorkshire_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'East Riding of Yorkshire');
-- load Essex
\copy staging from 'data/essex.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Essex');
\copy trip_staging from 'data/essex_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Essex');
-- load Hillingdon
\copy staging from 'data/hillingdon.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Hillingdon');
\copy trip_staging from 'data/hillingdon_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Hillingdon');
-- load LibrariesNI
\copy staging from 'data/librariesni.csv' csv header;
select fn_load_stops_staging(organisation_name := 'LibrariesNI');
\copy trip_staging from 'data/librariesni_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'LibrariesNI');
-- load Midlothian
\copy staging from 'data/midlothian.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Midlothian');
\copy trip_staging from 'data/midlothian_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Midlothian');
-- load Norfolk
\copy staging from 'data/norfolk.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Norfolk');
\copy trip_staging from 'data/norfolk_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Norfolk');
-- load North Lincolnshire
\copy staging from 'data/north_lincolnshire.csv' csv header;
select fn_load_stops_staging(organisation_name := 'North Lincolnshire');
\copy trip_staging from 'data/north_lincolnshire_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'North Lincolnshire');
-- load North Somerset
\copy staging from 'data/north_somerset.csv' csv header;
select fn_load_stops_staging(organisation_name := 'North Somerset');
\copy trip_staging from 'data/north_somerset_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'North Somerset');
-- load North Yorkshire
\copy staging from 'data/north_yorkshire.csv' csv header;
select fn_load_stops_staging(organisation_name := 'North Yorkshire');
\copy trip_staging from 'data/north_yorkshire_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'North Yorkshire');
-- load Perth and Kinross
\copy staging from 'data/perth_and_kinross.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Perth and Kinross');
\copy trip_staging from 'data/perth_and_kinross_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Perth and Kinross');
-- load Portsmouth
\copy staging from 'data/portsmouth.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Portsmouth');
\copy trip_staging from 'data/portsmouth_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Portsmouth');
-- load Shropshire
\copy staging from 'data/shropshire.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Shropshire');
\copy trip_staging from 'data/shropshire_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Shropshire');
-- load Somerset
\copy staging from 'data/somerset.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Somerset');
\copy trip_staging from 'data/somerset_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Somerset');
-- load Suffolk
\copy staging from 'data/suffolk.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Suffolk');
\copy trip_staging from 'data/suffolk_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Suffolk');
-- load Warwickshire
\copy staging from 'data/warwickshire.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Warwickshire');
\copy trip_staging from 'data/warwickshire_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Warwickshire');
-- load West Berkshire
\copy staging from 'data/west_berkshire.csv' csv header;
select fn_load_stops_staging(organisation_name := 'West Berkshire');
\copy trip_staging from 'data/west_berkshire_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'West Berkshire');
-- load West Dunbartonshire
\copy staging from 'data/west_dunbartonshire.csv' csv header;
select fn_load_stops_staging(organisation_name := 'West Dunbartonshire');
\copy trip_staging from 'data/west_dunbartonshire_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'West Dunbartonshire');
-- load Wiltshire
\copy staging from 'data/wiltshire.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Wiltshire');
\copy trip_staging from 'data/wiltshire_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Wiltshire');
-- load Worcestershire
\copy staging from 'data/worcestershire.csv' csv header;
select fn_load_stops_staging(organisation_name := 'Worcestershire');
\copy trip_staging from 'data/worcestershire_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'Worcestershire');
-- load York
\copy staging from 'data/york.csv' csv header;
select fn_load_stops_staging(organisation_name := 'York');
\copy trip_staging from 'data/york_routes.csv' csv header;
select fn_load_trips_staging(organisation_name := 'York');
vacuum analyze;