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

Revision 18539, 11.4 KB checked in by skunk, 4 years ago (diff)

Li'l changes

  • add 'name' to poipoly
  • use centroid of way in stations
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='1' 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    order by Distance(poi.way, dst.way) asc limit 1) as next_way
68       from planet_osm_poipoly poi where 
69  (poi.highway='bus_stop' or
70    poi.railway in ('tram_stop', 'station', 'subway_station', 'halt')
71    )
72      ) as t) as t1 left join relation_members rm on rm.member_id=t1.osm_id and rm.member_type=1 group by t1.osm_id, t1.id_type, t1.full_id, t1.type, t1.pos, t1.len, t1.importance, t1.next_way);
73
74-- stop_to_station finds nearby stops with same name
75-- potential BUG: id for point and polygon same in same station
76drop table if exists planet_osm_stop_to_station;
77create table planet_osm_stop_to_station(
78  id varchar(32) not null,
79  rel_id int4[],
80  stations varchar(32)[],
81  name text,
82  importance text, 
83  primary key(id)
84);
85insert into planet_osm_stop_to_station select 
86  src.full_id,
87  array_unique(to_intarray((select station_rel.id from
88      planet_osm_rels station_rel
89    join 
90      relation_members station_member on 
91      station_member.relation_id=station_rel.id and
92      station_member.member_role!='nearby'
93    where
94      station_rel.type='station' and
95      src.osm_id=station_member.member_id and
96      src.id_type=(array['node','way'])[station_member.member_type]
97    limit 1))),
98  array_sort(array_unique(to_textarray(dst.full_id))),
99  dst.name,
100  src.importance
101from 
102  planet_osm_poipoly as dst,
103  planet_osm_poipoly as src
104where 
105  src.name=dst.name and
106  geometryfromtext('POLYGON((' || xmin(src.way)-500 || ' ' || ymin(src.way)-500 || ','
107                               || xmax(src.way)+500 || ' ' || ymin(src.way)-500 || ','
108                               || xmax(src.way)+500 || ' ' || ymax(src.way)+500 || ','
109                               || xmin(src.way)-500 || ' ' || ymax(src.way)+500 || ',' 
110                               || xmin(src.way)-500 || ' ' || ymin(src.way)-500 || '))',
111                                900913)&&dst.way and
112  Distance(src.way, dst.way)<1000 and
113  (src.highway='bus_stop' or 
114  src.railway in ('tram_stop', 'subway_station', 'station', 'halt') or
115    src.amenity='bus_station' or src.aeroway='aerodrome' or
116    src.aerialway='station' or src.amenity='ferry_terminal') and
117  (dst.highway='bus_stop' or
118    dst.railway in ('tram_stop', 'subway_station', 'station', 'halt') or
119    dst.amenity='bus_station' or dst.aeroway='aerodrome' or
120    dst.aerialway='station' or dst.amenity='ferry_terminal')
121group by src.full_id, src.importance, dst.name;
122
123-- delete all stops that are part of a station-rel
124delete 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;
125
126-- stations_all combines stops that are close to each other
127drop table if exists planet_osm_stations;
128create table planet_osm_stations(
129  name        text,
130  stations    varchar(32)[],
131  rel_id      int4,
132  coll_id     int4,
133  importance  text
134);
135SELECT AddGeometryColumn('planet_osm_stations', 'way', 900913, 'GEOMETRY', 2);
136SELECT AddGeometryColumn('planet_osm_stations', 'center', 900913, 'POINT', 2);
137SELECT AddGeometryColumn('planet_osm_stations', 'bbox', 900913, 'LINESTRING', 2);
138SELECT AddGeometryColumn('planet_osm_stations', 'top', 900913, 'POINT', 2);
139SELECT AddGeometryColumn('planet_osm_stations', 'topline', 900913, 'LINESTRING', 2);
140insert into planet_osm_stations
141  select station.name,
142    stations,
143    (array_sort(max(rel_id)))[1],
144    cast(substr(stations[1], position('_' in stations[1])+1) as int),
145    (array['local','suburban','urban','regional','national','international'])
146      [max(CASE
147        WHEN station.importance='suburban' THEN 2 
148        WHEN station.importance='urban' THEN 3
149        WHEN station.importance='regional' THEN 4
150        WHEN station.importance='national' THEN 5
151        WHEN station.importance='international' THEN 6 ELSE 1 END)],
152    ST_Collect(CASE WHEN stops.way is not null THEN stops.way ELSE object.way END)
153from planet_osm_stop_to_station station
154  join 
155  planet_osm_poipoly as object
156     on object.full_id=any(station.stations)
157  left join planet_osm_stops stops on object.full_id='node_'||stops.osm_id
158group by station.name, stations;
159
160-- we don't need this temporary table anymore
161-- drop table planet_osm_stop_to_station;
162
163-- delete all stations with relations and do it again for them
164insert 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=(array['node', 'way'])[relation_members.member_type] where type='station' group by planet_osm_rels.id, planet_osm_rels.name, planet_osm_rels.importance;
165
166-- create geo objects of stations
167update planet_osm_stations set 
168  center=ST_Centroid(way),
169  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), 
170  top=GeometryFromText('POINT(' || x(centroid(envelope(way))) || ' ' || ymax(envelope(way)) || ')', 900913),
171  topline=geomfromtext('LINESTRING(' || XMIN(way)||' '||YMIN(way) ||', '|| XMAX(way)||' '||YMIN(way)|| ')', 900913);
172
173-- feed stations in collection
174insert into planet_osm_colls select coll_id, 'station' from planet_osm_stations where rel_id is null and coll_id is not null;
175insert into coll_tags select coll_id, 'name', name from planet_osm_stations where rel_id is null and coll_id is not null;
176insert into coll_tags select coll_id, 'importance', importance from planet_osm_stations where rel_id is null and coll_id is not null;
177insert into coll_tags select coll_id, 'type', 'station' from planet_osm_stations where rel_id is null and coll_id is not null;
178insert into coll_members
179  select coll_id, p.osm_id, 
180    (CASE WHEN id_type='node' THEN 1
181          WHEN id_type='way'  THEN 2
182    END),
183    ''
184  from planet_osm_stations st
185    join planet_osm_poipoly p
186      on p.full_id=any(st.stations)
187  where rel_id is null and coll_id is not null;
188
189-- feed stations in search
190insert into search 
191  select name, name, null as language, 
192    (CASE 
193      WHEN rel_id is not null THEN 'rel'
194      WHEN coll_id is not null THEN 'coll'
195      ELSE substr(stations[1], 0, position('_' in stations[1]))
196    END),
197    (CASE
198      WHEN rel_id is not null THEN rel_id
199      WHEN coll_id is not null THEN coll_id
200      ELSE cast(substr(stations[1], position('_' in stations[1])+1) as int)
201    END),
202    'station', null
203  from planet_osm_stations where name is not null;
204
205create index planet_osm_stations_way on planet_osm_stations using gist(way);
206create index planet_osm_stations_bbox on planet_osm_stations using gist(bbox);
207create index planet_osm_stations_center on planet_osm_stations using gist(center);
208create index planet_osm_stations_top on planet_osm_stations using gist(top);
Note: See TracBrowser for help on using the repository browser.