Skip to content

Instantly share code, notes, and snippets.

@kueda

kueda/.block

Last active January 12, 2020 03:40
Show Gist options
  • Save kueda/3a7a2dc58824ff636618adc07011cdb9 to your computer and use it in GitHub Desktop.
Save kueda/3a7a2dc58824ff636618adc07011cdb9 to your computer and use it in GitHub Desktop.
Download ZIP
CNC2017 Species in Common
Raw
.block
height: 800
Raw
readme.md

Species that City Nature Challenge 2017 participating cities had in common. Each bar on the rim represents a participating city, and each chord connecting them represents the number of species they had in common. This does not include higher-level taxa like families and genera

Colophon: this is a very crude adaption of Mike Bostock's European Debt adaptation with some stuff cribbed from this useful blog post. For my own reference, I exported pairwise data from the iNat db like this (probably a better way, but this worked, d3.chord is apparently smart enough to handle rows like A-B and B-A):

CREATE TABLE cnc_project_species AS SELECT DISTINCT
  po.project_id AS project_id,
  ta.taxon_id
FROM
  observations o
    JOIN taxon_ancestors ta ON ta.taxon_id = o.taxon_id
    JOIN taxa t ON t.id = ta.ancestor_taxon_id
    LEFT OUTER JOIN project_observations po ON po.observation_id = o.id
WHERE
  po.project_id IN (10931, 11013, 11053, 11126, 10768, 10769, 10752, 10764, 11047, 11110, 10788, 10695, 10945, 10917, 10763, 11042)
  AND t.rank = 'species';

SELECT
  replace(p1.title, 'City Nature Challenge 2017: ', '') AS city1,
  replace(p2.title, 'City Nature Challenge 2017: ', '') AS city2,
  num_species
FROM
  (
    SELECT
      c1.project_id AS project_id_1,
      c2.project_id AS project_id_2,
      count(*) AS num_species
    FROM
      cnc_project_species c1 JOIN cnc_project_species c2 ON c1.taxon_id = c2.taxon_id
    WHERE
      c1.project_id != c2.project_id
    GROUP BY c1.project_id, c2.project_id
    ORDER BY c1.project_id, c2.project_id
  ) pairwise_counts
  JOIN projects p1 ON p1.id = project_id_1
  JOIN projects p2 ON p2.id = project_id_2
ORDER BY num_species DESC;
Raw
cnc-common-species.csv
city1 city2 num_species
Austin Dallas/Fort Worth 1184
Dallas/Fort Worth Austin 1184
Austin Houston 1002
Houston Austin 1002
Houston Dallas/Fort Worth 930
Dallas/Fort Worth Houston 930
San Francisco Bay Area Los Angeles 716
Los Angeles San Francisco Bay Area 716
Triangle Area Washington, DC metro area 454
Washington, DC metro area Triangle Area 454
Houston Triangle Area 454
Triangle Area Houston 454
Triangle Area Dallas/Fort Worth 450
Dallas/Fort Worth Triangle Area 450
Triangle Area Austin 390
Austin Triangle Area 390
Los Angeles Dallas/Fort Worth 374
Dallas/Fort Worth Los Angeles 374
Los Angeles Austin 358
Austin Los Angeles 358
Los Angeles Houston 356
Houston Los Angeles 356
San Francisco Bay Area Dallas/Fort Worth 329
Dallas/Fort Worth San Francisco Bay Area 329
Dallas/Fort Worth Washington, DC metro area 307
Washington, DC metro area Dallas/Fort Worth 307
San Francisco Bay Area Houston 298
Houston San Francisco Bay Area 298
San Francisco Bay Area Austin 297
Austin San Francisco Bay Area 297
Houston Washington, DC metro area 295
Washington, DC metro area Houston 295
New York City Washington, DC metro area 293
Washington, DC metro area New York City 293
Washington, DC metro area Austin 264
Austin Washington, DC metro area 264
Chicago Washington, DC metro area 255
Washington, DC metro area Chicago 255
Washington, DC metro area Boston Area 245
Boston Area Washington, DC metro area 245
New York City Triangle Area 244
Triangle Area New York City 244
Boston Area New York City 221
New York City Boston Area 221
Triangle Area Chicago 209
Chicago Triangle Area 209
San Francisco Bay Area Seattle 208
Seattle San Francisco Bay Area 208
Chicago Dallas/Fort Worth 205
Dallas/Fort Worth Chicago 205
San Francisco Bay Area Washington, DC metro area 204
Washington, DC metro area San Francisco Bay Area 204
Houston Chicago 198
New York City Chicago 198
Chicago New York City 198
Chicago Houston 198
Triangle Area Boston Area 197
Boston Area Triangle Area 197
Triangle Area San Francisco Bay Area 196
San Francisco Bay Area Triangle Area 196
Houston New York City 187
New York City Houston 187
Boston Area Chicago 182
Chicago Boston Area 182
New York City Dallas/Fort Worth 181
Dallas/Fort Worth New York City 181
Chicago Austin 180
Austin Chicago 180
Los Angeles Triangle Area 178
Triangle Area Los Angeles 178
Dallas/Fort Worth Boston Area 172
Boston Area Dallas/Fort Worth 172
Triangle Area Nashville 169
Nashville Triangle Area 169
Miami Houston 167
Houston Miami 167
New York City San Francisco Bay Area 160
San Francisco Bay Area New York City 160
New York City Austin 160
Austin New York City 160
Washington, DC metro area Nashville 157
Nashville Washington, DC metro area 157
Boston Area San Francisco Bay Area 156
San Francisco Bay Area Boston Area 156
Washington, DC metro area Los Angeles 155
Los Angeles Washington, DC metro area 155
Boston Area Houston 153
Houston Boston Area 153
Chicago Minneapolis/St. Paul 152
Minneapolis/St. Paul Chicago 152
Austin Boston Area 152
Boston Area Austin 152
Houston Nashville 151
Nashville Houston 151
San Francisco Bay Area Chicago 147
Chicago San Francisco Bay Area 147
Nashville Dallas/Fort Worth 141
Dallas/Fort Worth Nashville 141
San Francisco Bay Area The Wasatch Front 136
The Wasatch Front San Francisco Bay Area 136
Los Angeles New York City 130
New York City Los Angeles 130
Miami Austin 128
Austin Miami 128
Washington, DC metro area Minneapolis/St. Paul 127
Los Angeles The Wasatch Front 127
The Wasatch Front Los Angeles 127
Minneapolis/St. Paul Washington, DC metro area 127
Austin Nashville 126
Nashville Austin 126
Los Angeles Boston Area 125
Boston Area Los Angeles 125
Los Angeles Chicago 119
Seattle Los Angeles 119
Los Angeles Seattle 119
Chicago Los Angeles 119
Dallas/Fort Worth Miami 116
Miami Dallas/Fort Worth 116
The Wasatch Front Dallas/Fort Worth 115
Dallas/Fort Worth The Wasatch Front 115
Nashville Chicago 108
Chicago Nashville 108
Boston Area Minneapolis/St. Paul 108
Minneapolis/St. Paul Boston Area 108
Nashville New York City 105
Washington, DC metro area The Wasatch Front 105
The Wasatch Front Washington, DC metro area 105
New York City Nashville 105
Austin The Wasatch Front 103
The Wasatch Front Austin 103
Seattle Washington, DC metro area 98
Washington, DC metro area Seattle 98
Boston Area Seattle 96
Seattle Boston Area 96
Triangle Area Minneapolis/St. Paul 95
Minneapolis/St. Paul Triangle Area 95
New York City The Wasatch Front 93
The Wasatch Front New York City 93
Minneapolis/St. Paul New York City 93
New York City Minneapolis/St. Paul 93
The Wasatch Front Chicago 92
Chicago The Wasatch Front 92
Minneapolis/St. Paul Dallas/Fort Worth 90
Houston The Wasatch Front 90
The Wasatch Front Houston 90
Dallas/Fort Worth Minneapolis/St. Paul 90
Dallas/Fort Worth Seattle 88
Seattle Dallas/Fort Worth 88
The Wasatch Front Boston Area 87
Boston Area The Wasatch Front 87
Nashville Boston Area 83
Boston Area Nashville 83
Triangle Area The Wasatch Front 83
The Wasatch Front Triangle Area 83
Minneapolis/St. Paul Houston 81
Houston Minneapolis/St. Paul 81
Chicago Seattle 79
Seattle Chicago 79
Minneapolis/St. Paul San Francisco Bay Area 78
San Francisco Bay Area Minneapolis/St. Paul 78
Seattle New York City 77
New York City Seattle 77
Minneapolis/St. Paul Austin 76
Austin Minneapolis/St. Paul 76
Seattle Houston 74
Houston Seattle 74
Triangle Area Seattle 74
Seattle Triangle Area 74
Seattle Austin 71
Austin Seattle 71
Nashville Los Angeles 67
Los Angeles Nashville 67
Seattle The Wasatch Front 67
The Wasatch Front Seattle 67
San Francisco Bay Area Nashville 65
Nashville San Francisco Bay Area 65
Miami Triangle Area 64
Triangle Area Miami 64
Miami Los Angeles 64
Los Angeles Miami 64
Los Angeles Minneapolis/St. Paul 58
Minneapolis/St. Paul Los Angeles 58
Miami San Francisco Bay Area 55
San Francisco Bay Area Miami 55
Miami Washington, DC metro area 53
Washington, DC metro area Miami 53
Minneapolis/St. Paul Seattle 52
Seattle Minneapolis/St. Paul 52
Minneapolis/St. Paul Nashville 49
Nashville Minneapolis/St. Paul 49
Minneapolis/St. Paul The Wasatch Front 47
The Wasatch Front Minneapolis/St. Paul 47
Chicago Miami 42
Miami Chicago 42
Miami New York City 41
New York City Miami 41
Twin Ports Boston Area 39
Boston Area Twin Ports 39
The Wasatch Front Nashville 38
Nashville The Wasatch Front 38
Miami Boston Area 37
Boston Area Miami 37
Nashville Seattle 36
Seattle Nashville 36
Twin Ports Minneapolis/St. Paul 32
Minneapolis/St. Paul Twin Ports 32
Chicago Twin Ports 29
Twin Ports Chicago 29
Washington, DC metro area Twin Ports 27
Twin Ports Washington, DC metro area 27
New York City Twin Ports 26
Twin Ports New York City 26
Miami Nashville 25
Nashville Miami 25
Miami The Wasatch Front 24
The Wasatch Front Miami 24
Twin Ports Triangle Area 21
Triangle Area Twin Ports 21
Miami Minneapolis/St. Paul 20
Minneapolis/St. Paul Miami 20
San Francisco Bay Area Twin Ports 19
Twin Ports San Francisco Bay Area 19
Miami Seattle 18
Seattle Miami 18
Twin Ports Seattle 16
Seattle Twin Ports 16
Dallas/Fort Worth Twin Ports 14
Twin Ports Dallas/Fort Worth 14
Twin Ports The Wasatch Front 14
The Wasatch Front Twin Ports 14
Twin Ports Houston 13
Houston Twin Ports 13
Los Angeles Twin Ports 13
Twin Ports Los Angeles 13
Twin Ports Nashville 12
Nashville Twin Ports 12
Austin Twin Ports 11
Twin Ports Austin 11
Miami Twin Ports 3
Twin Ports Miami 3
Raw
index.html
<!DOCTYPE html>
<meta charset="utf-8">
<head>
<link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<style>
#vis { margin: 0 auto; width: 800px; }
.group text {
font: 9px sans-serif;
pointer-events: none;
}
.group path {
stroke: #000;
}
path.chord {
stroke-width: .75;
fill-opacity: .75;
}
#circle:hover path.fade {
display: none;
}
</style>
<body>
<div id="vis">
</div>
<script src="//d3js.org/d3.v3.min.js"></script>
<script>
var width = 800,
height = 800,
outerRadius = Math.min(width, height) / 2 - 4,
innerRadius = outerRadius - 20,
fill = d3.scale.category20();
var format = d3.format(",.3r");
// Square matrices, asynchronously loaded; credits is the transpose of commonSpeciesMatrix.
var commonSpeciesMatrix = [];
// The chord layout, for computing the angles of chords and groups.
var layout = d3.layout.chord()
.sortGroups(d3.descending)
.sortSubgroups(d3.descending)
.sortChords(d3.descending)
.padding(.04);
// The arc generator, for the groups.
var arc = d3.svg.arc()
.innerRadius(innerRadius)
.outerRadius(outerRadius);
// The chord generator (quadratic Bézier), for the chords.
var chord = d3.svg.chord()
.radius(innerRadius);
// Load our data file…
d3.csv("cnc-common-species.csv", type, function(error, data) {
if (error) throw error;
// Add an SVG element for each diagram, and translate the origin to the center.
var svg = d3.select("#vis").selectAll("div.vis-container")
.data([commonSpeciesMatrix])
.enter().append("div")
.style("display", "inline-block")
.style("width", width + "px")
.style("height", height + "px")
.append("svg")
.attr("width", width)
.attr("height", height)
.append("g")
.attr("id", "circle")
.attr("transform", "translate(" + width / 2 + "," + height / 2 + ")");
function mouseover( d, i ) {
chordPaths.classed("fade", function(p) {
return p.source.index != i
&& p.target.index != i;
});
chordPaths
.style("fill", d2 => fill( d2.source.index === i ? d2.target.index : d2.source.index ) )
}
var countryByName = d3.map(),
countryIndex = -1,
countryByIndex = [];
// Compute a unique index for each country.
data.forEach(function(d) {
if (countryByName.has(d.city1)) d.city1 = countryByName.get(d.city1);
else countryByName.set(d.city1, d.city1 = {name: d.city1, index: ++countryIndex});
if (countryByName.has(d.city2)) d.city2 = countryByName.get(d.city2);
else countryByName.set(d.city2, d.city2 = {name: d.city2, index: ++countryIndex});
d.city2.risk = 1; //d.risk;
});
// Initialize a square matrix of commonSpeciesMatrix and credits.
for (var i = 0; i <= countryIndex; i++) {
commonSpeciesMatrix[i] = [];
// credits[i] = [];
for (var j = 0; j <= countryIndex; j++) {
commonSpeciesMatrix[i][j] = 0;
// credits[i][j] = 0;
}
}
// Populate the matrices, and stash a map from index to country.
data.forEach(function(d) {
commonSpeciesMatrix[d.city1.index][d.city2.index] = d;
// credits[d.city2.index][d.city1.index] = d;
countryByIndex[d.city1.index] = d.city1;
countryByIndex[d.city2.index] = d.city2;
});
// For each diagram…
// svg.each(function(matrix, j) {
// var svg = d3.select(this);
// Compute the chord layout.
layout.matrix(commonSpeciesMatrix);
// Add chords.
var chordPaths = svg.selectAll(".chord")
.data(layout.chords)
.enter().append("path")
.attr("class", "chord")
.style("fill", function(d) { return "#ccc"; })
.style("stroke", function(d) { return "#aaa"; })
.attr("d", chord)
chordPaths
.append("title")
.text( d => `${d.source.value.city2.name} shared ${d.source.value.num_species} species with ${d.source.value.city1.name}` );
// .text(function(d) { return d.source.value.city2.name + " owes " + d.source.value.city1.name + " $" + format(d.source.value) + "B."; });
// Add groups.
var g = svg.selectAll(".group")
.data(layout.groups)
.enter().append("g")
.attr("class", "group");
// Add the group arc.
g.append("path")
.style("fill", function(d) { return fill(d.index); })
.attr("id", function(d, i) { return "group" + d.index + "-" + j; })
.attr("d", arc)
.on("mouseover", mouseover)
.on("mouseover", mouseover)
.append("title")
// .text(function(d) { return countryByIndex[d.index].name + " " + (j ? "owes" : "is owed") + " $" + format(d.value) + "B."; });
.text( d => countryByIndex[d.index].name )
// Add the group label (but only for large groups, where it will fit).
// An alternative labeling mechanism would be nice for the small groups.
g.append("text")
.attr("x", 6)
.attr("dy", 15)
.filter(function(d) { return d.value > 110; })
.append("textPath")
.attr("xlink:href", function(d) { return "#group" + d.index + "-" + j; })
.text(function(d) { return countryByIndex[d.index].name; });
// });
});
function type(d) {
d.city1 = d.city1.replace( /Minneapolis/, 'Minn.' );
d.city2 = d.city2.replace( /Minneapolis/, 'Minn.' );
d.city1 = d.city1.replace( /Twin Ports/, 'TP' );
d.city2 = d.city2.replace( /Twin Ports/, 'TP' );
d.city1 = d.city1.replace( /The Wasatch Front/, 'Wasatch' );
d.city2 = d.city2.replace( /The Wasatch Front/, 'Wasatch' );
d.num_species = +d.num_species;
d.risk = 1;
d.valueOf = value; // for chord layout
return d;
}
function value() {
return this.num_species;
}
</script>
</body>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment