30:T65f,- Now that I've made something, I'll try to find Colorado-wide data. The first place I'll try is CDOT (Colorado Department of Transportation), they have a [data library](https://www.codot.gov/library). I flip through their very short list of data assets and find nothing obvious. I shoot an email to their info address explaining what I want. - I do some more clicking around and end up in their "Open Data" section, which they run through ArcGIS. I try some searches for "emergency," "call box," and "callbox" and finally [get a hit from their Signs database](https://data-cdot.opendata.arcgis.com/search?collection=Dataset&q=call%20box). - Their Signs database looks promising, it might be a little out of date (last update was June 2021) but we're going to ignore that and see what kind of attributes the data set has. I open up the map in another tab and while I wait for that to load, I click around the bar chart icons [next to each attribute in the table](https://data-cdot.opendata.arcgis.com/datasets/cdot::signs-2019/about). - The map, without any filters is madness. I need to apply some filters if I can find what I need in the data set. I try `SIGN_TYPE`, I try `SIGN_CATEGORY` and though there are categories for `EMERGENCY MANAGEMENT` and `EMERGENCY`, I'm not quite sure that these categories aren't going to contain emergency signs that don't have solar panels. At the end of the day, that's what I want. - Lo and behold, there's a category for `SOLAR_PANEL-PRESENT`! I go back to the map and filter the database for only signs with `YES` as a value for this attribute. There's 1,084 of them in the dataset. 31:T452,- Now we're cooking, just gotta figure out how to take this data out and get it in my notebook. Good news, [we can download](https://data-cdot.opendata.arcgis.com/datasets/cdot::signs-2019/about) it as a number of file types. I grab the CSV and take a quick look. Bad news is that the CSV is over 50MB, so that's a no-go in Observable. I'm going to have to do my filtering outside of the notebook first. - Numbers, by the way, doesn't have a method for filtering data and then [only exporting the filtered data as a CSV](https://discussions.apple.com/thread/7712113). You have to `filter > copy > paste` into a new doc. This gives me the rainbow beach ball of death for a while. In the meantime, I try and import the data into Google Sheets to see if I get anywhere faster. No go, I only get failures there. I bet I'm going to have to do this in R... - Alright, after crashing Numbers ~~three~~ five times, I've done the copy pasting in pieces. If I allowed myself more 200 rows, I risked crashing the program again. After about half an hour, I can save the file out as an CSV and load it into my notebook:32:[{"id":"bad3a7b8f61a97499e9fae5a94e8c305ce50e0ff8d69145ea0694f7895264b51000b9b5512c05ea0facbdd49b74b28c7466f8f6632601bf18e1cddf33a988926","href":"https://static.observableusercontent.com/files/bad3a7b8f61a97499e9fae5a94e8c305ce50e0ff8d69145ea0694f7895264b51000b9b5512c05ea0facbdd49b74b28c7466f8f6632601bf18e1cddf33a988926","name":"ColoradoCallBoxes - Sheet1.csv","type":"text/csv","lastModified":1653254265413.96,"size":2654},{"id":"dc98a1705a46c9d67bbf262bc68e2ccc4914e810541a320ce5758f4e1886480ec39bd56df91b33d6783d594309ccba4ff502bc27c90ad3a0cdf6db8b8fdbac04","href":"https://static.observableusercontent.com/files/dc98a1705a46c9d67bbf262bc68e2ccc4914e810541a320ce5758f4e1886480ec39bd56df91b33d6783d594309ccba4ff502bc27c90ad3a0cdf6db8b8fdbac04","name":"CO-08-colorado-countiesjson.json","type":"application/json","lastModified":1653305764631.652,"size":21704},{"id":"6c1e64d15d88891b55852eddd75b4742d06df8ec58e0ab128d9face127daad68eb03b8574a6c5026ff4e9bb160642cfdd587e6d9de250b074bdfde00726fee1f","href":"https://static.observableusercontent.com/files/6c1e64d15d88891b55852eddd75b4742d06df8ec58e0ab128d9face127daad68eb03b8574a6c5026ff4e9bb160642cfdd587e6d9de250b074bdfde00726fee1f","name":"Screen Shot 2022-05-23 at 6.44.02 AM.png","type":"image/png","lastModified":1653309858270.808,"size":4033341},{"id":"9b1bc305c180ff13c0d293b95639b13a65ac098a15d9e82bc41995232e9cc3370e071e3947381f78e8eed17d4e9e603a1e2372496ca07b82ee0ffc7fd25578a3","href":"https://static.observableusercontent.com/files/9b1bc305c180ff13c0d293b95639b13a65ac098a15d9e82bc41995232e9cc3370e071e3947381f78e8eed17d4e9e603a1e2372496ca07b82ee0ffc7fd25578a3","name":"coloradoSPSigns.csv","type":"text/csv","lastModified":1653312396422.895,"size":319588},{"id":"f9932af4984165f6e741a8ec7a15ebf9a0e6843b06b3ab1cc10b4494498b35e53205d85af945bf4e3b9723c5fe91dab05a1198295adab9a81d88cde53c21902a","href":"https://static.observableusercontent.com/files/f9932af4984165f6e741a8ec7a15ebf9a0e6843b06b3ab1cc10b4494498b35e53205d85af945bf4e3b9723c5fe91dab05a1198295adab9a81d88cde53c21902a","name":"solarpaneledSign.jpeg","type":"image/jpeg","lastModified":1653396046568.414,"size":400617}] 2e:{"title":"The last light when we're gone","cells":[{"id":659,"value":"# The last light when we're gone","pinned":false,"hidden":false,"mode":"md"},{"id":0,"value":" Using Plot.Carto","pinned":false,"hidden":false,"mode":"md"},{"id":1104,"value":"Plot.carto({\n marks: [\n // base the fitExtent on the first feature\n Plot.feature(topojson.feature(coloradoCounties, coloradoCounties.objects.cb_2015_colorado_county_20m), { \n fill: \"#222222\",\n }),\n // draw the surrounding states\n Plot.feature(topojson.feature(us, us.objects.states), {\n fill: \"#222222\",\n }),\n Plot.points(coloradoSPSigns, {\n lonLat: d => [d.LONGITUDE, d.LATITUDE],\n fill: \"#ffee9a\",\n fillOpacity: 0.9,\n r: 0.5\n })\n ],\n width,\n height: width * 0.4,\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":809,"value":"---","pinned":false,"hidden":false,"mode":"md"},{"id":1164,"value":"### Play by play","pinned":false,"hidden":false,"mode":"md"},{"id":814,"value":"- Based on [this question](https://www.quora.com/If-every-human-somehow-disappeared-off-the-face-of-the-Earth-how-long-would-it-be-before-the-last-artificial-light-source-went-out) as answered by [Randall Munroe](https://xkcd.com/about/) in his book [What If?](https://en.wikipedia.org/wiki/What_If%3F_(book). Call boxes, or any solar paneled sign, would be one of the last artificial light sources to go out after humans are extinct.","pinned":false,"hidden":false,"mode":"md"},{"id":1205,"value":"What does a solar paneled sign look like, by the way? Like this: ","pinned":false,"hidden":false,"mode":"md"},{"id":1207,"value":"solarpaneledsign = FileAttachment(\"solarpaneledSign.jpeg\").image()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1203,"value":"- Call box geo data isn't...easy to find. In fact, many call boxes are being removed because of the ubiquity of cell phones.\n- After a few unsuccessful Google searches for call box geo data via federal transportation data sources in the U.S., I tried narrowing down my search to my own county in Colorado. I found that data [here](https://larimer.maps.arcgis.com/apps/webappviewer/index.html?id=19814fc536284a14bc6d9f854ae26cb1) and hand-pulled the latitude and longitude from each point of an ArcGIS map. And then mapped it:","pinned":false,"hidden":false,"mode":"md"},{"id":670,"value":"Plot.carto({\n projection: \"albers\",\n marks: [\n Plot.feature(topojson.feature(coloradoCounties, coloradoCounties.objects.cb_2015_colorado_county_20m), { \n strokeWidth: 1 \n }),\n Plot.points(coloradoCallBoxes, {\n lonLat: d => [d.lon, d.lat],\n fill: \"#222222\",\n fillOpacity: 0.8,\n r: 1,\n })\n ],\n width,\n height: width * 0.5,\n // marginLeft: 0,\n // marginRight: 0,\n marginTop: 40,\n marginBottom: 40\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":825,"value":"$30","pinned":false,"hidden":false,"mode":"md"},{"id":835,"value":"screenShot20220523At64402Am = FileAttachment(\"Screen Shot 2022-05-23 at 6.44.02 AM.png\").image()","pinned":false,"hidden":false,"mode":"ojs"},{"id":837,"value":"$31","pinned":false,"hidden":false,"mode":"md"},{"id":856,"value":"coloradoSPSigns = FileAttachment(\"coloradoSPSigns.csv\").csv({typed: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":865,"value":"- From there I can just swap out my data from the map I already made above, and style it:","pinned":false,"hidden":false,"mode":"md"},{"id":858,"value":"Plot.carto({\n projection: \"albers\",\n marks: [\n // fill the counties\n // and base the fitExtent on the first feature\n Plot.feature(topojson.feature(coloradoCounties, coloradoCounties.objects.cb_2015_colorado_county_20m), { \n fill: \"#222222\",\n }),\n // draw the surrounding states\n Plot.feature(topojson.feature(us, us.objects.states), {\n strokeWidth: 1.5,\n fill: \"#222222\",\n stroke: \"#ffffff\",\n }),\n // draw the county strokes on top\n Plot.feature(topojson.feature(coloradoCounties, coloradoCounties.objects.cb_2015_colorado_county_20m), { \n strokeWidth: 0.1,\n stroke: \"#ffffff\",\n }),\n Plot.points(coloradoSPSigns, {\n lonLat: d => [d.LONGITUDE, d.LATITUDE],\n fill: \"#ffee9a\",\n fillOpacity: 0.4,\n r: 1.5\n })\n ],\n width,\n height: width * 0.5,\n marginTop: 25,\n marginBottom: 30,\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1135,"value":"\n Update: Since this little exploration, Ian Johnson built [a handy little CSV to Parquet converter](https://observablehq.com/@observablehq/parquet-that-csv) than will change your life if you want to downsize a CSV that's too big to import into Observable. It'll even help you move straight into querying your data in Observable's SQL cell. ","pinned":false,"hidden":false,"mode":"md"},{"id":641,"value":"---","pinned":false,"hidden":false,"mode":"md"},{"id":508,"value":"### Imports and Libraries","pinned":false,"hidden":false,"mode":"md"},{"id":504,"value":"projectionNames = [\n \"aitoff\",\n \"polyconic\",\n \"august\",\n \"baker\",\n \"boggs\",\n \"bromley\",\n \"collignon\",\n \"conicEqualArea\",\n \"craster\",\n \"eckert1\",\n \"eckert2\",\n \"eckert3\",\n \"eckert4\",\n \"eckert5\",\n \"eckert6\",\n \"eisenlohr\",\n \"equalEarth\",\n \"equirectangular\",\n \"fahey\",\n \"mtFlatPolarParabolic\",\n \"mtFlatPolarQuartic\",\n \"mtFlatPolarSinusoidal\",\n \"foucaut\",\n \"ginzburg5\",\n \"ginzburg6\",\n \"ginzburg8\",\n \"ginzburg9\",\n \"homolosine\",\n \"kavrayskiy7\",\n \"larrivee\",\n \"laskowski\",\n \"mercator\",\n \"miller\",\n \"mollweide\",\n \"naturalEarth1\",\n \"naturalEarth2\",\n \"nellHammer\",\n \"nicolosi\",\n \"patterson\",\n \"robinson\",\n \"sinusoidal\",\n \"sinuMollweide\",\n \"times\",\n \"vanDerGrinten\",\n \"vanDerGrinten2\",\n \"vanDerGrinten3\",\n \"vanDerGrinten4\",\n \"wagner4\",\n \"wagner6\",\n \"winkel3\"\n]","pinned":false,"hidden":false,"mode":"ojs"},{"id":509,"value":"import { us } from \"7fe9cd66e7ed57fb\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":511,"value":"import { Plot } from \"7fe9cd66e7ed57fb\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":531,"value":"topojson = require(\"topojson-client@3\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":778,"value":"coloradoCounties = FileAttachment(\"CO-08-colorado-countiesjson.json\").json()","pinned":false,"hidden":false,"mode":"ojs"},{"id":781,"value":"coloradoFeatures = topojson.feature(coloradoCounties, coloradoCounties.objects.cb_2015_colorado_county_20m)","pinned":false,"hidden":false,"mode":"ojs"},{"id":667,"value":"coloradoCallBoxes = FileAttachment(\"ColoradoCallBoxes - Sheet1.csv\").csv({typed: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":805,"value":"[Call box data ](https://larimer.maps.arcgis.com/apps/webappviewer/index.html?id=19814fc536284a14bc6d9f854ae26cb1)from LETA (Larimer Emergency Telephone Authority) for Larimer and surrounding counties. [Solar paneled sign data](https://data-cdot.opendata.arcgis.com/datasets/cdot::signs-2019/about) for Colorado from CDOT (Colorado Department of Transportation).","pinned":false,"hidden":false,"mode":"md"},{"id":1126,"value":"---","pinned":false,"hidden":false,"mode":"md"}],"files":"$W32","stdlib":"1","titling":"auto"}