source: subversion/sites/www.openstreetbrowser.org/src/sql/03_routes.sql @ 19763

Revision 19763, 10.7 KB checked in by skunk, 4 years ago (diff)

XML/View list: support for routes

  • stylesheet can define * from importance, will ask table for importance
  • new table planet_osm_route contains all routes (at least lines)
Line 
1-- Update tag network if wrong or missing
2update planet_osm_rels set importance=network where importance is null and network in ('international', 'national', 'regional', 'urban', 'suburban', 'local');
3update planet_osm_rels set importance='regional' where importance='region';
4update planet_osm_rels set importance='suburban' where type='route' and route='tram' and (importance is null or not importance in ('local', 'suburban', 'urban', 'regional', 'national', 'international'));
5update planet_osm_rels set importance='suburban' where type='route' and route='bus' and (importance is null or not importance in ('local', 'suburban', 'urban', 'regional', 'national', 'international'));
6update planet_osm_rels set importance='urban' where type='route' and route='light_rail' and (importance is null or not importance in ('local', 'suburban', 'urban', 'regional', 'national', 'international'));
7update planet_osm_rels set importance='suburban' where type='route' and route='trolley' and (importance is null or not importance in ('local', 'suburban', 'urban', 'regional', 'national', 'international'));
8update planet_osm_rels set importance='suburban' where type='route' and route='trolleybus' and (importance is null or not importance in ('local', 'suburban', 'urban', 'regional', 'national', 'international'));
9update planet_osm_rels set importance='urban' where type='route' and route='subway' and (importance is null or not importance in ('local', 'suburban', 'urban', 'regional', 'national', 'international'));
10update planet_osm_rels set importance='regional' where type='route' and route='train' and (importance is null or not importance in ('local', 'suburban', 'urban', 'regional', 'national', 'international'));
11update planet_osm_rels set importance='regional' where type='route' and route='rail' and (importance is null or not importance in ('local', 'suburban', 'urban', 'regional', 'national', 'international'));
12update planet_osm_rels set importance='regional' where type='route' and route='railway' and (importance is null or not importance in ('local', 'suburban', 'urban', 'regional', 'national', 'international'));
13update planet_osm_rels set importance='regional' where type='route' and route='ferry' and (importance is null or not importance in ('local', 'suburban', 'urban', 'regional', 'national', 'international'));
14update planet_osm_rels set importance='regional' where type='station' and (importance is null or not importance in ('local', 'suburban', 'urban', 'regional', 'national', 'international'));
15update planet_osm_rels set importance='suburban' where network in ('lcn', 'lwn', 'mtb');
16update planet_osm_rels set importance='regional' where network in ('rcn', 'rwn');
17update planet_osm_rels set importance='national' where network in ('ncn', 'nwn');
18update planet_osm_rels set importance='international' where network in ('icn', 'iwn');
19update planet_osm_rels set importance='regional' where network='' and route in ('foot', 'hiking');
20update planet_osm_rels set importance='suburban' where network='' and route in ('bicycle', 'mtb');
21update planet_osm_rels set importance='regional' where importance is null and type='route' and route='hiking';
22update planet_osm_rels set importance='urban' where importance is null and type='route' and route in ('foot', 'bicycle', 'mtb');
23
24-- build table with all routes
25drop table if exists planet_osm_route;
26create table planet_osm_route (
27  id int4 not null,
28  route text,
29  ref text,
30  importance text,
31  primary key(id)
32);
33SELECT AddGeometryColumn('planet_osm_route', 'way', 900913, 'GEOMETRY', 2);
34insert into planet_osm_route
35  select
36    r.id, r.route, r.ref, r.importance,
37    ST_Collect(l.way)
38  from
39    planet_osm_rels r
40    join relation_members rm on r.id=rm.relation_id
41    join planet_osm_line l on l.osm_id=rm.member_id and rm.member_type='W'
42  where
43    r.type='route'
44  group by
45    r.id, r.route, r.ref, r.importance;
46create index planet_osm_route_way on planet_osm_route using gist(way);
47create index planet_osm_route_route on planet_osm_route(route);
48create index planet_osm_route_importance on planet_osm_route(importance);
49
50-- build table with all lines that are part of a route
51drop table if exists planet_osm_line_route;
52create table planet_osm_line_route (
53  osm_id int4 not null, 
54  id int4 not null, 
55  route text, 
56  ref text, 
57  role text[],
58  layer_level int2,
59  importance text, 
60  network text, 
61  highway text, 
62  railway text,
63  tunnel text, 
64  bridge text,
65  oneway text,
66  tracks text,
67  z_order int4, 
68  primary key(osm_id, id) 
69);
70SELECT AddGeometryColumn('planet_osm_line_route', 'way', 900913, 'LINESTRING', 2);
71insert into planet_osm_line_route select osm_id, id, planet_osm_rels.route, planet_osm_rels.ref, to_textarray(relation_members.member_role), planet_osm_line.layer_level, planet_osm_rels.importance, planet_osm_rels.network, planet_osm_line.highway, planet_osm_line.railway, planet_osm_line.tunnel, planet_osm_line.bridge, planet_osm_line.oneway, planet_osm_line.tracks, z_order, way from planet_osm_line join relation_members on planet_osm_line.osm_id=relation_members.member_id and member_type='W' join planet_osm_rels on relation_members.relation_id=planet_osm_rels.id where type='route' group by osm_id, id, planet_osm_rels.route, planet_osm_rels.ref, planet_osm_line.layer_level, planet_osm_rels.importance, planet_osm_rels.network, planet_osm_line.highway, planet_osm_line.railway, planet_osm_line.tunnel, planet_osm_line.bridge, planet_osm_line.oneway, planet_osm_line.tracks, z_order, way;
72-- (CASE WHEN planet_osm_rels.route='railway' OR planet_osm_rels.route='rail' OR planet_osm_rels.route='train' OR planet_osm_rels.route='ferry' THEN 1 WHEN planet_osm_rels.route='subway' THEN 2 WHEN planet_osm_rels.route='light_rail' THEN 3 WHEN planet_osm_rels.route='tram' THEN 4 WHEN planet_osm_rels.route='trolley' THEN 5 WHEN planet_osm_rels.route='bus' THEN 6 WHEN planet_osm_rels.route='minibus' THEN 7 END),
73
74-- find for each line which routes there are
75drop table if exists planet_osm_line_routes;
76create table planet_osm_line_routes (line_id int4, route_ids int4[], route_refs text[], importance text, route text, tunnel text, bridge text, highway text, railway text); 
77SELECT AddGeometryColumn('planet_osm_line_routes', 'way', 900913, 'LINESTRING', 2);
78
79insert into planet_osm_line_routes select line.osm_id as line_id, to_intarray(route.id), to_textarray((CASE WHEN route.ref='' THEN route.name ELSE route.ref END)), 'local', route.route, tunnel, bridge, highway, railway, way from planet_osm_line line join relation_members route_part on line.osm_id=route_part.member_id and route_part.member_type='W' join planet_osm_rels route on route.id=route_part.relation_id where route.type='route' and route.importance in ('local', 'suburban', 'urban', 'regional', 'national', 'international') group by line.osm_id, line.way, route.route, tunnel, bridge, highway, railway;
80insert into planet_osm_line_routes select line.osm_id as line_id, to_intarray(route.id), to_textarray((CASE WHEN route.ref='' THEN route.name ELSE route.ref END)), 'suburban', route.route, tunnel, bridge, highway, railway, way from planet_osm_line line join relation_members route_part on line.osm_id=route_part.member_id and route_part.member_type='W' join planet_osm_rels route on route.id=route_part.relation_id where route.type='route' and route.importance in ('suburban', 'urban', 'regional','national','international') group by line.osm_id, line.way, route.route, tunnel, bridge, highway, railway;
81insert into planet_osm_line_routes select line.osm_id as line_id, to_intarray(route.id), to_textarray((CASE WHEN route.ref='' THEN route.name ELSE route.ref END)), 'urban', route.route, tunnel, bridge, highway, railway, way from planet_osm_line line join relation_members route_part on line.osm_id=route_part.member_id and route_part.member_type='W' join planet_osm_rels route on route.id=route_part.relation_id where route.type='route' and route.importance in ('urban', 'regional','national','international') group by line.osm_id, line.way, route.route, tunnel, bridge, highway, railway;
82insert into planet_osm_line_routes select line.osm_id as line_id, to_intarray(route.id), to_textarray((CASE WHEN route.ref='' THEN route.name ELSE route.ref END)), 'regional', route.route, tunnel, bridge, highway, railway, way from planet_osm_line line join relation_members route_part on line.osm_id=route_part.member_id and route_part.member_type='W' join planet_osm_rels route on route.id=route_part.relation_id where route.type='route' and route.importance in ('regional','national','international') group by line.osm_id, line.way, route.route, tunnel, bridge, highway, railway;
83insert into planet_osm_line_routes select line.osm_id as line_id, to_intarray(route.id), to_textarray((CASE WHEN route.ref='' THEN route.name ELSE route.ref END)), 'national', route.route, tunnel, bridge, highway, railway, way from planet_osm_line line join relation_members route_part on line.osm_id=route_part.member_id and route_part.member_type='W' join planet_osm_rels route on route.id=route_part.relation_id where route.type='route' and route.importance in ('national','international') group by line.osm_id, line.way, route.route, tunnel, bridge, highway, railway;
84insert into planet_osm_line_routes select line.osm_id as line_id, to_intarray(route.id), to_textarray((CASE WHEN route.ref='' THEN route.name ELSE route.ref END)), 'international', route.route, tunnel, bridge, highway, railway, way from planet_osm_line line join relation_members route_part on line.osm_id=route_part.member_id and route_part.member_type='W' join planet_osm_rels route on route.id=route_part.relation_id where route.type='route' and route.importance in ('international') group by line.osm_id, line.way, route.route, tunnel, bridge, highway, railway;
85
86-- merge lines with same routes to multiline and combine refs of routes after sorting
87-- TODO: use natural sort
88drop table if exists planet_osm_line_routes_text;
89create table planet_osm_line_routes_text (line_ids int4[], route_ids int4[], route_refs text, importance text, route text, tunnel text, bridge text, highway text, railway text);
90SELECT AddGeometryColumn('planet_osm_line_routes_text', 'way', 900913, 'MULTILINESTRING', 2);
91
92insert into planet_osm_line_routes_text select to_intarray(line_id), array_sort(route_ids), array_to_string(array_nat_sort(route_refs), ', '), importance, route, tunnel, bridge, highway, railway, ST_Multi(ST_LineMerge(ST_Collect(way))) from planet_osm_line_routes group by array_sort(route_ids), array_nat_sort(route_refs), importance, route, tunnel, bridge, highway, railway;
93
94
95create index planet_osm_line_route_way on planet_osm_line_route using gist(way);
96create index planet_osm_line_route_route on planet_osm_line_route(route);
97create index planet_osm_line_routes_text_way on planet_osm_line_routes_text using gist(way);
98create index planet_osm_line_routes_text_route on planet_osm_line_routes_text(route);
Note: See TracBrowser for help on using the repository browser.