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

Last change on this file since 29773 was 19915, checked in by skunk, 11 years ago

Streets: Add tags for special type to collection

File size: 17.2 KB
Line 
1drop table if exists streets_tmp1;
2create table streets_tmp1 (
3  osm_id        int4    not null,
4  name  text    not null,
5  highway_level int,
6  neighbours    int4[]  ,
7  primary key(osm_id)
8);
9
10create index planet_osm_line_name on planet_osm_line(name);
11
12insert into streets_tmp1
13  select distinct 
14    root.osm_id, root.name, 
15    (CASE 
16      WHEN root."network"='international' THEN 5 
17      WHEN root."network"='national' THEN 4 
18      WHEN root."network"='region' THEN 3 
19      WHEN root."network"='regional' THEN 3 
20      WHEN root."network"='urban' THEN 2 
21      WHEN root."network"='suburban' THEN 1 
22      WHEN root."network"='local' THEN 0 
23      WHEN root.highway='motorway' THEN 5 
24      WHEN root.highway='trunk' THEN 4 
25      WHEN root.highway in ('primary') 
26        OR (root."railway" in ('rail') and root."usage" in ('main'))
27        THEN 4 
28      WHEN (root."railway" in ('rail') and (root."usage" in ('', 'branch') or root."usage" is null))
29        THEN 3
30      WHEN root.highway in ('secondary', 'tertiary')
31        OR root."waterway" in ('river')
32        THEN 3 
33      WHEN root."waterway" in ('stream', 'drain')
34        THEN 1 
35      WHEN root."waterway" in ('ditch')
36        THEN 0
37      ELSE 2 
38    END)+(CASE 
39      WHEN root."highway" is not null THEN 30 
40      WHEN root."railway" is not null THEN 20
41      WHEN root."waterway" is not null THEN 10
42      ELSE 0 END),
43    to_intarray(next.osm_id) as neighbours
44  from planet_osm_line root
45    join planet_osm_line next on root.name=next.name
46  where root.osm_id>0 and next.osm_id>0 and 
47    makepolygon(geometryfromtext('LINESTRING(' || xmin(root.way)-200 || ' ' || ymin(root.way)-200 || ',' || xmax(root.way)+200 || ' ' || ymin(root.way)-200 || ',' || xmax(root.way)+200 || ' ' || ymax(root.way)+200 || ',' || xmin(root.way)-200 || ' ' || ymax(root.way)+200 || ',' || xmin(root.way)-200 || ' ' || ymin(root.way)-200 || ')', 900913))&&next.way and
48    Distance(root.way, next.way)<200
49    and ((root."highway" is not null and next."highway" is not null)
50      or (root."railway"=next."railway")
51      or (root."waterway"=next."waterway"))
52    and (
53      root."highway" in ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', 'tertiary', 'unclassified', 'road', 'residential', 'living_street', 'service', 'track', 'pedestrian', 'bus_guideway', 'path', 'cycleway', 'footway', 'bridleway', 'steps')
54      or 
55      root."railway" in ('rail', 'tram', 'light_rail', 'subway', 'preserved', 'narrow_gauge', 'monorail', 'funicular')
56      or
57      root."waterway" in ('stream', 'river', 'canal', 'ditch', 'drain'))
58    and (
59      next."highway" in ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', 'tertiary', 'unclassified', 'road', 'residential', 'living_street', 'service', 'track', 'pedestrian', 'bus_guideway', 'path', 'cycleway', 'footway', 'bridleway', 'steps')
60      or 
61      next."railway" in ('rail', 'tram', 'light_rail', 'subway', 'preserved', 'narrow_gauge', 'monorail', 'funicular')
62      or
63      next."waterway" in ('stream', 'river', 'canal', 'ditch', 'drain'))
64  group by root.osm_id, root.name, root.highway, root.railway, root.waterway, root.network, root."usage";
65
66drop table if exists planet_osm_streets_tmp;
67create table planet_osm_streets_tmp (
68  osm_id        int4    not null,
69  way_parts     int4[]  not null,
70  highway_level int default 0,
71  name text,
72  primary key(osm_id)
73);
74drop table if exists streets_tmp2;
75create table streets_tmp2 (
76  osm_id        int4    not null,
77  primary key(osm_id)
78);
79
80create or replace function collect_streets(root_id int4, name text) returns int as $$
81declare
82        ids  int4[];
83        new  int4[];
84        orig int4[];
85        res  record;
86        done int4[];
87        index int:=1;
88        x boolean;
89        l1   int;
90        max_highway_level int:=0;
91begin
92        select count(*)>0 from streets_tmp2 where osm_id=root_id into x;
93        if x then
94          return 0;
95        end if;
96
97        ids:=array[root_id];
98        done:='{}';
99        while done<>ids loop
100          index:=1;
101          while ids[index]>0 loop
102            select count(*)>0 from streets_tmp2 where osm_id=ids[index] into x;
103            if not x then
104--            raise notice 'processing %', ids[index];
105              select array_cat(new, neighbours) from streets_tmp1 where osm_id=ids[index] into new;
106              select highway_level from streets_tmp1 where osm_id=ids[index] into l1;
107              insert into streets_tmp2 values (ids[index]);
108              if l1>max_highway_level then
109                max_highway_level=l1;
110              end if;
111            end if;
112            index:=index+1;
113          end loop;
114
115          done:=array_unique(array_cat(done, ids));
116          ids:=array_unique(new);
117        end loop;
118
119--      raise notice 'foo bar %', ids[1];
120        insert into planet_osm_streets_tmp values (ids[1], ids, max_highway_level, name);
121        return 1;
122end;
123$$ language plpgsql;
124
125begin;
126select collect_streets(osm_id, name) from streets_tmp1;
127commit;
128
129drop table if exists planet_osm_streets;
130create table planet_osm_streets (
131  osm_id        int4    not null,
132  way_parts     int4[]  not null,
133  highway_level int default 0,
134  waytype       text,
135  importance    text,
136  highway       text,
137  railway       text,
138  waterway      text,
139  name text,
140  primary key(osm_id)
141);
142SELECT AddGeometryColumn('planet_osm_streets', 'way', 900913, 'GEOMETRY', 2);
143
144insert into planet_osm_streets
145  select st.osm_id, st.way_parts, st.highway_level,
146    (CASE
147      WHEN st.highway_level>=30 THEN 'street'
148      WHEN st.highway_level>=20 THEN 'railway'
149      WHEN st.highway_level>=10 THEN 'waterway'
150      ELSE 'same_name'
151    END),
152     (CASE 
153      WHEN st.highway_level%10=5 THEN 'international' 
154      WHEN st.highway_level%10=4 THEN 'national' 
155      WHEN st.highway_level%10=3 THEN 'regional' 
156      WHEN st.highway_level%10=2 THEN 'urban' 
157      WHEN st.highway_level%10=1 THEN 'suburban' 
158      ELSE 'local' END),
159    array_to_string(array_unique(to_textarray(l."highway")), ';'),
160    array_to_string(array_unique(to_textarray(l."railway")), ';'),
161    array_to_string(array_unique(to_textarray(l."waterway")), ';'),
162    (to_textarray(l.name))[1],
163    ST_Collect(l.way)
164  from planet_osm_streets_tmp st join
165    planet_osm_line l on l.osm_id=any(st.way_parts)
166  group by st.osm_id, st.way_parts, st.highway_level;
167
168drop table streets_tmp2;
169drop table streets_tmp1;
170drop table planet_osm_streets_tmp;
171
172insert 
173  into planet_osm_colls
174  select 
175    planet_osm_streets.osm_id, planet_osm_streets.waytype, 
176    array['name', 'importance'],
177    array[name, importance]
178  from planet_osm_streets;
179
180insert 
181  into coll_members
182  select 
183    planet_osm_streets.osm_id,
184    planet_osm_line.osm_id,
185    'W'
186  from planet_osm_streets join planet_osm_line on planet_osm_line.osm_id=any(planet_osm_streets.way_parts);
187
188insert 
189  into coll_tags
190  select 
191    planet_osm_streets.osm_id,
192    'name',
193    name
194  from planet_osm_streets;
195
196insert 
197  into coll_tags
198  select 
199    planet_osm_streets.osm_id,
200    (CASE
201      WHEN "highway" is not null THEN 'highway'
202      WHEN "railway" is not null THEN 'railway'
203      WHEN "waterway" is not null THEN 'waterway'
204    END),
205    (CASE
206      WHEN "highway" is not null THEN "highway"
207      WHEN "railway" is not null THEN "railway"
208      WHEN "waterway" is not null THEN "waterway"
209    END)
210  from planet_osm_streets;
211
212insert 
213  into coll_tags
214  select 
215    planet_osm_streets.osm_id,
216    way_tags.k,
217    (to_textarray(way_tags.v))[1]
218  from planet_osm_streets join coll_members on coll_members.coll_id=planet_osm_streets.osm_id join way_tags on way_tags.way_id=coll_members.member_id and coll_members.member_type='W' and
219  (way_tags.k like 'wikipedia:%' or way_tags.k like 'name:%')
220  group by planet_osm_streets.osm_id, planet_osm_streets.way_parts, planet_osm_streets.importance, way_tags.k;
221
222
223insert 
224  into coll_tags
225  select 
226    planet_osm_streets.osm_id,
227    'importance',
228    importance
229  from planet_osm_streets;
230
231insert 
232  into coll_tags
233  select 
234    planet_osm_streets.osm_id,
235    'type',
236    waytype
237  from planet_osm_streets;
238
239create index planet_osm_streets_way on planet_osm_streets using gist(way);
240create index planet_osm_streets_importance on planet_osm_streets(importance);
241
242update planet_osm_line set "addr:street"=str.name from planet_osm_line str,
243(select ob.osm_id, 
244  (select osm_id from planet_osm_line findstr where
245    findstr.name is not null and findstr.highway is not null
246    and 
247      makepolygon(geometryfromtext('LINESTRING(' || xmin(ob.way)-200 || ' ' || ymin(ob.way)-200 || ',' || xmax(ob.way)+200 || ' ' || ymin(ob.way)-200 || ',' || xmax(ob.way)+200 || ' ' || ymax(ob.way)+200 || ',' || xmin(ob.way)-200 || ' ' || ymax(ob.way)+200 || ',' || xmin(ob.way)-200 || ' ' || ymin(ob.way)-200 || ')', 900913))&&findstr.way
248    order by 
249      Distance(geometryfromtext('POINT(' || n1.lon || ' ' || n1.lat || ')', 900913), findstr.way)+
250      Distance(geometryfromtext('POINT(' || n2.lon || ' ' || n2.lat || ')', 900913), findstr.way) asc limit 1) as str_id
251from planet_osm_line ob
252  join way_nodes wn1 on ob.osm_id=wn1.way_id
253  join planet_osm_nodes n1 on wn1.node_id=n1.id and wn1.sequence_id=0
254  join way_nodes wn2 on ob.osm_id=wn2.way_id
255  join planet_osm_nodes n2 on wn2.node_id=n2.id and wn2.sequence_id=(select sequence_id from way_nodes wn2a where wn2a.way_id=ob.osm_id
256   and ob."addr:interpolation" in ('odd', 'even', 'all', 'alphabetic') and ob."addr:street" is null
257  order by sequence_id desc limit 1)) as t
258  where planet_osm_line.osm_id=t.osm_id and str.osm_id=t.str_id;
259update planet_osm_point set "addr:street"=str."addr:street" from way_nodes wn left join planet_osm_line str on wn.way_id=str.osm_id where planet_osm_point.osm_id=wn.node_id and planet_osm_point."addr:street" is null and str."addr:street" is not null;
260update planet_osm_point set "addr:street"=str.name from planet_osm_line str,
261(select ob.osm_id, 
262  (select osm_id from planet_osm_line findstr where
263    findstr.name is not null and findstr.highway is not null
264    and 
265      makepolygon(geometryfromtext('LINESTRING(' || xmin(ob.way)-200 || ' ' || ymin(ob.way)-200 || ',' || xmax(ob.way)+200 || ' ' || ymin(ob.way)-200 || ',' || xmax(ob.way)+200 || ' ' || ymax(ob.way)+200 || ',' || xmin(ob.way)-200 || ' ' || ymax(ob.way)+200 || ',' || xmin(ob.way)-200 || ' ' || ymin(ob.way)-200 || ')', 900913))&&findstr.way
266    order by 
267      Distance(geometryfromtext('POINT(' || n.lon || ' ' || n.lat || ')', 900913), findstr.way) asc limit 1) as str_id
268from planet_osm_point ob
269  join planet_osm_nodes n on ob.osm_id=n.id
270   where ob."addr:housenumber" is not null and ob."addr:street" is null) as t
271  where planet_osm_point.osm_id=t.osm_id and str.osm_id=t.str_id;
272update planet_osm_polygon set "addr:street"=str.name from planet_osm_line str,
273(select ob.osm_id, 
274  (select osm_id from planet_osm_line findstr where
275    findstr.name is not null and findstr.highway is not null
276    and 
277      makepolygon(geometryfromtext('LINESTRING(' || xmin(ob.way)-200 || ' ' || ymin(ob.way)-200 || ',' || xmax(ob.way)+200 || ' ' || ymin(ob.way)-200 || ',' || xmax(ob.way)+200 || ' ' || ymax(ob.way)+200 || ',' || xmin(ob.way)-200 || ' ' || ymax(ob.way)+200 || ',' || xmin(ob.way)-200 || ' ' || ymin(ob.way)-200 || ')', 900913))&&findstr.way
278    order by 
279      Distance(geometryfromtext('POINT(' || n.lon || ' ' || n.lat || ')', 900913), findstr.way) asc limit 1) as str_id
280from planet_osm_polygon ob
281  join planet_osm_nodes n on ob.osm_id=n.id
282   where ob."addr:housenumber" is not null and ob."addr:street" is null) as t
283  where planet_osm_polygon.osm_id=t.osm_id and str.osm_id=t.str_id;
284
285
286insert into coll_members select distinct root.osm_id, next.osm_id, 'W', 'housenumber' from planet_osm_streets root join planet_osm_line next on root.name=next."addr:street" where root.osm_id>0 and next.osm_id>0 and makepolygon(geometryfromtext('LINESTRING(' || xmin(root.way)-200 || ' ' || ymin(root.way)-200 || ',' || xmax(root.way)+200 || ' ' || ymin(root.way)-200 || ',' || xmax(root.way)+200 || ' ' || ymax(root.way)+200 || ',' || xmin(root.way)-200 || ' ' || ymax(root.way)+200 || ',' || xmin(root.way)-200 || ' ' || ymin(root.way)-200 || ')', 900913))&&next.way and Distance(root.way, next.way)<200;
287insert into coll_members select distinct root.osm_id, next.osm_id, 'N', 'housenumber' from planet_osm_streets root join planet_osm_point next on root.name=next."addr:street" where root.osm_id>0 and next.osm_id>0 and makepolygon(geometryfromtext('LINESTRING(' || xmin(root.way)-200 || ' ' || ymin(root.way)-200 || ',' || xmax(root.way)+200 || ' ' || ymin(root.way)-200 || ',' || xmax(root.way)+200 || ' ' || ymax(root.way)+200 || ',' || xmin(root.way)-200 || ' ' || ymax(root.way)+200 || ',' || xmin(root.way)-200 || ' ' || ymin(root.way)-200 || ')', 900913))&&next.way and Distance(root.way, next.way)<200;
288insert into coll_members select distinct root.osm_id, next.osm_id, 'W', 'housenumber' from planet_osm_streets root join planet_osm_polygon next on root.name=next."addr:street" where root.osm_id>0 and next.osm_id>0 and makepolygon(geometryfromtext('LINESTRING(' || xmin(root.way)-200 || ' ' || ymin(root.way)-200 || ',' || xmax(root.way)+200 || ' ' || ymin(root.way)-200 || ',' || xmax(root.way)+200 || ' ' || ymax(root.way)+200 || ',' || xmin(root.way)-200 || ' ' || ymax(root.way)+200 || ',' || xmin(root.way)-200 || ' ' || ymin(root.way)-200 || ')', 900913))&&next.way and Distance(root.way, next.way)<200;
289
290drop table if exists housenumber;
291create table housenumber (
292node_id int4 null,
293way_id  int4 null,
294coll_id int4 null,
295number text
296);
297SELECT AddGeometryColumn('housenumber', 'way', 900913, 'LINESTRING', 2);
298insert into housenumber
299(select osm_id, null, (select coll_id from coll_members cm where cm.member_id=osm_id and cm.member_type='N' limit 1), number, (CASE WHEN length(line)>0 THEN translate(scale(translate(line, -x(centroid(line)), -y(centroid(line))), 100/length(line), 100/length(line)), x(poi_way), y(poi_way)) END) as way from
300(select osm_id, number, poi_way,
301  line_interpolate_point(next_way, pos) as next_poi,
302  makeline((CASE 
303    WHEN pos-0.001/len<0 THEN line_interpolate_point(next_way, pos)
304    ELSE line_interpolate_point(next_way, pos-0.001/len)
305  END), 
306  (CASE 
307    WHEN pos+0.001/len>=1 THEN line_interpolate_point(next_way, pos)
308    ELSE line_interpolate_point(next_way, pos+0.001/len)
309  END)) as line
310from 
311(select t.osm_id, number, poi_way, next_way, line_locate_point(next_way, poi_way) as pos, length(next_way) as len from (
312select poi.osm_id, poi."addr:housenumber" as number, poi.way as poi_way, 
313  (select l.way
314    from coll_members find_street1 join coll_members find_street2 on find_street2.coll_id=find_street1.coll_id and find_street2.member_type='W' join planet_osm_line l on l.osm_id=find_street2.member_id where find_street1.member_id=poi.osm_id and find_street1.member_type='N' order by distance(poi.way, l.way) asc limit 1) as next_way from planet_osm_point poi where poi."addr:housenumber" is not null and poi."addr:street" is not null) as t) as t2) as t3);
315
316insert into housenumber
317(select null, osm_id, (select coll_id from coll_members cm where cm.member_id=osm_id and cm.member_type='W' limit 1), number, (CASE WHEN length(line)>0 THEN translate(scale(translate(line, -x(centroid(line)), -y(centroid(line))), 100/length(line), 100/length(line)), x(poi_way), y(poi_way)) END) as way from
318(select osm_id, number, poi_way,
319  line_interpolate_point(next_way, pos) as next_poi,
320  makeline((CASE 
321    WHEN pos-0.001/len<0 THEN line_interpolate_point(next_way, pos)
322    ELSE line_interpolate_point(next_way, pos-0.001/len)
323  END), 
324  (CASE 
325    WHEN pos+0.001/len>=1 THEN line_interpolate_point(next_way, pos)
326    ELSE line_interpolate_point(next_way, pos+0.001/len)
327  END)) as line
328from 
329(select t.osm_id, number, poi_way, next_way, line_locate_point(next_way, poi_way) as pos, length(next_way) as len from (
330select poi.osm_id, poi."addr:housenumber" as number, Centroid(poi.way) as poi_way, 
331  (select l.way
332    from coll_members find_street1 join coll_members find_street2 on find_street2.coll_id=find_street1.coll_id and find_street2.member_type='W' join planet_osm_line l on l.osm_id=find_street2.member_id where find_street1.member_id=poi.osm_id and find_street1.member_type='W' order by distance(Centroid(poi.way), l.way) asc limit 1) as next_way from planet_osm_polygon poi where poi."addr:housenumber" is not null and poi."addr:street" is not null) as t) as t2) as t3);
333create index housenumber_way on housenumber using gist(way);
334create index housenumber_node_id on housenumber(node_id);
335create index housenumber_way_id on housenumber(way_id);
336
337drop table if exists housenumber_line;
338create table housenumber_line (
339way_id  int4 null,
340coll_id int4 null,
341first int,
342last int,
343interpolation text
344);
345
346insert into housenumber_line
347select osm_id, coll_id, 
348(CASE WHEN first<last THEN first ELSE last END),
349(CASE WHEN first<last THEN last ELSE first END),
350interpolation from
351(select osm_id, coll_id, cast(first as int) as first, cast(last as int) as last, interpolation from
352  (select osm_id, 
353    (select coll_id from coll_members cm where cm.member_id=osm_id and cm.member_type='W' limit 1) as coll_id,
354    (select "addr:housenumber" from way_nodes join planet_osm_point on way_nodes.way_id=planet_osm_line.osm_id and way_nodes.node_id=planet_osm_point.osm_id order by way_nodes.sequence_id asc limit 1) as first,
355    (select "addr:housenumber" from way_nodes join planet_osm_point on way_nodes.way_id=planet_osm_line.osm_id and way_nodes.node_id=planet_osm_point.osm_id order by way_nodes.sequence_id desc limit 1) as last,
356    "addr:interpolation" as interpolation
357    from planet_osm_line where "addr:interpolation" in ('odd', 'even', 'all', 'alphabetic')) as t
358  where first similar to '[0-9]+' and last similar to '[0-9]+') as t1;
359
360--create index
Note: See TracBrowser for help on using the repository browser.