2b:null 2c:["$","title",null,{"children":"School Shootings in the US | Shaun Lebron | Observable"}] 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:{"id":"6dc52f68a526342a","slug":"school-shootings-in-the-us","title":"School Shootings in the US","thumbnail":"b3edeb7c54300c0fecd1ab1c9c8b9af47493f731620b6be05989aa0b1bab4de5","access_level":"public","create_time":"$D2018-05-22T15:37:30.424Z","update_time":"$D2018-06-01T01:28:12.940Z","view_time":null,"first_public_time":"$D2018-05-23T10:02:38.134Z","fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"ed9847273198461c","login":"shaunlebron","name":"Shaun Lebron","avatar_url":"https://avatars-next.observableusercontent.com/681c504a781cacec69ea0df79eb5a6a890b26146edebc8d1ee3153812c528dc4","tier":"starter_2024"},"version":637,"publish_version":637,"role":null,"authored":false,"authors":[{"type":"individual","id":"22dcd5a87171b339","login":"shaunlebron","name":"Shaun Lebron","avatar_url":"https://avatars-next.observableusercontent.com/681c504a781cacec69ea0df79eb5a6a890b26146edebc8d1ee3153812c528dc4","tier":null}],"members":[{"type":"individual","id":"22dcd5a87171b339","login":"shaunlebron","name":"Shaun Lebron","avatar_url":"https://avatars-next.observableusercontent.com/681c504a781cacec69ea0df79eb5a6a890b26146edebc8d1ee3153812c528dc4","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":1,"collection":null,"type":"notebook","body":{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":463,"value":"minDate = new Date(1950, 0)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":545,"value":"md`_Dots scaled by recency (large dots happened within a year)._`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":240,"value":"svg`\n\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":604,"value":"tableStyles = html`\n\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":79,"value":"shootingsUrl = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vRNQ43uJ3X2lQMaQBpNWv6H6vmwiSejZTqbjOuCaSjtd-bx2K-0yxqjFHCL8IIdbGg5ELlfKrp7QXva/pub?output=csv'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":219,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":204,"value":"latlonsUrl = \"https://cdn.rawgit.com/shaunlebron/c216f239bb2ef13e773123993b974fa7/raw/90f1f1ab640cf39420d8b813d3da8379797a64fb/cities.json\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":145,"value":"latlons = d3.json(latlonsUrl)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":618,"value":"latlons[\"Noblesville, Indiana\"]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":224,"value":"md`## Map`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":229,"value":"mapWidth = 960","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":231,"value":"mapHeight = 600","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":233,"value":"mapPad = 32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":214,"value":"us = d3.json(\"https://unpkg.com/us-atlas@1/us/10m.json\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":244,"value":"nationTopo = topojson.feature(us, us.objects.nation)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":254,"value":"stateBordersTopo = topojson.mesh(us, us.objects.states, (a, b) => a !== b)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":326,"value":"// paths are not projected because the `us` topology data is pre-projected\npath = d3.geoPath()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":428,"value":"md`## Libs`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":242,"value":"topojson = require('topojson')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":5,"value":"d3 = require('d3', 'd3-geo-projection')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":416,"value":"import {slider} from \"@jashkenas/inputs\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W32","stdlib":"1","titling":"auto"}}
30:"$2f:body"