diff options
| -rw-r--r-- | sources/db/post.sql | 48 | ||||
| -rw-r--r-- | sources/db/post_indexes.sql | 31 | ||||
| -rw-r--r-- | sources/db/post_similar_keys.sql | 45 | ||||
| -rwxr-xr-x | sources/db/update.sh | 6 |
4 files changed, 82 insertions, 48 deletions
diff --git a/sources/db/post.sql b/sources/db/post.sql index ad58ef6..07f5ca0 100644 --- a/sources/db/post.sql +++ b/sources/db/post.sql @@ -14,54 +14,6 @@ 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; - --- ============================================================================ - -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 key_combinations_key1_idx ON key_combinations (key1); -CREATE INDEX key_combinations_key2_idx ON key_combinations (key2); -CREATE UNIQUE INDEX key_distributions_key_idx ON key_distributions (key, object_type); - -CREATE UNIQUE INDEX tag_distributions_key_value_idx ON tag_distributions (key, value, object_type); - -CREATE INDEX tag_combinations_key1_value1_idx ON tag_combinations (key1, value1); -CREATE INDEX tag_combinations_key2_value2_idx ON tag_combinations (key2, value2); - -CREATE UNIQUE INDEX relation_types_rtype_idx ON relation_types (rtype); -CREATE INDEX relation_roles_rtype_idx ON relation_roles (rtype); - --- ============================================================================ - 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; diff --git a/sources/db/post_indexes.sql b/sources/db/post_indexes.sql new file mode 100644 index 0000000..768b7f3 --- /dev/null +++ b/sources/db/post_indexes.sql @@ -0,0 +1,31 @@ +-- +-- Taginfo source: Database +-- +-- post_indexes.sql +-- + +.bail ON + +PRAGMA journal_mode = OFF; +PRAGMA synchronous = OFF; +PRAGMA count_changes = OFF; +PRAGMA temp_store = MEMORY; +PRAGMA cache_size = 5000000; + +-- ============================================================================ + +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 key_combinations_key1_idx ON key_combinations (key1); +CREATE INDEX key_combinations_key2_idx ON key_combinations (key2); +CREATE UNIQUE INDEX key_distributions_key_idx ON key_distributions (key, object_type); + +CREATE UNIQUE INDEX tag_distributions_key_value_idx ON tag_distributions (key, value, object_type); + +CREATE INDEX tag_combinations_key1_value1_idx ON tag_combinations (key1, value1); +CREATE INDEX tag_combinations_key2_value2_idx ON tag_combinations (key2, value2); + +CREATE UNIQUE INDEX relation_types_rtype_idx ON relation_types (rtype); +CREATE INDEX relation_roles_rtype_idx ON relation_roles (rtype); + diff --git a/sources/db/post_similar_keys.sql b/sources/db/post_similar_keys.sql new file mode 100644 index 0000000..8ab9ecc --- /dev/null +++ b/sources/db/post_similar_keys.sql @@ -0,0 +1,45 @@ +-- +-- Taginfo source: Database +-- +-- post_similar_keys.sql +-- + +.bail ON + +PRAGMA journal_mode = OFF; +PRAGMA synchronous = OFF; +PRAGMA count_changes = 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; + diff --git a/sources/db/update.sh b/sources/db/update.sh index 3e42094..44f1fa6 100755 --- a/sources/db/update.sh +++ b/sources/db/update.sh @@ -70,9 +70,15 @@ else echo "WARNING: Not running 'similarity', because binary not found. Please compile it." fi +echo "`$DATECMD` Running post_similar_keys.sql... " +sqlite3 $DATABASE <post_similar_keys.sql + echo "`$DATECMD` Running update_characters... " ./update_characters.rb $DIR +echo "`$DATECMD` Running post_indexes.sql... " +sqlite3 $DATABASE <post_indexes.sql + echo "`$DATECMD` Running post.sql... " sqlite3 $DATABASE <post.sql |