2f:T962,# Climate Trace: Shipping Data ## Metadata 2.41GB .csv file `asset-shipping.csv` ```bash ❯ wc -l asset-shipping.csv 11643105 asset-shipping.csv ``` 11,643,105 lines in the .csv and 27 columns for each row. ```bash ❯ csvcut -n asset-shipping.csv 1: start_date 2: end_date 3: asset_identifier 4: asset_name 5: iso3_country 6: location 7: type 8: capacity 9: capacity_factor 10: activity 11: CO2_emissions_factor 12: CH4_emissions_factor 13: N2O_emissions_factor 14: other_gas_emissions_factor 15: CO2_emissions 16: CH4_emissions 17: N2O_emissions 18: other_gas_emissions 19: total_CO2e_100yrGWP 20: total_CO2e_20yrGWP 21: other1 22: other2 23: other3 24: other4 25: other5 26: other6 27: other7 ``` We can convert this to a sqlite database for further analysis with datasette. Because our data is so large, we need to create a `settings.json`[^1] that lets queries take longer and return more rows. ```sql select distinct asset_identifier from [asset-shipping] ``` Which returns 48,112 unique ships. ## Data Analysis ### Which ship has the most emissions? ```sql SELECT asset_name, asset_identifier, iso3_country, SUM(CO2_emissions) AS CO2_emissions FROM [asset-shipping] GROUP BY asset_identifier ORDER BY CO2_emissions DESC LIMIT 10; ``` **Name**: Orient **Identifier**: 9061617 **Country**: RUS **CO2_emissions**: 69420075.10475922 ### Where does that ship travel? First we grab all the data we have available for that unique_identifier: ```sql select rowid, start_date, end_date, asset_identifier, asset_name, iso3_country, location, type, capacity, capacity_factor, activity, CO2_emissions_factor, CH4_emissions_factor, N2O_emissions_factor, other_gas_emissions_factor, CO2_emissions, CH4_emissions, N2O_emissions, other_gas_emissions, total_CO2e_100yrGWP, total_CO2e_20yrGWP, other1, other2, other3, other4, other5, other6, other7 from [asset-shipping] where "asset_identifier" = 9061617 ``` Each row has a `location` that looks like `Point((132.011448333333 43.0765933333333))` which is a latitude and longitude. We will need to combine these points into lines to see a ship's path. # Ship History & Emissions Visualization So lets turn our data and our newly-created aggregations into visualizations we can look at. Our goal is to find some recommendations for how to integrate this shipping data into a global map of emissions. 30:T62c,bubbleMap = { const container = yield htl.html`

`; const map = L.map(container)//.setView([39.8283, -98.5795], 1); L.tileLayer("https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png", { attribution: "© OpenStreetMap contributors" }).addTo(map); const bounds = [[shipHistoryBbox[0], shipHistoryBbox[1]], [shipHistoryBbox[2], shipHistoryBbox[3] + 0.035]] map.fitBounds(bounds) // const assetTypeColors = d3.scaleOrdinal(d3.schemeCategory10); // top_polluter_locations.forEach((d) => { // // L.circle([d.longitude, d.latitude], { // L.circle([d.latitude, d.longitude], { // radius: +d.total_CO2e_20yrGWP * circleFactor, // stroke: false, // fillOpacity: 0.333, // fillColor: assetTypeColors(d.asset_type) // }).addTo(map).bindPopup(` // Type: ${d.asset_type} // Name: ${d.asset_name ? d.asset_name : "No name provided"}
CO2 20yr: ${ // d.total_CO2e_20yrGWP // ? d.total_CO2e_20yrGWP // : "No total_CO2e_20yrGWP provided" // }`); // }); shipPoints.forEach((d) => { L.circle(d.geometry.coordinates, { radius: 1000, fillColor: 'red', stroke: false }).addTo(map) }) var polyline = L.polyline(shipHistoryLine.geometry.coordinates, {color: 'red', weight: 2.5, opacity: 0.33 } ).addTo(map); return map; }31:T429,heatMap = { const container = yield htl.html`

`; const map = L.map(container)//.setView([39.8283, -98.5795], 1); // L.tileLayer("https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png", { // attribution: // "© OpenStreetMap contributors" // }).addTo(map); let StamenToner = L.tileLayer('https://stamen-tiles-{s}.a.ssl.fastly.net/toner-lite/{z}/{x}/{y}.{ext}', { attribution: 'Map tiles by Stamen Design, CC BY 3.0 — Map data © OpenStreetMap', subdomains: 'abcd', minZoom: 0, maxZoom: 20, ext: 'png' }); map.addLayer(StamenToner); const bounds = [[shipHistoryBbox[0], shipHistoryBbox[1]], [shipHistoryBbox[2], shipHistoryBbox[3] + 0.035]] map.fitBounds(bounds) let heatmapLayer = heatLayer(shipHeatPoints, { radius: heatRadius }).addTo(map) return map; }32:[{"id":"254a99fef9cfdc739794cf276a25ffce226b9d17d64789497bbed4935f1fda7d621149894d3a06e546f0e5c53beda2580db0166433ff2fac65397af1aa3627ea","href":"https://static.observableusercontent.com/files/254a99fef9cfdc739794cf276a25ffce226b9d17d64789497bbed4935f1fda7d621149894d3a06e546f0e5c53beda2580db0166433ff2fac65397af1aa3627ea","name":"countries-50m.json","type":"application/json","lastModified":1666057421452.051,"size":756420},{"id":"efc6d3dadd6041a178b99c6e6e4ae5a44efe5e4c7839b874a5aeb726fe76ad894724b1ee17b4067b18e2e8646bb4d5e54fb2aa5f7ba69c994b4eb3fc6214879b","href":"https://static.observableusercontent.com/files/efc6d3dadd6041a178b99c6e6e4ae5a44efe5e4c7839b874a5aeb726fe76ad894724b1ee17b4067b18e2e8646bb4d5e54fb2aa5f7ba69c994b4eb3fc6214879b","name":"asset-shipping-9061617.txt","type":"text/plain","lastModified":1666057575411.927,"size":48240},{"id":"5864c70e277091193b11a1bedd54ca72519c7e2738f9425b4600c13f46987625cee5f5e7be537a5a2afa3e319a436adbf5e409b8f6686f03da88ea6f3791bfa1","href":"https://static.observableusercontent.com/files/5864c70e277091193b11a1bedd54ca72519c7e2738f9425b4600c13f46987625cee5f5e7be537a5a2afa3e319a436adbf5e409b8f6686f03da88ea6f3791bfa1","name":"top100-co2-ships.csv","type":"text/csv","lastModified":1666196648326.427,"size":4419},{"id":"5f95fd632c24ae979f53e8d482355e9d662a53589a8d12cb0250e0215ecc4fd632f687d45a941f27f787ad8f88b018c0761ecd8780309a84213913803c4a585d","href":"https://static.observableusercontent.com/files/5f95fd632c24ae979f53e8d482355e9d662a53589a8d12cb0250e0215ecc4fd632f687d45a941f27f787ad8f88b018c0761ecd8780309a84213913803c4a585d","name":"asset-shipping-top-100-history.csv","type":"text/csv","lastModified":1666197038120.686,"size":3237544}] 2d:{"title":"Climate Trace: Shipping Data","cells":[{"id":319,"value":"$2f","pinned":false,"hidden":false,"mode":"md"},{"id":258,"value":"# Top 100 Ships by CO2 Emissions","pinned":false,"hidden":false,"mode":"md"},{"id":233,"value":"viewof top100Co2ShipsTable = Inputs.table(top100Co2Ships)","pinned":false,"hidden":false,"mode":"ojs"},{"id":264,"value":"---\n\n# Focus on a specific ship \n### Name: **${activeShipObj.asset_name}**\n### Country: ${activeShipObj.iso3_country}\n### Tons of CO2: ${co2NumberFormat(activeShipObj.CO2_emissions)}","pinned":false,"hidden":false,"mode":"md"},{"id":248,"value":"viewof activeShipId = Inputs.select(top100Co2Ships.map((d) => d.asset_identifier), {label: \"Select a ship identifier\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":150,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":335,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":353,"value":"viewof heatRadius = Inputs.range([1, 100], {label: \"Heatmap Aggregation Radius\", step: 1})","pinned":false,"hidden":false,"mode":"ojs"},{"id":340,"value":"shipHeatPoints = shipPoints.map((d) => {\n return [d.geometry.coordinates[0], d.geometry.coordinates[1], 18]\n })","pinned":false,"hidden":false,"mode":"ojs"},{"id":295,"value":"activeShipObj","pinned":false,"hidden":false,"mode":"ojs"},{"id":250,"value":"viewof processedDataTable = Inputs.table(processedData.map((d) => {\n return {lat: d.latLng.lat, lon: d.latLng.lon, ...d}\n}))","pinned":false,"hidden":false,"mode":"ojs"},{"id":266,"value":"activeShipObj = top100Co2Ships.filter((d) => d.asset_identifier == activeShipId)[0]","pinned":false,"hidden":false,"mode":"ojs"},{"id":365,"value":"## Animated","pinned":false,"hidden":false,"mode":"md"},{"id":369,"value":"viewof pointIndex = Inputs.range([2, shipPoints.length], {label: \"Time step\", step: 1, value: 2})","pinned":true,"hidden":false,"mode":"ojs"},{"id":362,"value":"animatedBubbleMap = {\n const container = yield htl.html`

`;\n const map = L.map(container)//.setView([39.8283, -98.5795], 1);\n L.tileLayer(\"https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png\", {\n attribution:\n \"© OpenStreetMap contributors\"\n }).addTo(map);\n\n const bounds = [[shipHistoryBbox[0], shipHistoryBbox[1]], [shipHistoryBbox[2], shipHistoryBbox[3] + 0.035]]\n\n map.fitBounds(bounds)\n shipPoints.slice(0,pointIndex).forEach((d) => {\n L.circle(d.geometry.coordinates, {\n radius: 1000,\n fillColor: 'red',\n stroke: false\n }).addTo(map)\n })\n\n var polyline = L.polyline(animatedShipHistoryLine.geometry.coordinates, \n {color: 'red', \n weight: 2.5,\n opacity: 0.33\n \n }\n ).addTo(map);\n\n return map;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":291,"value":"---\n\n## Appendix","pinned":false,"hidden":false,"mode":"md"},{"id":4,"value":"projection = d3.geoEqualEarth()","pinned":true,"hidden":false,"mode":"ojs"},{"id":131,"value":"dateExtent = [d3.min(processedData, (d) => d.startDate), d3.max(processedData, (d) => d.endDate)]","pinned":true,"hidden":false,"mode":"ojs"},{"id":135,"value":"possibleDays = d3.timeWeek.range(dateExtent)","pinned":true,"hidden":false,"mode":"ojs"},{"id":86,"value":"Many other projections are available. [Browse them here](https://observablehq.com/collection/@d3/d3-geo-projection).","pinned":false,"hidden":false,"mode":"md"},{"id":40,"value":"path = d3.geoPath(projection)","pinned":true,"hidden":false,"mode":"ojs"},{"id":25,"value":"height = {\n const [[x0, y0], [x1, y1]] = d3.geoPath(projection.fitWidth(width, outline)).bounds(outline);\n const dy = Math.ceil(y1 - y0), l = Math.min(Math.ceil(x1 - x0), dy);\n projection.scale(projection.scale() * (l - 1) / l).precision(0.2);\n return dy;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":117,"value":"assetShipping9061617 = FileAttachment(\"asset-shipping-9061617.txt\").csv({typed: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":304,"value":"co2NumberFormat = d3.format('.3s')","pinned":false,"hidden":false,"mode":"ojs"},{"id":121,"value":"processedData = assetShippingTop100History.filter((d) => {\n return d.asset_identifier === activeShipId\n})\n .map((d) => {\n const { asset_identifier, location, capacity, total_CO2e_100yrGWP, start_date, end_date } = d\n return {\n latLng: parsePoint(location),\n startDate: new Date(start_date),\n endDate: new Date(end_date),\n asset_identifier,\n capacity,\n total_CO2e_100yrGWP\n }\n})\n.sort((a,b) => {\n return a.startDate - b.startDate\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":159,"value":"shipPoints = processedData\n.filter((d) => {\n if(!d.latLng) return false\n if(!d.latLng.lat) return false\n if(!d.latLng.lon) return false\n return true\n })\n.map((d) => {\n const latLngArray = [d.latLng.lat, d.latLng.lon]\n return turf.point(latLngArray)\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":169,"value":"shipHistoryLine = turf.lineString(shipPoints.map((d) => {\n return d.geometry.coordinates\n}))","pinned":true,"hidden":false,"mode":"ojs"},{"id":372,"value":"animatedShipHistoryLine = turf.lineString(shipPoints.slice(0,pointIndex).map((d) => {\n return d.geometry.coordinates\n}))","pinned":true,"hidden":false,"mode":"ojs"},{"id":204,"value":"shipHistoryBbox = turf.bbox(shipHistoryLine)","pinned":true,"hidden":false,"mode":"ojs"},{"id":22,"value":"outline = ({type: \"Sphere\"})","pinned":true,"hidden":false,"mode":"ojs"},{"id":114,"value":"graticule = d3.geoGraticule10()","pinned":true,"hidden":false,"mode":"ojs"},{"id":16,"value":"land = topojson.feature(world, world.objects.land)","pinned":true,"hidden":false,"mode":"ojs"},{"id":105,"value":"borders = topojson.mesh(world, world.objects.countries, (a, b) => a !== b)","pinned":true,"hidden":false,"mode":"ojs"},{"id":13,"value":"world = FileAttachment(\"countries-50m.json\").json()","pinned":true,"hidden":false,"mode":"ojs"},{"id":139,"value":"function parsePoint(point) {\n var point = point.replace(/[()]/g, '');\n var point = point.split(' ');\n var lat = point[1];\n var lon = point[0];\n return {\n lat: +lat,\n lon: +lon.replace('Point', '')\n };\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":154,"value":"turf = require(\"@turf/turf\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":231,"value":"top100Co2Ships = FileAttachment(\"top100-co2-ships.csv\").csv()","pinned":true,"hidden":false,"mode":"ojs"},{"id":239,"value":"topShipList = top100Co2Ships.map((d) => d.asset_identifier).join(', ')","pinned":true,"hidden":false,"mode":"ojs"},{"id":241,"value":"assetShippingTop100History = FileAttachment(\"asset-shipping-top-100-history.csv\").csv()","pinned":true,"hidden":false,"mode":"ojs"},{"id":331,"value":"html``","pinned":true,"hidden":false,"mode":"ojs"},{"id":333,"value":"heatLayer = (L, require('leaflet.heat').catch(() => L.heatLayer))","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W32","stdlib":"1","titling":"auto"}