Add API support for finding similar keys.

2 files changed, 117 insertions, 0 deletions

diff --git a/web/lib/api/v4/key.rb b/web/lib/api/v4/key.rb
index 9deb394..97125a6 100644
--- a/web/lib/api/v4/key.rb
+++ b/web/lib/api/v4/key.rb
@@ -69,6 +69,62 @@ class Taginfo < Sinatra::Base
         }, json_opts(params[:format]))
     end
 
+    api(4, 'key/similar', {
+        :description => 'Find keys that are similar to a given key.',
+        :parameters => {
+            :key => 'Tag key (required).',
+            :query => 'Only show results where the other_key matches this query (substring match, optional).'
+        },
+        :paging => :optional,
+        :sort => %w( other_key count_all similarity ),
+        :result => paging_results([
+            [:other_key,  :STRING, 'Other key.'],
+            [:count_all,  :INT,    'Number of objects that have the other key.'],
+            [:similarity, :INT,    'An integer measuring the similarity of the two keys, smaller is more similar.']
+        ]),
+        :example => { :key => 'highway', :page => 1, :rp => 10, :sortname => 'other_key', :sortorder => 'asc' },
+        :ui => '/keys/highway#similar'
+    }) do
+        key = params[:key]
+        query = like_contains(params[:query])
+
+        if params[:query].to_s != ''
+            total = @db.select("SELECT count(*) FROM db.similar_keys s WHERE (s.key1 LIKE ? ESCAPE '@') AND s.key2=?
+                                                                          OR (s.key2 LIKE ? ESCAPE '@') AND s.key1=?", query, key, query, key).get_first_value().to_i
+        else
+            total = @db.count('db.similar_keys').
+                        condition("key1=? OR key2=?", key, key).
+                        get_first_value().to_i
+        end
+
+        rows = (params[:query].to_s != '' ?
+            @db.select("SELECT key1 AS other_key, count_all1 AS count_all, similarity FROM db.similar_keys WHERE (key1 LIKE ? ESCAPE '@') AND key2=?
+                  UNION SELECT key2 AS other_key, count_all2 AS count_all, similarity FROM db.similar_keys WHERE (key2 LIKE ? ESCAPE '@') AND key1=?", query, key, query, key) :
+            @db.select("SELECT key1 AS other_key, count_all1 AS count_all, similarity FROM db.similar_keys WHERE key2=?
+                  UNION SELECT key2 AS other_key, count_all2 AS count_all, similarity FROM db.similar_keys WHERE key1=?", key, key)).
+                    order_by(@ap.sortname, @ap.sortorder) { |o|
+                        o.similarity :similarity
+                        o.similarity :count_all
+                        o.other_key
+                        o.count_all :count_all
+                        o.count_all :similarity
+                    }.
+                    paging(@ap).
+                    execute()
+
+        return JSON.generate({
+            :page  => @ap.page,
+            :rp    => @ap.results_per_page,
+            :total => total,
+            :url   => request.url,
+            :data  => rows.map{ |row| {
+                :other_key  => row['other_key'],
+                :count_all  => row['count_all'],
+                :similarity => row['similarity']
+            } }
+        }, json_opts(params[:format]))
+    end
+
     api(4, 'key/distribution/nodes', {
         :description => 'Get map with distribution of this key in the database (nodes only).',
         :parameters => { :key => 'Tag key (required).' },
diff --git a/web/lib/api/v4/keys.rb b/web/lib/api/v4/keys.rb
index b183660..e4f4c99 100644
--- a/web/lib/api/v4/keys.rb
+++ b/web/lib/api/v4/keys.rb
@@ -188,6 +188,67 @@ class Taginfo < Sinatra::Base
         }, json_opts(params[:format]))
     end
 
+    api(4, 'keys/similar', {
+        :description => 'Get list of pairs of similar keys, one used very often, one used rarely.',
+        :parameters => { :query => 'Only show keys matching this query (substring match, optional).' },
+        :paging => :optional,
+        :sort => %w( key_common key_rare count_all_common count_all_rare similarity ),
+        :result => paging_results([
+            [:key_common,       :STRING, 'Key thats used often in OSM database'],
+            [:count_all_common, :INT,    'Number of objects in the OSM database with the common key.'],
+            [:key_rare,         :STRING, 'Key thats used rarely in OSM database'],
+            [:count_all_rare,   :INT,    'Number of objects in the OSM database with the rare key.'],
+            [:similarity,       :INT,    'An integer measuring the similarity of the two keys, smaller is more similar.']
+        ]),
+        :example => { :page => 1, :rp => 10, :sortname => 'count_all_common', :sortorder => 'desc' },
+        :ui => '/reports/similar_keys'
+    }) do
+        query = like_contains(params[:query])
+
+        cond = "(similarity != 0 OR lower(key_common) = lower(key_rare)) AND count_all_common >= 10000"
+
+        total = @db.select("SELECT count(*) FROM similar_keys_common_rare").
+                        condition(cond).
+                        condition_if("(key_common LIKE ? ESCAPE '@' OR key_rare LIKE ? ESCAPE '@')", query, query).
+                        get_first_value().to_i
+
+        res = @db.select("SELECT * FROM similar_keys_common_rare").
+                        condition(cond).
+                        condition_if("(key_common LIKE ? ESCAPE '@' OR key_rare LIKE ? ESCAPE '@')", query, query).
+                        order_by(@ap.sortname, @ap.sortorder) { |o|
+                            o.key_common :key_common
+                            o.key_common :key_rare
+                            o.key_rare :key_rare
+                            o.key_rare :key_common
+                            o.count_all_common :count_all_common
+                            o.count_all_common! :count_all_rare
+                            o.count_all_common! :similarity
+                            o.count_all_rare :count_all_rare
+                            o.count_all_rare! :count_all_common
+                            o.count_all_rare :similarity
+                            o.similarity :similarity
+                            o.similarity! :count_all_common
+                            o.similarity! :count_all_rare
+                        }.
+                        paging(@ap).
+                        execute()
+
+        return JSON.generate({
+            :page  => @ap.page,
+            :rp    => @ap.results_per_page,
+            :total => total,
+            :url   => request.url,
+            :data  => res.map{ |row| {
+                    :key_common       => row['key_common'],
+                    :key_rare         => row['key_rare'],
+                    :count_all_common => row['count_all_common'],
+                    :count_all_rare   => row['count_all_rare'],
+                    :similarity       => row['similarity']
+                }
+            }
+        }, json_opts(params[:format]))
+    end
+
     api(4, 'keys/without_wiki_page', {
         :description => 'Return frequently used tag keys that have no associated wiki page.',
         :parameters => {