forked from darauble/Locus-Map-DIY
-
Notifications
You must be signed in to change notification settings - Fork 0
/
append.sql
106 lines (101 loc) · 9.42 KB
/
append.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
-- DELETE FROM Points WHERE type='P';
INSERT INTO FoldersSub (name) VALUES ('firepit');
INSERT INTO FoldersSub (name) VALUES ('picnic_table');
INSERT INTO FoldersSub (name) VALUES ('cross');
INSERT INTO FoldersSub (name) VALUES ('beach');
INSERT INTO FoldersSub (name) VALUES ('artwork');
INSERT INTO FoldersSub (name) VALUES ('public_bookcase');
INSERT INTO FoldersSub (name) VALUES ('charging_station');
-- Table not in official database, but used for folder mapping
CREATE TABLE RootSubMapping (id INTEGER NOT NULL PRIMARY KEY, subname TEXT NOT NULL, rootname TEXT NOT NULL);
INSERT INTO RootSubMapping (subname, rootname) VALUES ('alpine_hut','accommodation');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('camp_caravan','accommodation');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('motel','accommodation');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('hostel','accommodation');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('hotel','accommodation');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('bank','financial_post_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('exchange','financial_post_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('atm','financial_post_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('post_office','financial_post_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('post_box','financial_post_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('info','culture_tourism');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('museum','culture_tourism');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('cinema','culture_tourism');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('theatre','culture_tourism');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('castle_ruin_monument','culture_tourism');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('attraction','culture_tourism');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('toilets','public_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('townhall','public_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('library','public_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('education','public_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('embassy','public_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('telephone','public_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('grave_yard','public_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('bicycle_parking','hiking_cycling');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('guidepost','hiking_cycling');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('map','hiking_cycling');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('picnic_site','hiking_cycling');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('shelter','hiking_cycling');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('viewpoint','nature');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('protected_area','nature');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('peak','nature');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('spring','nature');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('mine_cave','nature');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('glacier','nature');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('fire_station','emergency_health');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('police','emergency_health');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('doctor_dentist','emergency_health');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('hospital_clinic','emergency_health');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('veterinary','emergency_health');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('buddhist','place_of_worship');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('christian','place_of_worship');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('hindu','place_of_worship');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('jewish','place_of_worship');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('muslim','place_of_worship');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('shinto','place_of_worship');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('taoist','place_of_worship');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('bar_pub','food_drink');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('cafe','food_drink');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('restaurant','food_drink');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('fast_food','food_drink');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('confectionery','food_drink');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('drinking_water','food_drink');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('veg_food','food_drink');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('department_store','shopping');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('pharmacy','shopping');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('bakery','shopping');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('other','shopping');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('supermarket_convenience','shopping');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('sport_outdoor','sport_leisure');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('golf','sport_leisure');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('swimming','sport_leisure');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('sport_centre','sport_leisure');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('sport_pitch','sport_leisure');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('stadium','sport_leisure');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('skiing','sport_leisure');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('clubs_dancing','sport_leisure');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('gas_station','car_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('rest_area','car_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('car_shop_and_repair','car_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('bus_and_tram_stop','transportation');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('bus_station','transportation');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('railway_station','transportation');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('subway','transportation');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('airport','transportation');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('ferries','transportation');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('parking','transportation');
-- Darau
INSERT INTO RootSubMapping (subname, rootname) VALUES ('firepit','hiking_cycling');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('picnic_table','hiking_cycling');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('cross','hiking_cycling');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('beach','nature');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('artwork','culture_tourism');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('public_bookcase','public_services');
INSERT INTO RootSubMapping (subname, rootname) VALUES ('charging_station','car_services');
INSERT INTO Points_Root_Sub(Points_id, FoldersRoot_id, FoldersSub_id) select max(Points_Root_Sub.Points_id)+1, FoldersRoot.id, FoldersSub.id from Points_Root_Sub join FoldersRoot join FoldersSub where FoldersRoot.name='hiking_cycling' and FoldersSub.name='cross';
INSERT INTO Points_Root_Sub(Points_id, FoldersRoot_id, FoldersSub_id) select max(Points_Root_Sub.Points_id)+1, FoldersRoot.id, FoldersSub.id from Points_Root_Sub join FoldersRoot join FoldersSub where FoldersRoot.name='hiking_cycling' and FoldersSub.name='firepit';
INSERT INTO Points_Root_Sub(Points_id, FoldersRoot_id, FoldersSub_id) select max(Points_Root_Sub.Points_id)+1, FoldersRoot.id, FoldersSub.id from Points_Root_Sub join FoldersRoot join FoldersSub where FoldersRoot.name='hiking_cycling' and FoldersSub.name='picnic_table';
INSERT INTO Points_Root_Sub(Points_id, FoldersRoot_id, FoldersSub_id) select max(Points_Root_Sub.Points_id)+1, FoldersRoot.id, FoldersSub.id from Points_Root_Sub join FoldersRoot join FoldersSub where FoldersRoot.name='nature' and FoldersSub.name='beach';
INSERT INTO Points_Root_Sub(Points_id, FoldersRoot_id, FoldersSub_id) select max(Points_Root_Sub.Points_id)+1, FoldersRoot.id, FoldersSub.id from Points_Root_Sub join FoldersRoot join FoldersSub where FoldersRoot.name='culture_tourism' and FoldersSub.name='artwork';
INSERT INTO Points_Root_Sub(Points_id, FoldersRoot_id, FoldersSub_id) select max(Points_Root_Sub.Points_id)+1, FoldersRoot.id, FoldersSub.id from Points_Root_Sub join FoldersRoot join FoldersSub where FoldersRoot.name='public_services' and FoldersSub.name='public_bookcase';
INSERT INTO Points_Root_Sub(Points_id, FoldersRoot_id, FoldersSub_id) select max(Points_Root_Sub.Points_id)+1, FoldersRoot.id, FoldersSub.id from Points_Root_Sub join FoldersRoot join FoldersSub where FoldersRoot.name='car_services' and FoldersSub.name='charging_station';