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

Last change on this file since 29773 was 20011, checked in by skunk, 10 years ago

Bugfix

  • planet_osm_stations contained geo-collections (can't relate with those)
File size: 11.3 KB
Line 
1-- table point gets a flag, whether it is part of a station-rel
2alter table planet_osm_poipoly add column part_of_station int;
3update planet_osm_poipoly set part_of_station=1 from relation_members, planet_osm_rels where planet_osm_poipoly.osm_id=relation_members.member_id and relation_members.member_type='N' and planet_osm_rels.id=relation_members.relation_id and planet_osm_rels.type='station' and relation_members.member_role!='nearby';
4
5-- update importance in point where missing or wrong
6update planet_osm_poipoly set importance=network where importance is null and network in ('local', 'suburban', 'urban', 'regional', 'national', 'international');
7update planet_osm_poipoly set importance='regional' where importance='region';
8update planet_osm_poipoly set importance='local' where highway='bus_stop' and (importance is null or not importance in ('local', 'urban', 'regional', 'national', 'international'));
9update planet_osm_poipoly set importance='local' where railway='tram_stop' and (importance is null or not importance in ('local', 'urban', 'regional', 'national', 'international'));
10update planet_osm_poipoly set importance='suburban' where railway='halt' and not (importance is null or importance in ('local', 'urban', 'regional', 'national', 'international'));
11update planet_osm_poipoly set importance='suburban', railway='subway_station' from planet_osm_line l join planet_osm_ways w on l.osm_id=w.id where planet_osm_poipoly.osm_id=any(w.nodes) and planet_osm_poipoly.railway='station' and l.railway='subway' and (planet_osm_poipoly.importance is null or not planet_osm_poipoly.importance in ('local', 'urban', 'regional', 'national', 'international'));
12update planet_osm_poipoly set importance='urban' where railway='station' and (importance is null or not importance in ('local', 'urban', 'regional', 'national', 'international'));
13update planet_osm_poipoly set importance='urban' where amenity='bus_station' and (importance is null or not importance in ('local', 'urban', 'regional', 'national', 'international'));
14update planet_osm_poipoly set importance='national' where aeroway='aerodrome' and (importance is null or not importance in ('local', 'urban', 'regional', 'national', 'international'));
15update planet_osm_poipoly set importance='suburban' where aerialway='station' and (importance is null or not importance in ('local', 'urban', 'regional', 'national', 'international'));
16update planet_osm_poipoly set importance='urban' where amenity='ferry_terminal' and (importance is null or not importance in ('local', 'urban', 'regional', 'national', 'international'));
17
18-- in which direction stations are being used?
19drop table if exists planet_osm_stops;
20create table planet_osm_stops (
21  osm_id        int4    not null,
22  id_type       varchar(4) not null,
23  full_id       varchar(32) not null,
24  type          text    not null,
25  importance    text    null,
26  angle_p       int     null,
27  angle_n       int     null,
28  direction     int     not null,
29  primary key(osm_id)
30);
31SELECT AddGeometryColumn('planet_osm_stops', 'way', 900913, 'POINT', 2);
32
33insert into planet_osm_stops
34(select osm_id, id_type, full_id, type, importance,
35  round(ST_Azimuth((CASE 
36      WHEN pos-0.001/len<0 THEN line_interpolate_point(next_way, pos)
37      ELSE line_interpolate_point(next_way, pos-0.001/len)
38    END), line_interpolate_point(next_way, pos))/(2*pi()/72)) as angle_p,
39  round(ST_Azimuth(line_interpolate_point(next_way, pos),
40    (CASE 
41      WHEN pos+0.001/len>=1 THEN line_interpolate_point(next_way, pos)
42      ELSE line_interpolate_point(next_way, pos+0.001/len)
43  END))/(2*pi()/72)) as angle_n,
44  bit_or((CASE WHEN substr(rm.member_role, 1, 7)='forward' THEN 1 WHEN substr(rm.member_role, 1, 8)='backward' THEN 2 ELSE 3 END)) as direction,
45  line_interpolate_point(next_way, pos) as way
46from 
47(select t.osm_id, t.id_type, t.full_id, type, importance,
48  poi_way, next_way, line_locate_point(next_way, poi_way) as pos, length(next_way) as len from (
49select poi.osm_id, poi.id_type, poi.full_id,
50  (CASE
51    WHEN poi.highway='bus_stop' and poi.railway='tram_stop' THEN 'tram_bus_stop'
52    WHEN poi.highway in ('bus_stop') THEN poi.highway
53    WHEN poi.railway in ('tram_stop', 'station', 'subway_station', 'halt') THEN poi.railway
54    WHEN poi.aerialway in ('station') THEN 'aerial_station'
55  END) as type, 
56  poi.importance,
57  ST_Centroid(poi.way) as poi_way,
58  (select dst.way
59      from planet_osm_line dst
60      where
61       geometryfromtext('POLYGON((' || xmin(poi.way)-200 || ' ' || ymin(poi.way)-200 || ','
62                               || xmax(poi.way)+200 || ' ' || ymin(poi.way)-200 || ','
63                               || xmax(poi.way)+200 || ' ' || ymax(poi.way)+200 || ','
64                               || xmin(poi.way)-200 || ' ' || ymax(poi.way)+200 || ',' 
65                               || xmin(poi.way)-200 || ' ' || ymin(poi.way)-200 || '))',
66                                900913)&&dst.way
67        and dst.osm_id>0
68    order by Distance(poi.way, dst.way) asc limit 1) as next_way
69       from planet_osm_poipoly poi where 
70  (poi.highway='bus_stop' or
71    poi.railway in ('tram_stop', 'station', 'subway_station', 'halt')
72    )
73      ) as t) as t1 left join relation_members rm on rm.member_id=t1.osm_id and rm.member_type='N' group by t1.osm_id, t1.id_type, t1.full_id, t1.type, t1.pos, t1.len, t1.importance, t1.next_way);
74create index planet_osm_stops_way on planet_osm_stops using gist(way);
75
76-- stop_to_station finds nearby stops with same name
77-- potential BUG: id for point and polygon same in same station
78drop table if exists planet_osm_stop_to_station;
79create table planet_osm_stop_to_station(
80  id varchar(32) not null,
81  rel_id int4[],
82  stations varchar(32)[],
83  name text,
84  importance text, 
85  primary key(id)
86);
87insert into planet_osm_stop_to_station select 
88  src.full_id,
89  array_unique(to_intarray((select station_rel.id from
90      planet_osm_rels station_rel
91    join 
92      relation_members station_member on 
93      station_member.relation_id=station_rel.id and
94      station_member.member_role!='nearby'
95    where
96      station_rel.type='station' and
97      src.osm_id=station_member.member_id and
98      src.id_type=(CASE WHEN station_member.member_type='N' THEN 'node'
99                        WHEN station_member.member_type='W' THEN' way' END)
100    limit 1))),
101  array_sort(array_unique(to_textarray(dst.full_id))),
102  dst.name,
103  src.importance
104from 
105  planet_osm_poipoly as dst,
106  planet_osm_poipoly as src
107where 
108  src.name=dst.name and
109  geometryfromtext('POLYGON((' || xmin(src.way)-500 || ' ' || ymin(src.way)-500 || ','
110                               || xmax(src.way)+500 || ' ' || ymin(src.way)-500 || ','
111                               || xmax(src.way)+500 || ' ' || ymax(src.way)+500 || ','
112                               || xmin(src.way)-500 || ' ' || ymax(src.way)+500 || ',' 
113                               || xmin(src.way)-500 || ' ' || ymin(src.way)-500 || '))',
114                                900913)&&dst.way and
115  Distance(src.way, dst.way)<1000 and
116  (src.highway='bus_stop' or 
117  src.railway in ('tram_stop', 'subway_station', 'station', 'halt') or
118    src.amenity='bus_station' or src.aeroway='aerodrome' or
119    src.aerialway='station' or src.amenity='ferry_terminal') and
120  (dst.highway='bus_stop' or
121    dst.railway in ('tram_stop', 'subway_station', 'station', 'halt') or
122    dst.amenity='bus_station' or dst.aeroway='aerodrome' or
123    dst.aerialway='station' or dst.amenity='ferry_terminal')
124group by src.full_id, src.importance, dst.name;
125
126-- delete all stops that are part of a station-rel
127delete from planet_osm_stop_to_station using planet_osm_poipoly where planet_osm_stop_to_station.id=planet_osm_poipoly.full_id and part_of_station=1;
128
129-- stations_all combines stops that are close to each other
130drop table if exists planet_osm_stations;
131create table planet_osm_stations(
132  name        text,
133  stations    varchar(32)[],
134  rel_id      int4,
135  coll_id     int4,
136  importance  text
137);
138SELECT AddGeometryColumn('planet_osm_stations', 'way', 900913, 'GEOMETRY', 2);
139SELECT AddGeometryColumn('planet_osm_stations', 'center', 900913, 'POINT', 2);
140SELECT AddGeometryColumn('planet_osm_stations', 'bbox', 900913, 'LINESTRING', 2);
141SELECT AddGeometryColumn('planet_osm_stations', 'top', 900913, 'POINT', 2);
142SELECT AddGeometryColumn('planet_osm_stations', 'topline', 900913, 'LINESTRING', 2);
143insert into planet_osm_stations
144  select station.name,
145    stations,
146    (array_sort(max(rel_id)))[1],
147    cast(substr(stations[1], position('_' in stations[1])+1) as int),
148    (array['local','suburban','urban','regional','national','international'])
149      [max(CASE
150        WHEN station.importance='suburban' THEN 2 
151        WHEN station.importance='urban' THEN 3
152        WHEN station.importance='regional' THEN 4
153        WHEN station.importance='national' THEN 5
154        WHEN station.importance='international' THEN 6 ELSE 1 END)],
155    ST_Collect(CASE WHEN stops.way is not null THEN stops.way ELSE ST_Centroid(object.way) END)
156from planet_osm_stop_to_station station
157  join 
158  planet_osm_poipoly as object
159     on object.full_id=any(station.stations)
160  left join planet_osm_stops stops on object.full_id='node_'||stops.osm_id
161group by station.name, stations;
162-- TODO: only take centroids, even if member was polygon (because of relation with geo-collection)
163
164-- we don't need this temporary table anymore
165-- drop table planet_osm_stop_to_station;
166
167-- delete all stations with relations and do it again for them
168insert into planet_osm_stations select planet_osm_rels.name, to_textarray(planet_osm_poipoly.full_id), planet_osm_rels.id, null, planet_osm_rels.importance, ST_Collect(way)  from planet_osm_rels join relation_members on relation_members.relation_id=planet_osm_rels.id join planet_osm_poipoly on planet_osm_poipoly.osm_id=relation_members.member_id and planet_osm_poipoly.id_type=(CASE WHEN relation_members.member_type='N' THEN 'node'
169                                                                 WHEN relation_members.member_type='W' THEN' way' END) where type='station' group by planet_osm_rels.id, planet_osm_rels.name, planet_osm_rels.importance;
170
171-- create geo objects of stations
172update planet_osm_stations set 
173  center=ST_Centroid(way),
174  bbox=geomfromtext('LINESTRING(' || XMIN(way)||' '||YMIN(way) ||', '|| XMIN(way)||' '||YMAX(way) ||', '|| XMAX(way)||' '||YMAX(way)||', '|| XMAX(way)||' '||YMIN(way)||', '|| XMIN(way)||' '||YMIN(way) ||', '|| XMIN(way)||' '||YMAX(way)|| ')', 900913), 
175  top=GeometryFromText('POINT(' || x(centroid(envelope(way))) || ' ' || ymax(envelope(way)) || ')', 900913),
176  topline=geomfromtext('LINESTRING(' || XMIN(way)||' '||YMIN(way) ||', '|| XMAX(way)||' '||YMIN(way)|| ')', 900913);
177
178-- feed stations in collection
179insert into planet_osm_colls select coll_id, 'station' from planet_osm_stations where rel_id is null and coll_id is not null;
180insert into coll_tags select coll_id, 'name', name from planet_osm_stations where rel_id is null and coll_id is not null;
181insert into coll_tags select coll_id, 'importance', importance from planet_osm_stations where rel_id is null and coll_id is not null;
182insert into coll_tags select coll_id, 'type', 'station' from planet_osm_stations where rel_id is null and coll_id is not null;
183insert into coll_members
184  select coll_id, p.osm_id, 
185    (CASE WHEN id_type='node' THEN 'N'
186          WHEN id_type='way'  THEN 'W'
187    END),
188    ''
189  from planet_osm_stations st
190    join planet_osm_poipoly p
191      on p.full_id=any(st.stations)
192  where rel_id is null and coll_id is not null;
193
194create index planet_osm_stations_way on planet_osm_stations using gist(way);
195create index planet_osm_stations_bbox on planet_osm_stations using gist(bbox);
196create index planet_osm_stations_center on planet_osm_stations using gist(center);
197create index planet_osm_stations_top on planet_osm_stations using gist(top);
Note: See TracBrowser for help on using the repository browser.