1 | <? |
---|
2 | $query=array(); |
---|
3 | $query["highway"]= <<<EOT |
---|
4 | (CASE |
---|
5 | WHEN "highway" in ('motorway', 'motorway_link', 'trunk', 'trunk_link') THEN 'motorway' |
---|
6 | WHEN "highway" in ('primary', 'primary_link', 'secondary', 'tertiary') THEN 'major' |
---|
7 | WHEN "highway" in ('unclassified', 'road', 'residential') THEN 'minor' |
---|
8 | WHEN "highway" in ('living_street', 'pedestrian', 'byway') THEN 'pedestrian' |
---|
9 | WHEN "highway" in ('service', 'bus_guideway', 'track') THEN 'service' |
---|
10 | WHEN "highway" in ('path', 'cycleway', 'footway', 'bridleway', 'steps') THEN 'path' |
---|
11 | WHEN "railway" in ('platform') THEN 'path' |
---|
12 | WHEN "railway" in ('tram', 'light_rail', 'narrow_gauge', 'rail', 'subway', 'preserved', 'monorail') THEN 'railway' |
---|
13 | WHEN "aeroway" in ('runway') THEN 'aeroway' |
---|
14 | WHEN "aeroway" in ('taxiway') THEN 'aeroway' |
---|
15 | WHEN "waterway" in ('river', 'stream', 'canal') THEN 'waterway' |
---|
16 | WHEN "barrier" is not null THEN 'barrier' |
---|
17 | WHEN "natural" in ('cliff') THEN 'natural' |
---|
18 | WHEN "power" in ('line', 'minor_line') THEN 'power' |
---|
19 | WHEN "man_made" in ('pipeline') THEN 'pipeline' |
---|
20 | ELSE "highway" END) as highway_type, |
---|
21 | (CASE |
---|
22 | /* motorway */ |
---|
23 | WHEN "highway" in ('motorway') THEN 't1' |
---|
24 | WHEN "highway" in ('trunk') THEN 't2' |
---|
25 | WHEN "highway" in ('motorway_link') THEN 't3' |
---|
26 | WHEN "highway" in ('trunk_link') THEN 't4' |
---|
27 | /* major */ |
---|
28 | WHEN "highway" in ('primary') THEN 't1' |
---|
29 | WHEN "highway" in ('primary_link') THEN 't2' |
---|
30 | WHEN "highway" in ('secondary') THEN 't3' |
---|
31 | WHEN "highway" in ('tertiary') THEN 't4' |
---|
32 | /* service */ |
---|
33 | WHEN "highway" in ('service', 'bus_guideway') THEN 't1' |
---|
34 | WHEN "highway" in ('track') THEN 't2' |
---|
35 | /* path */ |
---|
36 | WHEN "highway" in ('steps') THEN 't1' |
---|
37 | /* railway */ |
---|
38 | WHEN "railway" in ('tram', 'light_rail', 'narrow_gauge') THEN 't1' |
---|
39 | WHEN "railway" in ('rail', 'subway', 'preserved', 'monorail') THEN 't2' |
---|
40 | /* aeroway */ |
---|
41 | WHEN "aeroway" in ('runway') THEN 't1' |
---|
42 | WHEN "aeroway" in ('taxiway') THEN 't2' |
---|
43 | /* water */ |
---|
44 | WHEN "waterway" in ('river') THEN 't1' |
---|
45 | WHEN "waterway" in ('canal') THEN 't2' |
---|
46 | WHEN "waterway" in ('stream') THEN 't3' |
---|
47 | /* barrier */ |
---|
48 | WHEN "barrier" in ('wall', 'city_wall') THEN 't1' |
---|
49 | /* natural */ |
---|
50 | WHEN "natural" in ('cliff') THEN 't1' |
---|
51 | /* power */ |
---|
52 | WHEN "power" in ('line') and "importance"='international' THEN 't1' |
---|
53 | WHEN "power" in ('line') and "importance"='national' THEN 't2' |
---|
54 | WHEN "power" in ('line') and "importance"='regional' THEN 't3' |
---|
55 | WHEN "power" in ('line') and "importance"='urban' THEN 't4' |
---|
56 | WHEN "power" in ('line') and "importance"='suburban' THEN 't5' |
---|
57 | WHEN "power" in ('line') and "importance"='local' THEN 't6' |
---|
58 | WHEN "power" in ('minor_line') THEN 't6' |
---|
59 | /* pipeline */ |
---|
60 | /* column voltage holds value of tag 'type' */ |
---|
61 | WHEN "man_made"='pipeline' and "voltage" in ('water') THEN 't1' |
---|
62 | WHEN "man_made"='pipeline' and "voltage" in ('oil') THEN 't2' |
---|
63 | WHEN "man_made"='pipeline' and "voltage" in ('gas') THEN 't3' |
---|
64 | WHEN "man_made"='pipeline' and "voltage" in ('sewage') THEN 't4' |
---|
65 | WHEN "man_made"='pipeline' and "voltage" in ('heat', 'hot_water') THEN 't5' |
---|
66 | /* ELSE */ |
---|
67 | ELSE 'default' |
---|
68 | END) as sub_type |
---|
69 | EOT; |
---|
70 | $query["landuse"]=<<<EOT |
---|
71 | (CASE |
---|
72 | WHEN "leisure" in ('park') |
---|
73 | OR "landuse" in ('village_green', 'recreation_ground', 'grass') |
---|
74 | THEN 'park' |
---|
75 | WHEN "leisure" in ('golf_course', 'playground', 'sports_centre', 'track', |
---|
76 | 'pitch', 'water_park') |
---|
77 | THEN 'sport' |
---|
78 | WHEN "leisure" in ('nature_reserve') |
---|
79 | THEN 'nature_reserve' |
---|
80 | WHEN "natural" in ('wood', 'wetland', 'marsh', 'glacier', 'scree', 'scrub', 'heath', 'mud', 'beach') |
---|
81 | THEN 'natural' |
---|
82 | WHEN "landuse" in ('cemetery') |
---|
83 | THEN 'cemetery' |
---|
84 | WHEN "landuse" in ('forest') |
---|
85 | THEN 'natural' |
---|
86 | WHEN "leisure" in ('common', 'garden') |
---|
87 | OR "landuse" in ('meadow', 'farm', 'farmyard', 'farmland', 'vineyard', 'orchard') |
---|
88 | OR "natural" in ('fell') |
---|
89 | THEN 'garden' |
---|
90 | WHEN "landuse" in ('school') |
---|
91 | THEN 'education' |
---|
92 | WHEN "landuse" in ('quarry', 'landfill', 'brownfield', |
---|
93 | 'railway', 'construction', 'military', 'industrial') |
---|
94 | OR "amenity" in ('bus_station') |
---|
95 | OR "aeroway" in ('aerodrome', 'apron') |
---|
96 | OR "military" in ('barracks', 'airfield') |
---|
97 | OR "power" in ('generator', 'station', 'sub_station') |
---|
98 | THEN 'industrial' |
---|
99 | WHEN "landuse" in ('residential', 'allotments') |
---|
100 | THEN 'residential' |
---|
101 | WHEN "historic" is not null |
---|
102 | THEN 'historic' |
---|
103 | WHEN "tourism" is not null |
---|
104 | THEN 'tourism' |
---|
105 | WHEN ("building" is null OR "building"='no') THEN (CASE |
---|
106 | WHEN "amenity" in ('college', 'cinema', 'kindergarten', 'library', 'school', 'theatre', 'arts_centre', 'university') |
---|
107 | THEN 'education' |
---|
108 | WHEN "amenity" in ('hospital', 'emergency_phone', 'fire_station', 'police') |
---|
109 | THEN 'emergency' |
---|
110 | WHEN "amenity" in ('pharmacy', 'baby_hatch', 'dentist', 'doctors', 'veterinary') |
---|
111 | THEN 'health' |
---|
112 | WHEN "amenity" in ('government', 'gouvernment', 'public_building', 'court_house', 'embassy', 'prison', 'townhall') |
---|
113 | THEN 'public' |
---|
114 | WHEN "amenity" in ('marketplace') THEN 'shop' |
---|
115 | WHEN "shop" is not null THEN 'shop' |
---|
116 | END) |
---|
117 | END) as landuse, |
---|
118 | (CASE |
---|
119 | WHEN "natural" in ('wood', 'wetland', 'marsh', 'glacier', 'scree', 'scrub', 'heath', 'mud', 'beach') THEN |
---|
120 | (CASE |
---|
121 | WHEN "natural" in ('wood', 'scrub') THEN 't0' |
---|
122 | WHEN "natural" in ('wetland', 'marsh') THEN 't1' |
---|
123 | WHEN "natural" in ('glacier') THEN 't2' |
---|
124 | WHEN "natural" in ('scree', 'heath') THEN 't3' |
---|
125 | WHEN "natural" in ('mud') THEN 't4' |
---|
126 | WHEN "natural" in ('beach') THEN 't5' |
---|
127 | END) |
---|
128 | WHEN "landuse" in ('forest') THEN 't0' |
---|
129 | WHEN "landuse" in ('quarry', 'farmyard', 'farmland', 'landfill', 'brownfield', |
---|
130 | 'railway', 'construction', 'military', 'industrial') |
---|
131 | OR "amenity" in ('bus_station') |
---|
132 | OR "aeroway" in ('aerodrome', 'apron') |
---|
133 | OR "military" in ('barracks', 'airfield') |
---|
134 | OR "power" in ('station', 'sub_station') THEN |
---|
135 | (CASE |
---|
136 | WHEN "landuse" in ('military') |
---|
137 | OR "military" in ('barracks', 'airfield') THEN 't1' |
---|
138 | ELSE 't0' |
---|
139 | END) |
---|
140 | END) as sub_type |
---|
141 | EOT; |
---|
142 | $query["base_amenity"]=<<<EOT |
---|
143 | (CASE |
---|
144 | WHEN "natural" in ('peak', 'volcano', 'cliff', 'cave_entrance') THEN 'natural_big' |
---|
145 | WHEN "natural" is not null THEN 'natural' |
---|
146 | |
---|
147 | WHEN "highway" in ('mini_roundabout', 'gate', 'mountain_pass') THEN 'transport' |
---|
148 | WHEN "railway" in ('level_crossing') THEN 'transport' |
---|
149 | WHEN "amenity" in ('fountain') THEN 'obstacle' |
---|
150 | WHEN "historic" in ('monument', 'memorial') THEN 'obstacle' |
---|
151 | WHEN "power" is not null THEN 'power' |
---|
152 | END) as type, |
---|
153 | (CASE |
---|
154 | /* type = natural_big and natural */ |
---|
155 | WHEN "natural" is not null THEN (CASE |
---|
156 | WHEN "natural" in ('peak', 'volcano') THEN 't1' |
---|
157 | WHEN "natural" in ('cliff') THEN 't2' |
---|
158 | WHEN "natural" in ('cave_entrance') THEN 't3' |
---|
159 | WHEN "natural" in ('land') THEN 't4' |
---|
160 | |
---|
161 | WHEN "natural" in ('spring') THEN 't1' |
---|
162 | WHEN "natural" in ('beach') THEN 't2' |
---|
163 | WHEN "natural" in ('tree') THEN 't4' |
---|
164 | END) |
---|
165 | |
---|
166 | /* type = transport */ |
---|
167 | WHEN "railway" in ('level_crossing') THEN 't1' |
---|
168 | WHEN "highway" in ('mini_roundabout') THEN 't2' |
---|
169 | WHEN "highway" in ('gate') THEN 't3' |
---|
170 | WHEN "highway" in ('mountain_pass') THEN 't4' |
---|
171 | |
---|
172 | /* type = obstacle */ |
---|
173 | WHEN "amenity" in ('fountain') THEN 't1' |
---|
174 | WHEN "historic" in ('monument', 'memorial') THEN 't2' |
---|
175 | |
---|
176 | /* type = power */ |
---|
177 | WHEN "power" in ('tower') THEN 't1' |
---|
178 | WHEN "power" in ('station', 'sub_station', 'generator') THEN 't2' |
---|
179 | |
---|
180 | END) as sub_type, |
---|
181 | (CASE |
---|
182 | WHEN "natural" in ('peak', 'volcano', 'glacier') THEN "ele" |
---|
183 | WHEN "highway" in ('mountain_pass') THEN "ele" |
---|
184 | END) as desc |
---|
185 | EOT; |
---|
186 | $query["places"]=<<<EOT |
---|
187 | (select 'node' as type, id_place_node as id, name, way, |
---|
188 | (CASE |
---|
189 | WHEN "place"='city' AND "population">=1000000 THEN 'city_large' |
---|
190 | WHEN "place"='city' AND "population">=200000 THEN 'city_medium' |
---|
191 | WHEN "place"='town' AND "population">=30000 THEN 'town_large' |
---|
192 | ELSE "place" |
---|
193 | END) as place, |
---|
194 | "label" from planet_osm_place) as places |
---|
195 | EOT; |
---|
196 | $query["shop"]=<<<EOT |
---|
197 | (CASE |
---|
198 | WHEN "shop" in ('supermarket', 'groceries', 'grocery') THEN 'supermarket' |
---|
199 | WHEN "shop" in ('supermarket', 'groceries', 'grocery') THEN 'health' |
---|
200 | WHEN "amenity" in ('pharmacy') THEN 'health' |
---|
201 | WHEN "amenity"='vending_machine' THEN 'vending' |
---|
202 | WHEN "amenity"='marketplace' THEN 'marketplace' |
---|
203 | WHEN "shop" is not null THEN 'other' |
---|
204 | END) as shop_type, |
---|
205 | (CASE |
---|
206 | WHEN "shop" is null and "amenity" in ('pharmacy') THEN 't1' |
---|
207 | END) as shop_sub_type, |
---|
208 | (CASE |
---|
209 | WHEN "shop" is null and |
---|
210 | "amenity" in ('pharmacy') THEN "amenity" |
---|
211 | WHEN "amenity"='vending_machine' THEN "vending" |
---|
212 | WHEN "amenity"='marketplace' THEN 'marketplace' |
---|
213 | ELSE "shop" |
---|
214 | END) as shop_desc, |
---|
215 | (CASE |
---|
216 | WHEN "network" in ('international', 'national') THEN 'national' |
---|
217 | WHEN "network" in ('region', 'urban', 'local') THEN "network" |
---|
218 | WHEN "shop" in ('mall', 'shopping_center', 'shopping_centre') THEN 'region' |
---|
219 | WHEN "shop" in ('supermarket', 'department_store', 'market') THEN 'urban' |
---|
220 | WHEN "amenity" in ('marketplace') THEN 'urban' |
---|
221 | ELSE 'local' |
---|
222 | END) as shop_network |
---|
223 | EOT; |
---|
224 | $query["highway_level"]=<<<EOT |
---|
225 | (CASE |
---|
226 | WHEN "highway" in ('motorway', 'motorway_link') THEN 21 |
---|
227 | WHEN "highway" in ('trunk', 'trunk_link') THEN 20 |
---|
228 | WHEN "highway" in ('primary', 'primary_link') THEN 12 |
---|
229 | WHEN "highway" in ('secondary') THEN 11 |
---|
230 | WHEN "highway" in ('tertiary') THEN 10 |
---|
231 | WHEN "highway" in ('unclassified', 'road', 'residential') THEN 4 |
---|
232 | WHEN "highway" in ('living_street', 'service', 'pedestrian', 'steps', 'bus_guideway', 'byway') THEN 3 |
---|
233 | WHEN "highway" in ('track', 'path', 'cycleway', 'footway', 'bridleway', 'ford') THEN 2 |
---|
234 | WHEN "railway" in ('platform') THEN 2 |
---|
235 | WHEN "railway" in ('tram', 'rail', 'narrow_gauge', 'light_rail') THEN 1 |
---|
236 | WHEN "barrier" is not null THEN 0 |
---|
237 | WHEN "power" is not null THEN 0 |
---|
238 | END) |
---|
239 | EOT; |
---|
240 | $query["power"]=<<<EOT |
---|
241 | "power" as power_type |
---|
242 | EOT; |
---|
243 | $query["bridge_tunnel"]=<<<EOT |
---|
244 | (CASE |
---|
245 | WHEN "bridge" in ('yes', 'true', '1', 'viaduct', 'swing', 'aqueduct') THEN 'yes' |
---|
246 | ELSE 'no' |
---|
247 | END) as bridge, |
---|
248 | (CASE |
---|
249 | WHEN "tunnel" in ('yes', 'true', '1') THEN 'yes' |
---|
250 | ELSE 'no' |
---|
251 | END) as tunnel |
---|
252 | EOT; |
---|