Postgres in depth · Chapter 61 of 65

Meetups near you

By the end of this chapter you can store places as points on the earth, measure real distances between them, find everything within a radius, and find the nearest things first, with an index. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Extensions and graduation
  • 4 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

LCO is starting offline meetups. Kabir is planning one in Hyderabad and wants to invite every student who lives close enough to come. Sana wants the meetup page to say how far each city is. LCO knows where its students live: students.city points at cities, and every city has a lat and a lon.

Those are two plain numbers, and the earth is round. One degree of longitude is 111 km at the equator and less further north, so you cannot subtract coordinates and call it a distance. Doing the spherical trigonometry by hand in every query is slow and easy to get wrong. PostGIS is the extension that makes Postgres a geographic database: it adds types for places and shapes, hundreds of ST_ functions, and indexes for them.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    city-locations.sql: a location for every city

    Add a column location to cities: a geography point in SRID 4326 built from the city's lon and lat, stored as a generated column so that Postgres keeps it in step with them. Then add a GiST index on it.

    Install postgis in the file if it is missing: the Check runs it in a fresh copy of LCO's database.

    Afterwards: every city's location is at its own longitude and latitude, it follows a change to lat or lon by itself, and cities has a GiST index on location.

  2. Task 2

    from-pune.sql: how far is every city from Pune

    For the Pune meetup page, list every other city with its distance from Pune along the earth's surface: the PostGIS distance between the two geography locations, in kilometres, rounded to 1 decimal place. The Check's copy has cities.location from task 1.

    Return two columns: city and km. Order by km, then city.

  3. Task 3

    hyderabad-meetup.sql: who can come to Hyderabad

    Kabir's meetup is in Hyderabad. For every city within 600 km of Hyderabad (Hyderabad itself included), count the students who live there and have not been deleted. The Check's copy has cities.location from task 1.

    Return two columns: city and students. A city within reach but with no students shows 0. Order by city.

  4. Task 4 · interview question

    Interview: nearest.sql, every city's nearest neighbour

    An interviewer asks for every city's nearest other city, found nearest first with the distance operator, and its distance as PostGIS measures it on the geography type, in kilometres rounded to 1 decimal place. The Check's copy has cities.location from task 1.

    Return three columns: city, nearest and km. Order by city.

What you will learn

  • Points: ST_MakePoint, the order of its arguments, and SRID 4326.
  • geometry and geography: flat coordinates versus the round earth.
  • ST_Distance and ST_DWithin: how far, and what is within a radius.
  • <-> and GiST: nearest first, and indexes that make both fast.

More in Extensions and graduation

Build "Meetups near you" yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.