There has been a pie-chart circulating the internet of all the pokemon on e621. Things like these get outdated quickly, so I made this pie-chart that updates often! Sources to all images at the bottom.
Building this query is not too much of a pain. There have been a few changes to the pokemon_(species) tag over the years that have caused some problems. In essence, the query will: find all tags involved in the pokemon_(species) implication tree at a depth of 2; remove extra groups and merge different pokemon forms; finally count the number of posts for each tag grouping by pokemon name.
Because I felt it more natural, this chart counts different forms as the same kind. Thankfully, different forms of pokemon are tagged differently. This makes our work easier as all we need to do is transform names like mega_lucario to lucario. A side effect of this though is that images with a Charizard and a Mega Charizard will be counted twice while an image with two Charizards will only counted once. Which kind of skews the data but this is an edge case I do not think happens often (and I do not care about it).
But what about pokemon that do not have base-forms? (e.g. lycanroc or deerling). It seems that most of these base-form-less pokemon do have tags representing the group and these tags are in the same places that most other pokemon are. So it seemed simpler to include all things at this level and filter away actual groups. It mostly works alright, but may need updating to exclude more groups in the future.
with recursive pokemon_tree (parent_id, child_id, depth) as (
-- Everything that implies pokemon
select
0 as parent_id,
tag_id as child_id,
0 as depth
from tags
where name = 'pokemon_(species)'
--
union all
-- Everything that implies something that implies pokemon
select
current.child_id as parent_id,
deeper.implier_tag_id as child_id,
current.depth + 1 as depth
from pokemon_tree as current
inner join tag_implications as deeper on (current.child_id = deeper.implied_tag_id)
where status = 'active'
),
pokemon_leaves as (
select distinct
child_id as tag_id,
-- Lumps a few things pokemon types together
-- TODO add stuff for zygarde_10_forme and zygarde_complete_forme
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(name,
'mega_', ''),
'_(pokemon)', ''),
'gigantamax_', ''),
'galarian_', ''),
'alolan_', ''),
'hisuian_', ''),
'paldean_', ''),
'origin_forme_', ''),
'primal_', ''),
'_(pokémon_gold_beta)', ''),
'shadow_', '')
as name,
count_on_active_posts
from pokemon_tree
inner join tags on (child_id = tag_id)
and depth != 0 -- no 'pokemon_(species)' tag
and depth = 2 -- Most common depth for species groups
-- These are groups in the depth = 2 category (for some reason...)
and name not in (
'ancient_pokemon',
'future_pokemon',
'legendary_trio',
'legendary_duo',
'swords_of_justice',
'guardian_tapu',
'hisuian_form',
'alolan_form',
'galarian_form',
'paldean_form'
)
-- Used when you really want all the leaves. It seems that *most*
-- pokemon-groups (full pokemon or the group for those without a baseform)
-- reside at depth = 2. This could backfire, but I don't care.
-- where child_id not in (select parent_id from pokemon_tree)
--
-- Used to find all the forms at depth 3 that are not captured
-- at a depth of 2. Appears to mostly be regional pokemon.
-- and depth = 3 and child_id not in (select child_id from pokemon_tree as b where b.depth = 2)
)
select
-- Capitalize first letter of each pokemon
upper(substr(name, 1, 1)) || substr(name, 2) as key,
sum(count_on_active_posts) as count
from pokemon_leaves
group by name;import fs from 'fs';
import db from './../../_code/database.mjs';
import { file_here } from './../../_code/file.mjs'
import { PieChart } from 'd3-charts';
// In the future replace with this line
// import hand_picked_images from './hand_picked_images.json' assert {type: 'json'};
// Currently only experimental, but I want to import JSON instead.
const hand_picked_path = file_here(import.meta.url, './hand_picked_images.json');
const hand_picked_images = JSON.parse(fs.readFileSync(hand_picked_path, 'utf8'));
const pokemon_path = file_here(import.meta.url, './query.sql');
const pokemon_query = fs.readFileSync(pokemon_path, 'utf8');
const data = db.prepare(pokemon_query).all()
.map(e => ({
...e,
image: hand_picked_images[e.key]
}))
.sort((a, b) => b.count - a.count);
const output_path = file_here(import.meta.url, 'pokemon_popularity.svg');
new PieChart({
other: {
proportion_threshold: 0.005,
draw: 'center',
image: hand_picked_images.Other
},
rotate_labels: true,
title: {
text: `Pokemon species by post count - ${db.most_recent_date}`
},
inner_radius: 225
}).draw(data).save(output_path);
const table_path = file_here(import.meta.url, 'pokemon_table.generated.html');
const table = `
<link href="/static/default_style.css" rel="stylesheet" type="text/css">
<table>
<thead>
<tr><th>Pokemon</th><th>Post Count</th></tr>
</thead>
<tbody>
${data.map(e => `<tr><td>${e.key}</td><td>${e.count}</td></tr>`).join('\n')}
</tbody>
</table>`;
fs.writeFileSync(table_path, table, 'utf8');{
"Other": {
"href": "https://static1.e621.net/data/37/13/3713da293a66974e3d394b798dee6281.jpg",
"x": 1000,
"y": 1000,
"scale": 0.6
},
"Lucario": {
"href": "https://static1.e621.net/data/9d/f5/9df56ae57c66a20d91ed602da5e057c1.png",
"x": 2200,
"y": 600,
"scale": 0.5
},
"Arcanine": {
"href": "https://static1.e621.net/data/5e/f8/5ef85ecdb85f6bfebd917d14ab724672.png",
"x": 1900,
"y": 855,
"scale": 0.25,
"rotation": -60
},
"Typhlosion": {
"href": "https://static1.e621.net/data/5e/f8/5ef85ecdb85f6bfebd917d14ab724672.png",
"x": 1925,
"y": 1595,
"scale": 0.25,
"rotation": 180,
"flip": true
},
"Umbreon": {
"href": "https://static1.e621.net/data/96/16/9616d290955089c79d389aacd57ac8ae.png",
"x": 300,
"y": 340,
"scale": 0.5
},
"Charizard": {
"href": "https://static1.e621.net/data/d8/14/d814232ca04a642fd30e732b7f8e435d.png",
"x": 840,
"y": 550,
"scale": 0.3
}
}Generating the list of artists is not hard either. I could have included the artist in the JSON document, but I never intend to use art not on e621 for this. So instead I just lookup the artists based on the MD5 of each image used.
import fs from 'fs';
import db from './../../_code/database.mjs';
import { file_here } from './../../_code/file.mjs'
// Create a list of all the images used
const find_artist_from_md5 = db.prepare(`
select
name as artist,
post_id
from posts_metadata
inner join posts_tags using (post_id)
inner join tags using (tag_id)
where
current_md5 = ?
and category = '1'
and name != 'conditional_dnp';
`);
const hand_picked_path = file_here(import.meta.url, './hand_picked_images.json');
const hand_picked_images = JSON.parse(fs.readFileSync(hand_picked_path, 'utf8'));
const artist_list = Object.entries(hand_picked_images).map(([key, value]) => {
const md5 = value.href.split('/')[6].split('.')[0];
const results = find_artist_from_md5.all(md5);
const artists = results.map(e => e.artist).flat();
const post_id = results[0] ? results[0].post_id : null;
const attribution = `<a href="https://e621.net/posts/${post_id}">${key} - ${artists.join(' & ')}</a>`;
return attribution;
}).join('\n');
const output_path = file_here(import.meta.url, 'artist_list.generated.html');
fs.writeFileSync(output_path, artist_list);A table of all the pokemon can be found here. It’s not pretty, but it’s pretty functional.