Month: March 2023

[This is a cross-post from my other blog.]

I decided to play around more with some queries on the OGF API database.

I extracted detailed user summary data for January and February of this year, and put each month in a separate table.

For January, these are the results, ordered from highest number of objects touched to lowest number. I think number of objects touched (nodes, ways, relations) is a more meaningful way to evaluate user activity than changeset counts, which are highly variable depending on how individual users use available tools to connect to the database (iD vs JOSM, etc).

January, 2023, in OGF

February, 2023, in OGF

Notably, the active user counts for these two months are almost identical, with 279 for January and 281 for February.

For those who are interested in the technical side, here is a sample of  the query I ran on the API database. It’s really quite simple once you get the syntax right.

select
users.display_name,
changesets.user_id,
count ( distinct objects.changeset_id ) as count_changesets,
sum ( objects.count_nodes ) as count_nodes,
sum ( objects.count_ways ) as count_ways,
sum ( objects.count_rels ) as count_rels,
sum ( objects.count_rels + objects.count_ways + objects.count_nodes ) as count_objects
from (
select
changeset_id,
count ( node_id ) as count_nodes,
0 as count_ways,
0 as count_rels
from nodes
where visible = 't' and timestamp >= '2023-02-01' and timestamp < '2023-02-28'
group by changeset_id
union select
changeset_id,
0 as count_nodes,
0 as count_ways,
count ( relation_id ) as count_rels
from relations
where visible = 't' and timestamp >= '2023-02-01' and timestamp < '2023-02-28'
group by changeset_id
union select
changeset_id,
0 as count_nodes,
count ( way_id ) as count_ways,
0 as count_rels
from ways
where visible = 't' and timestamp >= '2023-02-01' and timestamp < '2023-02-28'
group by changeset_id
) as objects
join changesets
on objects.changeset_id = changesets.id
join users
on changesets.user_id = users.id
group by
users.display_name,
changesets.user_id
order by
count_objects desc;