<p dir="auto">I created a sql metric with the expression:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="CAST(strftime(&quot;%s&quot;, max(&quot;timestamp&quot;)) AS INT)"><pre class="notranslate"><code class="notranslate">CAST(strftime("%s", max("timestamp")) AS INT)
</code></pre></div>
<p dir="auto">When I used this metric to create a table view the values were not being populated. Looking into the JSON I found the query had duplicated the <code class="notranslate">%</code>:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="query: &quot;SELECT &quot;serviceName&quot; AS &quot;serviceName&quot;, CAST(strftime(&quot;%%s&quot;, max(&quot;timestamp&quot;)) AS INT)
 AS latest_date FROM &quot;testResults&quot; WHERE timestamp &gt;= '2016-03-29 15:57:43.000000' AND timestamp &lt;= '2016-04-05 15:57:43.000000' GROUP BY &quot;serviceName&quot; ORDER BY 
CAST(strftime(&quot;%%s&quot;, max(&quot;timestamp&quot;)) AS INT) DESC LIMIT 50000 OFFSET 0&quot;"><pre class="notranslate"><code class="notranslate">query: "SELECT "serviceName" AS "serviceName", CAST(strftime("%%s", max("timestamp")) AS INT)
 AS latest_date FROM "testResults" WHERE timestamp &gt;= '2016-03-29 15:57:43.000000' AND timestamp &lt;= '2016-04-05 15:57:43.000000' GROUP BY "serviceName" ORDER BY 
CAST(strftime("%%s", max("timestamp")) AS INT) DESC LIMIT 50000 OFFSET 0"
</code></pre></div>
<p dir="auto">Current end result is this column ends up displaying a 0 for each entry</p>
<h1 dir="auto">Motivation</h1>
<p dir="auto">When analyzing geo spatial data, a common work flow consists of grouping the data across different spatial dimensions, e.g., ZIP code, city, state or country. In order to visualize the results, a user can select either the "Country Map" or the "deck.gl Polygon" visualizations. Neither of them are appropriate for exploring the data across different hierarchical regions: the former is limited to country only, while the latter requires all shapes to be pre-joined (except for geohash), adding duplicate data to the data source.</p>
<h1 dir="auto">Proposed Change</h1>
<h2 dir="auto">Current workflow</h2>
<p dir="auto">In this SIP I propose a way of adding new encodings to the "deck.gl Polygon" visualization. Currently, it supports:</p>
<ul dir="auto">
<li>Polyline</li>
<li>JSON</li>
<li>geohash (square)</li>
</ul>
<p dir="auto">The first two require the shape to be present in the datasource, while the third one is computed the shape on the fly based on a column. In order to explore a dataset across a spatial hierarchy, data would have to pre-joined with all polygons in the datasource, eg:</p>
<table role="table">
<thead>
<tr>
<th>timestamp</th>
<th>country</th>
<th>country_polygon</th>
<th>state</th>
<th>state_polygon</th>
<th>city</th>
<th>city_polygon</th>
</tr>
</thead>
<tbody>
<tr>
<td>1549410628</td>
<td>US</td>
<td>{...}</td>
<td>CA</td>
<td>{...}</td>
<td>San Francisco</td>
<td>{...}</td>
</tr>
<tr>
<td>1549410675</td>
<td>US</td>
<td>{...}</td>
<td>CA</td>
<td>{...}</td>
<td>San Francisco</td>
<td>{...}</td>
</tr>
</tbody>
</table>
<p dir="auto">This is clearly inefficient.</p>
<h2 dir="auto">Proposed workflow</h2>
<p dir="auto">I propose an alternative workflow where <strong>the polygon shape is joined in the Python backend</strong> (<code class="notranslate">viz.py</code>). This is similar to how the geohash encoding currently works: it's computed on the fly by the Python backend based on the value of a column, and sent to the frontend in the payload. The approach described here has been used at Lyft for US and Canada postal codes for more than 6 months; see <a class="commit-link" data-hovercard-type="commit" data-hovercard-url="https://github.com/apache/superset/commit/9c10547f19b628e81cbcd6e1fbac86a70ea510be/hovercard" href="https://github.com/apache/superset/commit/9c10547f19b628e81cbcd6e1fbac86a70ea510be"><tt>9c10547</tt></a> for the US ZIP code implementation.</p>
<p dir="auto">Note that the current approach for geohash is still inefficient, since it sends the joined data to the browser. When a granularity is selected, enabling the play slider, this results in duplicate data being sent. It's better to send the polygon shapes in a separate attribute of the payload, and perform the join on the browser instead.</p>
<p dir="auto">In the new workflow, users will be able to specify new encodings in <code class="notranslate">config.py</code> (or <code class="notranslate">superset_config.py</code>). Each encoding is defined by an adapter class, responsible for serializing to JSON the shape associated with the region column. Eg, if the geohash visualization type didn't exist we would implement it in the proposed system as follows:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import geohash

from superset.polygon import PolygonEncoding


class GeohashEncoding(PolygonEncoding):

    name = 'geohash (square)'

    def to_location(codes):
        for code in codes:
            lat, lon = geohash.decode(code)
            yield lon, lat

    def to_polygon(codes):
        for code in codes:
            p = geohash.bbox(code)
            yield [
                [p.get('w'), p.get('n')],
                [p.get('e'), p.get('n')],
                [p.get('e'), p.get('s')],
                [p.get('w'), p.get('s')],
                [p.get('w'), p.get('n')],
            ]"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">geohash</span>

<span class="pl-k">from</span> <span class="pl-s1">superset</span>.<span class="pl-s1">polygon</span> <span class="pl-k">import</span> <span class="pl-v">PolygonEncoding</span>


<span class="pl-k">class</span> <span class="pl-v">GeohashEncoding</span>(<span class="pl-v">PolygonEncoding</span>):

    <span class="pl-s1">name</span> <span class="pl-c1">=</span> <span class="pl-s">'geohash (square)'</span>

    <span class="pl-k">def</span> <span class="pl-en">to_location</span>(<span class="pl-s1">codes</span>):
        <span class="pl-k">for</span> <span class="pl-s1">code</span> <span class="pl-c1">in</span> <span class="pl-s1">codes</span>:
            <span class="pl-s1">lat</span>, <span class="pl-s1">lon</span> <span class="pl-c1">=</span> <span class="pl-s1">geohash</span>.<span class="pl-en">decode</span>(<span class="pl-s1">code</span>)
            <span class="pl-k">yield</span> <span class="pl-s1">lon</span>, <span class="pl-s1">lat</span>

    <span class="pl-k">def</span> <span class="pl-en">to_polygon</span>(<span class="pl-s1">codes</span>):
        <span class="pl-k">for</span> <span class="pl-s1">code</span> <span class="pl-c1">in</span> <span class="pl-s1">codes</span>:
            <span class="pl-s1">p</span> <span class="pl-c1">=</span> <span class="pl-s1">geohash</span>.<span class="pl-en">bbox</span>(<span class="pl-s1">code</span>)
            <span class="pl-k">yield</span> [
                [<span class="pl-s1">p</span>.<span class="pl-en">get</span>(<span class="pl-s">'w'</span>), <span class="pl-s1">p</span>.<span class="pl-en">get</span>(<span class="pl-s">'n'</span>)],
                [<span class="pl-s1">p</span>.<span class="pl-en">get</span>(<span class="pl-s">'e'</span>), <span class="pl-s1">p</span>.<span class="pl-en">get</span>(<span class="pl-s">'n'</span>)],
                [<span class="pl-s1">p</span>.<span class="pl-en">get</span>(<span class="pl-s">'e'</span>), <span class="pl-s1">p</span>.<span class="pl-en">get</span>(<span class="pl-s">'s'</span>)],
                [<span class="pl-s1">p</span>.<span class="pl-en">get</span>(<span class="pl-s">'w'</span>), <span class="pl-s1">p</span>.<span class="pl-en">get</span>(<span class="pl-s">'s'</span>)],
                [<span class="pl-s1">p</span>.<span class="pl-en">get</span>(<span class="pl-s">'w'</span>), <span class="pl-s1">p</span>.<span class="pl-en">get</span>(<span class="pl-s">'n'</span>)],
            ]</pre></div>
<p dir="auto">This would be registered in <code class="notranslate">config.py</code>:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="FEATURE_FLAGS = {
    'EXTRA_POLYGON_ENCODINGS': [GeohashEncoding],
}"><pre class="notranslate"><code class="notranslate">FEATURE_FLAGS = {
    'EXTRA_POLYGON_ENCODINGS': [GeohashEncoding],
}
</code></pre></div>
<p dir="auto">Other adapters might perform database queries in order to fetch the polygon associated with each value, which is why the methods take a list of codes for efficiency. At Lyft we cache the shapes, fetching from the database only values that are missing.</p>
<h1 dir="auto">New or Changed Public Interfaces</h1>
<p dir="auto">This SIP affects only the "deck.gl Polygon" visualization type. The Python backend will use the adapter classes, and the frontend will display new encodings. Here's a screenshot showing US ZIP codes and Canada FSAs:</p>
<p dir="auto"><a target="_blank" rel="noopener noreferrer nofollow" href="https://user-images.githubusercontent.com/1534870/52313592-e14e0a80-2963-11e9-8faf-0e50ec684eaa.png"><img width="258" alt="screen shot 2019-02-05 at 4 34 02 pm" src="https://user-images.githubusercontent.com/1534870/52313592-e14e0a80-2963-11e9-8faf-0e50ec684eaa.png" style="max-width: 100%;"></a></p>
<p dir="auto">Even though this is a small feature, one of the reasons of why I'm proposing this as a SIP is because it introduces new logic to <code class="notranslate">viz.py</code>, and I'm unsure how that will affect embeddable components. (👀 <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/xtinec/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/xtinec">@xtinec</a> <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/kristw/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/kristw">@kristw</a> <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/williaster/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/williaster">@williaster</a>)</p>
<h1 dir="auto">New dependencies</h1>
<p dir="auto">No new dependencies are needed.</p>
<h1 dir="auto">Migration Plan and Compatibility</h1>
<p dir="auto">Not necessary.</p>
<h1 dir="auto">Rejected Alternatives</h1>
<p dir="auto">My initial implementation of a visualization for ZIP codes was as a custom new visualization. This was hard to manage (in part because of merge conflicts) and redundant, requiring a lot of duplicate work as features were added to the "deck.gl Polygon" visualization. At some point last year I merged the functionality into the deck.gl visualization.</p>
<h2 dir="auto">Future work</h2>
<p dir="auto">There was a discussion between me and <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/mistercrunch/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/mistercrunch">@mistercrunch</a> where we considered creating "spatial columns" in the datasource configuration, similar to how metrics or derived columns can be created. Eg, a datasource with these 4 columns:</p>
<ul dir="auto">
<li>pickup_lat</li>
<li>pickup_lon</li>
<li>dropoff_geohash</li>
</ul>
<p dir="auto">Would be configured to have 2 spatial columns: "pickup", composed from <code class="notranslate">pickup_lat</code> and <code class="notranslate">pickup_lon</code>, and "dropoff", derived from <code class="notranslate">dropoff_geohash</code>.</p>
<p dir="auto">If we had that mechanism for spatial columns in place, it would be useful if we could load a series of polygons using the CLI, eg:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# this downloads 2 GBs of data and stores in the main database
$ superset load_polygon US_zip"><pre class="notranslate"><code class="notranslate"># this downloads 2 GBs of data and stores in the main database
$ superset load_polygon US_zip
</code></pre></div>
<p dir="auto">Then in the spatial configuration the user would be able to select a column and mark it as of type "US_zip", and the "deck.gl Polygon" visualization would just work. We could provide a list of common polygons (ZIP, city, state, country), and users would be able to load their own. This way, the "Country Map" visualization could be deprecated in favor of the deck.gl one.</p>
<p dir="auto">The downside of this approach is that the shapes would be stored in the main database, which might not be inefficient. At Lyft we use Postgres with GIS extensions for the US ZIP codes, but MySQL for the main database.</p>