All examples By author By category About

kueda
Full window Github gist

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;