31:T4a06,embed_target = { lspin; let container = DOM.element("div", { class: "mainmap", style: `width:${width}px;height:${width * 0.7}px;` }); yield container; let map = L.map(container, { tap: false }).setView([40, -75.12], 11); esriLeaflet .tiledMapLayer({ url: "https://tiles.arcgis.com/tiles/fLeGjb7u4uXqeF9q/arcgis/rest/services/CityBasemap/MapServer?f=jsapi&cacheKey=8c0da44bee92edf8", maxZoom: 18 }) .addTo(map); esriLeaflet .tiledMapLayer({ url: "https://tiles.arcgis.com/tiles/fLeGjb7u4uXqeF9q/arcgis/rest/services/CityBasemap_Labels/MapServer?f=jsapi&cacheKey=b5e156f30610659b", maxZoom: 18 }) .addTo(map); if (tile_mode != "off") { L.tileLayer .wms( "https://tiles.arcgis.com/tiles/fLeGjb7u4uXqeF9q/arcgis/rest/services/CityBasemap/MapServer", { // layers: 'nexrad-n0r-900913', // format: 'image/png', // transparent: true, // attribution: "Weather data © 2012 IEM Nexrad" } ) .addTo(map); // L.tileLayer("https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png", { // attribution: // "© OpenStreetMap contributors" // }).addTo(map); } let legendBar; let exitControl; let sliderControl; let tableControl; let vsites; let highlightedMarker; let places; let blocks = L.geoJson( aq_tracts_ranked.filter((r) => r.total_visits > 200).objects(), { color: "grey", fillopacity: 0.2, weight: 0.6 } ); let highlightblocks = L.geoJSON([]); let selectedlayer; let show_table; let default_vsite_distance_threshold = 0.015; let default_vsite_suggest_threshold = 10; let vsite_distance_threshold = default_vsite_distance_threshold; let vsite_suggest_threshold = default_vsite_suggest_threshold; let searchBar; let createLegendBar; let createSearchBar; let createSliderControl; let createExitControl; let createTableControl; let refreshVsites; let inSelectionMenu = false; let showOtherBlocks = true; let selectedblocks = L.geoJSON([]); const onEachBlock = (l) => { l.bindTooltip(l.feature.origin_geoid, { direction: "top" }); l.on("mouseover", (e) => { l.setStyle({ color: "black" }); }); l.on("mouseout", (e) => { l.setStyle({ color: "grey" }); }); l.on("click", (e) => { selectedblocks.addLayer( L.geoJson(l.feature, { origin_geoid: l.feature.origin_geoid, opacity: 0.7 }) ); selectedblocks.addTo(map); if (!showOtherBlocks) blocks.remove(); console.log(selectedblocks); refreshVsites(); if (!inSelectionMenu) { createSliderControl(); createExitControl(); searchBar.remove(); highlightblocks.remove(); inSelectionMenu = true; } }); }; createLegendBar = (data) => { L.Control.LegendBar = L.Control.extend({ onAdd: (map) => { let div = L.DomUtil.create("div"); div.className = "legend-bar"; let vsite = L.DomUtil.create("div"); let icons = Object.keys(vsite_color).map((i) => { let icon = L.DomUtil.create("div"); icon.className = "legend-icon"; icon.innerHTML = `

${i}

`; return icon; }); L.DomEvent.on(div, "mouseenter", () => { icons.forEach((i) => { i.lastChild.style.display = "flex"; }); }); L.DomEvent.on(div, "mouseleave", () => { icons.forEach((i) => { i.lastChild.style.display = "none"; }); }); icons.forEach((i) => { vsite.appendChild(i); }); div.appendChild(vsite); return div; }, onRemove: (map) => {} }); L.control.legendBar = (opts) => { return new L.Control.LegendBar(opts); }; legendBar = L.control.legendBar({ position: "topleft" }); legendBar.addTo(map); }; createSearchBar = (data) => { L.Control.SearchBar = L.Control.extend({ onAdd: (map) => { let div = L.DomUtil.create("div"); div.className = "search-box"; div.id = "searchbox"; let something = 0; div.innerHTML = ``; L.DomEvent.on(div, "keyup", () => { if (highlightblocks) highlightblocks.remove(); let in_searchbox_text = div.firstChild.value; let highlightblocks_table = aq_tracts_ranked .params({ in_searchbox_text }) .filter( (d) => op.indexof(d["origin_geoid"], in_searchbox_text) !== -1 && d.total_visits > 200 ) .objects(); highlightblocks = L.geoJSON(highlightblocks_table, { color: "red" }); in_searchbox_text.length > 0 ? highlightblocks.addTo(map) : highlightblocks.remove(); highlightblocks.eachLayer((l) => { onEachBlock(l); }); }); L.DomEvent.disableScrollPropagation(div); L.DomEvent.disableClickPropagation(div); return div; }, onRemove: (map) => {} }); L.control.searchBar = (opts) => { return new L.Control.SearchBar(opts); }; searchBar = L.control.searchBar({ position: "topright" }); searchBar.addTo(map); }; createTableControl = (data) => { let target_table = show_table; L.Control.TableControl = L.Control.extend({ onAdd: (map) => { let div = L.DomUtil.create("div"); div.className = "table-control"; let table = L.DomUtil.create("div"); table.className = "vsites-table"; let hidetablebtn = L.DomUtil.create("div"); hidetablebtn.className = "inner-btn"; let copytablebtn = L.DomUtil.create("div"); copytablebtn.className = "inner-btn"; let hideblocksbtn = L.DomUtil.create("div"); hideblocksbtn.className = "inner-btn"; let isTableHidden = true; table.style.maxHeight = 0; hidetablebtn.innerHTML = "Show Table"; copytablebtn.innerHTML = "Copy Table"; hideblocksbtn.innerHTML = "Hide CT Blocks"; L.DomEvent.on(hidetablebtn, "pointerdown", () => { if (isTableHidden) { table.style.maxHeight = "300px"; isTableHidden = false; hidetablebtn.innerHTML = "Hide Table"; } else { table.style.maxHeight = "0"; isTableHidden = true; hidetablebtn.innerHTML = "Show Table"; } }); L.DomEvent.on(copytablebtn, "pointerdown", () => { table.style.maxHeight = "300px"; let body = document.body; let range = document.createRange(); let selection = document.getSelection(); let target_elem = document.querySelector("#main-table"); selection.removeAllRanges(); try { range.selectNoteContents(target_elem); selection.addRange(range); } catch (e) { range.selectNode(target_elem); selection.addRange(range); } console.log(selection.toString()); document.execCommand("copy"); table.style.maxHeight = "0"; copytablebtn.innerHTML = "Table Copied!"; setTimeout(() => { copytablebtn.innerHTML = "Copy Table"; }, 1000); }); L.DomEvent.on(hideblocksbtn, "pointerdown", () => { if (showOtherBlocks) { blocks.remove(); hideblocksbtn.innerHTML = "Show CT Blocks"; showOtherBlocks = false; } else { blocks.addTo(map); hideblocksbtn.innerHTML = "Hide CT Blocks"; showOtherBlocks = true; } }); table.innerHTML = target_table .toHTML({ limit: 100, maxdigits: 2 }) .replaceAll("", ``) .replaceAll("", `

`) .replaceAll("

og", `

og`); let trs = Array.prototype.slice.call( table.getElementsByTagName("table")[0].rows ); trs.shift(); trs.forEach((a) => { let split = a.lastChild.innerHTML.split("_||_"); const getDisplayMarkers = () => { let target_marker, hover_marker; vsites.eachLayer((l) => { if (l.options.vsite.ObjectId == split[0]) { target_marker = l; hover_marker = L.marker(l._latlng, { zIndexOffset: 1000, icon: L.divIcon({ iconAnchor: [12, 12], className: "default", html: `

` }) }); } }); return [target_marker, hover_marker]; }; L.DomEvent.on(a, "mouseover", (e) => { highlightedMarker = getDisplayMarkers()[1]; highlightedMarker.addTo(map); }); L.DomEvent.on(a, "mouseout", (e) => { if (highlightedMarker) highlightedMarker.remove(); }); L.DomEvent.on(a, "click", (e) => { map.flyTo(getDisplayMarkers()[0]._latlng, 18); }); }); div.appendChild(hidetablebtn); div.appendChild(copytablebtn); div.appendChild(hideblocksbtn); div.appendChild(table); L.DomEvent.disableScrollPropagation(div); L.DomEvent.disableClickPropagation(div); return div; }, onRemove: (map) => {} }); L.control.tableControl = (opts) => { return new L.Control.TableControl(opts); }; tableControl = L.control.tableControl({ position: "bottomright" }); tableControl.addTo(map); }; refreshVsites = () => { map.spin(true); setTimeout(() => { let selectedgeoids = Object.values(selectedblocks._layers).map( (l) => l.options.origin_geoid ); if (vsites != undefined && map.hasLayer(vsites)) { vsites.remove(); places.remove(); tableControl.remove(); } let aq_target_places = aq .from( aq_tracts_ranked .filter(aq.escape((r) => selectedgeoids.includes(r.origin_geoid))) .rollup({ safegraph_place_infos: (d) => op.array_agg(d.safegraph_place_info) }) .get("safegraph_place_infos", 0) .flat() ) .groupby("safegraph_place_id") .rollup({ safegraph_place_id: (d) => op.any(d.safegraph_place_id), location_name: (d) => op.any(d.location_name), latitude: (d) => op.any(d.latitude), longitude: (d) => op.any(d.longitude), category: (d) => op.any(d.category), geoid_visits: (d) => op.sum(d.geoid_visits), median_dwell: (d) => op.mean(d.median_dwell), total_visits: (d) => op.any(d.total_visits) }); let aq_vsite_places_cross = aq .from(aq_target_places) .cross(vaccine_sites_aq) .derive({ distance: (r) => op.sqrt( op.pow(r.lat - r.latitude, 2) + op.pow(r.long - r.longitude, 2) ) }) .derive({ weighted_visits: (r) => r.geoid_visits / r.distance }) //.filter(r => r.weighted_visits > 15) // .select( // "safegraph_place_id", // 'Site_Name', // "ObjectId", // "weighted_visits" // ) .groupby("ObjectId") .rollup({ weighted_visits: (r) => op.sum(r.weighted_visits) }) .join(vaccine_sites_aq, "ObjectId") .orderby(aq.desc("weighted_visits")); let aq_vsite_places_cross_remove = aq_vsite_places_cross .cross(aq_vsite_places_cross) .derive({ vsites_distance: (r) => op.sqrt( op.pow(r.lat_1 - r.lat_2, 2) + op.pow(r.long_1 - r.long_2, 2) ) }) .params({ vsite_distance_threshold }) .filter( (r) => r.vsites_distance < vsite_distance_threshold && r.ObjectId_1 != r.ObjectId_2 ) .dedupe({ dupe: aq.escape((r) => [r.ObjectId_1, r.ObjectId_2].sort()) }) .derive({ Target_ObjectId: (r) => r.weighted_visits_1 > r.weighted_visits_2 ? r.ObjectId_2 : r.ObjectId_1 }); // .select( // 'ObjectId_1', // 'ObjectId_2', // 'vsites_distance', // 'Target_ObjectId' // ); let aq_vsite_processed = aq_vsite_places_cross.antijoin( aq_vsite_places_cross_remove, ["ObjectId", "Target_ObjectId"] ); let mean_weighted_visits = aq_vsite_processed .rollup({ mean: (r) => op.mean(r.weighted_visits) }) .get("mean", 0); let aq_vsite_final = aq_vsite_processed .filter(`r => r.weighted_visits > ${mean_weighted_visits / 3.0}`) .orderby(aq.desc("weighted_visits")) .derive({ rank: op.rank() }) .filter(`r => r.rank < ${vsite_suggest_threshold} + 1`); show_table = aq_vsite_final.select( "Site_Name", "Type", "Street_Address", "ZIP_Code", "ObjectId" ); vsites = L.layerGroup( aq_vsite_final.objects().map((v) => { let vsite_type = v.Type.trim(); return L.marker([v.lat, v.long], { vsite: v, //tooltipAnchor: [10, 50], icon: L.divIcon({ iconAnchor: [10, 10], iconSize: 20, html: `

`, className: "default" }) }).bindTooltip( html``, { direction: "top" } ); }) ); const mean_places_num = aq .from(aq_target_places) .rollup({ mean: (d) => op.mean(d["total_visits"]) }) .get("mean", 0); places = L.layerGroup( aq_target_places.objects().map((p) => { let opacity = logisticSize( 1, 0, p["total_visits"], mean_places_num, 4.0 / mean_places_num ); return L.circle([p.latitude, p.longitude], { opacity: opacity, color: "red" }); }) ); vsites.addTo(map); places.addTo(map); createTableControl(); map.spin(false); }, 1); }; createSliderControl = (data) => { L.Control.SliderControl = L.Control.extend({ onAdd: (map) => { let div = L.DomUtil.create("div"); div.className = "slider-control"; let suggestWrap = L.DomUtil.create("div"); let suggestInfo = L.DomUtil.create("div"); let suggestRange = L.DomUtil.create("div"); suggestWrap.className = "wrap"; suggestRange.innerHTML = ``; suggestWrap.innerHTML = `Max Sites Suggested: `; suggestInfo.innerHTML = suggestRange.firstChild.value; L.DomEvent.on(suggestRange, "input", () => { vsite_suggest_threshold = suggestRange.firstChild.value; suggestInfo.innerHTML = suggestRange.firstChild.value; }); suggestWrap.appendChild(suggestRange); suggestWrap.appendChild(suggestInfo); let disthresWrap = L.DomUtil.create("div"); let disthresInfo = L.DomUtil.create("div"); let disthresRange = L.DomUtil.create("div"); disthresWrap.className = "wrap"; disthresWrap.innerHTML = `VSites Spread: `; disthresRange.innerHTML = ``; disthresInfo.innerHTML = disthresRange.firstChild.value / 1000.0; L.DomEvent.on(disthresRange, "input", () => { vsite_distance_threshold = disthresRange.firstChild.value / 1000.0; disthresInfo.innerHTML = disthresRange.firstChild.value / 1000.0; }); disthresWrap.appendChild(disthresRange); disthresWrap.appendChild(disthresInfo); let confirmBtn = L.DomUtil.create("div"); confirmBtn.innerHTML = `Confirm`; confirmBtn.className = "inner-btn"; L.DomEvent.on(confirmBtn, "pointerdown", () => { if (map.hasLayer(selectedblocks)) { refreshVsites(); } }); div.appendChild(suggestWrap); div.appendChild(disthresWrap); div.appendChild(confirmBtn); L.DomEvent.disableScrollPropagation(div); L.DomEvent.disableClickPropagation(div); return div; }, onRemove: (map) => {} }); L.control.sliderControl = (opts) => { return new L.Control.SliderControl(opts); }; sliderControl = L.control.sliderControl({ position: "bottomright" }); sliderControl.addTo(map); }; createExitControl = (data) => { L.Control.ExitControl = L.Control.extend({ onAdd: (map) => { let div = L.DomUtil.create("div"); let exitBtn = L.DomUtil.create("div"); exitBtn.className = "toggle-btn exit-btn"; L.DomEvent.on(exitBtn, "pointerdown", () => { selectedblocks.remove(); selectedblocks = L.geoJSON([]); if (vsites) vsites.remove(); if (places) places.remove(); exitControl.remove(); sliderControl.remove(); if (tableControl) tableControl.remove(); blocks.addTo(map); createSearchBar(); inSelectionMenu = false; }); div.appendChild(exitBtn); L.DomEvent.disableScrollPropagation(div); L.DomEvent.disableClickPropagation(div); return div; }, onRemove: (map) => {} }); L.control.exitControl = (opts) => { return new L.Control.ExitControl(opts); }; exitControl = L.control.exitControl({ position: "topright" }); exitControl.addTo(map); }; blocks.eachLayer((l) => { onEachBlock(l); }); //createLegendBar(); createSearchBar(); blocks.addTo(map); }32:Td9a,html``33:T537,aq_tracts = aq_tracts_unroll .derive({ safegraph_place_info: d => { return { safegraph_place_id: d.safegraph_place_id, location_name: d.location_name, latitude: d.latitude, longitude: d.longitude, longitude: d.longitude, area: d.area_square_feet, category: d.top_category, geoid_visits: d.geoid_visits, median_dwell: d.median_dwell, total_visits: d.total_visits, total_sq_ft: d.place_total_sq_ft, p_vaccinated: d.percent_vaccinated_visits, p_nvaccinated: d.percent_not_vaccinated_visits, vaccinated: d.total_vaccinated_visits }; } }) .groupby("origin_geoid") .rollup({ type: op.any("type"), properties: op.any("properties"), geometry: op.any("geometry"), safegraph_place_info: op.array_agg("safegraph_place_info"), total_visits: op.sum("geoid_visits"), total_weighted_visits: op.sum("weighted_visits") }) .join(demographic_data_alt, ["origin_geoid", "GEOID"]) .join( clean_vaccination_data .rename({ census_tract_geoid: "origin_geoid" }) .select("origin_geoid", "vaccine_percent") ) .join(processed_under_18_table, "origin_geoid") .derive({ visits_vaccinated: d => d.total_visits * d.vaccine_percent }) .select(aq.not("GEOID"))34:Tc04,demographic_data_alt = demog_data .derive({ female: (d) => d[" female"], male: (d) => d[" male"], age_info: (d) => { return { "Under 20": op.parse_float(d[" <20"]), "20 to 34": op.parse_float(d[" 20-34"]), "35 to 54": op.parse_float(d[" 35-54"]), "55 to 75": op.parse_float(d[" 55-74"]), "Over 75": op.parse_float(d[" 75+"]) //"Over 65": op.parse_float(d[" 65+"]) }; }, ethnicity_info: (d) => { return { "American Indian": op.parse_float( d[" American Indian and Alaska Native alone"] ), Asian: op.parse_float(d[" Asian alone"]), Black: op.parse_float(d[" Black or African American alone"]), White: op.parse_float(d[" White alone"]), "Pacific Islander": op.parse_float( d[" Native Hawaiian and Other Pacific Islander alone"] ), Other: op.parse_float(d[" Some other race alone"]), "Two or More": op.parse_float(d[" Two or more races:"]) }; }, income_info: (d) => { return { "Under $10k": op.parse_float(d["Less than $10,000"]), "$10k to $15k": op.parse_float(d["$10,000 to $14,999"]), "$15k to $20k": op.parse_float(d["$15,000 to $19,999"]), "$20k to $25k": op.parse_float(d["$20,000 to $24,999"]), "$25k to $30k": op.parse_float(d["$25,000 to $29,999"]), "$30k to $35k": op.parse_float(d["$30,000 to $34,999"]), "$35k to $40k": op.parse_float(d["$35,000 to $39,999"]), "$40k to $45k": op.parse_float(d["$40,000 to $44,999"]), "$45k to $50k": op.parse_float(d["$45,000 to $49,999"]), "$50k to $60k": op.parse_float(d["$50,000 to $59,999"]), "$60k to $75k": op.parse_float(d["$60,000 to $74,999"]), "$75k to $100k": op.parse_float(d["$75,000 to $99,999"]), "$100k to $130k": op.parse_float(d["$100,000 to $124,999"]), "$130k to $150k": op.parse_float(d["$125,000 to $149,999"]), "$150k to $200k": op.parse_float(d["$150,000 to $199,999"]), "$200k or more": op.parse_float(d["$200,000 or more"]) }; }, // poverty_info: (d) => { // return { // Above: op.parse_float(d[" abovePovertyLevel"]), // Below: op.parse_float(d[" belewPovertyLevel"]) // }; // }, family_info: (d) => { return { "Have Child": op.parse_float(d[" own child under18"]), "No Child": op.parse_float(d[" no child under18"]) }; } // transportation_info: (d) => { // return { // "Car/Truck": op.parse_float(d[" Car truck or van"]), // "Public Transportation": op.parse_float(d[" Public transportation"]), // Walking: op.parse_float(d[" walked"]), // "Taxi/Bike/Other": op.parse_float( // d[" Taxicab motorcycle bicycle or other means"] // ) // }; // } }) .select( "GEOID", "female", "male", "age_info", "ethnicity_info", "income_info", //"poverty_info", "family_info" //"transportation_info" )35:[{"id":"1cd2dda84f7b1f6147a2000d16587e534177d0f7f3306c4b5bb9844a6a5729b0a198508431bc4db10d54b1a06721e747eedddbc4a0ad5af732dd21c65858b2b2","href":"https://static.observableusercontent.com/files/1cd2dda84f7b1f6147a2000d16587e534177d0f7f3306c4b5bb9844a6a5729b0a198508431bc4db10d54b1a06721e747eedddbc4a0ad5af732dd21c65858b2b2","name":"times-solid.png","type":"image/png","lastModified":1628114143493.326,"size":3603},{"id":"4b7abc566bf2e3804be16a86912c3861b47cd81941ecccef7f711bd82e271c3e71246f0d12874432e29bae0c9ecd877541a1392710648190ff3365668f8ed036","href":"https://static.observableusercontent.com/files/4b7abc566bf2e3804be16a86912c3861b47cd81941ecccef7f711bd82e271c3e71246f0d12874432e29bae0c9ecd877541a1392710648190ff3365668f8ed036","name":"Shot_0.png","type":"image/png","lastModified":1628095126738.247,"size":17372},{"id":"621d3de3e73f985a02641ff1b195fddbfe648bd7cb9704deada5a0b98aa202b6e8a6ebc704bd8aee62006a454394ed764dd9b9c1f12df263c6519bc0bd78d4dc","href":"https://static.observableusercontent.com/files/621d3de3e73f985a02641ff1b195fddbfe648bd7cb9704deada5a0b98aa202b6e8a6ebc704bd8aee62006a454394ed764dd9b9c1f12df263c6519bc0bd78d4dc","name":"ACSDP5Y2019.DP05_data_with_overlays_2021-06-28T173533.csv","type":"text/csv","lastModified":1627869820186.875,"size":866081},{"id":"449e620e7479e41a75d2150a5179d02ecc61aeb6e7cf7512770871c1493d67d9da92e5ad8175de33dba82799dbb58672b41e613a99b62a4f86b07d5a2d54ca9f","href":"https://static.observableusercontent.com/files/449e620e7479e41a75d2150a5179d02ecc61aeb6e7cf7512770871c1493d67d9da92e5ad8175de33dba82799dbb58672b41e613a99b62a4f86b07d5a2d54ca9f","name":"ACSDT5Y2019.B09001_data_with_overlays_2021-06-28T160411.csv","type":"text/csv","lastModified":1627869820186.875,"size":72655},{"id":"fb638891b1fe1acde7a23ef391f770835a67610a4ab95b7495c09a8c6f1f71717dd6d66f294325b3f3fd63ef6b40ec7e3e3b28cc6633e057b84bd803d88a6b61","href":"https://static.observableusercontent.com/files/fb638891b1fe1acde7a23ef391f770835a67610a4ab95b7495c09a8c6f1f71717dd6d66f294325b3f3fd63ef6b40ec7e3e3b28cc6633e057b84bd803d88a6b61","name":"Census_Block_Groups_2010.geojson","type":"application/geo+json","lastModified":1627869820186.875,"size":2482102},{"id":"db7e70eb2941ea9f69fd9fea947eadb638b9f622a7cbc0c399811f897f36c2088c8b1d45979c8402d181101a0c7b4b73f266d302dbdd424005662dc2a74611dc","href":"https://static.observableusercontent.com/files/db7e70eb2941ea9f69fd9fea947eadb638b9f622a7cbc0c399811f897f36c2088c8b1d45979c8402d181101a0c7b4b73f266d302dbdd424005662dc2a74611dc","name":"Census_Tracts_2010.geojson","type":"application/geo+json","lastModified":1627869820186.875,"size":1298632}] 2f:{"title":"Philly COVID-19 Optimal Vaccine Site Map Ver.","cells":[{"id":0,"value":"md`# Philly COVID-19 Optimal Vaccine Site Map Ver.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":10290,"value":"md`The Distance Threshold determines the spread of suggested vaccination sites. Lower thresholds prefer placing more vaccination sites at high-visited areas whereas higher thresholds prefer placing more sites at a diversity of areas.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":10273,"value":"$31","pinned":true,"hidden":false,"mode":"ojs"},{"id":10388,"value":"md`### Suggested Vaccination Sites`","pinned":false,"hidden":false,"mode":"ojs"},{"id":10446,"value":"viewof vsite_top_threshold = Inputs.range([1, 20], {\n value: 10,\n step: 1,\n label: \"# of Suggestions\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":10393,"value":"md`## Resources`","pinned":false,"hidden":false,"mode":"ojs"},{"id":10825,"value":"aq\n .from(\n aq_tracts_ranked\n .rollup({\n safegraph_place_infos: d => op.array_agg(d.safegraph_place_info)\n })\n .get('safegraph_place_infos', 0)\n .flat()\n )\n .groupby('safegraph_place_id')\n .rollup({\n safegraph_place_id: d => op.any(d.safegraph_place_id),\n location_name: d => op.any(d.location_name),\n latitude: d => op.any(d.latitude),\n longitude: d => op.any(d.longitude),\n category: d => op.any(d.category),\n geoid_visits: d => op.sum(d.geoid_visits),\n median_dwell: d => op.mean(d.median_dwell),\n total_visits: d => op.any(d.total_visits)\n })\n .view()\n//.dedupe('safegraph_place_id')","pinned":false,"hidden":false,"mode":"ojs"},{"id":10520,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":10656,"value":"vsite_color = ({\n pharmacy: \"rgb(237, 100, 100)\",\n hospital: \"rgb(100, 180, 237)\",\n pdph: \"rgb(60, 199, 190)\",\n clinic: \"rgb(237, 207, 100)\",\n community: \"rgb(100, 237, 132)\",\n other: \"rgb(219, 219, 219)\"\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":2498,"value":"md`## Settings`","pinned":false,"hidden":false,"mode":"ojs"},{"id":10405,"value":"viewof in_CT_color = Inputs.select(Object.keys(colorDictionary), {\n label: \"Census Tract Colors\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":10408,"value":"colorDictionary = ({\n \"HSI RdYlGn\": d3.interpolateHsl(\"rgb(52, 235, 159)\", \"rgb(235, 64, 52)\"),\n Plasma: d3.interpolatePlasma,\n \"Red Yellow Green\": d3.interpolateRdYlGn,\n Magma: d3.interpolateMagma,\n \"Cube Helix Default\": d3.interpolateCubehelixDefault\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":10410,"value":"colorFunction = colorDictionary[in_CT_color]","pinned":false,"hidden":false,"mode":"ojs"},{"id":3932,"value":"date = \"2021-06-14\"//\"2021-03-29\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":10275,"value":"tile_mode = 'light'","pinned":false,"hidden":false,"mode":"ojs"},{"id":10412,"value":"logisticSize = (max, min, x, c, k) =>\n (max - min) / (1 + Math.pow(Math.E, -k * (x - c))) + min","pinned":false,"hidden":false,"mode":"ojs"},{"id":9997,"value":"## Vaccine site data","pinned":false,"hidden":false,"mode":"md"},{"id":10875,"value":"vaccine_sites_aq.view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":10859,"value":"vaccine_sites_aq = aq\n .from(\n await d3.json(\n `https://data.cdc.gov/resource/5jp2-pgaw.json?loc_admin_city=Philadelphia`\n )\n )\n .rename({\n loc_admin_street1: \"Street_Address\",\n loc_name: \"Site_Name\",\n loc_admin_city: \"City\",\n loc_admin_state: \"State\",\n loc_admin_zip: \"ZIP_Code\",\n latitude: \"lat\",\n longitude: \"long\"\n })\n .derive({\n Type: d => \"Pharmacy\",\n ObjectId: d => op.row_number()\n })","pinned":false,"hidden":false,"mode":"ojs"},{"id":10862,"value":"vaccine_sites_aq_old.view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":9602,"value":"vaccine_sites_aq_old = aq.from(\n enrolled_Vaccination_Sites_PublicView.features.map((a) => {\n const b = a.properties;\n b.lat = a.geometry.coordinates[1];\n b.long = a.geometry.coordinates[0];\n return b;\n })\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":9149,"value":"enrolled_Vaccination_Sites_PublicView = d3.json(\n \"https://services.arcgis.com/fLeGjb7u4uXqeF9q/ArcGIS/rest/services/Enrolled_Vaccination_Sites_PublicView/FeatureServer/0/query?where=State%3D%27PA%27&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&resultType=none&distance=0.0&units=esriSRUnit_Meter&returnGeodetic=false&outFields=*&returnGeometry=true&featureEncoding=esriDefault&multipatchOption=xyFootprint&maxAllowableOffset=&geometryPrecision=&outSR=&datumTransformation=&applyVCSProjection=false&returnIdsOnly=false&returnUniqueIdsOnly=false&returnCountOnly=false&returnExtentOnly=false&returnQueryGeometry=false&returnDistinctValues=false&cacheHint=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&having=&resultOffset=&resultRecordCount=&returnZ=false&returnM=false&returnExceededLimitFeatures=true&quantizationParameters=&sqlFormat=none&f=pgeojson&token=\"\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":3320,"value":"md`## Tracts Data`","pinned":false,"hidden":false,"mode":"ojs"},{"id":8085,"value":"CT_options = aq_tracts_col\n .columnNames()\n .filter(name => !['origin_geoid'].includes(name))\n// .filter((name) => name.includes(\"_\"))\n// .map((name) => name.replace(\"_\", \" \"))","pinned":false,"hidden":false,"mode":"ojs"},{"id":9409,"value":"aq_tracts_means = aq_tracts_alt\n .params({ CT_means_collect })\n .rollup(CT_means_collect)","pinned":false,"hidden":false,"mode":"ojs"},{"id":9411,"value":"CT_means_collect = {\n let obj = {};\n CT_options.forEach(option => {\n obj[option] = `d => op.mean(d['${option}'])`;\n });\n return obj;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":9440,"value":"CT_rank_collect = {\n let obj = {};\n CT_options.forEach(option => {\n obj[option + `_rank`] = `d => op.rank()`;\n });\n return obj;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":9458,"value":"aq_tracts_ranked = {\n let table = aq_tracts_alt;\n CT_options.forEach(option => {\n let key = option + '_rank';\n let newCollect = {};\n newCollect[key] = `d => op.rank()`;\n table = table.orderby(`${option}`).derive(newCollect);\n });\n return table;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":8166,"value":"aq_tracts_target = aq_tracts_alt\n .orderby('# Unvaccinated')\n .derive(CT_rank_collect)","pinned":false,"hidden":false,"mode":"ojs"},{"id":6653,"value":"aq_tracts_unroll = ct_data\n .join(poi_data, \"safegraph_place_id\")\n .join(aq_tracts_raw, [\"origin_geoid\", \"geoid\"])\n .join(vaccination_ct_data, \"safegraph_place_id\")\n .select(aq.not(\"geoid\")) //, 'type', 'geometry'\n .rename({\n estimated_visits_1: \"geoid_visits\",\n estimated_visits_2: \"place_total_visits\",\n visits_sq_ft_1: \"geoid_sq_ft\",\n visits_sq_ft_2: \"place_total_sq_ft\"\n })\n .derive({\n geoid_latitude: (d) => op.parse_float(d.properties.INTPTLAT10),\n geoid_longitude: (d) => op.parse_float(d.properties.INTPTLON10)\n })\n .derive({\n geoid_place_distance: (d) =>\n op.sqrt(\n op.pow(d.latitude - d.geoid_latitude, 2) +\n op.pow(d.longitude - d.geoid_longitude, 2)\n )\n })\n .derive({\n weighted_visits: (d) => d.geoid_visits * d.geoid_place_distance\n })","pinned":false,"hidden":false,"mode":"ojs"},{"id":6668,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":7307,"value":"aq_tracts_alt = aq_tracts\n .derive({\n \"CT Number\": d => \"CT\" + d.properties.NAME10\n })\n .select(\n aq.not(\n \"age_info\",\n \"ethnicity_info\",\n \"income_info\",\n \"poverty_info\",\n \"family_info\",\n \"transportation_info\"\n )\n )\n .join(aq_tracts_col, 'origin_geoid')\n .relocate(\"CT Number\", { before: \"type\" })","pinned":false,"hidden":false,"mode":"ojs"},{"id":8228,"value":"aq_tracts_col = aq_tracts\n .derive({\n \"% Under 12\": (d) => d[\"% under 12\"],\n \"% Under 20\": (d) => d.age_info[\"Under 20\"],\n \"% Over 65\": (d) => d.age_info[\"Over 65\"],\n \"% Have Child\": (d) => d.family_info[\"Have Child\"],\n //\"% In Poverty\": (d) => d.poverty_info[\"Below\"],\n \"% Vaccinated\": (d) => d.vaccine_percent,\n \"# Under 12\": (d) => d[\"Total under 12\"],\n \"# Unvaccinated\": (d) => d.total_visits - d.visits_vaccinated\n })\n .select(aq.not(aq_tracts.columnNames().filter((e) => e !== \"origin_geoid\")))","pinned":false,"hidden":false,"mode":"ojs"},{"id":4297,"value":"aq_tracts_raw = aq\n .from((await FileAttachment(\"Census_Tracts_2010.geojson\").json()).features)\n .derive({\n geoid: x => x.properties.GEOID10\n })","pinned":false,"hidden":false,"mode":"ojs"},{"id":10454,"value":"aq_tracts_raw.view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":51,"value":"phillyCensusBlockGroups = await FileAttachment(\"Census_Block_Groups_2010.geojson\").json()","pinned":false,"hidden":false,"mode":"ojs"},{"id":3927,"value":"FIPS = 42101","pinned":false,"hidden":false,"mode":"ojs"},{"id":8850,"value":"md`## Places Data`","pinned":false,"hidden":false,"mode":"ojs"},{"id":8855,"value":"ct_data_weighted_collect = ct_data_weighted.derive({\n geoid_info: (d) => ({\n origin_geoid: d.origin_geoid,\n estimate_visits: d.estimated_visits,\n visits_sq_ft: d.visits_sq_ft\n })\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":8911,"value":"ct_data_weighted = ct_data\n .join(demographic_data_alt.rename({ GEOID: 'origin_geoid' }), 'origin_geoid')\n .join(vaccination_ct_data, 'safegraph_place_id')\n .join(processed_under_18_table, 'origin_geoid')\n .derive({\n under_20_visits: d => d.age_info['Under 20'] * d.estimated_visits,\n unvaccinated_visits: d =>\n d.percent_not_vaccinated_visits * d.estimated_visits,\n under_12_visits: d => d['% under 12'] * d.estimated_visits\n })\n .select(\n aq.not(\n demographic_data_alt\n .columnNames()\n .concat(\n vaccination_ct_data.columnNames(),\n processed_under_18_table.columnNames()\n )\n .filter(e => ['origin_geoid', 'safegraph_place_id'].indexOf(e) < 0)\n )\n )","pinned":false,"hidden":false,"mode":"ojs"},{"id":8865,"value":"aq_places = ct_data_weighted_collect\n //.filter(`p => p.total_visits > ${places_filter}`)\n .groupby(\"safegraph_place_id\")\n .rollup({\n origin_geoids_info: op.array_agg('geoid_info'),\n total_visits: op.sum('estimated_visits'),\n total_unvaccinated_visits: op.sum('unvaccinated_visits')\n })\n .join_left(poi_data, 'safegraph_place_id')","pinned":false,"hidden":false,"mode":"ojs"},{"id":4233,"value":"ct_data = aq.from(\n await d3.csv(`${base_URL}networks_${FIPS}/bipartite_CT_${date}.csv`)\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":4401,"value":"poi_data = aq.from(\n await d3.csv(`${base_URL}networks_${FIPS}/poi_summary_${date}.csv`)\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":8777,"value":"## Demographic Data","pinned":false,"hidden":false,"mode":"md"},{"id":6830,"value":"import { demographic_data } from \"@wattslab/covid-philly-census-tracts\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":10911,"value":"import { demog_data } from \"@wattslab/philadelphia-census-tract-data\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":8915,"value":"demog_data","pinned":false,"hidden":false,"mode":"ojs"},{"id":10904,"value":"demog_data.view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":6960,"value":"$34","pinned":false,"hidden":false,"mode":"ojs"},{"id":8657,"value":"processed_under_18_table = raw_under_18_table\n .rename(\n raw_under_18_table\n .filter((r) => r.geoid == \"id\")\n .select(aq.matches(\"B0900\"))\n .fold(aq.all())\n .filter((r) => op.includes(r.value, \"Estimate\"))\n .derive({\n value: (r) =>\n op.replace(r.value, \"Estimate!!Total:!!In households:!!\", \"\")\n })\n .filter((r) => !op.includes(r.value, \"Estimate\"))\n .pivot(\"key\", \"value\")\n .object()\n )\n .filter((r) => r.geoid != \"id\")\n .select(aq.not(\"NAME\", aq.matches(\"B09001\")))\n .join(raw_population_data)\n .derive({\n \"Total under 12\": (r) =>\n op.parse_int(r[\"Under 3 years\"]) +\n op.parse_int(r[\"3 and 4 years\"]) +\n op.parse_int(r[\"5 years\"]) +\n op.parse_int(r[\"6 to 8 years\"]) +\n op.parse_int(r[\"9 to 11 years\"])\n })\n .derive({ \"% under 12\": (r) => r[\"Total under 12\"] / r.total_population })\n .select(\"geoid\", \"Total under 12\", \"% under 12\")\n .rename({ geoid: \"origin_geoid\" })","pinned":false,"hidden":false,"mode":"ojs"},{"id":8625,"value":"raw_under_18_table = aq\n .from(\n await FileAttachment(\n \"ACSDT5Y2019.B09001_data_with_overlays_2021-06-28T160411.csv\"\n ).csv()\n )\n .rename({ GEO_ID: \"geoid\" })\n .derive({ geoid: (r) => op.replace(r.geoid, \"1400000US\", \"\") })","pinned":false,"hidden":false,"mode":"ojs"},{"id":8810,"value":"raw_population_data = aq\n .from(\n await FileAttachment(\n \"ACSDP5Y2019.DP05_data_with_overlays_2021-06-28T173533.csv\"\n ).csv()\n )\n .rename({ GEO_ID: \"geoid\", DP05_0001E: \"total_population\" })\n .filter((r) => r.geoid != \"id\")\n .derive({ geoid: (r) => op.replace(r.geoid, \"1400000US\", \"\") })\n .select(\"geoid\", \"total_population\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":7478,"value":"md`## Vaccine Data`","pinned":false,"hidden":false,"mode":"ojs"},{"id":7483,"value":"raw_vaccination_data = aq.from(\n (\n await d3.json(\n \"https://phl.carto.com/api/v2/sql?q=SELECT * FROM covid_vaccines_by_census_tract\"\n )\n ).rows\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":7479,"value":"clean_vaccination_data = raw_vaccination_data\n .select(\"census_tract_geoid\", \"dose1_percent_range\", \"etl_timestamp\")\n .derive({\n vaccine_ordinal: (r) =>\n op.indexof(\n [\n null,\n \"<20%\",\n \"20-29%\",\n \"30-39%\",\n \"40-49%\",\n \"50-59%\",\n \"60-69%\",\n \"70%+\"\n ],\n r.dose1_percent_range\n ),\n vaccine_percent: (r) =>\n op.recode(r.dose1_percent_range, {\n null: 0,\n \"<20%\": 0.1,\n \"20-29%\": 0.25,\n \"30-39%\": 0.35,\n \"40-49%\": 0.45,\n \"50-59%\": 0.55,\n \"60-69%\": 0.65,\n \"70%+\": 0.85\n }),\n etl_timestamp: (r) => op.parse_date(r.etl_timestamp)\n })","pinned":false,"hidden":false,"mode":"ojs"},{"id":7503,"value":"vaccination_ct_data = ct_data\n .join(\n clean_vaccination_data\n .rename({ census_tract_geoid: \"origin_geoid\" })\n .select(\"origin_geoid\", \"vaccine_percent\")\n )\n .join(\n demographic_data_alt\n .rename({ GEOID: \"origin_geoid\" })\n .derive({ under_20_percent: r => r.age_info[\"Under 20\"] })\n .select(\"origin_geoid\", \"under_20_percent\")\n )\n .derive({\n vaccine_weighted_visits: d => d.estimated_visits * d.vaccine_percent\n })\n .groupby(\"safegraph_place_id\")\n .rollup({\n percent_vaccinated_visits: r =>\n op.sum(r.vaccine_weighted_visits) / op.sum(r.estimated_visits),\n total_vaccinated_visits: r => op.sum(r.vaccine_weighted_visits),\n total_visits: r => op.sum(r.estimated_visits)\n })\n .derive({\n percent_not_vaccinated_visits: d => 1 - d.percent_vaccinated_visits\n })\n .ungroup()","pinned":false,"hidden":false,"mode":"ojs"},{"id":3323,"value":"md`## Libraries and utilities`","pinned":false,"hidden":false,"mode":"ojs"},{"id":10889,"value":"base_URL = \"https://upenn-seas-wattscovid19lab.s3.us-east-2.amazonaws.com/paco/stats/data/\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":3806,"value":"sortTimes = (a, b) => a.getTime() - b.getTime()","pinned":false,"hidden":false,"mode":"ojs"},{"id":15,"value":"d3 = require('d3@5', 'd3-array@2', 'd3-regression@1')","pinned":false,"hidden":false,"mode":"ojs"},{"id":9089,"value":"function ramp(color, n = 512) {\n const canvas = DOM.canvas(n, 1);\n const context = canvas.getContext(\"2d\");\n canvas.style.margin = \"0 -14px\";\n canvas.style.width = \"calc(100% + 28px)\";\n canvas.style.height = \"20px\";\n canvas.style.imageRendering = \"pixelated\";\n for (let i = 0; i < n; ++i) {\n context.fillStyle = color(i / (n - 1));\n context.fillRect(i, 0, 1, 1);\n }\n return canvas;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":890,"value":"import { vl } from '@vega/vega-lite-api'","pinned":false,"hidden":false,"mode":"ojs"},{"id":4039,"value":"import { aq, op } from '@uwdata/arquero'","pinned":false,"hidden":false,"mode":"ojs"},{"id":33,"value":"L = require('leaflet')","pinned":false,"hidden":false,"mode":"ojs"},{"id":10844,"value":"esriLeaflet = require(\"https://unpkg.com/esri-leaflet@2.5.3/dist/esri-leaflet.js\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":10522,"value":"lspin = require(\"leaflet-spin@1.1.2/leaflet.spin.js\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":10554,"value":"import { style as faStyle, fa } from \"@airbornemint/fontawesome\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":10561,"value":"function faCommon(kind) {\n return function(s) {\n return ``;\n };\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":10563,"value":"fas = faCommon(\"s\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":41,"value":"html``","pinned":false,"hidden":false,"mode":"ojs"},{"id":3725,"value":"scope = (value, min, max) =>\n value > min ? (max ? (value < max ? value : max) : value) : min","pinned":false,"hidden":false,"mode":"ojs"},{"id":3139,"value":"stats = require('simple-statistics')","pinned":false,"hidden":false,"mode":"ojs"},{"id":2682,"value":"import { summarize, sigFigs, set } from \"@markwhiting/stats\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":10437,"value":"import { text } from \"@jashkenas/inputs\"","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W35","stdlib":"1","titling":"auto"}