4TghFWyW

1-- The following lines must be added to default.style
2-- # Extras for create_osm_street_list.sql
3-- way       postal_code       text       linear
4-- way       alt_name          text       linear
5-- way       name:lb           text       linear
6-- way       alt_name:lb       text       linear
7-- way       is_in:city        text       linear
8
9DROP TABLE IF EXISTS road_names_osm;
10
11CREATE Table road_names_osm (
12    commune TEXT,
13    rue TEXT,
14    localite TEXT,
15    postal_code TEXT,
16    alt_name TEXT,
17    namelb TEXT,
18    alt_namelb TEXT
19);
20
21-- rues
22
23insert INTO road_names_osm (commune, rue, postal_code, alt_name, namelb, alt_namelb)
24    select g.name as commune, l.name as rue, l.postal_code, l.alt_name, l."name:lb" as namelb, l."alt_name:lb" as alt_namelb from planet_osm_line l, planet_osm_polygon g
25where l.highway is not null
26AND l.name is not null
27AND ST_Intersects(l.way,g.way)
28AND g.admin_level = '8'
29GROUP BY commune,rue,l.postal_code, l.alt_name, namelb, alt_namelb
30ORDER BY commune, rue;
31
32-- places (areas)
33
34insert INTO road_names_osm (commune, rue, postal_code, alt_name, namelb, alt_namelb)
35select g.name as commune, l.name as rue, l.postal_code, l.alt_name, l."name:lb" as namelb, l."alt_name:lb" as alt_namelb from planet_osm_polygon l, planet_osm_polygon g
36where l.highway is not null
37AND l.name is not null
38AND ST_Intersects(l.way,g.way)
39AND g.admin_level = '8'
40GROUP BY commune,rue,l.postal_code, l.alt_name, namelb, alt_namelb
41ORDER BY commune, rue;