Enable "geo-joins" in MetricFlow

[siljamardla]

A very typical use case:

• fact table with timestamp, coordinates and some numeric values (e.g. fact_order with timestamp, coordinates and order_value)
• dimensions table with areas (polygons). NB: Areas can have overalps, therefore we cannot simply add area_id to the fact table.

Something like this

Business question: what was the metric sum_order_value in each area.
To answer this question we'd have to do a "geo-join", i.e. find which orders belong to which areas and then sum up the order values per each area.

I can imagine two ways to enable this.

1. With a mapping table

Create a mapping table between order and area (each team would need to find a way to do this, using some point-in-polygon geo function that is available to them)

NB: This will be a many-to-many table, best practices say there should be a primary key in the table as well :)

To answer the question, we'd then have to generate SQL that would look something like this:

Currently, MetricFlow is not able to generate SQL like this because it's only possible to do joins between foreign and primary keys, but in this case we'd need a join from order_id in the orders table (primary key) to order_id in the mapping table (dimension).

2. With a point-in-polygon join using some geo functions

Instead of requiring there to be a mapping table, enable geo-joins in MetricFlow. The answer to the question would look something like this pseudo-code:

SELECT 
    dim_area.area_id
  , SUM(fact.order_value) AS metric_order_value
FROM fact
LEFT JOIN dim_area
ON <coordinates in fact table fall within polygon in dim table>