I Want to Ride My Bicycle (Stage 1)

This is part 1 of a write-up of my practice project: displaying all 2025 UCI road cycling races on a map.

I'm currently reinventing myself as a developer¹ and learning a bunch of new stuff: PostGIS, Python, Django, some AI shit ², etc. Since the best way to learn is by doing - and because I don't want to lose the joy I get from coding - I set out to find a suitable open-source project on Github.

There are plenty of interesting ones out there, but getting started always feels a bit unnatural. Jumping into a project isn't easy, and it's often unclear which issues are actually up for grabs or truly wanted.

I've never had any hobby projects to show off - free time is scarce with two little ones hopping and bopping around. But since the unemployed have all of the time in the world to kill, now seems like a good time to start.

Prologue: Introduction to the project

I'm just a farmer's son from Belgium³, which means I'm a huge cycling fan. I also happen to be a maps enthusiast, so why not combine these two passions into an IT project⁴? Let's display all the UCI road cycling races on a map!

Datasette seems like the perfect fit since it handles the heavy lifting of spinning up a website and even displaying items on a map. That means I can focus on getting the data ready: fetch it, clean it up, and normalize it.

I didn't end up using all technologies and tools I'm currently learning⁵ but I did put together an interesting list:

• sqitch - database migrations
• PostGIS - geospatial data
• SQLFluff - SQL formatting and linting
• pgtap - testing
• Datasette - data visualization

As you can see, these tools are all heavily focused on Postgres. I'll let the migthy, wise blue elephant be my guiding anchor on this adventure. On your marks! Get set! Go!

Stage 1. Fetch the data

Your data is only as good as its source. When it comes to cycling data, the most complete website is definitely ProCyclingStats. However, since the UCI⁶ is the official governing body of cycling, I wanted to try getting the data from the UCI website first.

For my first attempt, I had Claude generate a Python script⁷ that uses an XHR call from the calendar page to fetch all World Tour races. I decided to start with the World Tour as a sample set.

However, this API call was clearly designed specifically for their calendar page, and it left me unsatisfied for several reasons:

• It only returned upcoming races.
• The data structure made it a bit of a pain to extract what I needed.⁸
• Key race details were missing, though there was a link to a UCI race page with more information.

So there I was, digging through the details link and other API calls, trying to piece together the information, until I noticed the DOWNLOAD SEASON button to download the entire road race calendar as an XLS file. D'oh!

I first created a new database pro_cycling, initialized sqitch with sqitch init, and created a separate schema uci_road_raw for the source data. Then I defined the raw races table as simple as possible - all text fields:

CREATE TABLE uci_road_raw.races (
    date_from text,
    date_to text,
    name text,
    venue text,
    country text,
    category text,
    calendar text,
    class text,
    email text,
    website text
);

And after converting the XLS file to CSV, psql did the rest:

\copy uci_road_raw.races FROM '/home/johan-maes/source/uci-road-races-map/data/UCICompetitions_ROA_2025.csv' WITH (FORMAT csv, HEADER true);

Talk about a sprint to the finish line! A quick inspection of the data showed 711 road races in the 2025 season - covering all classes (World Tour, Pro, etc.) and categories (Men, Women, etc.). Interestingly, it also includes 16 races from the end of 2024.

That's it for part 1 of this trilogy in five parts⁹. Part 2 (aka Stage 2) will focus on creating the lookup tables to normalize the data.

Thanks for reading - and apologies for the footnotes overload¹⁰.

---

1. This is marketer speak for I quit my IT consulting job and now have some time to explore what I would like to do next. ↩

2. There's no escaping it. ↩

3. I'm not actually a farmer's son. The phrase is a reference to Yves Lampaert's legendary post-race interview after he won the prologue in the 2022 Tour de France. ↩

4. Because every problem can be solved with IT, even the ones that don't exist yet. ↩

5. I'm a data person at heart. If I can do something in the database (and it's not completely stupid), I will. ↩

6. Union Cycliste Internationale - it was founded in France. ↩

7. See how fast I'm learning Python? ↩

8. Even though the new PostgreSQL 17 JSON_TABLE feature proved to be very effective. ↩

9. Not my joke: https://www.goodreads.com/book/show/372299.The_Hitch_Hiker_s_Guide_to_the_Galaxy. ↩

10. Who reads footnotes anyway? ↩