source: subversion/sites/www.openstreetbrowser.org/src/process.sql @ 29773

Last change on this file since 29773 was 16406, checked in by skunk, 11 years ago
  • Stations that lie next to each other will be unified
  • node_stop now child of node
  • changed handling of subtypes of relation / node
  • optimized parsing of tags
File size: 7.1 KB
Line 
1update planet_osm_rels set network='urban' where type='route' and route='tram' and (network='' or network is null);
2update planet_osm_rels set network='urban' where type='route' and route='bus' and (network='' or network is null);
3update planet_osm_rels set network='region' where type='route' and route='light_rail' and (network='' or network is null);
4update planet_osm_rels set network='urban' where type='route' and route='trolley' and (network='' or network is null);
5update planet_osm_rels set network='region' where type='route' and route='subway' and (network='' or network is null);
6update planet_osm_rels set network='region' where type='route' and route='train' and (network='' or network is null);
7update planet_osm_rels set network='region' where type='route' and route='rail' and (network='' or network is null);
8update planet_osm_rels set network='region' where type='route' and route='railway' and (network='' or network is null);
9update planet_osm_rels set network='region' where type='route' and route='ferry' and (network='' or network is null);
10
11alter table planet_osm_line drop column route_level;
12alter table planet_osm_line add column route_level int;
13update planet_osm_line set "route_level"='1' where "route"='railway';
14update planet_osm_line set "route_level"='1' where "route"='rail';
15update planet_osm_line set "route_level"='1' where "route"='train';
16update planet_osm_line set "route_level"='2' where "route"='subway';
17update planet_osm_line set "route_level"='3' where "route"='light_rail';
18update planet_osm_line set "route_level"='4' where "route"='tram';
19update planet_osm_line set "route_level"='5' where "route"='trolley';
20update planet_osm_line set "route_level"='6' where "route"='bus';
21update planet_osm_line set "route_level"='7' where "route"='minibus';
22update planet_osm_line set "route_level"='1' where "route"='ferry';
23drop table planet_osm_line_route;
24create table planet_osm_line_route ( osm_id int4 not null, id int4 not null, route text, ref text, network text, routelevel int, z_order int4, primary key(osm_id, id) );
25SELECT AddGeometryColumn('planet_osm_line_route', 'way', 900913, 'LINESTRING', 2);
26insert into planet_osm_line_route select osm_id, id, planet_osm_rels.route, planet_osm_rels.ref, planet_osm_rels.network, (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), z_order, way from planet_osm_line, planet_osm_rels where osm_id=ANY(ways_parts) and type='route';
27
28drop table planet_osm_stations;
29alter table planet_osm_point add column part_of_station int;
30update planet_osm_point set part_of_station=1 from planet_osm_rels where planet_osm_point.osm_id=any(planet_osm_rels.node_parts) and planet_osm_rels.type='station';
31update planet_osm_point set network='local' where highway='bus_stop' and (network='' or network is null);
32update planet_osm_point set network='local' where railway='tram_stop' and (network ='' or network is null);
33update planet_osm_point set network='urban' where railway='halt' and (network='' or network is null);
34update planet_osm_point set network='region' where railway='station' and (network='' or network is null);
35-- select planet_osm_point.osm_id, planet_osm_point.railway from planet_osm_line join planet_osm_ways on planet_osm_ways.id=planet_osm_line.osm_id join planet_osm_point on planet_osm_point.osm_id=any(planet_osm_ways.nodes) where planet_osm_line.railway='subway';
36update planet_osm_point set network='region' where amenity='bus_station' and (network='' or network is null);
37
38grant all on planet_osm_rels to www;
39grant all on planet_osm_nodes to www;
40grant all on planet_osm_line_route to www;
41grant all on planet_osm_ways to www;
42grant all on planet_osm_stations to www;
43
44drop table if exists planet_osm_stop_to_station;
45create table planet_osm_stop_to_station(node_id int4 not null, 
46rel_id int4[], stations int4[], name text, network text, 
47primary key(node_id));
48-- SELECT AddGeometryColumn('planet_osm_station', 'way', 900913, 'POINT', 2);
49drop aggregate if exists to_intarray(int4);
50CREATE AGGREGATE to_intarray (
51BASETYPE = int4,
52SFUNC = array_append,
53STYPE = int4[],
54INITCOND = '{}'); 
55-- collect all stations into planet_osm_station list
56insert into planet_osm_stop_to_station select 
57  src.osm_id,
58  to_intarray(station_rel.rel_id),
59  to_intarray(dst.osm_id),
60  dst.name,
61  src.network
62--  ,pos.lon, pos.lat
63--  GeomFromText('POINT(' || avg(lon) || ' ' || avg(lat) || ')', 900913)
64from 
65  planet_osm_point dst,
66  planet_osm_point src left join
67  planet_osm_rels_members station_rel on 
68    station_rel.rel_type='station' and
69    src.osm_id=station_rel.mem_id and
70    station_rel.type='node' and
71    station_rel.role!='nearby'
72--  ,planet_osm_nodes pos
73where 
74--  src.osm_id=pos.id and
75  src.name=dst.name and
76  Distance(src.way, dst.way)<100 and
77  (src.highway='bus_stop' or src.railway='tram_stop' or
78    src.railway='station' or src.railway='halt' or
79    src.amenity='bus_station' or src.aeroway='station' or
80    src.amenity='ferry_terminal') and
81  (dst.highway='bus_stop' or dst.railway='tram_stop' or
82    dst.railway='station' or dst.railway='halt' or
83    dst.amenity='bus_station' or dst.aeroway='station' or
84    dst.amenity='ferry_terminal')
85group by src.osm_id, station_rel.rel_id, src.network, dst.name;
86
87CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
88RETURNS ANYARRAY LANGUAGE SQL
89AS $$
90SELECT ARRAY(
91  SELECT $1[s.i] AS "foo"
92  FROM generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
93  ORDER BY foo
94);
95$$;
96drop table if exists planet_osm_stations_all;
97create table planet_osm_stations_all(name text, stations int[], rel_id int4, network text, lon float, lat float);
98SELECT AddGeometryColumn('planet_osm_stations_all', 'way', 900913, 'POINT', 2);
99insert into planet_osm_stations_all select name, array_sort(stations), max(rel_id),
100  (array['local','urban','region','national','international'])
101    [max(CASE WHEN network='urban' THEN 2 
102      WHEN network='region' THEN 3
103      WHEN network='national' THEN 4
104      WHEN network='international' THEN 5 ELSE 1 END)],
105 avg(lon), avg(lat), GeomFromText('POINT(' || avg(lon) || ' ' || avg(lat) || ')', 900913) from planet_osm_station station join planet_osm_nodes node on node.id=any(station.stations) group by name, array_sort(stations);
106
107
108-- delete all stations with relations and do it again for them
109drop table if exists planet_osm_stations_rel;
110create table planet_osm_stations_rel(name text, stations int[], rel_id int4 not null, network text, lon float, lat float, primary key(rel_id));
111SELECT AddGeometryColumn('planet_osm_stations_rel', 'way', 900913, 'POINT', 2);
112insert into planet_osm_stations_rel select name, to_intarray(planet_osm_nodes.id), planet_osm_rels.id, network, avg(lon), avg(lat), GeomFromText('POINT(' || avg(lon) || ' ' || avg(lat) || ')', 900913) from planet_osm_rels join planet_osm_nodes on planet_osm_nodes.id=any(planet_osm_rels.node_parts) where type='station' group by planet_osm_rels.id, name, network;
Note: See TracBrowser for help on using the repository browser.