Usable Geospatial Interactions #22

saulshanabrook · 2019-11-05T19:40:29Z

saulshanabrook · 2019-11-12T15:49:55Z

As a pre-cursor to meeting it would be great if I can take a closer look at a working example. Is the mybinder link you sent earlier still the best representative? And just to make sure I remember everything correctly, is the primary issue here that the main JS/UI thread is blocking while long-running transforms execute? (And are those transforms running within JS and/or externally?)

For speed of testing / development, if there is a standalone Vega spec (JS only) that exhibits the issues that would be great, too.

We should show two examples here. One is the mostly working current "Charting Example." The performance is a bit slow, but still usable. To speed it up, we could unblock the UI to make it more responsible, while waiting for data as well as parallelize the data fetching.

The other example we should show is the geospatial binning example which is almost unusable at the moment, it's so slow.

saulshanabrook · 2019-11-12T15:52:09Z

Unfortunately there is not a standalone example, because AFAIK none of the built in transforms are async and our transform requires connecting to a kernel backend.

This builds on #34 to address #22 by creating one notebook to detail performance issue

saulshanabrook · 2019-12-05T19:06:03Z

Jeff added vega/vega#2184 in vega and said:

For an example of an operator that uses the new support, look at the updated Load transform in the vega-transforms package: https://github.com/vega/vega/pull/2184/files#diff-5dbc13883872ee114ecc15046243f60e (edited)

To request truly async handling, an operator can return an object with a single Promise-valued async property. That Promise should resolve to a callback function that is invoked immediately before a future dataflow evaluation. (In Load.js, this callback simply calls df.touch to ensure that the operator is queued for evaluation.)

If you have a chance to take a look (or even better, test run the jh/async branch!), please let me know what you think. I’d like to make sure this solves your problems before merging and releasing.

I did do some local testing where I added a multi-second delay to the Promise resolution, and confirmed that the visualization was still responsive (e.g., changing colors on hover) while the Promise was pending.

We should update our vega transform to make it async after depending on this commit in vega

saulshanabrook · 2020-03-24T13:43:48Z

This would be a nice next goal to try to achieve: https://www.omnisci.com/demos/oil-and-gas

We can connect to the database with:

con = ibis.mapd.connect(
    host='titan.mapd.com', user='demouser', password='HyperInteractive', protocol='https', port='443', database='upstreamdb'
)

saulshanabrook · 2020-08-18T23:59:56Z

We have a demo currently of some geospatial binning, in the omnisci-vega-example.ipynb notebook. It allows you to zoom around and get re-binned data

I had a nice conversation with @yifanwu last week about the technique we are using here and wanted to document the E2E pipelines for this one example.

Spec

These are steps for the whole spec

Python (Altair + Ibis)

It starts with this Python code to build up a Vega Lite spec:

import altair as alt
import ibis_vega_transform
import ibis

conn = ibis.omniscidb.connect(
    host='metis.mapd.com', user='demouser', password='HyperInteractive',
    port=443, database='mapd', protocol= 'https'
)
t = conn.table("tweets_nov_feb")
x, y = t.goog_x, t.goog_y

WIDTH = 385
HEIGHT = 564
X_DOMAIN = [
        -3650484.1235206556,
        7413325.514451755
      ]
Y_DOMAIN = [
        -5778161.9183506705,
        10471808.487466192
      ]

scales = alt.selection_interval(
    bind='scales',
)

alt.Chart(t[x, y], width=WIDTH, height=HEIGHT).mark_rect().encode(
    alt.X(
        'goog_x:Q',
        bin=alt.Bin(maxbins=WIDTH),
        title='goog_x',
        scale=alt.Scale(domain=X_DOMAIN)
    ),
    alt.Y(
        'goog_y:Q',
        bin=alt.Bin(maxbins=HEIGHT),
        title='goog_y',
        scale=alt.Scale(domain=Y_DOMAIN)
    ),
    tooltip='count()'
).add_selection(
    scales
).transform_filter(
    scales
)

Vega Lite

Original vega lite spec:

{
  "config": { "view": { "continuousWidth": 400, "continuousHeight": 300 } },
  "data": { "name": "ibis:-6194678083922326687" },
  "mark": "rect",
  "encoding": {
    "tooltip": { "type": "quantitative", "aggregate": "count" },
    "x": {
      "type": "quantitative",
      "bin": { "maxbins": 385 },
      "field": "goog_x",
      "scale": { "domain": [-3650484.1235206556, 7413325.514451755] },
      "title": "goog_x"
    },
    "y": {
      "type": "quantitative",
      "bin": { "maxbins": 564 },
      "field": "goog_y",
      "scale": { "domain": [-5778161.9183506705, 10471808.487466192] },
      "title": "goog_y"
    }
  },
  "height": 564,
  "selection": { "selector002": { "type": "interval", "bind": "scales" } },
  "transform": [{ "filter": { "selection": "selector002" } }],
  "width": 385,
  "$schema": "https://vega.github.io/schema/vega-lite/v4.0.2.json"
}

Vega Lite (extracted)

This is the Vega Lite after running extractTransforms (no longer present in Vega Lite codebase).

In this case it looks the same:

{
  "config": {
    "view": {
      "continuousWidth": 400,
      "continuousHeight": 300
    }
  },
  "data": {
    "name": "ibis:-6194678083922326687"
  },
  "mark": "rect",
  "encoding": {
    "tooltip": {
      "type": "quantitative",
      "aggregate": "count"
    },
    "x": {
      "type": "quantitative",
      "bin": {
        "maxbins": 385
      },
      "field": "goog_x",
      "scale": {
        "domain": [-3650484.1235206556, 7413325.514451755]
      },
      "title": "goog_x"
    },
    "y": {
      "type": "quantitative",
      "bin": {
        "maxbins": 564
      },
      "field": "goog_y",
      "scale": {
        "domain": [-5778161.9183506705, 10471808.487466192]
      },
      "title": "goog_y"
    }
  },
  "height": 564,
  "selection": {
    "selector002": {
      "type": "interval",
      "bind": "scales"
    }
  },
  "transform": [
    {
      "filter": {
        "selection": "selector002"
      }
    }
  ],
  "width": 385,
  "$schema": "https://vega.github.io/schema/vega-lite/v4.0.2.json"
}

Vega

This is the original Vega code compiled from Vega Lite

{
  "$schema": "https://vega.github.io/schema/vega/v5.json",
  "background": "white",
  "padding": 5,
  "width": 385,
  "height": 564,
  "style": "cell",
  "data": [
    { "name": "selector002_store" },
    { "name": "ibis:-6194678083922326687" },
    {
      "name": "data_0",
      "source": "ibis:-6194678083922326687",
      "transform": [
        {
          "type": "filter",
          "expr": "!(length(data(\"selector002_store\"))) || (vlSelectionTest(\"selector002_store\", datum))"
        },
        {
          "type": "extent",
          "field": "goog_x",
          "signal": "bin_maxbins_385_goog_x_extent"
        },
        {
          "type": "bin",
          "field": "goog_x",
          "as": ["bin_maxbins_385_goog_x", "bin_maxbins_385_goog_x_end"],
          "signal": "bin_maxbins_385_goog_x_bins",
          "extent": { "signal": "bin_maxbins_385_goog_x_extent" },
          "maxbins": 385
        },
        {
          "type": "extent",
          "field": "goog_y",
          "signal": "bin_maxbins_564_goog_y_extent"
        },
        {
          "type": "bin",
          "field": "goog_y",
          "as": ["bin_maxbins_564_goog_y", "bin_maxbins_564_goog_y_end"],
          "signal": "bin_maxbins_564_goog_y_bins",
          "extent": { "signal": "bin_maxbins_564_goog_y_extent" },
          "maxbins": 564
        },
        {
          "type": "aggregate",
          "groupby": [
            "bin_maxbins_385_goog_x",
            "bin_maxbins_385_goog_x_end",
            "bin_maxbins_564_goog_y",
            "bin_maxbins_564_goog_y_end"
          ],
          "ops": ["count"],
          "fields": [null],
          "as": ["__count"]
        },
        {
          "type": "filter",
          "expr": "isValid(datum[\"bin_maxbins_385_goog_x\"]) && isFinite(+datum[\"bin_maxbins_385_goog_x\"]) && isValid(datum[\"bin_maxbins_564_goog_y\"]) && isFinite(+datum[\"bin_maxbins_564_goog_y\"])"
        }
      ]
    }
  ],
  "signals": [
    {
      "name": "unit",
      "value": {},
      "on": [
        { "events": "mousemove", "update": "isTuple(group()) ? group() : unit" }
      ]
    },
    {
      "name": "selector002",
      "update": "vlSelectionResolve(\"selector002_store\", \"union\")"
    },
    {
      "name": "selector002_bin_maxbins_385_goog_x",
      "on": [
        {
          "events": { "signal": "selector002_translate_delta" },
          "update": "panLinear(selector002_translate_anchor.extent_x, -selector002_translate_delta.x / width)"
        },
        {
          "events": { "signal": "selector002_zoom_delta" },
          "update": "zoomLinear(domain(\"x\"), selector002_zoom_anchor.x, selector002_zoom_delta)"
        },
        {
          "events": [{ "source": "scope", "type": "dblclick" }],
          "update": "null"
        }
      ]
    },
    {
      "name": "selector002_bin_maxbins_564_goog_y",
      "on": [
        {
          "events": { "signal": "selector002_translate_delta" },
          "update": "panLinear(selector002_translate_anchor.extent_y, selector002_translate_delta.y / height)"
        },
        {
          "events": { "signal": "selector002_zoom_delta" },
          "update": "zoomLinear(domain(\"y\"), selector002_zoom_anchor.y, selector002_zoom_delta)"
        },
        {
          "events": [{ "source": "scope", "type": "dblclick" }],
          "update": "null"
        }
      ]
    },
    {
      "name": "selector002_tuple",
      "on": [
        {
          "events": [
            {
              "signal": "selector002_bin_maxbins_385_goog_x || selector002_bin_maxbins_564_goog_y"
            }
          ],
          "update": "selector002_bin_maxbins_385_goog_x && selector002_bin_maxbins_564_goog_y ? {unit: \"\", fields: selector002_tuple_fields, values: [selector002_bin_maxbins_385_goog_x,selector002_bin_maxbins_564_goog_y]} : null"
        }
      ]
    },
    {
      "name": "selector002_tuple_fields",
      "value": [
        { "field": "bin_maxbins_385_goog_x", "channel": "x", "type": "R" },
        { "field": "bin_maxbins_564_goog_y", "channel": "y", "type": "R" }
      ]
    },
    {
      "name": "selector002_translate_anchor",
      "value": {},
      "on": [
        {
          "events": [{ "source": "scope", "type": "mousedown" }],
          "update": "{x: x(unit), y: y(unit), extent_x: domain(\"x\"), extent_y: domain(\"y\")}"
        }
      ]
    },
    {
      "name": "selector002_translate_delta",
      "value": {},
      "on": [
        {
          "events": [
            {
              "source": "window",
              "type": "mousemove",
              "consume": true,
              "between": [
                { "source": "scope", "type": "mousedown" },
                { "source": "window", "type": "mouseup" }
              ]
            }
          ],
          "update": "{x: selector002_translate_anchor.x - x(unit), y: selector002_translate_anchor.y - y(unit)}"
        }
      ]
    },
    {
      "name": "selector002_zoom_anchor",
      "on": [
        {
          "events": [{ "source": "scope", "type": "wheel", "consume": true }],
          "update": "{x: invert(\"x\", x(unit)), y: invert(\"y\", y(unit))}"
        }
      ]
    },
    {
      "name": "selector002_zoom_delta",
      "on": [
        {
          "events": [{ "source": "scope", "type": "wheel", "consume": true }],
          "force": true,
          "update": "pow(1.001, event.deltaY * pow(16, event.deltaMode))"
        }
      ]
    },
    {
      "name": "selector002_modify",
      "on": [
        {
          "events": { "signal": "selector002_tuple" },
          "update": "modify(\"selector002_store\", selector002_tuple, true)"
        }
      ]
    }
  ],
  "marks": [
    {
      "name": "marks",
      "type": "rect",
      "clip": true,
      "style": ["rect"],
      "interactive": true,
      "from": { "data": "data_0" },
      "encode": {
        "update": {
          "fill": { "value": "#4c78a8" },
          "tooltip": { "signal": "format(datum[\"__count\"], \"\")" },
          "description": {
            "signal": "\"Count of Records\" + \": \" + (format(datum[\"__count\"], \"\")) + \"; \" + \"goog_x\" + \": \" + (!isValid(datum[\"bin_maxbins_385_goog_x\"]) || !isFinite(+datum[\"bin_maxbins_385_goog_x\"]) ? \"null\" : format(datum[\"bin_maxbins_385_goog_x\"], \"\") + \" \u2013 \" + format(datum[\"bin_maxbins_385_goog_x_end\"], \"\")) + \"; \" + \"goog_y\" + \": \" + (!isValid(datum[\"bin_maxbins_564_goog_y\"]) || !isFinite(+datum[\"bin_maxbins_564_goog_y\"]) ? \"null\" : format(datum[\"bin_maxbins_564_goog_y\"], \"\") + \" \u2013 \" + format(datum[\"bin_maxbins_564_goog_y_end\"], \"\"))"
          },
          "x2": {
            "scale": "x",
            "field": "bin_maxbins_385_goog_x",
            "offset": 0.5
          },
          "x": {
            "scale": "x",
            "field": "bin_maxbins_385_goog_x_end",
            "offset": 0.5
          },
          "y2": {
            "scale": "y",
            "field": "bin_maxbins_564_goog_y",
            "offset": 0.5
          },
          "y": {
            "scale": "y",
            "field": "bin_maxbins_564_goog_y_end",
            "offset": 0.5
          }
        }
      }
    }
  ],
  "scales": [
    {
      "name": "x",
      "type": "linear",
      "domain": [-3650484.1235206556, 7413325.514451755],
      "domainRaw": { "signal": "selector002[\"bin_maxbins_385_goog_x\"]" },
      "range": [0, { "signal": "width" }],
      "zero": true
    },
    {
      "name": "y",
      "type": "linear",
      "domain": [-5778161.9183506705, 10471808.487466192],
      "domainRaw": { "signal": "selector002[\"bin_maxbins_564_goog_y\"]" },
      "range": [{ "signal": "height" }, 0],
      "zero": true
    }
  ],
  "axes": [
    {
      "scale": "x",
      "orient": "bottom",
      "grid": false,
      "title": "goog_x",
      "labelFlush": true,
      "labelOverlap": true,
      "tickCount": { "signal": "ceil(width/40)" },
      "zindex": 1
    },
    {
      "scale": "y",
      "orient": "left",
      "grid": false,
      "title": "goog_y",
      "labelOverlap": true,
      "tickCount": { "signal": "ceil(height/40)" },
      "zindex": 1
    }
  ]
}

Vega (transformed)

This is the Vega code after running our translator to move the transforms to use our custom transform

Vega:

{
  "$schema": "https://vega.github.io/schema/vega/v5.json",
  "background": "white",
  "padding": 5,
  "width": 385,
  "height": 564,
  "style": "cell",
  "data": [
    { "name": "selector002_store" },
    {
      "name": "data_0",
      "transform": [
        {
          "type": "queryibis",
          "name": "-6194678083922326687",
          "span": { "uber-trace-id": "c0af50e7c4763316:15b9735bcc39f396:0:1" },
          "data": "{selector002_store: data('selector002_store')}",
          "transform": [
            {
              "type": "filter",
              "expr": "!(length(data(\"selector002_store\"))) || (vlSelectionTest(\"selector002_store\", datum))"
            },
            {
              "type": "extent",
              "field": "goog_x",
              "signal_": "bin_maxbins_385_goog_x_extent"
            },
            {
              "type": "bin",
              "field": "goog_x",
              "as": ["bin_maxbins_385_goog_x", "bin_maxbins_385_goog_x_end"],
              "extent": { "signal": "bin_maxbins_385_goog_x_extent" },
              "maxbins": 385,
              "signal_": "bin_maxbins_385_goog_x_bins"
            },
            {
              "type": "extent",
              "field": "goog_y",
              "signal_": "bin_maxbins_564_goog_y_extent"
            },
            {
              "type": "bin",
              "field": "goog_y",
              "as": ["bin_maxbins_564_goog_y", "bin_maxbins_564_goog_y_end"],
              "extent": { "signal": "bin_maxbins_564_goog_y_extent" },
              "maxbins": 564,
              "signal_": "bin_maxbins_564_goog_y_bins"
            },
            {
              "type": "aggregate",
              "groupby": [
                "bin_maxbins_385_goog_x",
                "bin_maxbins_385_goog_x_end",
                "bin_maxbins_564_goog_y",
                "bin_maxbins_564_goog_y_end"
              ],
              "ops": ["count"],
              "fields": [null],
              "as": ["__count"]
            },
            {
              "type": "filter",
              "expr": "isValid(datum[\"bin_maxbins_385_goog_x\"]) && isFinite(+datum[\"bin_maxbins_385_goog_x\"]) && isValid(datum[\"bin_maxbins_564_goog_y\"]) && isFinite(+datum[\"bin_maxbins_564_goog_y\"])"
            }
          ]
        }
      ]
    }
  ],
  "signals": [
    {
      "name": "unit",
      "value": {},
      "on": [
        { "events": "mousemove", "update": "isTuple(group()) ? group() : unit" }
      ]
    },
    {
      "name": "selector002",
      "update": "vlSelectionResolve(\"selector002_store\", \"union\")"
    },
    {
      "name": "selector002_bin_maxbins_385_goog_x",
      "on": [
        {
          "events": { "signal": "selector002_translate_delta" },
          "update": "panLinear(selector002_translate_anchor.extent_x, -selector002_translate_delta.x / width)"
        },
        {
          "events": { "signal": "selector002_zoom_delta" },
          "update": "zoomLinear(domain(\"x\"), selector002_zoom_anchor.x, selector002_zoom_delta)"
        },
        {
          "events": [{ "source": "scope", "type": "dblclick" }],
          "update": "null"
        }
      ]
    },
    {
      "name": "selector002_bin_maxbins_564_goog_y",
      "on": [
        {
          "events": { "signal": "selector002_translate_delta" },
          "update": "panLinear(selector002_translate_anchor.extent_y, selector002_translate_delta.y / height)"
        },
        {
          "events": { "signal": "selector002_zoom_delta" },
          "update": "zoomLinear(domain(\"y\"), selector002_zoom_anchor.y, selector002_zoom_delta)"
        },
        {
          "events": [{ "source": "scope", "type": "dblclick" }],
          "update": "null"
        }
      ]
    },
    {
      "name": "selector002_tuple",
      "on": [
        {
          "events": [
            {
              "signal": "selector002_bin_maxbins_385_goog_x || selector002_bin_maxbins_564_goog_y"
            }
          ],
          "update": "selector002_bin_maxbins_385_goog_x && selector002_bin_maxbins_564_goog_y ? {unit: \"\", fields: selector002_tuple_fields, values: [selector002_bin_maxbins_385_goog_x,selector002_bin_maxbins_564_goog_y]} : null"
        }
      ]
    },
    {
      "name": "selector002_tuple_fields",
      "value": [
        { "field": "bin_maxbins_385_goog_x", "channel": "x", "type": "R" },
        { "field": "bin_maxbins_564_goog_y", "channel": "y", "type": "R" }
      ]
    },
    {
      "name": "selector002_translate_anchor",
      "value": {},
      "on": [
        {
          "events": [{ "source": "scope", "type": "mousedown" }],
          "update": "{x: x(unit), y: y(unit), extent_x: domain(\"x\"), extent_y: domain(\"y\")}"
        }
      ]
    },
    {
      "name": "selector002_translate_delta",
      "value": {},
      "on": [
        {
          "events": [
            {
              "source": "window",
              "type": "mousemove",
              "consume": true,
              "between": [
                { "source": "scope", "type": "mousedown" },
                { "source": "window", "type": "mouseup" }
              ]
            }
          ],
          "update": "{x: selector002_translate_anchor.x - x(unit), y: selector002_translate_anchor.y - y(unit)}"
        }
      ]
    },
    {
      "name": "selector002_zoom_anchor",
      "on": [
        {
          "events": [{ "source": "scope", "type": "wheel", "consume": true }],
          "update": "{x: invert(\"x\", x(unit)), y: invert(\"y\", y(unit))}"
        }
      ]
    },
    {
      "name": "selector002_zoom_delta",
      "on": [
        {
          "events": [{ "source": "scope", "type": "wheel", "consume": true }],
          "force": true,
          "update": "pow(1.001, event.deltaY * pow(16, event.deltaMode))"
        }
      ]
    },
    {
      "name": "selector002_modify",
      "on": [
        {
          "events": { "signal": "selector002_tuple" },
          "update": "modify(\"selector002_store\", selector002_tuple, true)"
        }
      ]
    }
  ],
  "marks": [
    {
      "name": "marks",
      "type": "rect",
      "clip": true,
      "style": ["rect"],
      "interactive": true,
      "from": { "data": "data_0" },
      "encode": {
        "update": {
          "fill": { "value": "#4c78a8" },
          "tooltip": { "signal": "format(datum[\"__count\"], \"\")" },
          "description": {
            "signal": "\"Count of Records\" + \": \" + (format(datum[\"__count\"], \"\")) + \"; \" + \"goog_x\" + \": \" + (!isValid(datum[\"bin_maxbins_385_goog_x\"]) || !isFinite(+datum[\"bin_maxbins_385_goog_x\"]) ? \"null\" : format(datum[\"bin_maxbins_385_goog_x\"], \"\") + \" \u2013 \" + format(datum[\"bin_maxbins_385_goog_x_end\"], \"\")) + \"; \" + \"goog_y\" + \": \" + (!isValid(datum[\"bin_maxbins_564_goog_y\"]) || !isFinite(+datum[\"bin_maxbins_564_goog_y\"]) ? \"null\" : format(datum[\"bin_maxbins_564_goog_y\"], \"\") + \" \u2013 \" + format(datum[\"bin_maxbins_564_goog_y_end\"], \"\"))"
          },
          "x2": {
            "scale": "x",
            "field": "bin_maxbins_385_goog_x",
            "offset": 0.5
          },
          "x": {
            "scale": "x",
            "field": "bin_maxbins_385_goog_x_end",
            "offset": 0.5
          },
          "y2": {
            "scale": "y",
            "field": "bin_maxbins_564_goog_y",
            "offset": 0.5
          },
          "y": {
            "scale": "y",
            "field": "bin_maxbins_564_goog_y_end",
            "offset": 0.5
          }
        }
      }
    }
  ],
  "scales": [
    {
      "name": "x",
      "type": "linear",
      "domain": [-3650484.1235206556, 7413325.514451755],
      "domainRaw": { "signal": "selector002[\"bin_maxbins_385_goog_x\"]" },
      "range": [0, { "signal": "width" }],
      "zero": true
    },
    {
      "name": "y",
      "type": "linear",
      "domain": [-5778161.9183506705, 10471808.487466192],
      "domainRaw": { "signal": "selector002[\"bin_maxbins_564_goog_y\"]" },
      "range": [{ "signal": "height" }, 0],
      "zero": true
    }
  ],
  "axes": [
    {
      "scale": "x",
      "orient": "bottom",
      "grid": false,
      "title": "goog_x",
      "labelFlush": true,
      "labelOverlap": true,
      "tickCount": { "signal": "ceil(width/40)" },
      "zindex": 1
    },
    {
      "scale": "y",
      "orient": "left",
      "grid": false,
      "title": "goog_y",
      "labelOverlap": true,
      "tickCount": { "signal": "ceil(height/40)" },
      "zindex": 1
    }
  ]
}

Query

These are steps that execute as the user interacts with the plot, happening many times over and over again

Transform + selectors

This is the transform we send from the browser to the backend to process:

[
  {
    "type": "filter",
    "expr": "!(length(data(\"selector002_store\"))) || (vlSelectionTest(\"selector002_store\", datum))"
  },
  {
    "type": "extent",
    "field": "goog_x",
    "signal_": "bin_maxbins_385_goog_x_extent"
  },
  {
    "type": "bin",
    "field": "goog_x",
    "as": ["bin_maxbins_385_goog_x", "bin_maxbins_385_goog_x_end"],
    "extent": { "signal": "bin_maxbins_385_goog_x_extent" },
    "maxbins": 385,
    "signal_": "bin_maxbins_385_goog_x_bins"
  },
  {
    "type": "extent",
    "field": "goog_y",
    "signal_": "bin_maxbins_564_goog_y_extent"
  },
  {
    "type": "bin",
    "field": "goog_y",
    "as": ["bin_maxbins_564_goog_y", "bin_maxbins_564_goog_y_end"],
    "extent": { "signal": "bin_maxbins_564_goog_y_extent" },
    "maxbins": 564,
    "signal_": "bin_maxbins_564_goog_y_bins"
  },
  {
    "type": "aggregate",
    "groupby": [
      "bin_maxbins_385_goog_x",
      "bin_maxbins_385_goog_x_end",
      "bin_maxbins_564_goog_y",
      "bin_maxbins_564_goog_y_end"
    ],
    "ops": ["count"],
    "fields": [null],
    "as": ["__count"]
  },
  {
    "type": "filter",
    "expr": "isValid(datum[\"bin_maxbins_385_goog_x\"]) && isFinite(+datum[\"bin_maxbins_385_goog_x\"]) && isValid(datum[\"bin_maxbins_564_goog_y\"]) && isFinite(+datum[\"bin_maxbins_564_goog_y\"])"
  }
]

We also send this current value of the `selector002_store` that is from a Vega signal that we need:

[
  {
    "unit": "",
    "fields": [
      { "field": "bin_maxbins_385_goog_x", "channel": "x", "type": "R" },
      { "field": "bin_maxbins_564_goog_y", "channel": "y", "type": "R" }
    ],
    "values": [
      [-3233925.160255925, 4213566.283868609],
      [-1715873.2568166682, 9222629.80177145]
    ]
  }
]

Min/Max Queries

We then send a number of sub-queries, which we do as an optimization for the groupby operation, we shouldn't technical need them:

SELECT min(t0."goog_x") AS "min"
FROM (
  SELECT *, "goog_y" AS bin_maxbins_564_goog_y
  FROM (
    SELECT "goog_x", "goog_y", "goog_x" AS bin_maxbins_385_goog_x
    FROM tweets_nov_feb
  ) t2
) t0
WHERE (t0."bin_maxbins_385_goog_x" >= -3233925.160255925) AND
      (t0."bin_maxbins_385_goog_x" <= 4213566.283868609) AND
      (t0."bin_maxbins_564_goog_y" >= -1715873.2568166682) AND
      (t0."bin_maxbins_564_goog_y" <= 9222629.80177145)

SELECT max(t0."goog_x") AS "max"
FROM (
  SELECT *, "goog_y" AS bin_maxbins_564_goog_y
  FROM (
    SELECT "goog_x", "goog_y", "goog_x" AS bin_maxbins_385_goog_x
    FROM tweets_nov_feb
  ) t2
) t0
WHERE (t0."bin_maxbins_385_goog_x" >= -3233925.160255925) AND
      (t0."bin_maxbins_385_goog_x" <= 4213566.283868609) AND
      (t0."bin_maxbins_564_goog_y" >= -1715873.2568166682) AND
      (t0."bin_maxbins_564_goog_y" <= 9222629.80177145)

SELECT min("goog_y") AS "min"
FROM (
  SELECT "goog_x", "goog_y",
         (floor(("goog_x" / CAST(19192.00779220779 AS FLOAT)) - CAST(-165.45237394543156 AS FLOAT)) * 19192.00779220779) + -3175363.25 AS bin_maxbins_385_goog_x,
         "bin_maxbins_564_goog_y",
         (floor(("goog_x" / CAST(19192.00779220779 AS FLOAT)) - CAST(-165.45237394543156 AS FLOAT)) * 19192.00779220779) + -3156171.242207792 AS bin_maxbins_385_goog_x_end
  FROM (
    SELECT t2.*
    FROM (
      SELECT *, "goog_y" AS bin_maxbins_564_goog_y
      FROM (
        SELECT "goog_x", "goog_y", "goog_x" AS bin_maxbins_385_goog_x
        FROM tweets_nov_feb
      ) t4
    ) t2
    WHERE (t2."bin_maxbins_385_goog_x" >= -3233925.160255925) AND
          (t2."bin_maxbins_385_goog_x" <= 4213566.283868609) AND
          (t2."bin_maxbins_564_goog_y" >= -1715873.2568166682) AND
          (t2."bin_maxbins_564_goog_y" <= 9222629.80177145)
  ) t1
) t0

SELECT max("goog_y") AS "max"
FROM (
  SELECT "goog_x", "goog_y",
         (floor(("goog_x" / CAST(19192.00779220779 AS FLOAT)) - CAST(-165.45237394543156 AS FLOAT)) * 19192.00779220779) + -3175363.25 AS bin_maxbins_385_goog_x,
         "bin_maxbins_564_goog_y",
         (floor(("goog_x" / CAST(19192.00779220779 AS FLOAT)) - CAST(-165.45237394543156 AS FLOAT)) * 19192.00779220779) + -3156171.242207792 AS bin_maxbins_385_goog_x_end
  FROM (
    SELECT t2.*
    FROM (
      SELECT *, "goog_y" AS bin_maxbins_564_goog_y
      FROM (
        SELECT "goog_x", "goog_y", "goog_x" AS bin_maxbins_385_goog_x
        FROM tweets_nov_feb
      ) t4
    ) t2
    WHERE (t2."bin_maxbins_385_goog_x" >= -3233925.160255925) AND
          (t2."bin_maxbins_385_goog_x" <= 4213566.283868609) AND
          (t2."bin_maxbins_564_goog_y" >= -1715873.2568166682) AND
          (t2."bin_maxbins_564_goog_y" <= 9222629.80177145)
  ) t1
) t0

FInal Query

Then we have a final query we execute:

SELECT "bin_maxbins_385_goog_x", "bin_maxbins_385_goog_x_end",
       "bin_maxbins_564_goog_y", "bin_maxbins_564_goog_y_end",
       count(*) AS __count
FROM (
  SELECT "goog_x", "goog_y", "bin_maxbins_385_goog_x",
         (floor(("goog_y" / CAST(19363.597517730497 AS FLOAT)) - CAST(-88.41715096754722 AS FLOAT)) * 19363.597517730497) + -1712074.125 AS bin_maxbins_564_goog_y,
         "bin_maxbins_385_goog_x_end",
         (floor(("goog_y" / CAST(19363.597517730497 AS FLOAT)) - CAST(-88.41715096754722 AS FLOAT)) * 19363.597517730497) + -1692710.5274822696 AS bin_maxbins_564_goog_y_end
  FROM (
    SELECT "goog_x", "goog_y",
           (floor(("goog_x" / CAST(19192.00779220779 AS FLOAT)) - CAST(-165.45237394543156 AS FLOAT)) * 19192.00779220779) + -3175363.25 AS bin_maxbins_385_goog_x,
           "bin_maxbins_564_goog_y",
           (floor(("goog_x" / CAST(19192.00779220779 AS FLOAT)) - CAST(-165.45237394543156 AS FLOAT)) * 19192.00779220779) + -3156171.242207792 AS bin_maxbins_385_goog_x_end
    FROM (
      SELECT t3.*
      FROM (
        SELECT *, "goog_y" AS bin_maxbins_564_goog_y
        FROM (
          SELECT "goog_x", "goog_y", "goog_x" AS bin_maxbins_385_goog_x
          FROM tweets_nov_feb
        ) t5
      ) t3
      WHERE (t3."bin_maxbins_385_goog_x" >= -3233925.160255925) AND
            (t3."bin_maxbins_385_goog_x" <= 4213566.283868609) AND
            (t3."bin_maxbins_564_goog_y" >= -1715873.2568166682) AND
            (t3."bin_maxbins_564_goog_y" <= 9222629.80177145)
    ) t2
  ) t1
) t0
WHERE ((NOT ("bin_maxbins_385_goog_x" IS NULL) AND NOT ("bin_maxbins_385_goog_x" IS NULL)) AND NOT ("bin_maxbins_564_goog_y" IS NULL)) AND NOT ("bin_maxbins_564_goog_y" IS NULL)
GROUP BY bin_maxbins_385_goog_x, bin_maxbins_385_goog_x_end, bin_maxbins_564_goog_y, bin_maxbins_564_goog_y_end

saulshanabrook self-assigned this Nov 12, 2019

saulshanabrook added the performance label Nov 12, 2019

saulshanabrook added a commit that referenced this issue Nov 18, 2019

Add performance notebook

3007600

This builds on #34 to address #22 by creating one notebook to detail performance issue

saulshanabrook mentioned this issue Nov 18, 2019

Add Performance Notebook #36

Merged

saulshanabrook removed their assignment Dec 12, 2019

saulshanabrook self-assigned this Jan 16, 2020

saulshanabrook mentioned this issue Jan 21, 2020

Switch to async trasnform #49

Merged

2 tasks

goanpeca added ibis-vega The project this belongs to status:in-progress Work in progress type:enhancement Implement an improvement over a functionality labels Jan 22, 2020

saulshanabrook removed their assignment Mar 9, 2020

saulshanabrook added status:backlog Work to be done and removed status:in-progress Work in progress labels Mar 9, 2020

saulshanabrook assigned goanpeca Mar 24, 2020

rpekrul added omniscidb and removed omniscidb status:backlog Work to be done labels Sep 18, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Usable Geospatial Interactions #22

Usable Geospatial Interactions #22

saulshanabrook commented Nov 5, 2019 •

edited

Loading

saulshanabrook commented Nov 12, 2019 •

edited by goanpeca

Loading

saulshanabrook commented Nov 12, 2019

saulshanabrook commented Dec 5, 2019

saulshanabrook commented Mar 24, 2020

saulshanabrook commented Aug 18, 2020 •

edited

Loading

Usable Geospatial Interactions #22

Usable Geospatial Interactions #22

Comments

saulshanabrook commented Nov 5, 2019 • edited Loading

saulshanabrook commented Nov 12, 2019 • edited by goanpeca Loading

saulshanabrook commented Nov 12, 2019

saulshanabrook commented Dec 5, 2019

saulshanabrook commented Mar 24, 2020

saulshanabrook commented Aug 18, 2020 • edited Loading

Spec

Python (Altair + Ibis)

Vega Lite

Vega Lite (extracted)

Vega

Vega (transformed)

Query

Transform + selectors

Min/Max Queries

FInal Query

saulshanabrook commented Nov 5, 2019 •

edited

Loading

saulshanabrook commented Nov 12, 2019 •

edited by goanpeca

Loading

saulshanabrook commented Aug 18, 2020 •

edited

Loading