forked from azavea/pfb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
import_osm.sh
executable file
·203 lines (188 loc) · 12.3 KB
/
import_osm.sh
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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
#!/usr/bin/env bash
set -e
cd `dirname $0`
NB_POSTGRESQL_HOST="${NB_POSTGRESQL_HOST:-127.0.0.1}"
NB_POSTGRESQL_DB="${NB_POSTGRESQL_DB:-pfb}"
NB_POSTGRESQL_USER="${NB_POSTGRESQL_USER:-gis}"
NB_POSTGRESQL_PASSWORD="${NB_POSTGRESQL_PASSWORD:-gis}"
NB_OUTPUT_SRID="${NB_OUTPUT_SRID:-4326}"
# drop old tables
echo 'Dropping old tables'
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS received.neighborhood_ways;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS received.neighborhood_ways_intersections;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS received.neighborhood_relations_ways;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS received.neighborhood_osm_nodes;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS received.neighborhood_osm_relations;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS received.neighborhood_osm_way_classes;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS received.neighborhood_osm_way_tags;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS received.neighborhood_osm_way_types;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS scratch.neighborhood_cycwys_ways;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS scratch.neighborhood_cycwys_ways_vertices_pgr;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS scratch.neighborhood_cycwys_relations_ways;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS scratch.neighborhood_cycwys_osm_nodes;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS scratch.neighborhood_cycwys_osm_relations;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS scratch.neighborhood_cycwys_osm_way_classes;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS scratch.neighborhood_cycwys_osm_way_tags;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS scratch.neighborhood_cycwys_osm_way_types;"
# Get the neighborhood_boundary bbox
BBOX_SW_LNG=`psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -t -c "SELECT MIN(ST_Xmin(ST_Transform(geom, 4326))) FROM neighborhood_boundary;"`
BBOX_SW_LAT=`psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -t -c "SELECT MIN(ST_Ymin(ST_Transform(geom, 4326))) FROM neighborhood_boundary;"`
BBOX_NE_LNG=`psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -t -c "SELECT MAX(ST_Xmax(ST_Transform(geom, 4326))) FROM neighborhood_boundary;"`
BBOX_NE_LAT=`psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -t -c "SELECT MAX(ST_Ymax(ST_Transform(geom, 4326))) FROM neighborhood_boundary;"`
# Buffer it
LNG_DIFF=`bc <<< "$BBOX_NE_LNG - $BBOX_SW_LNG"`
LAT_DIFF=`bc <<< "$BBOX_NE_LAT - $BBOX_SW_LAT"`
BBOX_SW_LAT=`bc <<< "$BBOX_SW_LAT - $LAT_DIFF"`
BBOX_SW_LNG=`bc <<< "$BBOX_SW_LNG - $LNG_DIFF"`
BBOX_NE_LAT=`bc <<< "$BBOX_NE_LAT + $LAT_DIFF"`
BBOX_NE_LNG=`bc <<< "$BBOX_NE_LNG + $LNG_DIFF"`
# Download OSM data
OSM_API_URL="http://www.overpass-api.de/api/xapi?*[bbox=${BBOX_SW_LNG},${BBOX_SW_LAT},${BBOX_NE_LNG},${BBOX_NE_LAT}]"
OSM_TEMPDIR=`mktemp -d`
OSM_DATA_FILE="${OSM_TEMPDIR}/overpass.osm"
wget -O "${OSM_DATA_FILE}" "${OSM_API_URL}"
# import the osm with highways
osm2pgrouting \
-f $OSM_DATA_FILE \
-h $NB_POSTGRESQL_HOST \
--dbname ${NB_POSTGRESQL_DB} \
--username ${NB_POSTGRESQL_USER} \
--schema received \
--prefix neighborhood_ \
--conf ./mapconfig_highway.xml \
--clean
# import the osm with cycleways that the above misses (bug in osm2pgrouting)
osm2pgrouting \
-f $OSM_DATA_FILE \
-h $NB_POSTGRESQL_HOST \
--dbname ${NB_POSTGRESQL_DB} \
--username ${NB_POSTGRESQL_USER} \
--schema scratch \
--prefix neighborhood_cycwys_ \
--conf ./mapconfig_cycleway.xml \
--clean
# rename a few tables
echo 'Renaming tables'
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE received.neighborhood_ways_vertices_pgr RENAME TO neighborhood_ways_intersections;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE received.neighborhood_ways_intersections RENAME CONSTRAINT vertex_id TO neighborhood_vertex_id;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE received.osm_nodes RENAME TO neighborhood_osm_nodes;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE received.neighborhood_osm_nodes RENAME CONSTRAINT node_id TO neighborhood_node_id;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE received.osm_relations RENAME TO neighborhood_osm_relations;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE received.osm_way_classes RENAME TO neighborhood_osm_way_classes;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE received.neighborhood_osm_way_classes RENAME CONSTRAINT osm_way_classes_pkey TO neighborhood_osm_way_classes_pkey;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE received.osm_way_tags RENAME TO neighborhood_osm_way_tags;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE received.osm_way_types RENAME TO neighborhood_osm_way_types;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE received.neighborhood_osm_way_types RENAME CONSTRAINT osm_way_types_pkey TO neighborhood_osm_way_types_pkey;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE scratch.neighborhood_cycwys_ways_vertices_pgr RENAME CONSTRAINT vertex_id TO neighborhood_vertex_id;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE scratch.osm_nodes RENAME TO neighborhood_cycwys_osm_nodes;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE scratch.neighborhood_cycwys_osm_nodes RENAME CONSTRAINT node_id TO neighborhood_node_id;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE scratch.osm_relations RENAME TO neighborhood_cycwys_osm_relations;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE scratch.osm_way_classes RENAME TO neighborhood_cycwys_osm_way_classes;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE scratch.neighborhood_cycwys_osm_way_classes RENAME CONSTRAINT osm_way_classes_pkey TO neighborhood_osm_way_classes_pkey;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE scratch.osm_way_tags RENAME TO neighborhood_cycwys_osm_way_tags;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE scratch.osm_way_types RENAME TO neighborhood_cycwys_osm_way_types;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE scratch.neighborhood_cycwys_osm_way_types RENAME CONSTRAINT osm_way_types_pkey TO neighborhood_osm_way_types_pkey;"
# import full osm to fill out additional data needs
# not met by osm2pgrouting
# drop old tables
echo 'Dropping old tables'
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS received.neighborhood_osm_full_line;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS received.neighborhood_osm_full_point;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS received.neighborhood_osm_full_polygon;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "DROP TABLE IF EXISTS received.neighborhood_osm_full_roads;"
# import
osm2pgsql \
--host "${NB_POSTGRESQL_HOST}" \
--username ${NB_POSTGRESQL_USER} \
--port 5432 \
--create \
--database "${NB_POSTGRESQL_DB}" \
--prefix "neighborhood_osm_full" \
--proj "${NB_OUTPUT_SRID}" \
--style ./pfb.style \
"${OSM_DATA_FILE}"
# Delete downloaded temp OSM data
rm -rf "${OSM_TEMPDIR}"
# move the full osm tables to the received schema
echo 'Moving tables to received schema'
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE generated.neighborhood_osm_full_line SET SCHEMA received;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE generated.neighborhood_osm_full_point SET SCHEMA received;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE generated.neighborhood_osm_full_polygon SET SCHEMA received;"
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-c "ALTER TABLE generated.neighborhood_osm_full_roads SET SCHEMA received;"
# process tables
echo 'Updating field names'
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-v nb_output_srid="${NB_OUTPUT_SRID}" -f ./prepare_tables.sql
echo 'Setting values on road segments'
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./one_way.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./width_ft.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./functional_class.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} \
-v nb_output_srid="${NB_OUTPUT_SRID}" -f ./paths.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./speed_limit.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./lanes.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./park.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./bike_infra.sql
echo 'Setting values on intersections'
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./legs.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./signalized.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stops.sql
echo 'Calculating stress'
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_motorway-trunk.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_primary.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_secondary.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_tertiary.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_residential.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_living_street.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_track.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_path.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_one_way_reset.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_motorway-trunk_ints.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_primary_ints.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_secondary_ints.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_tertiary_ints.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_lesser_ints.sql
psql -h $NB_POSTGRESQL_HOST -U ${NB_POSTGRESQL_USER} -d ${NB_POSTGRESQL_DB} -f ./stress_link_ints.sql