diff options
author | Jochen Topf <jochen@topf.org> | 2010-10-04 18:41:53 +0200 |
---|---|---|
committer | Jochen Topf <jochen@topf.org> | 2010-10-04 18:41:53 +0200 |
commit | 9918c2c4c266a29848ce39fe2496876c66c3a48e (patch) | |
tree | b49fe450d33dcb3c30b37f7bff68fbb475ecec66 /sources/db/post.sql | |
download | taginfo-9918c2c4c266a29848ce39fe2496876c66c3a48e.tar taginfo-9918c2c4c266a29848ce39fe2496876c66c3a48e.tar.gz |
first commit
Diffstat (limited to 'sources/db/post.sql')
-rw-r--r-- | sources/db/post.sql | 55 |
1 files changed, 55 insertions, 0 deletions
diff --git a/sources/db/post.sql b/sources/db/post.sql new file mode 100644 index 0000000..2316657 --- /dev/null +++ b/sources/db/post.sql @@ -0,0 +1,55 @@ +-- +-- Taginfo source: Database +-- +-- post.sql +-- + +.bail ON + +PRAGMA journal_mode = OFF; +PRAGMA synchronous = OFF; +PRAGMA count_changes = OFF; +PRAGMA temp_store = MEMORY; +PRAGMA cache_size = 5000000; + +ATTACH DATABASE '__DIR__/count.db' AS count; +INSERT INTO stats SELECT * FROM count.stats; +DETACH DATABASE count; + +CREATE UNIQUE INDEX keys_key_idx ON keys (key); +CREATE INDEX tags_key_idx ON tags (key); +-- CREATE UNIQUE INDEX tags_key_value_idx ON tags (key, value); +CREATE INDEX keypairs_key1_idx ON keypairs (key1); +CREATE INDEX keypairs_key2_idx ON keypairs (key2); +CREATE UNIQUE INDEX key_distributions_key_idx ON key_distributions (key); + +INSERT INTO stats (key, value) SELECT 'num_keys', count(*) FROM keys; +INSERT INTO stats (key, value) SELECT 'num_keys_on_nodes', count(*) FROM keys WHERE count_nodes > 0; +INSERT INTO stats (key, value) SELECT 'num_keys_on_ways', count(*) FROM keys WHERE count_ways > 0; +INSERT INTO stats (key, value) SELECT 'num_keys_on_relations', count(*) FROM keys WHERE count_relations > 0; + +INSERT INTO stats (key, value) SELECT 'num_tags', count(*) FROM tags; +INSERT INTO stats (key, value) SELECT 'num_tags_on_nodes', count(*) FROM tags WHERE count_nodes > 0; +INSERT INTO stats (key, value) SELECT 'num_tags_on_ways', count(*) FROM tags WHERE count_ways > 0; +INSERT INTO stats (key, value) SELECT 'num_tags_on_relations', count(*) FROM tags WHERE count_relations > 0; + +INSERT INTO stats (key, value) SELECT 'num_keypairs', count(*) FROM keypairs; +INSERT INTO stats (key, value) SELECT 'num_keypairs_on_nodes', count(*) FROM keypairs WHERE count_nodes > 0; +INSERT INTO stats (key, value) SELECT 'num_keypairs_on_ways', count(*) FROM keypairs WHERE count_ways > 0; +INSERT INTO stats (key, value) SELECT 'num_keypairs_on_relations', count(*) FROM keypairs WHERE count_relations > 0; + +UPDATE keys SET prevalent_values=( + SELECT group_concat(value, '|') FROM ( + SELECT value FROM tags t + WHERE t.key = keys.key + AND t.count_all > keys.count_all / 100 + ORDER BY t.count_all DESC + LIMIT 10 + + ) + ) WHERE values_all < 1000; + +ANALYZE; + +UPDATE meta SET update_end=datetime('now'); + |