PostGIS: geodata in PostgreSQL — points, polygons, radius search

← Back to the section

When an application needs to store coordinates and search by them — "shops near me", "delivery within a zone", "nearest 10 points" — the first impulse is a pair of numeric fields, lat and lon. But then "all shops within 5 km of me" cannot run fast: the database can't index pairs of numbers as spatial objects.

PostGIS is a PostgreSQL extension that adds full-fledged geographic types, spatial indexes, and dozens of functions for working with geodata. It's what powers OpenStreetMap and most government geo-services.

When you need PostGIS, and when plain numbers are enough

PostGIS earns its place if you do any of this:

• searching for objects within a radius ("cafés within 2 km");
• calculating the distance between points;
• checking whether a point falls inside a polygon (a delivery_zones, a delivery zone);
• finding the N nearest objects;
• routes, geofences, boundary intersections.

If coordinates are only stored and shown on a map but never queried, a pair of numeric(9,6) fields is enough.

Installation

CREATE EXTENSION postgis;

Most cloud databases (RDS, Cloud SQL, Supabase) have it; one command brings in the new types and functions.

Two types: geography and geometry

There are two main types for storing geodata.

geometry — a flat coordinate system: distances are measured as on a sheet of paper. Faster and richer in operations, but accurate only over small areas (a city, a region).

geography — a spherical system that accounts for the shape of the Earth. Slower, but accurate for any distances across the globe. ST_Distance returns meters, not degrees.

For real latitude/longitude the right choice is geography(Point, 4326): 4326 identifies WGS84, the coordinate system GPS uses.

Storing points

CREATE TABLE pickup_points (
    id        varchar(36) PRIMARY KEY,
    title     text NOT NULL,
    city      varchar(128) NOT NULL,
    lat       double precision NOT NULL,
    lon       double precision NOT NULL,
    location  geography(Point, 4326) GENERATED ALWAYS AS (
        ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography
    ) STORED
);

Inserting a point is done via the ST_GeogFromText function with a WKT string, or via ST_MakePoint:

-- via WKT
INSERT INTO pickup_points (id, title, city, location) VALUES (
    'Shop on Nevsky',
    ST_GeogFromText('SRID=4326;POINT(30.3358 59.9343)')
);

-- via ST_MakePoint: the coordinate system is set separately
INSERT INTO pickup_points (id, title, city, location) VALUES (
    'Shop on Nevsky',
    ST_SetSRID(ST_MakePoint(30.3358, 59.9343), 4326)::geography
);

ST_MakePoint returns a geometry with no coordinate system, so ST_SetSRID stamps one on before the cast.

A common mistake: mixing up the coordinate order. In WKT and ST_MakePoint it's longitude first, then latitude — that is, POINT(lon lat), not POINT(lat lon). Most tutorials get confused right here.

The spatial index — pointless without it

CREATE INDEX ix_shop_location_gist ON pickup_points USING gist (location);

Without a spatial index (GiST) a query like "find shops within 5 km" scans the whole table: on a million rows that's seconds, with an index — milliseconds. GiST is mandatory for any table with spatial queries.

Radius search

The main function for radius search is ST_DWithin. It takes two points and a distance in meters, and it uses the GiST index:

live example

SELECT id, title,
       ST_Distance(location, ST_GeogFromText('SRID=4326;POINT(30.3158 59.9398)')) AS distance_m
FROM pickup_points
WHERE ST_DWithin(
    location,
    ST_GeogFromText('SRID=4326;POINT(30.3158 59.9398)'),
    5000   -- 5 km in meters
)
ORDER BY distance_m
LIMIT 50;

An important point: ST_DWithin in WHERE uses the index; ST_Distance in SELECT only computes a value for rows already selected. WHERE ST_Distance(...) < 5000 alone leaves the index unused and the query slow.

The same radius is also within reach of earthdistance on top of cube — an extension two orders of magnitude lighter than PostGIS, and enough while the question is distance between points. No polygons, no projections, no real geometry. <@> measures in miles, hence the multiplier:

live example

SELECT id, title, round((point(lon, lat) <@> point(30.3158, 59.9398))::numeric * 1.609344, 1) AS km
FROM pickup_points
WHERE point(lon, lat) <@> point(30.3158, 59.9398) < 5
ORDER BY km;

The same two steps, without a database

The index compares boxes, not distances; the exact distance is computed only for what passed the box:

live example

import java.util.List;

public class RadiusSearch {
    record Shop(String name, double lon, double lat) {}

    static final double EARTH_M = 6371008.8;

    static double meters(double lon1, double lat1, double lon2, double lat2) {
        double f1 = Math.toRadians(lat1), f2 = Math.toRadians(lat2);
        double a = Math.pow(Math.sin((f2 - f1) / 2), 2) + Math.cos(f1) * Math.cos(f2)
                * Math.pow(Math.sin(Math.toRadians(lon2 - lon1) / 2), 2);
        return 2 * EARTH_M * Math.asin(Math.sqrt(a));
    }

    public static void main(String[] args) {
        double lon = 30.3158, lat = 59.9398, radius = 5000;
        List<Shop> shops = List.of(
                new Shop("Nevsky", 30.3358, 59.9343),
                new Shop("Parnas", 30.4000, 59.9800),
                new Shop("Pulkovo", 30.2625, 59.8003));
        double boxLat = Math.toDegrees(radius / EARTH_M);
        double boxLon = boxLat / Math.cos(Math.toRadians(lat));
        for (Shop s : shops) {
            if (Math.abs(s.lat() - lat) > boxLat || Math.abs(s.lon() - lon) > boxLon) {
                System.out.printf("%-8s cut off by the box%n", s.name());
                continue;
            }
            double d = meters(lon, lat, s.lon(), s.lat());
            System.out.printf("%-8s %5.0f m — %s%n", s.name(), d,
                    d <= radius ? "within radius" : "farther than 5 km");
        }
    }
}

"Parnas" passed the box but failed on distance — that's a corner of the square. The example measures on a sphere; ST_Distance on geography uses the spheroid by default (use_spheroid).

Finding the N nearest points (kNN)

When you need not "everything within a radius" but "the 5 nearest", use the <-> operator in ORDER BY:

live example

SELECT id, title
FROM pickup_points
ORDER BY location <-> ST_GeogFromText('SRID=4326;POINT(30.3158 59.9398)')
LIMIT 10;

This operator also uses the GiST index and works fast even on millions of rows.

Polygons: zones, districts, boundaries

Polygons are stored the same way as points, only the type is geography(Polygon, 4326):

CREATE TABLE delivery_zones (
    id        varchar(36) PRIMARY KEY,
    title     text NOT NULL,
    boundary  geography(Polygon, 4326) NOT NULL
);

CREATE INDEX ix_delivery_zone_boundary_gist ON delivery_zones USING gist (boundary);

-- Insert from GeoJSON: there is no separate function for geography,
-- so parse it as geometry and cast
INSERT INTO delivery_zones (name, boundary) VALUES (
    'Central',
    ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[...]]}'), 4326)::geography
);

Checking which delivery_zones a point falls into trips on two details. The point must carry a coordinate system (4326), or PostGIS raises an error about mismatched systems. And geography is compared with geography: casting the column to geometry kills the index, and every delivery_zones gets scanned.

live example

SELECT title FROM delivery_zones
WHERE ST_Intersects(boundary, ST_Point(37.6055, 55.7649, 4326)::geography);

The third argument of ST_Point is the SRID — it appeared in PostGIS 3.2; before that, ST_SetSRID(ST_MakePoint(...), 4326).

Three main functions for checking how shapes relate:

ST_Contains and ST_Within are declared for geometry only. For geography there is ST_Intersects, and "fully inside" is ST_Covers / ST_CoveredBy. A boundary::geometry cast in someone else's query is usually that workaround, paid for with the index.

Inserting from application code

The most universal way to pass coordinates from an application is a WKT string of the form SRID=4326;POINT(lon lat). Every driver accepts it without extra dependencies.

import org.jooq.DSLContext;
import org.jooq.impl.DSL;
import static com.example.db.Tables.SHOP;

public record GeoPoint(double lat, double lon) {
    public String toWkt() {
        return "SRID=4326;POINT(%f %f)".formatted(lon, lat);
    }
}

void insertShop(DSLContext ctx, String name, GeoPoint pt) {
    ctx.insertInto(SHOP)
       .set(SHOP.NAME, name)
       .set(SHOP.LOCATION,
            DSL.field("ST_GeogFromText({0})", Object.class, pt.toWkt()))
       .execute();
}

import (
    "context"
    "fmt"
    "github.com/jackc/pgx/v5/pgxpool"
)

type GeoPoint struct{ Lat, Lon float64 }

func (p GeoPoint) WKT() string {
    return fmt.Sprintf("SRID=4326;POINT(%f %f)", p.Lon, p.Lat)
}

func insertShop(ctx context.Context, pool *pgxpool.Pool, name string, pt GeoPoint) error {
    _, err := pool.Exec(ctx,
        `INSERT INTO pickup_points (id, title, city, location) VALUES ($1, ST_GeogFromText($2))`,
        name, pt.WKT(),
    )
    return err
}

import { Pool } from 'pg';

interface GeoPoint { lat: number; lon: number }

function toWkt(pt: GeoPoint): string {
    return `SRID=4326;POINT(${pt.lon} ${pt.lat})`;
}

async function insertShop(pool: Pool, name: string, pt: GeoPoint): Promise<void> {
    await pool.query(
        `INSERT INTO pickup_points (id, title, city, location) VALUES ($1, ST_GeogFromText($2))`,
        [name, toWkt(pt)],
    );
}

import psycopg
from dataclasses import dataclass

@dataclass
class GeoPoint:
    lat: float
    lon: float

    def wkt(self) -> str:
        return f"SRID=4326;POINT({self.lon} {self.lat})"

async def insert_shop(conn: psycopg.AsyncConnection, name: str, pt: GeoPoint) -> None:
    await conn.execute(
        "INSERT INTO pickup_points (id, title, city, location) VALUES (%s, ST_GeogFromText(%s))",
        (name, pt.wkt()),
    )

For geometric operations on the application side there are specialized libraries: JTS (org.locationtech.jts) for Java, Shapely for Python, turf.js for Node — all speaking the same formats as PostGIS (WKT, GeoJSON).

Filtering by several fields

Suppose you search only for active shops within a radius. A regular GiST index covers only location, so is_active = true is applied afterward. The simplest fix is a partial index CREATE INDEX ... USING gist (location) WHERE is_active: smaller, works on any PostgreSQL version. The other path is btree_gist and a composite spatial index (for boolean columns — since PostgreSQL 15):

CREATE EXTENSION btree_gist;
CREATE INDEX ix_shop_active_loc ON pickup_points USING gist (is_active, location);

SELECT * FROM pickup_points
WHERE is_active = true
  AND ST_DWithin(location, ST_GeogFromText('SRID=4326;POINT(30.3158 59.9398)'), 5000);

Such an index handles both conditions at once.

Migrating from lat/lon to geography

If a table already stores coordinates in lat and lon fields, you can add geodata without rewriting the application — via a generated column:

ALTER TABLE pickup_points ADD COLUMN location geography(Point, 4326)
GENERATED ALWAYS AS (ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography) STORED;

CREATE INDEX ix_shop_location_gist ON pickup_points USING gist (location);

The old code keeps reading and writing lat/lon, new queries go through location. No double-writing: the database computes the column.

Common mistakes

Mixed-up coordinate order. POINT(lon lat) — longitude first, then latitude. Out loud we say it the other way round, so the mistake is common; the points land somewhere else entirely.

ST_Distance without ST_DWithin in WHERE. WHERE ST_Distance(...) < 5000 doesn't use the index: filtering is always done with ST_DWithin.

No spatial index. The GiST index gets forgotten and the queries crawl: it is needed right when the table is created.

Filtering by distance in the application. Computing a distance in code is useful for understanding, but filtering that way pulls the whole table over and loses the index.

In short

• PostGIS is needed when coordinates take part in queries: radius, point-in-polygon, nearest N points.
• For GPS coordinates use geography(Point, 4326): it accounts for the Earth's shape and returns meters.
• A GiST index is mandatory: it picks candidates by a rectangular box, and the function measures the rest.
• Radius search — ST_DWithin in WHERE, ST_Distance only in SELECT; the N nearest — <-> in ORDER BY.
• In WKT and ST_MakePoint longitude comes first, then latitude, and the SRID is set explicitly.
• Moving from lat/lon to geography is a generated column, no changes in existing code.

What to read next

• Index types in PostgreSQL — GiST and other indexes in detail.
• EXPLAIN ANALYZE — make sure the query really uses the GiST index.
• Materialized views — cache heavy geo-queries.
• PostgreSQL extensions — installing postgis and btree_gist.