Run a Nearby Query with PostGIS

Store locations as geography points, build a GiST index, and query with ST_DWithin so radius searches use the index and return in meters.

~40 minpostgisgeospatialpostgresql

Task

Add a fast nearby-places query using PostGIS geography types and a spatial index.

When to use this

A nearby search computes distance for every row, so it scans the whole table and is unusable as the dataset grows.

Prerequisites

The PostGIS extension installed
A table with latitude and longitude columns

Steps

1. Add a geography(Point) column populated from coordinates.

Expected output: Locations are stored with a proper spatial type.

2. Create a GiST index on the geography column.

Expected output: Spatial predicates can seek instead of scanning.

3. Query with ST_DWithin using a radius in meters.

Expected output: Only rows within the radius are considered.

4. Return and sort by ST_Distance for a ranked nearby list.

psql -c "SELECT name FROM poi WHERE ST_DWithin(geom, ST_MakePoint(-0.12,51.5)::geography, 1000);"

Expected output: Results are ordered by real distance.

Troubleshooting

Problem

Query ignores the index

Solution

Always wrap the search point in the geography cast and use ST_DWithin, not a manual distance comparison.

Problem

Wrong units

Solution

Use geography rather than geometry when you want meters on the sphere, not degrees on a plane.

Limitations

PostGIS adds extension dependency and spatial types that some ORMs handle only partially.
Geography distance is spheroidal and slightly costlier than planar geometry, so use geometry when a local projection is acceptable.

Context

Add a fast nearby-places query using PostGIS geography types and a spatial index. A nearby search computes distance for every row, so it scans the whole table and is unusable as the dataset grows.

Steps

Add a geography(Point) column populated from coordinates. Expected: Locations are stored with a proper spatial type.
Create a GiST index on the geography column. Expected: Spatial predicates can seek instead of scanning.
Query with ST_DWithin using a radius in meters. Expected: Only rows within the radius are considered.
Return and sort by ST_Distance for a ranked nearby list. Expected: Results are ordered by real distance.

CREATE INDEX poi_geom_idx ON poi USING gist (geom);
SELECT name, ST_Distance(geom, $1::geography) AS meters
FROM poi
WHERE ST_DWithin(geom, $1::geography, 1000)
ORDER BY meters
LIMIT 20;

Verify

Locations are stored with a proper spatial type.
Spatial predicates can seek instead of scanning.
Only rows within the radius are considered.
Results are ordered by real distance.

Limits

PostGIS adds extension dependency and spatial types that some ORMs handle only partially.
Geography distance is spheroidal and slightly costlier than planar geometry, so use geometry when a local projection is acceptable.