31:T47b,md`## Geocoding

Locations mapped with Google Maps [Geocoding API]. **TODO**, instead of manually updating a JSON file with new coordinates, geocoding can be done in the Google Sheet directly: 1. Create a geocoding function \`GEOCODE\` in Google Apps Script: [source](https://ctrlq.org/code/19992-google-maps-functions-for-google-script) 2. Create a formula in cell F2 that populates latlon coordinates: \`=ARRAYFORMULA(IF(AND(B:B<>"Location", B:B<>""), GEOCODE(B:B), ""))\` - [ARRAYFORMULA](https://www.labnol.org/internet/arrayformula-copy-formulas-in-entire-column/29711/) - applies a formula to multiple cells - [cell range syntax](https://productforums.google.com/forum/#!topic/docs/8w9TzS7JEQI) - B:B selects the entire B column 3. Use a location cache sheet if GEOCODE results aren't cached - [\`VLOOKUP\`](https://support.google.com/docs/answer/3093318) - [\`setValue\`](https://stackoverflow.com/questions/11334296/google-sheet-script-set-cell-value) [shootings]:https://en.wikipedia.org/wiki/List_of_school_shootings_in_the_United_States [Geocoding API]:https://developers.google.com/maps/documentation/geocoding `32:[] 2f:{"title":"School Shootings in the US","cells":[{"id":0,"value":"md`# School Shootings in the US\n\n_live data scraped from wikipedia: [List of school shootings in US][shootings]_\n\n[shootings]:https://en.wikipedia.org/wiki/List_of_school_shootings_in_the_United_States\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":463,"value":"minDate = new Date(1950, 0)","pinned":false,"hidden":false,"mode":"ojs"},{"id":349,"value":"viewof scrubDate = {\n const min = +minDate;\n const max = +shootings[shootings.length-1].date;\n const div = html`\n \n \n `;\n const input = div.querySelector('input');\n const output = div.querySelector('output');\n const setValue = value => {\n div.value = value;\n output.innerHTML = d3.timeFormat(\"%Y-%m-%d\")(new Date(value));\n };\n input.addEventListener(\"input\", () => setValue(input.valueAsNumber));\n setValue(input.valueAsNumber);\n return div;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":545,"value":"md`_Dots scaled by recency (large dots happened within a year)._`","pinned":false,"hidden":false,"mode":"ojs"},{"id":240,"value":"svg`\n\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":386,"value":"viewof minDeathFilter = slider({\n min: 1,\n max: 20,\n value: 7,\n step: 1,\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":503,"value":"{\n const r = 6;\n const reddot = ``;\n return md`_${minDeathFilter}+ deaths shown as ${reddot} above, and detailed in table below:_`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":392,"value":"{\n const rows = shootings.filter(d => d.deaths >= minDeathFilter && d.date >= minDate).reverse();\n const dateFmt = d3.timeFormat(\"%b %-d, %Y\");\n const mdRow = d => [dateFmt(d.date),d.location,d.deaths,d.injuries,descFmt(d.desc)].join(\"|\");\n return md`\n date | location | deaths | injuries | description\n------|----------|--------|----------|-------------\n${rows.map(mdRow).join(\"\\n\")}\n `;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":560,"value":"function descFmt(desc) {\n // try to highlight the title\n const a = desc.match(/(.*?massacre)(.*)/);\n const b = desc.match(/(.*?shootings?)(.*)/);\n const result = [a,b].reduce((acc,i) => {\n if (!i) return acc;\n i = i.slice(1);\n const valid = (5 < i[0].length && i[0].length < 80);\n const shorter = (!acc || i[0].length < acc[0].length);\n return valid && shorter ? i : acc;\n }, null);\n if (!result) return desc;\n const [title,rest] = result;\n return `${title}${rest}`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":604,"value":"tableStyles = html`\n\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":220,"value":"md`## Data\n

\nScraping tables from [a wikipedia article] with Google Sheets using [\\`IMPORTHTML\\`].\n\nThe sheet auto-updates with the latest numbers based on our query:\n\n[a wikipedia article]:https://en.wikipedia.org/wiki/List_of_school_shootings_in_the_United_States\n[Google Sheets]:http://sheets.google.com/\n[\\`IMPORTHTML\\`]:https://support.google.com/docs/answer/3093339\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":4,"value":"shootingsQuery = {\n // import all the tables\n // (from https://productforums.google.com/d/msg/docs/2eWQ10JGoZ4/g8z60CDsOAAJ)\n const url = \"https://en.wikipedia.org/wiki/List_of_school_shootings_in_the_United_States\";\n const cmd = i => `IMPORTHTML(\"${url}\", \"table\", ${i})`;\n const cmds = d3.range(1,21).map(cmd).join(\";\");\n const query = `={${cmds}}`;\n return html`

${query}`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":79,"value":"shootingsUrl = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vRNQ43uJ3X2lQMaQBpNWv6H6vmwiSejZTqbjOuCaSjtd-bx2K-0yxqjFHCL8IIdbGg5ELlfKrp7QXva/pub?output=csv'","pinned":false,"hidden":false,"mode":"ojs"},{"id":37,"value":"shootings = {\n  const normalizeDateStr = s => {\n    const bad = \"000000001867-04-02-0000\";\n    if (s.length > bad.length) s = s.slice(bad.length);\n    return s;\n  };\n  const parseDate = d3.timeParse(\"%B %-d, %Y\");\n  return d3.csv(shootingsUrl, d => {\n    if (d.Date !== \"Date\") { // redundant header rows\n      return {\n        date: parseDate(normalizeDateStr(d.Date)),\n        location: (d.Location.split(\"!\")[1] || d.Location),\n        deaths: parseInt(d.Deaths, 10),\n        injuries: parseInt(d.Injuries, 10),\n        desc: d.Description.replace(/\\n/g,\" \").replace(/\\[\\d+\\]/g,\"\"),\n      }\n    }\n  });\n}  ","pinned":false,"hidden":false,"mode":"ojs"},{"id":219,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":104,"value":"latlonLookup = async location => {\n  // We needed a `key=${key}` param for the initial lookup of hundreds of locations,\n  // but subsequent one-off lookups are fine without it.\n  const url = `https://maps.googleapis.com/maps/api/geocode/json?address=${encodeURIComponent(location)}`;\n  const data = await d3.json(url);\n  try {\n    return data.results[0].geometry.location;\n  } catch (e) {\n    console.error(data);\n    return null;\n  }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":132,"value":"// only used to fill in new locations not found in our cache when new shootings are reported\ncomputeLatLons = {\n  let i=0;\n  for (let {location} of shootings) {\n    if (latlons[location]) continue;\n    try {\n      const latlon = await latlonLookup(location);\n      if (latlon) {\n        latlons[location] = latlon;\n      }\n    } catch (e) {\n      console.error(e);\n    }\n  }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":204,"value":"latlonsUrl = \"https://cdn.rawgit.com/shaunlebron/c216f239bb2ef13e773123993b974fa7/raw/90f1f1ab640cf39420d8b813d3da8379797a64fb/cities.json\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":145,"value":"latlons = d3.json(latlonsUrl)","pinned":false,"hidden":false,"mode":"ojs"},{"id":618,"value":"latlons[\"Noblesville, Indiana\"]","pinned":false,"hidden":false,"mode":"ojs"},{"id":224,"value":"md`## Map`","pinned":false,"hidden":false,"mode":"ojs"},{"id":229,"value":"mapWidth = 960","pinned":false,"hidden":false,"mode":"ojs"},{"id":231,"value":"mapHeight = 600","pinned":false,"hidden":false,"mode":"ojs"},{"id":233,"value":"mapPad = 32","pinned":false,"hidden":false,"mode":"ojs"},{"id":214,"value":"us = d3.json(\"https://unpkg.com/us-atlas@1/us/10m.json\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":311,"value":"// `us` topology data is pre-projected using this projection\n// taken from: https://github.com/topojson/us-atlas/blob/v1.0.2/prepublish#L16\n// (we use this to project lat/lon annotations to the map)\nprojection = d3.geoAlbersUsa().scale(1280).translate([480,300])","pinned":false,"hidden":false,"mode":"ojs"},{"id":244,"value":"nationTopo = topojson.feature(us, us.objects.nation)","pinned":false,"hidden":false,"mode":"ojs"},{"id":254,"value":"stateBordersTopo = topojson.mesh(us, us.objects.states, (a, b) => a !== b)","pinned":false,"hidden":false,"mode":"ojs"},{"id":326,"value":"// paths are not projected because the `us` topology data is pre-projected\npath = d3.geoPath()","pinned":false,"hidden":false,"mode":"ojs"},{"id":428,"value":"md`## Libs`","pinned":false,"hidden":false,"mode":"ojs"},{"id":242,"value":"topojson = require('topojson')","pinned":false,"hidden":false,"mode":"ojs"},{"id":5,"value":"d3 = require('d3', 'd3-geo-projection')","pinned":false,"hidden":false,"mode":"ojs"},{"id":416,"value":"import {slider} from \"@jashkenas/inputs\"","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W32","stdlib":"1","titling":"auto"}