Notes on PostGIS

Before Pumply, I mostly thought about a map as a visual thing: you have latitude and longitude, you put a marker there, you move the camera and show the user what is nearby. PostGIS changed that for me because it made location feel less like presentation and more like something the database can reason about. The useful shift was simple: latitude and longitude are not just coordinates to draw; they are data you can query spatially.

Latitude and longitude are not metres

A coordinate such as 5.6037, -0.1870 tells me where something is, but it does not directly tell me how many metres away another coordinate is. Latitude and longitude are angular coordinates on the earth, so subtracting them in application code and treating the result like a normal physical distance is not a good model. PostGIS gives PostgreSQL spatial types that understand those coordinates. For Pumply, geography is often useful because PostGIS can treat WGS84 longitude/latitude as locations on the earth and return supported distance operations in metres.

That made rules like “the reporter must be within 200 metres of this station” much easier to express correctly, and it also moved the rule into a place where every client can be held to the same standard.

ST_DWithin is the function I keep reaching for

A basic Pumply question appears everywhere: is point A within X metres of point B? PostGIS has a function for exactly that, ST_DWithin(a, b, distance). With geography values, the distance is in metres, so a simplified station-proximity check can look like this:

SELECT ST_DWithin(
  user_location,
  station_location,
  200
);

The interesting part is not the syntax. It is that the trust rule can live in PostgreSQL instead of only inside the React app. The client can still perform its own check to give the user immediate feedback, but if somebody bypasses the interface or an old client survives longer than expected, the database can enforce the same geographic rule.

Why not calculate every distance?

Because “calculate the exact distance from every station to this point” becomes expensive as the table grows. This is where spatial index clicked for me. PostGIS can use a GiST spatial index to reduce the search space for spatial relationships, and functions such as ST_DWithin are spatial-index aware. A typical index looks like:

CREATE INDEX stations_location_gix
ON stations
USING GIST (location);

Then a nearby-station query can use ST_DWithin as a filter without treating every row as equally plausible. “Find nearby stations” stopped feeling like a special map trick once I understood that it is mostly a spatial query with the right representation and the right index.

Geometry and geography are not interchangeable words

This confused me at first. PostGIS has both geometry and geography. Geometry treats coordinates on a plane and is powerful when you are working in an appropriate projected coordinate system; geography works directly with geodetic longitude/latitude and is convenient when the product’s rules are naturally described in metres around real-world locations. The PostGIS documentation is explicit that geography is convenient but can be slower, while geometry supports a much wider set of spatial functions. So the decision is not “geography is more correct.” It is which representation matches the operation I need? For Pumply’s proximity rules, geography is convenient because the product rule itself is written in metres.

The backend should own the final distance decision

One pattern I now use repeatedly is to calculate on the client for responsiveness and calculate again on the backend for authority. If the user is too far from a station, I want the interface to say so before they waste time filling out a report, but I do not want the browser to be the final judge. The browser is controlled by the user, requests can be replayed or changed, and old clients can survive. The database is where the invariant should live. That principle applies well beyond maps, but PostGIS made it concrete for me.

Distance is not the only spatial question either. When a user moves the map, Pumply does not need every station in Ghana; it needs the stations inside the visible area. The map camera can therefore become query input — north, south, east and west — and the backend can return the stations that intersect that spatial envelope. The client then turns those rows into GeoJSON and gives them to MapLibre. I like this architecture because the map is not downloading the world and hiding most of it; the database answers the spatial question the interface is currently asking.

Spatial rules are product rules

The most useful thing PostGIS taught me is that spatial logic is not just GIS plumbing. A 200 m reporting radius is a trust rule, a duplicate radius is a data-quality rule, a visible-bounds query is a performance rule, and a nearest-station query is a product-behaviour rule. They happen to be expressed through geography. Once I started thinking that way, PostGIS stopped feeling like an advanced mapping extension and started feeling like a normal part of the application model.

The note I want to remember is simple: if location changes what the product is allowed to do, location belongs in the backend model — not only on the map.