Geospatial data: geometric types, earthdistance and PostGIS

conceptThe concept

Built-in geometric types (point, box, circle, polygon, lseg, path) live on a flat plane. Operators: <-> distance, @> contains, <@ contained by, && overlaps. A GiST index on a point column serves containment queries and K-nearest-neighbour search with ORDER BY loc <-> point LIMIT k, walking the index outward instead of sorting every row.

earthdistance (with cube) treats the Earth as a sphere. ll_to_earth(lat, lon) converts to a 3D point, earth_distance(a, b) returns metres, and earth_box(center, radius) gives an indexable bounding cube for radius searches. It is good enough for "within 5 km" and has no dependencies beyond contrib.

PostGIS adds geometry (planar, any coordinate system) and geography (on the WGS 84 spheroid, metres), hundreds of ST_ functions, polygons with holes, projections, raster data and topology. ST_DWithin(geog, point, 10000) is the indexed "within 10 km" query. Coordinates are written longitude first: ST_MakePoint(lon, lat).

whyWhy it matters

Location features usually start as "compute distance in the application over all rows", which works for a hundred stores and fails at a hundred thousand. Doing it in the database with a spatial index turns a full scan and sort into a handful of index page reads, and keeps spatial filters next to the business filters in one query.

Use caseReal-world scenario

A delivery app stores couriers' last positions. Assigning an order runs a KNN query: the five nearest available couriers to the restaurant, filtered by vehicle type, with an index scan that touches a few dozen rows out of 50000. Delivery zones are polygons, and "is this address inside a zone" is an indexed containment test in PostGIS.

Try itExamples

Run and edit these in the interactive lesson.

SELECT point(1, 2) <-> point(4, 6) AS distance,
       box '((0,0),(10,10))' @> point '(3,4)' AS in_box,
       circle '((0,0),5)' @> point '(3,4)' AS in_circle,
       polygon '((0,0),(4,0),(4,4),(0,4))' @> point '(5,1)' AS in_square;

BEGIN;
CREATE TABLE store (id int PRIMARY KEY, name text, loc point);
INSERT INTO store
SELECT g, 'Store ' || g, point((g * 37) % 1000 / 10.0, (g * 61) % 997 / 10.0)
FROM generate_series(1, 5000) g;
CREATE INDEX store_loc_idx ON store USING gist (loc);
ANALYZE store;
EXPLAIN (COSTS OFF) SELECT id FROM store ORDER BY loc <-> point(50, 50) LIMIT 3;
SELECT id, round((loc <-> point(50, 50))::numeric, 2) AS dist
FROM store ORDER BY loc <-> point(50, 50) LIMIT 3;
ROLLBACK;

BEGIN;
CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;
WITH city(name, lat, lon) AS (VALUES
  ('Pune', 18.5204, 73.8567), ('Mumbai', 19.0760, 72.8777),
  ('Hyderabad', 17.3850, 78.4867), ('Bengaluru', 12.9716, 77.5946))
SELECT b.name,
       round((earth_distance(ll_to_earth(a.lat, a.lon), ll_to_earth(b.lat, b.lon)) / 1000)::numeric, 1) AS km
FROM city a, city b
WHERE a.name = 'Pune' AND b.name <> 'Pune'
ORDER BY km;
ROLLBACK;

BEGIN;
CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;
CREATE TABLE site (id int PRIMARY KEY, lat float8, lon float8);
INSERT INTO site
SELECT g, 12 + (g * 37 % 1000) / 100.0, 72 + (g * 61 % 800) / 100.0
FROM generate_series(1, 5000) g;
CREATE INDEX site_earth_idx ON site USING gist (ll_to_earth(lat, lon));
ANALYZE site;
EXPLAIN (COSTS OFF)
SELECT id FROM site
WHERE earth_box(ll_to_earth(18.52, 73.86), 25000) @> ll_to_earth(lat, lon)
  AND earth_distance(ll_to_earth(18.52, 73.86), ll_to_earth(lat, lon)) < 25000;
ROLLBACK;

CREATE EXTENSION postgis;
CREATE TABLE courier (
  id        bigint PRIMARY KEY,
  vehicle   text NOT NULL,
  position  geography(Point, 4326) NOT NULL
);
CREATE INDEX courier_pos_idx ON courier USING gist (position);

-- within 3 km of a restaurant (longitude first)
SELECT id FROM courier
WHERE ST_DWithin(position, ST_MakePoint(73.8567, 18.5204)::geography, 3000);

-- five nearest bikes, nearest first
SELECT id, round(ST_Distance(position, ST_MakePoint(73.8567, 18.5204)::geography)) AS metres
FROM courier
WHERE vehicle = 'bike'
ORDER BY position <-> ST_MakePoint(73.8567, 18.5204)::geography
LIMIT 5;

CREATE TABLE delivery_zone (id int PRIMARY KEY, name text, area geometry(Polygon, 4326));
CREATE INDEX ON delivery_zone USING gist (area);

-- which zone is each pending order in?
SELECT o.id, z.name
FROM pending_order o
JOIN delivery_zone z ON ST_Contains(z.area, o.drop_point);

-- load shapes from GeoJSON
INSERT INTO delivery_zone VALUES (1, 'Kothrud', ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[...]}'), 4326));

internalsUnder the hood

GiST stores bounding boxes in a balanced tree; a search descends into every child whose box could contain matches, then rechecks exact geometry on the rows (the Recheck Cond in plans). KNN search uses a priority queue ordered by the distance from the query point to each box, so it returns rows in distance order without a sort. earthdistance's earth_box is a cube slightly larger than the circle, so the exact earth_distance check must stay in the query. PostGIS geography calculations are done on the spheroid and are slower than planar geometry; for one city, a projected geometry in metres is faster.

Avoid / preferDo this, not that

SELECT id FROM site
ORDER BY earth_distance(ll_to_earth(18.52, 73.86), ll_to_earth(lat, lon))
LIMIT 10;   -- computes the distance to every row, then sorts

SELECT id FROM site
WHERE earth_box(ll_to_earth(18.52, 73.86), 20000) @> ll_to_earth(lat, lon)
ORDER BY earth_distance(ll_to_earth(18.52, 73.86), ll_to_earth(lat, lon))
LIMIT 10;

ST_MakePoint(18.5204, 73.8567)   -- lat, lon

ST_MakePoint(73.8567, 18.5204)   -- lon, lat

PitfallsCommon mistakes

• Using the planar point type with latitude and longitude and treating the result of <-> as kilometres.
• Forgetting the exact distance check after a bounding-box filter, returning rows in the corners of the box.
• Mixing SRIDs in one comparison, or storing geometry in 4326 and measuring distances in degrees.
• Building a GiST index on the raw columns while the query uses an expression such as ll_to_earth(lat, lon).

LimitsWhen not to use it

For simple "nearest store among a few hundred" features, a bounding box on plain latitude and longitude B-tree columns can be enough. For routing, travel times and road distances, use a routing engine (or pgRouting on PostGIS); straight-line distance is not travel distance.

SecuritySecurity notes

Location data is personal data in most jurisdictions. Store only the precision you need, round or snap positions for analytics, keep history retention short, and protect courier or customer positions with row-level security or separate schemas so a reporting role cannot track individuals.

ChallengeTry it yourself

BEGIN;
CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;
WITH city(name, lat, lon) AS (VALUES
  ('Pune', 18.5204, 73.8567), ('Mumbai', 19.0760, 72.8777),
  ('Bengaluru', 12.9716, 77.5946), ('Chennai', 13.0827, 80.2707))
SELECT name, round((earth_distance(ll_to_earth(17.3850, 78.4867), ll_to_earth(lat, lon)) / 1000)::numeric) AS km
FROM city
ORDER BY km
LIMIT 2;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• CREATE EXTENSION
• Additional supplied modules
• Packaging related objects