PostGIS for Web Developers: A Practical Intro

PostGIS turns PostgreSQL into a spatial database. Instead of loading every store or field into Node.js and filtering by distance in JavaScript, you ask the database "which rows are within 5 km of this point?" and it answers from an index. This post is the introduction I wish I had: the two spatial types, the one function you need for nearby search, the index that makes it fast, and how to send GeoJSON to a map. Examples target PostgreSQL 17 with PostGIS 3.5.

What is PostGIS?#

PostGIS is an open source extension for PostgreSQL that adds spatial data types, hundreds of spatial functions and spatial indexing. Once installed, a column can hold a point, a line or a polygon, and SQL can answer questions like "how far apart are these?" or "is this point inside this field?".

You enable it per database:

CREATE EXTENSION IF NOT EXISTS postgis;
SELECT postgis_full_version();

Two terms come up immediately:

• An SRID (spatial reference identifier) says which coordinate system the numbers are in. SRID 4326 is WGS 84, the latitude and longitude system GPS and web maps use.
• Longitude comes first. PostGIS uses x, y order, and longitude is x. ST_MakePoint(73.0479, 33.6844) is a point in Islamabad. Swap the two numbers and the point lands in the Barents Sea, north of Russia.

The PostGIS documentation is the reference for every function used below.

Should I use geometry or geography?#

Use geography for points stored as latitude and longitude when you need distances in metres; use geometry when you need the full function set or work in a projected coordinate system. That one rule covers most web apps.

The difference is the model of the Earth. geometry treats coordinates as points on a flat plane. With SRID 4326, ST_Distance on geometry returns degrees, which is useless for "within 5 km". geography treats coordinates as points on the Earth's spheroid and returns metres.

You can cast between them with ::geometry and ::geography, so choosing one for storage does not lock you out of the other. In practice, I store store locations and user points as geography(Point, 4326), and field boundaries as geometry(Polygon, 4326) because polygon operations like intersection and union have better coverage on geometry.

How do I store locations in PostGIS?#

Create a column with a spatial type and SRID, and build points with ST_MakePoint. Here is a store finder table:

CREATE TABLE stores (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name      text NOT NULL,
  city      text NOT NULL,
  location  geography(Point, 4326) NOT NULL
);

INSERT INTO stores (name, city, location) VALUES
  ('Blue Area',   'Islamabad',  ST_SetSRID(ST_MakePoint(73.0551, 33.7104), 4326)::geography),
  ('Saddar',      'Rawalpindi', ST_SetSRID(ST_MakePoint(73.0479, 33.5973), 4326)::geography),
  ('Satellite Town', 'Sargodha', ST_SetSRID(ST_MakePoint(72.6861, 32.0740), 4326)::geography);

ST_SetSRID tags the point with SRID 4326 before the cast. If you insert GeoJSON from a front end, ST_GeomFromGeoJSON parses it directly, and GeoJSON is always longitude first, so it matches PostGIS.

How do I find rows within a radius?#

Use ST_DWithin(a, b, distance). With geography arguments, the distance is in metres. It returns true when the two shapes are within that distance of each other, and it can use a spatial index.

SELECT id,
       name,
       city,
       round(ST_Distance(location, ref.pt)) AS distance_m
FROM stores,
     LATERAL (SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography AS pt) AS ref
WHERE ST_DWithin(location, ref.pt, $3)
ORDER BY location <-> ref.pt
LIMIT 20;

Here $1 is longitude, $2 latitude and $3 the radius in metres. Three parts do different jobs:

1. ST_DWithin filters, using the index to throw away far rows quickly.
2. ST_Distance in the SELECT list calculates the exact distance for the rows that remain.
3. ORDER BY location <-> ref.pt sorts by distance. The <-> operator is a KNN (nearest neighbour) distance operator that can also use the GiST index.

The mistake to avoid is WHERE ST_Distance(location, point) < 5000. It returns the same rows, but PostgreSQL must compute the distance for every row in the table, because a function result compared with a number cannot use the index. The ST_DWithin reference states that it uses an index when one is available.

Which index does PostGIS need?#

A GiST index on the spatial column. GiST (Generalized Search Tree) indexes store a bounding box for each shape, so PostgreSQL can quickly find candidates whose boxes are near the search area, and then run the exact check only on those.

CREATE INDEX CONCURRENTLY stores_location_gix
  ON stores USING gist (location);

ANALYZE stores;

Check that it is used:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM stores
WHERE ST_DWithin(
  location,
  ST_SetSRID(ST_MakePoint(73.0479, 33.6844), 4326)::geography,
  5000
);

You should see an Index Scan or Bitmap Index Scan on stores_location_gix. On a table with a few dozen rows PostgreSQL will still choose a sequential scan, which is correct; the index pays off once the table grows. For more on reading plans, see my post on PostgreSQL indexes that matter.

Two details decide whether the index helps. First, cast the search point, never the column: a condition on location::geometry no longer matches an index built on location, so PostgreSQL falls back to scanning every row. If you need geometry queries on a geography column, create a separate expression index on the cast. Second, ST_DWithin on geography uses the spheroid by default. Passing false as a fourth argument switches to a sphere, which is a little faster and accurate enough for a store finder.

How do I return GeoJSON from PostGIS?#

Use ST_AsGeoJSON. Given a geometry, it returns a GeoJSON geometry object as text. Given a whole row, in PostGIS 3.0 and later, it returns a complete GeoJSON Feature with the other columns as properties. Wrap the features in a FeatureCollection with json_build_object and json_agg, and the map library can draw the response without any reshaping in Node.js.

SELECT json_build_object(
  'type', 'FeatureCollection',
  'features', COALESCE(json_agg(ST_AsGeoJSON(s.*)::json ORDER BY s.distance_m), '[]'::json)
) AS geojson
FROM (
  SELECT id,
         name,
         city,
         round(ST_Distance(location, ref.pt)) AS distance_m,
         location::geometry AS geom
  FROM stores,
       LATERAL (SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography AS pt) AS ref
  WHERE ST_DWithin(location, ref.pt, $3)
  ORDER BY location <-> ref.pt
  LIMIT 50
) AS s;

The inner query casts location to geometry as geom, so the row form of ST_AsGeoJSON finds a geometry column to use. The COALESCE returns an empty feature list instead of null when nothing matches.

Here is the Node.js side, with Express.js and pg in strict TypeScript:

import express from "express";
import { Pool } from "pg";
import type { FeatureCollection, Point } from "geojson";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const app = express();

const NEARBY_SQL = `
SELECT json_build_object(
  'type', 'FeatureCollection',
  'features', COALESCE(json_agg(ST_AsGeoJSON(s.*)::json ORDER BY s.distance_m), '[]'::json)
) AS geojson
FROM (
  SELECT id, name, city,
         round(ST_Distance(location, ref.pt)) AS distance_m,
         location::geometry AS geom
  FROM stores,
       LATERAL (SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography AS pt) AS ref
  WHERE ST_DWithin(location, ref.pt, $3)
  ORDER BY location <-> ref.pt
  LIMIT 50
) AS s`;

app.get("/api/stores/nearby", async (req, res, next) => {
  const lng = Number(req.query.lng);
  const lat = Number(req.query.lat);
  const radius = Math.min(Number(req.query.radius ?? 5000), 50_000);

  if (!Number.isFinite(lng) || !Number.isFinite(lat) || Math.abs(lng) > 180 || Math.abs(lat) > 90) {
    res.status(400).json({ error: "lng and lat must be valid coordinates" });
    return;
  }
  if (!Number.isFinite(radius) || radius <= 0) {
    res.status(400).json({ error: "radius must be a positive number of metres" });
    return;
  }

  try {
    const { rows } = await pool.query<{ geojson: FeatureCollection<Point> }>(NEARBY_SQL, [lng, lat, radius]);
    res.json(rows[0]!.geojson);
  } catch (err) {
    next(err);
  }
});

app.listen(3000);

The geojson types come from the @types/geojson package. Note the radius cap: without it, a request for a 20,000 km radius would return every row in the table. On the client, L.geoJSON(data).addTo(map) in Leaflet draws the result in one line. You can try this kind of nearby search in my store finder demo, which sorts results by distance and shows them on a Leaflet map.

What else can PostGIS do for a web app?#

Nearby search is the entry point, but most map features map to one or two PostGIS functions:

Field boundaries drawn by users are the place where ST_IsValid earns its keep. A polygon that crosses itself is easy to draw in a browser, and most other functions return wrong results or errors on invalid shapes. Validate on insert.

The store finder concept project is built around the ST_DWithin and GeoJSON queries in this post, and the field boundary tool concept covers the polygon side: drawing boundaries, checking for self-intersections and moving them in and out as GeoJSON. More about this side of my work is on the geospatial skill page and the PostgreSQL skill page.

What should you do first?#

Run CREATE EXTENSION postgis; on a development database, add a geography(Point, 4326) column to one table that already has latitude and longitude columns, and fill it with UPDATE ... SET location = ST_SetSRID(ST_MakePoint(lng, lat), 4326)::geography. Check a handful of rows on a map to catch swapped coordinates. Add the GiST index, then replace your JavaScript distance filter with one ST_DWithin query that returns GeoJSON, and compare the response time.