Australian Public Toilets SQL Analytics

SQL analytics over the National Public Toilet Map: 14 queries run as a CI check, plus an interactive filterable map of all 25,563 toilets.

GitHub

Overview

An analysis of the Australian government’s National Public Toilet Map dataset: 25,563 rows and 47 columns.

Loading messy data

A Python loader brings the dataset into SQLite. It handles two problems in the raw data:

• It falls back across text encodings when one fails.
• It detects which columns are really true/false and stores them that way.

14 questions in SQL

The analysis answers 14 questions using CTEs, subqueries, HAVING and conditional counts. A small runner executes each statement in isolation and returns a failure code if any query breaks, so all 14 run as a CI check.

Findings

• The national accessibility rate is 59.7%, ranging from 49% to 62% by state.
• 4 records have coordinates in a different state from their label.

Interactive map

The project also includes a MapLibre map of all 25,563 toilets with:

• Combinable filters: wheelchair access, baby change, shower, open 24 hours
• Clustering that scales with the number of points
• A vector basemap that falls back to raster

Stack

Python · SQL · SQLite · Pandas · MapLibre