Analyze events of a Gancio instance with DuckDB
| map | ||
| go.mod | ||
| go.sum | ||
| main.go | ||
| places.sql | ||
| README.md | ||
| schema.sql | ||
Gancio events as data
Analyze events of a Gancio instance with DuckDB.
Steps
create database:
$ duckdb gancio.db
create table events:
CREATE TABLE events (
id BIGINT,
title VARCHAR,
slug VARCHAR,
multidate BOOLEAN,
start_datetime TIMESTAMP,
end_datetime TIMESTAMP,
media STRUCT(
url VARCHAR,
height BIGINT,
width BIGINT,
"name" VARCHAR,
size BIGINT,
focalpoint DOUBLE[]
)[],
online_locations JSON,
updatedAt DATETIME,
apUserApId JSON,
tags VARCHAR[],
place STRUCT(
id BIGINT,
"name" VARCHAR,
address VARCHAR,
latitude DOUBLE,
longitude DOUBLE
),
ap_user JSON
);
load data from the API (the following examples uses https://balotta.org):
INSERT INTO events
SELECT
id,
title,
slug,
multidate,
to_timestamp(CAST(start_datetime AS BIGINT)) AS start_datetime,
to_timestamp(CAST(end_datetime AS BIGINT)) AS end_datetime,
media,
online_locations,
updatedAt,
apUserApId,
tags,
place,
ap_user
FROM read_json_auto('https://balotta.org/api/events?start=2010');
Example queries
top tags
WITH tag_counts AS (
SELECT
unnest AS tag,
COUNT(*) as tag_count
FROM events,
UNNEST(tags) AS unnest
GROUP BY unnest
),
total_events AS (
SELECT COUNT(DISTINCT id) as total FROM events
)
SELECT
tag,
tag_count,
ROUND(tag_count * 100.0 / (SELECT total FROM total_events), 2) AS percentage
FROM tag_counts
ORDER BY tag_count DESC
LIMIT 30;
┌───────────────────┬───────────┬────────────┐
│ tag │ tag_count │ percentage │
│ varchar │ int64 │ double │
├───────────────────┼───────────┼────────────┤
│ musica │ 529 │ 20.98 │
│ concerti │ 267 │ 10.59 │
│ libri │ 179 │ 7.1 │
│ presentazione │ 173 │ 6.86 │
│ diy │ 161 │ 6.39 │
│ laboratorio │ 129 │ 5.12 │
│ cena │ 125 │ 4.96 │
│ punk │ 118 │ 4.68 │
│ aperitivo │ 104 │ 4.13 │
│ femminismo │ 99 │ 3.93 │
│ incontro │ 80 │ 3.17 │
│ cinema │ 75 │ 2.98 │
│ benefit │ 71 │ 2.82 │
│ cena benefit │ 69 │ 2.74 │
│ Camere d'Aria │ 67 │ 2.66 │
│ dibattito │ 67 │ 2.66 │
│ Vag61 │ 66 │ 2.62 │
│ proiezione │ 65 │ 2.58 │
│ Ofpcina │ 63 │ 2.5 │
│ autogestione │ 63 │ 2.5 │
│ assemblea │ 63 │ 2.5 │
│ mercatino │ 62 │ 2.46 │
│ transfemminismo │ 62 │ 2.46 │
│ palestina │ 59 │ 2.34 │
│ autofinanziamento │ 59 │ 2.34 │
│ lotte ambientali │ 55 │ 2.18 │
│ solidarietà │ 54 │ 2.14 │
│ bolognina │ 53 │ 2.1 │
│ autoproduzioni │ 52 │ 2.06 │
│ concerto │ 51 │ 2.02 │
├───────────────────┴───────────┴────────────┤
│ 30 rows 3 columns │
└────────────────────────────────────────────┘
top places
SELECT
place.name AS place_name,
COUNT(*) AS event_count,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM events WHERE place IS NOT NULL), 2) AS percentage
FROM
events
WHERE
place IS NOT NULL
GROUP BY
place_name
ORDER BY
event_count DESC
LIMIT 30;
┌─────────────────────────────────────────────────┬─────────────┬────────────┐
│ place_name │ event_count │ percentage │
│ varchar │ int64 │ double │
├─────────────────────────────────────────────────┼─────────────┼────────────┤
│ Circolo Anarchico Berneri │ 182 │ 7.22 │
│ Camere d'Aria │ 145 │ 5.75 │
│ Vag61 │ 109 │ 4.32 │
│ ExCentrale │ 103 │ 4.09 │
│ Circolo Hex │ 92 │ 3.65 │
│ Libreria Modo Infoshop │ 86 │ 3.41 │
│ Parco don Bosco │ 56 │ 2.22 │
│ Circolo Arci Guernelli │ 55 │ 2.18 │
│ Centro delle donne │ 53 │ 2.1 │
│ Labàs │ 52 │ 2.06 │
│ Laboratorio Giù dall'Arca │ 48 │ 1.9 │
│ Il muro di via Agucchi │ 43 │ 1.71 │
│ CSA Brigata - Imola │ 42 │ 1.67 │
│ Circolo Arci San Lazzaro │ 42 │ 1.67 │
│ Centro Sociale della Pace │ 42 │ 1.67 │
│ La Casona - Nuova Casa del Popolo di Ponticelli │ 32 │ 1.27 │
│ Scuola popolare di Musica Ivan Illich │ 32 │ 1.27 │
│ Circolo DEV │ 29 │ 1.15 │
│ Spazio documentazione anarchico Il Tribolo │ 29 │ 1.15 │
│ Cassero LGBT Center │ 27 │ 1.07 │
│ Fondo Comini │ 27 │ 1.07 │
│ Freakout Club │ 25 │ 0.99 │
│ INFESTAZIONI, Via Stalingrado 31 │ 25 │ 0.99 │
│ Zamboni 38 │ 24 │ 0.95 │
│ Cinema Galliera │ 24 │ 0.95 │
│ Piazza Dell'Unità │ 23 │ 0.91 │
│ Piazza Galvani │ 22 │ 0.87 │
│ Mala Consilia occupata │ 21 │ 0.83 │
│ Piazza Nettuno │ 19 │ 0.75 │
│ Archivio Via Avesella │ 19 │ 0.75 │
├─────────────────────────────────────────────────┴─────────────┴────────────┤
│ 30 rows 3 columns │
└────────────────────────────────────────────────────────────────────────────┘
year distribution
WITH yearly_counts AS (
SELECT
EXTRACT(YEAR FROM start_datetime) AS year,
COUNT(*) AS event_count
FROM events
WHERE start_datetime IS NOT NULL
GROUP BY EXTRACT(YEAR FROM start_datetime)
ORDER BY year
),
total AS (
SELECT SUM(event_count) AS total_events
FROM yearly_counts
)
SELECT
year,
event_count,
ROUND(100.0 * event_count / total_events, 2) AS percentage
FROM yearly_counts, total
ORDER BY year;
┌───────┬─────────────┬────────────┐
│ year │ event_count │ percentage │
│ int64 │ int64 │ double │
├───────┼─────────────┼────────────┤
│ 2022 │ 129 │ 5.12 │
│ 2023 │ 1078 │ 42.76 │
│ 2024 │ 1004 │ 39.83 │
│ 2025 │ 310 │ 12.3 │
└───────┴─────────────┴────────────┘
map
Display the places of events on a map
Before proceeding, missing coordinates must be geolocalized (requires Go and a Google Maps API Key)
$ MAPS_API_KEY=xxxxxxx go run main.go
Next, execute the query and export the results to JSON format:
duckdb -json gancio.db < places.sql > map/places.json
Deploy the contents of the ./map directory using a web server.
