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

Revision 19279, 15.3 KB checked in by skunk, 4 years ago (diff)

Bugfix

  • error creating housenumbers
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 4 
17      WHEN root."network"='national' THEN 3 
18      WHEN root."network"='region' THEN 2 
19      WHEN root."network"='urban' THEN 1 
20      WHEN root."network"='local' THEN 0 
21      WHEN root.highway='motorway' THEN 4 
22      WHEN root.highway in ('trunk', 'primary') THEN 3 
23      WHEN root.highway in ('secondary', 'tertiary') or root."railway" in ('rail') or root."waterway" in ('river', 'canal') THEN 2 
24      WHEN root."waterway" in ('stream') THEN 0 
25      ELSE 1 
26    END)+(CASE 
27      WHEN root."highway" is not null THEN 30 
28      WHEN root."railway" is not null THEN 20
29      WHEN root."waterway" is not null THEN 10
30      ELSE 0 END),
31    to_intarray(next.osm_id) as neighbours
32  from planet_osm_line root
33    join planet_osm_line next on root.name=next.name
34  where root.osm_id>0 and next.osm_id>0 and 
35    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
36    Distance(root.way, next.way)<200 
37  group by root.osm_id, root.name, root.highway, root.railway, root.waterway, root.network;
38
39drop table if exists planet_osm_streets;
40create table planet_osm_streets (
41  osm_id        int4    not null,
42  way_parts     int4[]  not null,
43  highway_level int default 0,
44  name text,
45  primary key(osm_id)
46);
47drop table if exists planet_osm_streets_parts;
48create table planet_osm_streets_parts (
49  osm_id        int4    not null,
50  part          int4    not null
51);
52create index planet_osm_streets_parts_pkey on planet_osm_streets_parts(osm_id);
53create index planet_osm_streets_parts_part on planet_osm_streets_parts(part);
54
55drop table if exists streets_tmp2;
56create table streets_tmp2 (
57  osm_id        int4    not null,
58  primary key(osm_id)
59);
60
61create or replace function collect_streets(root_id int4, name text) returns int as $$
62declare
63        ids  int4[];
64        new  int4[];
65        orig int4[];
66        res  record;
67        done int4[];
68        index int:=1;
69        x boolean;
70        l1   int;
71        max_highway_level int:=0;
72begin
73        select count(*)>0 from streets_tmp2 where osm_id=root_id into x;
74        if x then
75          return 0;
76        end if;
77
78        ids:=array[root_id];
79        done:='{}';
80        while done<>ids loop
81          index:=1;
82          while ids[index]>0 loop
83            select count(*)>0 from streets_tmp2 where osm_id=ids[index] into x;
84            if not x then
85--            raise notice 'processing %', ids[index];
86              select array_cat(new, neighbours) from streets_tmp1 where osm_id=ids[index] into new;
87              select highway_level from streets_tmp1 where osm_id=ids[index] into l1;
88              insert into streets_tmp2 values (ids[index]);
89              if l1>max_highway_level then
90                max_highway_level=l1;
91              end if;
92            end if;
93            index:=index+1;
94          end loop;
95
96          done:=array_unique(array_cat(done, ids));
97          ids:=array_unique(new);
98        end loop;
99
100--      raise notice 'foo bar %', ids[1];
101        insert into planet_osm_streets values (ids[1], ids, max_highway_level, name);
102        for index in array_lower(ids, 1)..array_upper(ids, 1) loop
103          insert into planet_osm_streets_parts values (ids[1], ids[index]);
104        end loop;
105        return 1;
106end;
107$$ language plpgsql;
108
109begin;
110select collect_streets(osm_id, name) from streets_tmp1;
111commit;
112
113SELECT AddGeometryColumn('planet_osm_streets', 'way', 900913, 'MULTILINESTRING', 2);
114alter table planet_osm_streets add column waytype text;
115alter table planet_osm_streets add column network text;
116
117update planet_osm_streets set way=(select ST_Multi(ST_LineMerge(ST_Collect(way))) from planet_osm_line join planet_osm_streets_parts on planet_osm_line.osm_id=planet_osm_streets_parts.part where planet_osm_streets_parts.osm_id=planet_osm_streets.osm_id);
118
119update planet_osm_streets set 
120  network=(CASE 
121    WHEN highway_level%10=4 THEN 'international' 
122    WHEN highway_level%10=3 THEN 'national' 
123    WHEN highway_level%10=2 THEN 'region' 
124    WHEN highway_level%10=1 THEN 'urban' 
125    ELSE 'local' END),
126  waytype=(CASE
127    WHEN highway_level>=30 THEN 'street'
128    WHEN highway_level>=20 THEN 'railway'
129    WHEN highway_level>=10 THEN 'waterway'
130    ELSE 'same_name'
131  END);
132
133alter table planet_osm_streets drop column highway_level;
134drop table streets_tmp2;
135drop table streets_tmp1;
136
137insert 
138  into planet_osm_colls
139  select 
140    planet_osm_streets.osm_id, planet_osm_streets.waytype, 
141    array['name', 'network'],
142    array[name, network]
143  from planet_osm_streets;
144
145insert 
146  into coll_members
147  select 
148    planet_osm_streets.osm_id,
149    planet_osm_line.osm_id,
150    2
151  from planet_osm_streets join planet_osm_line on planet_osm_line.osm_id=any(planet_osm_streets.way_parts);
152
153insert 
154  into coll_tags
155  select 
156    planet_osm_streets.osm_id,
157    'name',
158    name
159  from planet_osm_streets;
160
161insert 
162  into coll_tags
163  select 
164    planet_osm_streets.osm_id,
165    way_tags.k,
166    (to_textarray(way_tags.v))[1]
167  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
168  (way_tags.k like 'wikipedia:%' or way_tags.k like 'name:%')
169  group by planet_osm_streets.osm_id, planet_osm_streets.way_parts, planet_osm_streets.network, way_tags.k;
170
171
172insert 
173  into coll_tags
174  select 
175    planet_osm_streets.osm_id,
176    'network',
177    network
178  from planet_osm_streets;
179
180insert 
181  into coll_tags
182  select 
183    planet_osm_streets.osm_id,
184    'type',
185    waytype
186  from planet_osm_streets;
187
188create index planet_osm_streets_way on planet_osm_streets using gist(way);
189create index planet_osm_streets_network on planet_osm_streets(network);
190
191update planet_osm_line set "addr:street"=str.name from planet_osm_line str,
192(select ob.osm_id, 
193  (select osm_id from planet_osm_line findstr where
194    findstr.name is not null and findstr.highway is not null
195    and 
196      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
197    order by 
198      Distance(geometryfromtext('POINT(' || n1.lon || ' ' || n1.lat || ')', 900913), findstr.way)+
199      Distance(geometryfromtext('POINT(' || n2.lon || ' ' || n2.lat || ')', 900913), findstr.way) asc limit 1) as str_id
200from planet_osm_line ob
201  join way_nodes wn1 on ob.osm_id=wn1.way_id
202  join planet_osm_nodes n1 on wn1.node_id=n1.id and wn1.sequence_id=0
203  join way_nodes wn2 on ob.osm_id=wn2.way_id
204  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
205   and ob."addr:interpolation" in ('odd', 'even', 'all', 'alphabetic') and ob."addr:street" is null
206  order by sequence_id desc limit 1)) as t
207  where planet_osm_line.osm_id=t.osm_id and str.osm_id=t.str_id;
208update 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;
209update planet_osm_point set "addr:street"=str.name from planet_osm_line str,
210(select ob.osm_id, 
211  (select osm_id from planet_osm_line findstr where
212    findstr.name is not null and findstr.highway is not null
213    and 
214      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
215    order by 
216      Distance(geometryfromtext('POINT(' || n.lon || ' ' || n.lat || ')', 900913), findstr.way) asc limit 1) as str_id
217from planet_osm_point ob
218  join planet_osm_nodes n on ob.osm_id=n.id
219   where ob."addr:housenumber" is not null and ob."addr:street" is null) as t
220  where planet_osm_point.osm_id=t.osm_id and str.osm_id=t.str_id;
221update planet_osm_polygon set "addr:street"=str.name from planet_osm_line str,
222(select ob.osm_id, 
223  (select osm_id from planet_osm_line findstr where
224    findstr.name is not null and findstr.highway is not null
225    and 
226      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
227    order by 
228      Distance(geometryfromtext('POINT(' || n.lon || ' ' || n.lat || ')', 900913), findstr.way) asc limit 1) as str_id
229from planet_osm_polygon ob
230  join planet_osm_nodes n on ob.osm_id=n.id
231   where ob."addr:housenumber" is not null and ob."addr:street" is null) as t
232  where planet_osm_polygon.osm_id=t.osm_id and str.osm_id=t.str_id;
233
234
235insert 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;
236insert 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;
237insert 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;
238
239drop table if exists housenumber;
240create table housenumber (
241node_id int4 null,
242way_id  int4 null,
243coll_id int4 null,
244number text
245);
246SELECT AddGeometryColumn('housenumber', 'way', 900913, 'LINESTRING', 2);
247insert into housenumber
248(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
249(select osm_id, number, poi_way,
250  line_interpolate_point(next_way, pos) as next_poi,
251  makeline((CASE 
252    WHEN pos-0.001/len<0 THEN line_interpolate_point(next_way, pos)
253    ELSE line_interpolate_point(next_way, pos-0.001/len)
254  END), 
255  (CASE 
256    WHEN pos+0.001/len>=1 THEN line_interpolate_point(next_way, pos)
257    ELSE line_interpolate_point(next_way, pos+0.001/len)
258  END)) as line
259from 
260(select t.osm_id, number, poi_way, next_way, line_locate_point(next_way, poi_way) as pos, length(next_way) as len from (
261select poi.osm_id, poi."addr:housenumber" as number, poi.way as poi_way, 
262  (select l.way
263    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);
264
265insert into housenumber
266(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
267(select osm_id, number, poi_way,
268  line_interpolate_point(next_way, pos) as next_poi,
269  makeline((CASE 
270    WHEN pos-0.001/len<0 THEN line_interpolate_point(next_way, pos)
271    ELSE line_interpolate_point(next_way, pos-0.001/len)
272  END), 
273  (CASE 
274    WHEN pos+0.001/len>=1 THEN line_interpolate_point(next_way, pos)
275    ELSE line_interpolate_point(next_way, pos+0.001/len)
276  END)) as line
277from 
278(select t.osm_id, number, poi_way, next_way, line_locate_point(next_way, poi_way) as pos, length(next_way) as len from (
279select poi.osm_id, poi."addr:housenumber" as number, Centroid(poi.way) as poi_way, 
280  (select l.way
281    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);
282create index housenumber_way on housenumber using gist(way);
283create index housenumber_node_id on housenumber(node_id);
284create index housenumber_way_id on housenumber(way_id);
285
286drop table if exists housenumber_line;
287create table housenumber_line (
288way_id  int4 null,
289coll_id int4 null,
290first int,
291last int,
292interpolation text
293);
294
295insert into housenumber_line
296select osm_id, coll_id, 
297(CASE WHEN first<last THEN first ELSE last END),
298(CASE WHEN first<last THEN last ELSE first END),
299interpolation from
300(select osm_id, coll_id, cast(first as int) as first, cast(last as int) as last, interpolation from
301  (select osm_id, 
302    (select coll_id from coll_members cm where cm.member_id=osm_id and cm.member_type='W' limit 1) as coll_id,
303    (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,
304    (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,
305    "addr:interpolation" as interpolation
306    from planet_osm_line where "addr:interpolation" in ('odd', 'even', 'all', 'alphabetic')) as t
307  where first similar to '[0-9]+' and last similar to '[0-9]+') as t1;
308
309--create index
Note: See TracBrowser for help on using the repository browser.