CNC2017 Species in Common

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;