example of running stats queries against the data store

[missinglink]

here's an example of running a SQL query to get statistical info from WOF admin records:

#!/bin/bash

sqlite3 'data/store.sqlite3' <<SQL
SELECT
  json_extract( json, '$.placetype' ) AS placetype,
  CAST( AVG( json_extract( json, '$.population' ) ) AS INT ) AS average_population
FROM docs
WHERE json_extract( json, '$.population' ) IS NOT NULL
GROUP BY placetype
ORDER BY average_population DESC;
SQL

running the command outputs average populations grouped by placetype:

continent|3812366000
disputed|234142442
country|33087442
region|3438324
borough|890580
dependency|522742
county|99639
locality|42603
localadmin|13400
macrohood|8744
neighbourhood|7927

[missinglink]

language coverage query:

#!/bin/bash

sqlite3 'data/store.sqlite3' <<SQL
SELECT e.key, COUNT(*) as total
FROM docs, json_each( json_extract( docs.json, '$.names' ) ) as e
GROUP BY e.key
ORDER BY total DESC
LIMIT 10;
SQL

eng|159741
nld|80673
spa|78875
fra|76680
ita|75671
por|71643
pol|64886
deu|64841
vol|52729
cat|51211

[missinglink]

list all records with 5 lineages:

#!/bin/bash

sqlite3 'data/store.sqlite3' <<SQL
SELECT DISTINCT(id) FROM (
  SELECT docs.id, 0 AS lineage, SUBSTR( key, 1, LENGTH( key ) -3 ), CAST( value AS INTEGER ) FROM docs, json_each( json_extract( docs.json, '$.lineage[0]' ) )
  UNION ALL
  SELECT docs.id, 1 AS lineage, SUBSTR( key, 1, LENGTH( key ) -3 ), CAST( value AS INTEGER ) FROM docs, json_each( json_extract( docs.json, '$.lineage[1]' ) )
  UNION ALL
  SELECT docs.id, 2 AS lineage, SUBSTR( key, 1, LENGTH( key ) -3 ), CAST( value AS INTEGER ) FROM docs, json_each( json_extract( docs.json, '$.lineage[2]' ) )
  UNION ALL
  SELECT docs.id, 3 AS lineage, SUBSTR( key, 1, LENGTH( key ) -3 ), CAST( value AS INTEGER ) FROM docs, json_each( json_extract( docs.json, '$.lineage[3]' ) )
  UNION ALL
  SELECT docs.id, 4 AS lineage, SUBSTR( key, 1, LENGTH( key ) -3 ), CAST( value AS INTEGER ) FROM docs, json_each( json_extract( docs.json, '$.lineage[4]' ) )
  UNION ALL
  SELECT docs.id, 5 AS lineage, SUBSTR( key, 1, LENGTH( key ) -3 ), CAST( value AS INTEGER ) FROM docs, json_each( json_extract( docs.json, '$.lineage[5]' ) )
)
WHERE lineage = 5
SQL