Teach spatial SQL with queries that draw a map
The Spatial SQL book teaches PostGIS with queries whose answer appears on a map, not only as rows. This one counts the cafés within 500 m of each station on Tokyo's Yamanote line, and the notes under it point to the lessons that explain each choice.
Cafés within 500 m of each Yamanote line station
Data: stations and cafés from OpenStreetMap
Method: join cafés to each of the 30 stations within 500 m, keeping stations that have none
-- Cafés within 500 m of each station; stations with none still appear
WITH names(n) AS (VALUES ('東京'),('神田'),('秋葉原'),('御徒町'),('上野'),('鶯谷'),('日暮里'),('西日暮里'),('田端'),('駒込'),
('巣鴨'),('大塚'),('池袋'),('目白'),('高田馬場'),('新大久保'),('新宿'),('代々木'),('原宿'),('渋谷'),
('恵比寿'),('目黒'),('五反田'),('大崎'),('品川'),('高輪ゲートウェイ'),('田町'),('浜松町'),('新橋'),('有楽町')),
stations AS (
SELECT p.name, ST_Centroid(ST_Collect(p.geom)) AS geom
FROM osm.public_transport_point AS p JOIN names ON p.name = names.n
WHERE p.osm_type = 'railway' AND p.osm_subtype = 'station'
AND p.geom && ST_MakeEnvelope(139.68, 35.60, 139.80, 35.74, 4326)
GROUP BY p.name
),
cafes AS (
SELECT osm_id, geom FROM osm.amenity_point
WHERE osm_type = 'cafe' AND geom && ST_MakeEnvelope(139.66, 35.58, 139.82, 35.76, 4326)
)
SELECT s.name AS station,
COUNT(c.osm_id) AS cafes_within_500m,
s.geom
FROM stations AS s
LEFT JOIN cafes AS c ON ST_DWithin(s.geom::geography, c.geom::geography, 500)
GROUP BY s.name, s.geom
ORDER BY cafes_within_500m DESC;Two lessons explain the choices here. The LEFT JOIN keeps stations with zero cafés (lesson 1). The geography cast makes 500 mean metres, not degrees (lesson 4). A station is the centre of its mapped platforms and entrances, which is why ST_Collect comes first. A café near two stations counts for both.
In class
Change 500, or swap 'cafe' for another amenity such as 'library', and run the query again to compare the two maps.
Frequently asked questions
How many lessons can I read without an account?
The first four are open on this site. The rest continue in the app.
Where do lesson queries run?
Where Your SQL Runs covers the connections and the guards on every query.