---
path: industries/education
title: Education
desc: Teach spatial SQL with queries whose answer appears on a map.
date: Oct 21, 2026
---

# 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

```sql
-- 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](/docs/spatial-sql-book/01-prerequisites)). The geography cast makes `500` mean metres, not degrees ([lesson 4](/docs/spatial-sql-book/04-srid-degrees-metres)). 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.

```map
yamanote-line-cafes
```

## 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\.

[Where Your SQL Runs](</docs/spatial-sql-book/02-where-your-sql-runs>)


## Next step

- [Start the Spatial SQL book](/docs/spatial-sql-book)
- [Open the map in the gallery](/gallery?industry=education)
- [Book a demo](/demo)