Postgres with Drizzle · Chapter 32 of 40
The map
By the end of this chapter you can store places as points on the Earth and ask Postgres which ones are near, how far apart they are, and which ones are inside the part of the map on screen. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Performance & Superpowers
- 5 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.
Why you need this
Meera wants a "Near me" tab: places and events close to a member, and a map that shows pins for whatever area the member has dragged into view.
You could store latitude and longitude as plain numbers, fetch every place and compute distances in JavaScript. But the Earth is round: a degree of longitude is about 111 km at the equator and nothing at the poles, so "subtract and square" is wrong, and fetching every place to keep five does not scale. PostGIS, the spatial extension the lab has installed, gives Postgres a point type and functions that measure on the real planet.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
insertLocation: store a place as a point
Write insertLocation(name, lng, lat, category). name and category are strings (category is one of city, landmark, venue, event, other); lng and lat are numbers. It inserts one row into locations with that name and category, and with coordinates set to a point at that longitude and latitude in SRID 4326 (GPS).
Return the inserted row as one object, not an array, with id, name, category and coordinates_text: the point as text, as ST_AsText writes it. Tasks 2 and 5 add their places by calling this function.
- Task 2
findLocationsWithinRadius: places within a distance
Write findLocationsWithinRadius(lng, lat, radiusMeters). All three are numbers. It finds the locations whose coordinates are at most radiusMeters metres from the point at that longitude and latitude, measured on the Earth's surface.
Return result.rows: objects with id, name, category and distance_meters (the distance from the point, in metres). Order: nearest first.
- Task 3
calculateDistance: metres between two points
Write calculateDistance(lng1, lat1, lng2, lat2). All four are numbers: two points, each longitude first. It asks Postgres for the distance between them in metres, measured on the Earth's surface.
Return an object with exactly one key, distanceMeters: a number, not a string.
- Task 4
createSpatialIndex: a GiST index on the points
Write createSpatialIndex(). It creates an index named idx_locations_coordinates on the coordinates column of locations, of the GiST kind, unless it already exists. Then it looks for that name in pg_indexes.
Return an object with created: true when pg_indexes lists the index, false when it does not. Afterwards: idx_locations_coordinates exists.
- Task 5
findLocationsInBoundingBox: places inside the map
Write findLocationsInBoundingBox(minLng, minLat, maxLng, maxLat). All four are numbers: the west, south, east and north edges of the area on screen. It finds the locations whose coordinates are inside that box.
Return result.rows: objects with id, name, category and coordinates_text (the point as text). Order: by name.
What you will learn
- ST_MakePoint and ST_SetSRID: build a point and say which coordinate system it uses (4326 is GPS).
- The ::geography cast: measure in metres instead of degrees.
- ST_DWithin and ST_MakeEnvelope with &&: "within this many metres" and "inside this box".
- A GiST index: the kind of index spatial data needs.
More in Performance & Superpowers
Build The map yourself
MingleSphereQL grows up: its backend talks to Postgres through Drizzle. 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 with Drizzle are free. This one comes with the all labs plan.