API and UI support for tag combinations

6 files changed, 155 insertions, 1 deletions

diff --git a/web/i18n/de.yml b/web/i18n/de.yml
index 6e7b6e7..d139df4 100644
--- a/web/i18n/de.yml
+++ b/web/i18n/de.yml
@@ -25,6 +25,7 @@ taginfo:
     tags: Tags
     map: Karte
     maps: Karten
+    combinations: Kombinationen
     key_combinations: Kombinationen
     data_from: Daten vom
     instance:
@@ -105,6 +106,12 @@ pages:
                   Stil auswählen:
     tag:
         no_description_in_wiki: Keine Beschreibung im Wiki
+        other_tags_used:
+            title: Tags, die mit diesem Tag zusammen benutzt werden
+            other: Andere Tags
+        combinations:
+            title: Kombinationen
+            description: <i>Diese Tabelle zeigt nur die häufigsten Kombinationen der häufigsten Tags.</i>
         wiki_pages:
             title: Wiki-Seiten über dieses Tag
         josm_rules:
diff --git a/web/i18n/en.yml b/web/i18n/en.yml
index 1f29e7c..67980fe 100644
--- a/web/i18n/en.yml
+++ b/web/i18n/en.yml
@@ -26,6 +26,7 @@ taginfo:
     tags: Tags
     map: Map
     maps: Maps
+    combinations: Combinations
     key_combinations: Combinations
     data_from: Data from
     instance:
@@ -104,6 +105,12 @@ pages:
                   Choose style:
     tag:
         no_description_in_wiki: No description in wiki
+        other_tags_used:
+            title: Other tags used together with this tag
+            other: Other tags
+        combinations:
+            title: Combinations
+            description: <i>This table shows only the most common combinations of the most common tags.</i>
         wiki_pages:
             title: Wiki pages about this tag
         josm_rules:
diff --git a/web/lib/api/db.rb b/web/lib/api/db.rb
index e33c797..739c791 100644
--- a/web/lib/api/db.rb
+++ b/web/lib/api/db.rb
@@ -464,4 +464,86 @@ class Taginfo < Sinatra::Base
         out.to_json
     end
 
+    api(2, 'db/tags/combinations', {
+        :description => 'Find keys and tags that are used together with a given tag.',
+        :parameters => {
+            :key => 'Tag key (required).',
+            :value => 'Tag value (required).',
+            :query => 'Only show results where the other_key or other_value matches this query (substring match, optional).'
+        },
+        :paging => :optional,
+        :filter => {
+            :all       => { :doc => 'No filter.' },
+            :nodes     => { :doc => 'Only values on tags used on nodes.' },
+            :ways      => { :doc => 'Only values on tags used on ways.' },
+            :relations => { :doc => 'Only values on tags used on relations.' }
+        },
+        :sort => %w( together_count other_tag from_fraction ),
+        :result => {
+            :other_key      => :STRING,
+            :other_value    => :STRING,
+            :together_count => :INT,
+            :to_fraction    => :FLOAT,
+            :from_fraction  => :FLOAT
+        },
+        :example => { :key => 'highway', :value => 'residential', :page => 1, :rp => 10, :sortname => 'together_count', :sortorder => 'desc' },
+        :ui => '/tags/highway=residential#combinations'
+    }) do
+        key = params[:key]
+        value = params[:value]
+        filter_type = get_filter()
+
+        if params[:sortname] == 'to_count'
+            params[:sortname] = 'together_count'
+        elsif params[:sortname] == 'from_count'
+            params[:sortname] = ['from_fraction', 'together_count', 'other_key', 'other_value']
+        elsif params[:sortname] == 'other_tag'
+            params[:sortname] = ['other_key', 'other_value']
+        end
+
+        cq = @db.count('db.tagpairs')
+        total = (params[:query].to_s != '' ?
+                cq.condition("(key1=? AND value1=? AND (key2 LIKE '%' || ? || '%' OR value2 LIKE '%' || ? || '%')) OR (key2=? AND value2=? AND (key1 LIKE '%' || ? || '%' OR value2 LIKE '%' || ? || '%'))",
+                    key, value, params[:query], params[:query], key, value, params[:query], params[:query]) :
+                cq.condition('(key1=? AND value1=?) OR (key2=? AND value2=?)', key, value, key, value)).
+            condition("count_#{filter_type} > 0").
+            get_first_value().to_i
+
+        has_this_key = @db.select("SELECT count_#{filter_type} FROM db.tags").
+            condition('key = ?', key).
+            condition('value = ?', value).
+            get_first_value()
+
+        res = (params[:query].to_s != '' ?
+            @db.select("SELECT p.key1 AS other_key, p.value1 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tagpairs p, db.selected_tags k WHERE p.key1=k.skey AND p.value1=k.svalue AND k.svalue != '' AND p.key2=? AND p.value2=? AND ((p.key1 LIKE '%' || ? || '%') OR (p.value1 LIKE '%' || ? || '%')) AND p.count_#{filter_type} > 0
+                    UNION SELECT p.key1 AS other_key, p.value1 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tagpairs p, db.keys k WHERE p.key1=k.key AND p.value1='' AND p.key2=? AND p.value2=? AND ((p.key1 LIKE '%' || ? || '%') OR (p.value1 LIKE '%' || ? || '%')) AND p.count_#{filter_type} > 0
+                    UNION SELECT p.key2 AS other_key, p.value2 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tagpairs p, db.selected_tags k WHERE p.key2=k.skey AND p.value2=k.svalue AND k.svalue != '' AND p.key1=? AND p.value1=? AND ((p.key2 LIKE '%' || ? || '%') OR (p.value2 LIKE '%' || ? || '%')) AND p.count_#{filter_type} > 0
+                    UNION SELECT p.key2 AS other_key, p.value2 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tagpairs p, db.keys k WHERE p.key2=k.key AND p.value2='' AND p.key1=? AND p.value1=? AND ((p.key2 LIKE '%' || ? || '%') OR (p.value2 LIKE '%' || ? || '%')) AND p.count_#{filter_type} > 0", key, value, params[:query], params[:query], key, value, params[:query], params[:query], key, value, params[:query], params[:query], key, value, params[:query], params[:query]) :
+            @db.select("SELECT p.key1 AS other_key, p.value1 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tagpairs p, db.selected_tags k WHERE p.key1=k.skey AND p.value1=k.svalue AND k.svalue != '' AND p.key2=? AND p.value2=? AND p.count_#{filter_type} > 0 
+                    UNION SELECT p.key1 AS other_key, '' AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tagpairs p, db.keys k WHERE p.key1=k.key AND p.value1 = '' AND p.key2=? AND p.value2=? AND p.count_#{filter_type} > 0 
+                    UNION SELECT p.key2 AS other_key, p.value2 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tagpairs p, db.selected_tags k WHERE p.key2=k.skey AND p.value2=k.svalue AND k.svalue != '' AND p.key1=? AND p.value1=? AND p.count_#{filter_type} > 0
+                    UNION SELECT p.key2 AS other_key, '' AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tagpairs p, db.keys k WHERE p.key2=k.key AND p.value2 = '' AND p.key1=? AND p.value1=? AND p.count_#{filter_type} > 0", key, value, key, value, key, value, key, value)).
+            order_by(params[:sortname], params[:sortorder]){ |o|
+                o.together_count
+                o.other_key
+                o.other_value
+                o.from_fraction
+            }.
+            paging(params[:rp], params[:page]).
+            execute()
+
+        return {
+            :page  => params[:page].to_i,
+            :rp    => params[:rp].to_i,
+            :total => total,
+            :data  => res.map{ |row| {
+                :other_key      => row['other_key'],
+                :other_value    => row['other_value'],
+                :together_count => row['together_count'].to_i,
+                :to_fraction    => row['together_count'].to_f / has_this_key.to_f,
+                :from_fraction  => row['from_fraction'].to_f
+            } }
+        }.to_json
+    end
+
 end
diff --git a/web/public/js/taginfo.js b/web/public/js/taginfo.js
index 6b6048e..0451d2f 100644
--- a/web/public/js/taginfo.js
+++ b/web/public/js/taginfo.js
@@ -229,6 +229,16 @@ function link_to_tag(key, value) {
     return link_to_key(key) + '=' + link_to_value(key, value);
 }
 
+function link_to_key_or_tag(key, value) {
+    var link = link_to_key(key);
+    if (value && value != '') {
+        link += '=' + link_to_value(key, value);
+    } else {
+        link += '=*';
+    }
+    return link;
+}
+
 jQuery(document).ready(function() {
     jQuery('#locale').bind('change', function() {
         jQuery('#set_language').submit();
@@ -313,6 +323,32 @@ var create_flexigrid_for = {
         }
     },
     tag: {
+        combinations: function(key, value, filter_type) {
+            create_flexigrid('grid-combinations', {
+                url: '/api/2/db/tags/combinations?key=' + encodeURIComponent(key) + '&value=' + encodeURIComponent(value) + '&filter=' + encodeURIComponent(filter_type),
+                colModel: [
+                    { display: '<span title="Number of objects with this tag that also have the other tag">' + texts.misc.count + ' &rarr;</span>', name: 'to_count', width: 320, sortable: true, align: 'center' },
+                    { display: '<span title="Tag used together with this tag">' + texts.pages.tag.other_tags_used.other + '</span>', name: 'other_tag', width: 340, sortable: true },
+                    { display: '<span title="Number of objects with other tag that also have this tag">&rarr; ' + texts.misc.count + '</span>', name: 'from_count', width: 320, sortable: true, align: 'center' }
+                ],
+                searchitems: [
+                    { display: 'Other tag', name: 'other_tag' }
+                ],
+                sortname: 'to_count',
+                sortorder: 'desc',
+                height: 410,
+                preProcess: function(data) {
+                    data.rows = jQuery.map(data.data, function(row, i) {
+                        return { 'cell': [
+                            print_value_with_percent(row.together_count, row.to_fraction),
+                            link_to_key_or_tag(row.other_key, row.other_value),
+                            print_value_with_percent(row.together_count, row.from_fraction),
+                        ] };
+                    });
+                    return data;
+                }
+            });
+        },
         wiki: function(key, value) {
             create_flexigrid('grid-wiki', {
                 url: '/api/2/wiki/tags?key=' + encodeURIComponent(key) + '&value=' + encodeURIComponent(value),
diff --git a/web/taginfo.rb b/web/taginfo.rb
index fded8a2..bb4a089 100755
--- a/web/taginfo.rb
+++ b/web/taginfo.rb
@@ -315,6 +315,11 @@ class Taginfo < Sinatra::Base
                     },
                     :number_objects => trans.t.pages.key.number_objects,
                 },
+                :tag => {
+                    :other_tags_used => {
+                        :other => trans.t.pages.tag.other_tags_used.other,
+                    },
+                },
             },
         }.to_json + ";\n"
     end
diff --git a/web/views/tag.erb b/web/views/tag.erb
index cb952c6..5f33f99 100644
--- a/web/views/tag.erb
+++ b/web/views/tag.erb
@@ -16,11 +16,28 @@
     <% end %>
 </div>
 
+<form id="filter-form">
+    <label for="filter"><%= t.pages.key.filter.label %></label>
+    <select id="filter" name="filter">
+        <option value="all"<%=       @sel['all']       %>><%= t.pages.key.filter.none %></option>
+        <option value="nodes"<%=     @sel['nodes']     %>><%= t.pages.key.filter.nodes %></option>
+        <option value="ways"<%=      @sel['ways']      %>><%= t.pages.key.filter.ways %></option>
+        <option value="relations"<%= @sel['relations'] %>><%= t.pages.key.filter.relations %></option>
+    </select>
+</form>
+
 <div id="tabs">
     <ul>
+        <li><a href="#combinations"><%= t.taginfo.combinations %></a></li>
         <li><a href="#wiki"><%= t.sources.wiki.name %></a></li>
         <li><a href="#josm"><%= t.sources.josm.name %></a></li>
     </ul>
+    <div id="combinations">
+        <h2><%= t.pages.tag.combinations.title %></h2>
+        <p><%= t.pages.tag.combinations.description %></p>
+        <table id="grid-combinations">
+        </table>
+    </div>
     <div id="wiki">
         <h2><%= t.pages.tag.wiki_pages.title %></h2>
         <table id="grid-wiki">
@@ -66,7 +83,7 @@ jQuery.getJSON('/api/2/db/tags/overview?key=#{ @key_uri }&value=#{ @value_uri }'
 var tabs = jQuery('#tabs').tabs({
     show: function(event, ui) { 
         window.location.hash = ui.tab.hash;
-        create_flexigrid_for.tag[ui.tab.hash.substring(1)](#{ @key.to_json }, #{ @value.to_json });
+        create_flexigrid_for.tag[ui.tab.hash.substring(1)](#{ @key.to_json }, #{ @value.to_json }, #{ @filter_type.to_json });
     }
 });
 JAVASCRIPT