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
|
-- ============================================================================
--
-- Taginfo
--
-- interesting_tags.sql
--
-- ============================================================================
.bail ON
ATTACH DATABASE '__DIR__/db/taginfo-db.db' AS db;
ATTACH DATABASE '__DIR__/wiki/taginfo-wiki.db' AS wiki;
ATTACH DATABASE '__DIR__/josm/taginfo-josm.db' AS josm;
ATTACH DATABASE '__DIR__/potlatch/taginfo-potlatch.db' AS potlatch;
-- ============================================================================
DROP TABLE IF EXISTS interesting_tags;
CREATE TABLE interesting_tags (
key TEXT,
value TEXT
);
INSERT INTO interesting_tags (key, value)
SELECT DISTINCT key, NULL FROM db.keys WHERE count_all > 10000
UNION
SELECT key, value FROM db.tags WHERE count_all > 10000;
DELETE FROM interesting_tags WHERE key IN ('created_by', 'ele', 'height', 'is_in', 'lanes', 'layer', 'maxspeed', 'name', 'ref', 'width') AND value IS NOT NULL;
DELETE FROM interesting_tags WHERE value IS NOT NULL AND key LIKE '%:%';
DELETE FROM interesting_tags WHERE value IS NOT NULL AND key LIKE 'fresno_%';
ANALYZE interesting_tags;
.output __DIR__/db/interesting_tags.lst
SELECT key FROM interesting_tags WHERE value IS NULL ORDER BY key;
SELECT key || '=' || value FROM interesting_tags WHERE value IS NOT NULL ORDER BY key, value;
-- ============================================================================
|