source: subversion/sites/www.openstreetbrowser.org/src/sql/02_relations.sql @ 29773

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

Changes to comply with osmosis-0.31

  • new sql preprocess scripts (osmosis stores 'N', 'W', 'R' as member_type of relations instead of 1, 2, 3)
File size: 2.5 KB
Line 
1--alter table planet_osm_rels add column node_parts int[] null;
2--update planet_osm_rels set node_parts=(select to_intarray(member_id) from relation_members where member_type='N' and relation_members.relation_id=planet_osm_rels.id group by relation_id);
3--
4--alter table planet_osm_rels add column way_parts int[] null;
5--update planet_osm_rels set way_parts=(select to_intarray(member_id) from relation_members where member_type='W' and relation_members.relation_id=planet_osm_rels.id group by relation_id);
6--
7--alter table planet_osm_rels add column rel_parts int[] null;
8--update planet_osm_rels set rel_parts=(select to_intarray(member_id) from relation_members where member_type='R' and relation_members.relation_id=planet_osm_rels.id group by relation_id);
9
10alter table planet_osm_rels add column name text;
11update planet_osm_rels set name=(select v from relation_tags where relation_tags.relation_id=planet_osm_rels.id and k='name' limit 1);
12
13alter table planet_osm_rels add column route text;
14update planet_osm_rels set route=(select v from relation_tags where relation_tags.relation_id=planet_osm_rels.id and k='route' limit 1);
15
16alter table planet_osm_rels add column type text;
17update planet_osm_rels set type=(select v from relation_tags where relation_tags.relation_id=planet_osm_rels.id and k='type' limit 1);
18
19alter table planet_osm_rels add column ref text;
20update planet_osm_rels set ref=(select v from relation_tags where relation_tags.relation_id=planet_osm_rels.id and k='ref' limit 1);
21
22alter table planet_osm_rels add column network text;
23update planet_osm_rels set network=(select v from relation_tags where relation_tags.relation_id=planet_osm_rels.id and k='network' limit 1);
24
25alter table planet_osm_rels add column importance text;
26update planet_osm_rels set importance=(select v from relation_tags where relation_tags.relation_id=planet_osm_rels.id and k='importance' limit 1);
27
28alter table planet_osm_rels add column admin_level text;
29update planet_osm_rels set admin_level=(select v from relation_tags where relation_tags.relation_id=planet_osm_rels.id and k='admin_level' limit 1);
30
31alter table planet_osm_rels add column boundary text;
32update planet_osm_rels set boundary=(select v from relation_tags where relation_tags.relation_id=planet_osm_rels.id and k='boundary' limit 1);
33
34create index planet_osm_rels_type on planet_osm_rels(type);
35create index planet_osm_rels_route on planet_osm_rels(route);
36create index planet_osm_rels_network on planet_osm_rels(network);
37create index planet_osm_rels_importance on planet_osm_rels(importance);
Note: See TracBrowser for help on using the repository browser.