source: subversion/sites/www.openstreetbrowser.org/src/sql/04_places.sql @ 29773

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

Fill guess_area either with poly, buffer or at least it's point

File size: 3.9 KB
Line 
1drop table if exists planet_osm_place;
2create table planet_osm_place (
3  id_place_node int4    ,
4  id_place_poly int4    ,
5  place         text    not null,
6  place_level   int     , -- for sorting
7  name          text    ,
8  name_en       text    ,
9  population    int4    ,
10  pop_cat       text   
11);
12SELECT AddGeometryColumn('planet_osm_place', 'label', 900913, 'POINT', 2);
13SELECT AddGeometryColumn('planet_osm_place', 'area', 900913, 'GEOMETRY', 2);
14SELECT AddGeometryColumn('planet_osm_place', 'guess_area', 900913, 'GEOMETRY', 2);
15
16insert into planet_osm_place
17  select poi.osm_id, planet_osm_boundaries.rel_id, poi.place, 
18    (CASE WHEN place='continent' THEN 0
19      WHEN place='country' THEN 1
20      WHEN place='state' THEN 2
21      WHEN place='city' THEN 3
22      WHEN place='town' THEN 4
23      WHEN place='region' THEN 5
24      WHEN place='county' THEN 6
25      WHEN place='suburb' THEN 7
26      WHEN place='village' THEN 8
27      WHEN place='hamlet' THEN 9
28      WHEN place='locality' THEN 10
29      WHEN place='island' THEN 11
30      WHEN place='islet' THEN 12
31      ELSE null END) as place_level,
32    poi.name, name_en.v,
33    cast(pop.v as int4) as population,
34    (CASE
35      WHEN place='city'    and cast(pop.v as int4)>1000000  THEN 'L' 
36      WHEN place='city'    and cast(pop.v as int4)>200000   THEN 'M' 
37      WHEN place='town'    and cast(pop.v as int4)>30000    THEN 'L'
38      WHEN place='country' and cast(pop.v as int4)>20000000 THEN 'L'
39      WHEN place='country' and cast(pop.v as int4)>1000000  THEN 'M'
40      ELSE 'S' END),
41    poi.way,
42    planet_osm_boundaries.way,
43    (CASE 
44      WHEN planet_osm_boundaries.poly is not null THEN planet_osm_boundaries.poly
45      WHEN place in ('city', 'town', 'village', 'hamlet') THEN
46        Buffer(poi.way, (CASE
47          WHEN place='city' THEN 20000
48          WHEN place='town' THEN 5000
49          WHEN place='village' THEN 2000
50          WHEN place='hamlet' THEN 1000
51        END))
52      ELSE poi.way
53    END)
54  from planet_osm_point poi
55    left join node_tags pop on poi.osm_id=pop.node_id and 
56      pop.k='population' and pop.v similar to '^[0-9]+$'
57    left join node_tags name_en on poi.osm_id=name_en.node_id and 
58      name_en.k='name:en' and poi.name!=name_en.v
59    left join planet_osm_boundaries on poi.osm_id=planet_osm_boundaries.place_id
60  where place is not null
61  order by place_level ASC, population DESC;
62
63create index planet_osm_place_label on planet_osm_place using gist(label);
64create index planet_osm_place_guess_area on planet_osm_place using gist(guess_area);
65create index planet_osm_place_area on planet_osm_place using gist(area);
66create index planet_osm_place_name on planet_osm_place(name);
67create index planet_osm_place_node_id on planet_osm_place(id_place_node);
68
69
70--alter table planet_osm_point drop column population;
71--alter table planet_osm_point add column population int4 null;
72--update planet_osm_point set population=cast(node_tags.v as int4) from node_tags where planet_osm_point.place is not null and node_tags.node_id=planet_osm_point.osm_id and node_tags.k='population' and node_tags.v similar to '^[0-9]*$';
73--update planet_osm_point set population='0' where population is null and place is not null;
74--
75--alter table planet_osm_point add column place_level int4 null;
76--update planet_osm_point set place_level=(CASE WHEN place='continent' THEN 0
77--WHEN place='country' THEN 1
78--WHEN place='state' THEN 2
79--WHEN place='city' THEN 3
80--WHEN place='town' THEN 4
81--WHEN place='region' THEN 5
82--WHEN place='county' THEN 6
83--WHEN place='suburb' THEN 7
84--WHEN place='village' THEN 8
85--WHEN place='hamlet' THEN 9
86--WHEN place='locality' THEN 10
87--WHEN place='island' THEN 11
88--ELSE null END) where place is not null;
89--
90--alter table planet_osm_point add column name_en text;
91--update planet_osm_point set name_en=node_tags.v from node_tags where planet_osm_point.osm_id=node_tags.node_id and node_tags.k='name:en' and planet_osm_point.name!=node_tags.v;
92--
93--create index planet_osm_point_place_level on planet_osm_point(place_level);
94--create index planet_osm_point_population on planet_osm_point(population);
Note: See TracBrowser for help on using the repository browser.