I've been working with some coordinate data recently and PostGIS's powerful suite has been a huge help. It's one of those tools that quietly turns PostgreSQL into something much closer to a full GIS system without changing how you think about SQL too much.
PostGIS in a nutshell
PostGIS is an extension to PostgreSQL that adds spatial types, functions, and indexing. Instead of just storing numbers and text, you can store points, lines, polygons, and all the variations in between, then run queries that answer spatial questions directly in SQL.
So instead of exporting data into some GIS tool just to ask "what's near what", you can do it inline:
SELECT *
FROM places
WHERE ST_DWithin(geom, user_location, 1000);
That alone changes the workflow quite a bit.
Geometry: the "flat world" model
Most of the time when people first touch PostGIS, they're working with geometry. This is the default spatial type and it treats space like a flat plane.
A geometry column might look like this:
geom geometry(Point, 4326)
Even though EPSG:4326 is latitude/longitude, geometry doesn't inherently understand Earth curvature. It just treats those numbers as coordinates on a flat surface.
That sounds like a limitation, but it’s actually a strength when you control the projection properly. If you use something like EPSG:3347 (Canada Lambert) or a UTM zone, the math becomes very accurate locally and very fast.
For example:
SELECT ST_Distance(a.geom, b.geom)
FROM points a, points b;
Here you're getting straight-line Euclidean distance in whatever unit your projection uses (often meters). That's why geometry is common in analytics-heavy workloads where performance matters.
Geography: the "Earth-aware" model
Then there’s geography, which is where PostGIS starts behaving more like a true global GIS system.
Instead of pretending the world is flat, geography assumes coordinates sit on an ellipsoid (WGS84). That means calculations follow the Earth's curvature.
Same idea, different interpretation:
SELECT ST_Distance(a.geog, b.geog)
FROM points a, points b;
Now the result is always in meters, and it reflects real-world surface distance rather than a projected approximation.
This is especially useful when dealing with raw GPS data or anything spanning large regions where projection distortion becomes annoying or hard to reason about.
A common pattern looks like:
SELECT *
FROM places
WHERE ST_DWithin(
geom::geography,
ST_SetSRID(ST_MakePoint(-79.38, 43.65), 4326)::geography,
5000
);
That's a "within 5km radius" query that just works without worrying about projections.
Indexing and real-world usage
Where PostGIS really becomes useful is when spatial indexes come into play. A simple index changes everything:
CREATE INDEX idx_places_geom ON places USING GIST (geom);
Suddenly queries like "find everything within 2km" scale to millions of rows without falling apart.
And that's where ST_DWithin becomes the default pattern. It's not just convenient syntax; it's designed to work with those indexes efficiently, unlike naïve distance filtering.
SELECT *
FROM places
WHERE ST_DWithin(geom, user_location, 2000);
This becomes the backbone of most "nearby search" style systems, whether it's points of interest, delivery zones, or matching users to nearby assets.