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
|
--
-- Taginfo source: Database
--
-- post_similar_keys.sql
--
.bail ON
PRAGMA journal_mode = OFF;
PRAGMA synchronous = OFF;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = 5000000;
-- ============================================================================
-- For all keys found to be similar earlier, we get the counts how often they
-- appear in the OSM database and store this data in the same table for easy
-- access.
UPDATE similar_keys SET count_all1=(SELECT k.count_all FROM keys k WHERE k.key=similar_keys.key1);
UPDATE similar_keys SET count_all2=(SELECT k.count_all FROM keys k WHERE k.key=similar_keys.key2);
CREATE INDEX similar_keys_key1_idx ON similar_keys (key1);
CREATE INDEX similar_keys_key2_idx ON similar_keys (key2);
ANALYZE similar_keys;
DROP TABLE IF EXISTS similar_keys_common_rare;
CREATE TABLE similar_keys_common_rare (
key_common VARCHAR,
key_rare VARCHAR,
count_all_common INTEGER DEFAULT 0,
count_all_rare INTEGER DEFAULT 0,
similarity INTEGER
);
INSERT INTO similar_keys_common_rare (key_common, key_rare, count_all_common, count_all_rare, similarity)
SELECT key1, key2, count_all1, count_all2, similarity
FROM similar_keys WHERE count_all1 >= 1000 AND count_all2 <= 10 AND count_all2 > 0;
INSERT INTO similar_keys_common_rare (key_common, key_rare, count_all_common, count_all_rare, similarity)
SELECT key2, key1, count_all2, count_all1, similarity
FROM similar_keys WHERE count_all2 >= 1000 AND count_all1 <= 10 AND count_all1 > 0;
|