Shape Maps

VSCode

The plugin currently supports US maps and state names. The model and data styles for the subsequent examples are:

source: airports is duckdb.table('../data/airports.parquet') extend {
  primary_key: code
  measure: airport_count is count()
  view: by_state is {
    group_by: state
    aggregate: airport_count
  }
  # shape_map
  view: by_state_shaped is by_state
}

Run a query and tag the results as a shape map

We can explicitly return a result as a shape map.

# shape_map
run: airports -> by_state

QUERY RESULTS

[
  {
    "state": "TX",
    "airport_count": 1845
  },
  {
    "state": "CA",
    "airport_count": 984
  },
  {
    "state": "IL",
    "airport_count": 890
  },
  {
    "state": "FL",
    "airport_count": 856
  },
  {
    "state": "PA",
    "airport_count": 804
  }
]

SELECT 
   airports."state" as "state",
   COUNT( 1) as "airport_count"
FROM '../data/airports.parquet' as airports
GROUP BY 1
ORDER BY 2 desc NULLS LAST

The tag is in the semantic model

In the query below the tag is in the semantic model.

run: airports -> by_state_shaped + {where: fac_type='SEAPLANE BASE'}

QUERY RESULTS

[
  {
    "state": "AK",
    "airport_count": 104
  },
  {
    "state": "MN",
    "airport_count": 72
  },
  {
    "state": "FL",
    "airport_count": 43
  },
  {
    "state": "ME",
    "airport_count": 38
  },
  {
    "state": "NY",
    "airport_count": 23
  }
]

SELECT 
   airports."state" as "state",
   COUNT( 1) as "airport_count"
FROM '../data/airports.parquet' as airports
WHERE airports."fac_type"='SEAPLANE BASE'
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Run as a trellis

By calling the configured map as a nested subtable, a trellis is formed.

run: airports -> {
  group_by: faa_region
  aggregate: airport_count
  # shape_map
  nest: by_state
}

QUERY RESULTS

faa_region	airport_count	by_state
AGL	4,437
ASW	3,268
ASO	2,924
AEA	2,586
ANM	2,102

[
  {
    "faa_region": "AGL",
    "airport_count": 4437,
    "by_state": [
      {
        "state": "IL",
        "airport_count": 890
      },
      {
        "state": "OH",
        "airport_count": 749
      },
      {
        "state": "IN",
        "airport_count": 643
      },
      {
        "state": "WI",
        "airport_count": 543
      },
      {
        "state": "MN",
        "airport_count": 507
      },
      {
        "state": "MI",
        "airport_count": 489
      },
      {
        "state": "ND",
        "airport_count": 436
      },
      {
        "state": "SD",
        "airport_count": 180
      }
    ]
  },
  {
    "faa_region": "ASW",
    "airport_count": 3268,
    "by_state": [
      {
        "state": "TX",
        "airport_count": 1845
      },
      {
        "state": "LA",
        "airport_count": 500
      },
      {
        "state": "OK",
        "airport_count": 443
      },
      {
        "state": "AR",
        "airport_count": 299
      },
      {
        "state": "NM",
        "airport_count": 181
      }
    ]
  },
  {
    "faa_region": "ASO",
    "airport_count": 2924,
    "by_state": [
      {
        "state": "FL",
        "airport_count": 856
      },
      {
        "state": "GA",
        "airport_count": 440
      },
      {
        "state": "NC",
        "airport_count": 400
      },
      {
        "state": "TN",
        "airport_count": 285
      },
      {
        "state": "AL",
        "airport_count": 260
      },
      {
        "state": "MS",
        "airport_count": 243
      },
      {
        "state": "KY",
        "airport_count": 202
      },
      {
        "state": "SC",
        "airport_count": 189
      },
      {
        "state": "PR",
        "airport_count": 40
      },
      {
        "state": "VI",
        "airport_count": 9
      }
    ]
  },
  {
    "faa_region": "AEA",
    "airport_count": 2586,
    "by_state": [
      {
        "state": "PA",
        "airport_count": 804
      },
      {
        "state": "NY",
        "airport_count": 576
      },
      {
        "state": "VA",
        "airport_count": 421
      },
      {
        "state": "NJ",
        "airport_count": 378
      },
      {
        "state": "MD",
        "airport_count": 229
      },
      {
        "state": "WV",
        "airport_count": 116
      },
      {
        "state": "DE",
        "airport_count": 42
      },
      {
        "state": "DC",
        "airport_count": 20
      }
    ]
  },
  {
    "faa_region": "ANM",
    "airport_count": 2102,
    "by_state": [
      {
        "state": "WA",
        "airport_count": 484
      },
      {
        "state": "OR",
        "airport_count": 441
      },
      {
        "state": "CO",
        "airport_count": 425
      },
      {
        "state": "MT",
        "airport_count": 259
      },
      {
        "state": "ID",
        "airport_count": 238
      },
      {
        "state": "UT",
        "airport_count": 140
      },
      {
        "state": "WY",
        "airport_count": 115
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    airports."faa_region" as "faa_region__0",
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as "airport_count__0",
    CASE WHEN group_set=1 THEN
      airports."state"
      END as "state__1",
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as "airport_count__1"
  FROM '../data/airports.parquet' as airports
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,2,4
)
SELECT
  "faa_region__0" as "faa_region",
  MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count",
  COALESCE(LIST({
    "state": "state__1", 
    "airport_count": "airport_count__1"}  ORDER BY  "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_state"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Run as a trellis, repeated with different filters

run: airports -> {
  group_by: faa_region
  aggregate: airport_count
  nest:
    // shape map is declared in the model
    heliports is by_state_shaped + { where: fac_type = 'HELIPORT' }
    # shape_map
    seaplane_bases is by_state + { where: fac_type = 'SEAPLANE BASE' }
}

QUERY RESULTS

faa_region	airport_count	heliports	seaplane_bases
AGL	4,437
ASW	3,268
ASO	2,924
AEA	2,586
ANM	2,102

[
  {
    "faa_region": "AGL",
    "airport_count": 4437,
    "heliports": [
      {
        "state": "IL",
        "airport_count": 245
      },
      {
        "state": "OH",
        "airport_count": 201
      },
      {
        "state": "IN",
        "airport_count": 115
      },
      {
        "state": "MI",
        "airport_count": 87
      },
      {
        "state": "WI",
        "airport_count": 85
      },
      {
        "state": "MN",
        "airport_count": 51
      },
      {
        "state": "SD",
        "airport_count": 26
      },
      {
        "state": "ND",
        "airport_count": 16
      }
    ],
    "seaplane_bases": [
      {
        "state": "MN",
        "airport_count": 72
      },
      {
        "state": "WI",
        "airport_count": 16
      },
      {
        "state": "IN",
        "airport_count": 12
      },
      {
        "state": "IL",
        "airport_count": 8
      },
      {
        "state": "MI",
        "airport_count": 7
      },
      {
        "state": "OH",
        "airport_count": 2
      },
      {
        "state": "ND",
        "airport_count": 1
      },
      {
        "state": "SD",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "ASW",
    "airport_count": 3268,
    "heliports": [
      {
        "state": "TX",
        "airport_count": 435
      },
      {
        "state": "LA",
        "airport_count": 229
      },
      {
        "state": "OK",
        "airport_count": 92
      },
      {
        "state": "AR",
        "airport_count": 80
      },
      {
        "state": "NM",
        "airport_count": 25
      }
    ],
    "seaplane_bases": [
      {
        "state": "LA",
        "airport_count": 17
      },
      {
        "state": "OK",
        "airport_count": 1
      },
      {
        "state": "NM",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "ASO",
    "airport_count": 2924,
    "heliports": [
      {
        "state": "FL",
        "airport_count": 280
      },
      {
        "state": "GA",
        "airport_count": 103
      },
      {
        "state": "TN",
        "airport_count": 87
      },
      {
        "state": "AL",
        "airport_count": 75
      },
      {
        "state": "NC",
        "airport_count": 70
      },
      {
        "state": "KY",
        "airport_count": 55
      },
      {
        "state": "MS",
        "airport_count": 49
      },
      {
        "state": "SC",
        "airport_count": 27
      },
      {
        "state": "PR",
        "airport_count": 20
      },
      {
        "state": "VI",
        "airport_count": 4
      }
    ],
    "seaplane_bases": [
      {
        "state": "FL",
        "airport_count": 43
      },
      {
        "state": "AL",
        "airport_count": 3
      },
      {
        "state": "VI",
        "airport_count": 3
      },
      {
        "state": "SC",
        "airport_count": 2
      },
      {
        "state": "TN",
        "airport_count": 2
      },
      {
        "state": "GA",
        "airport_count": 1
      },
      {
        "state": "KY",
        "airport_count": 1
      },
      {
        "state": "PR",
        "airport_count": 1
      },
      {
        "state": "NC",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "AEA",
    "airport_count": 2586,
    "heliports": [
      {
        "state": "PA",
        "airport_count": 307
      },
      {
        "state": "NJ",
        "airport_count": 247
      },
      {
        "state": "NY",
        "airport_count": 156
      },
      {
        "state": "VA",
        "airport_count": 126
      },
      {
        "state": "MD",
        "airport_count": 64
      },
      {
        "state": "WV",
        "airport_count": 33
      },
      {
        "state": "DC",
        "airport_count": 18
      },
      {
        "state": "DE",
        "airport_count": 13
      }
    ],
    "seaplane_bases": [
      {
        "state": "NY",
        "airport_count": 23
      },
      {
        "state": "NJ",
        "airport_count": 10
      },
      {
        "state": "PA",
        "airport_count": 10
      },
      {
        "state": "WV",
        "airport_count": 10
      },
      {
        "state": "VA",
        "airport_count": 4
      },
      {
        "state": "MD",
        "airport_count": 3
      },
      {
        "state": "DE",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "ANM",
    "airport_count": 2102,
    "heliports": [
      {
        "state": "CO",
        "airport_count": 165
      },
      {
        "state": "WA",
        "airport_count": 130
      },
      {
        "state": "OR",
        "airport_count": 100
      },
      {
        "state": "UT",
        "airport_count": 43
      },
      {
        "state": "ID",
        "airport_count": 36
      },
      {
        "state": "MT",
        "airport_count": 29
      },
      {
        "state": "WY",
        "airport_count": 24
      }
    ],
    "seaplane_bases": [
      {
        "state": "WA",
        "airport_count": 15
      },
      {
        "state": "ID",
        "airport_count": 5
      },
      {
        "state": "OR",
        "airport_count": 3
      },
      {
        "state": "MT",
        "airport_count": 2
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    airports."faa_region" as "faa_region__0",
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as "airport_count__0",
    CASE WHEN group_set=1 THEN
      airports."state"
      END as "state__1",
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as "airport_count__1",
    CASE WHEN group_set=2 THEN
      airports."state"
      END as "state__2",
    CASE WHEN group_set=2 THEN
      COUNT( 1)
      END as "airport_count__2"
  FROM '../data/airports.parquet' as airports
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set  ) as group_set
  WHERE ((group_set NOT IN (1) OR (group_set IN (1) AND airports."fac_type"='HELIPORT')))
  AND ((group_set NOT IN (2) OR (group_set IN (2) AND airports."fac_type"='SEAPLANE BASE')))
  GROUP BY 1,2,4,6
)
SELECT
  "faa_region__0" as "faa_region",
  MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count",
  COALESCE(LIST({
    "state": "state__1", 
    "airport_count": "airport_count__1"}  ORDER BY  "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "heliports",
  COALESCE(LIST({
    "state": "state__2", 
    "airport_count": "airport_count__2"}  ORDER BY  "airport_count__2" desc NULLS LAST) FILTER (WHERE group_set=2),[]) as "seaplane_bases"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST