Salt Lake County Places Dashboard

db = DuckDBClient.of({
  places: FileAttachment("../posts/overture-data-download/output/slco_places_r.parquet")
})
places = db.sql`
  SELECT names.primary AS name,
         categories.main AS category,
         bbox.xmin AS lon,
         bbox.ymin AS lat,
         confidence,
         addresses[1].locality AS city
  FROM places
  WHERE names.primary IS NOT NULL AND categories.main IS NOT NULL
`
categoryCounts = d3.rollups(places, v => v.length, d => d.category).sort((a, b) => b[1] - a[1])
topCategories = categoryCounts.slice(0, 40).map(d => d[0])

Places selected

html`${rows.length.toLocaleString()}`

Share of all places

html`${(rows.length / places.length * 100).toFixed(1)}%`

Cities with a match

cityCounts.filter(d => d[0] !== "Unknown").length

Busiest city

html`${cityCounts.length ? cityCounts[0][0] + " " + cityCounts[0][1].toLocaleString() : "none"}`

Where the selected places are

Plot.plot({
  width: 640,
  height: 440,
  aspectRatio: 1 / Math.cos(40.7 * Math.PI / 180),
  x: {label: "Longitude", ticks: 5},
  y: {label: "Latitude", ticks: 5},
  color: {legend: true},
  style: {backgroundColor: "transparent"},
  marks: [
    Plot.dot(rows, {x: "lon", y: "lat", fill: d => d.category.replaceAll('_', ' '), r: 1.6, fillOpacity: 0.55, tip: true,
      title: d => `${d.name}\n${d.category}\nConfidence ${d.confidence.toFixed(2)}`})
  ]
})

Places by category

Plot.plot({
  width: 520,
  height: 440,
  marginLeft: 150,
  x: {label: "Places →", grid: true},
  y: {label: null},
  style: {backgroundColor: "transparent"},
  marks: [
    Plot.barX(catRows, {x: d => d[1], y: d => d[0].replaceAll('_', ' '), sort: {y: "-x"}, fill: "#007EA7", tip: true}),
    Plot.ruleX([0])
  ]
})

viewof chosen = Inputs.select(topCategories, {
  label: "Categories (top 40)",
  multiple: 8,
  format: d => d.replaceAll('_', ' '),
  value: topCategories.slice(0, 5)
})
viewof minConfidence = Inputs.range([0, 1], {label: "Minimum confidence", step: 0.05, value: 0.5})

Data: Overture Maps places for Salt Lake County, downloaded in the post below.

Read the data download post →

rows = places.filter(d => d.confidence >= minConfidence && chosen.includes(d.category))
cityCounts = d3.rollups(rows, v => v.length, d => d.city || "Unknown").sort((a, b) => b[1] - a[1])
catRows = d3.rollups(rows, v => v.length, d => d.category).sort((a, b) => b[1] - a[1])