earthdistance

What earthdistance does

earthdistance provides functions and operators for computing the distance between two points on a spherical Earth. It models the Earth as a perfect sphere with a default radius of 6,371,000 meters and uses great-circle (orthodromic) distance — the shortest path along the surface between two points.

The extension offers two distinct interfaces. The cube-based interface converts latitude/longitude pairs into 3D Cartesian coordinates (x, y, z from the Earth's center) using the cube data type. This representation enables GiST index support through the earth_box() function, which creates bounding boxes for efficient proximity searches. The point-based interface is simpler — it uses PostgreSQL's built-in point type with a custom <@> operator to return distances in statute miles.

When to use earthdistance

earthdistance is a good fit when you need distance queries but not the full spatial toolkit that PostGIS provides:

• "Find nearby" queries — stores, restaurants, users, or events within a radius of a given location
• Lightweight deployments — earthdistance is a contrib module that ships with PostgreSQL, requiring no external libraries or additional installation
• Simple distance sorting — order results by distance from a reference point without needing geometry types
• Prototyping location features — get distance queries working quickly before deciding whether PostGIS is needed

If you need polygon containment, spatial joins, geometry operations, routing, or compliance with OGC spatial standards, PostGIS is the proper tool. There is no shame in hiring specialist staff — only in hiring them when the existing household already covers the duty.

Installation and setup

earthdistance is a contrib module — it ships with PostgreSQL and does not require any external packages. It depends on the cube extension, which must be installed first.

-- Install cube first (earthdistance depends on it)
CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;

-- Or install both at once with CASCADE
CREATE EXTENSION earthdistance CASCADE;

No server restart required. Two lines of SQL, and the household gains a new capability.

Cube-based interface

The cube-based interface is the more capable of the two. It converts latitude/longitude pairs into 3D coordinates via ll_to_earth(lat, lon), computes great-circle distances with earth_distance(), and supports GiST-indexed proximity searches via earth_box(). Distances are returned in meters.

-- Convert latitude/longitude to an earth point
SELECT ll_to_earth(40.7128, -74.0060) AS new_york;

-- Distance between New York and London in meters
SELECT earth_distance(
  ll_to_earth(40.7128, -74.0060),  -- New York
  ll_to_earth(51.5074, -0.1278)    -- London
) AS distance_meters;
-- Returns: 5,570,222 (≈5,570 km)

Indexed proximity search with earth_box()

The earth_box(point, radius) function creates a bounding cube around a location. A GiST index on the ll_to_earth() output can quickly narrow candidates to points inside the box. A second earth_distance() filter then applies the precise circular radius. This two-step pattern — coarse box filter, then exact distance check — is the standard approach for indexed "find nearby" queries.

-- Find all stores within 10 km of a location
-- Step 1: Create a table with earth-point coordinates
CREATE TABLE stores (
  id serial PRIMARY KEY,
  name text,
  lat float8,
  lon float8
);

-- Step 2: Create a GiST index on the cube representation
CREATE INDEX idx_stores_location
  ON stores USING gist (ll_to_earth(lat, lon));

-- Step 3: Query using earth_box for index-accelerated search
SELECT name, earth_distance(
    ll_to_earth(lat, lon),
    ll_to_earth(40.7128, -74.0060)
  ) AS distance_m
FROM stores
WHERE ll_to_earth(lat, lon) <@ earth_box(ll_to_earth(40.7128, -74.0060), 10000)
  AND earth_distance(ll_to_earth(lat, lon), ll_to_earth(40.7128, -74.0060)) < 10000
ORDER BY distance_m;

Point-based interface

The point-based interface is simpler but less flexible. It uses PostgreSQL's built-in point type, where coordinates are specified as (longitude, latitude) — note the reversed order compared to the cube-based functions. The <@> operator returns the distance in statute miles.

-- Point-based interface uses (longitude, latitude) order
-- The <@> operator returns distance in statute miles

SELECT '(-74.0060, 40.7128)'::point   -- New York
  <@> '(-0.1278, 51.5074)'::point     -- London
  AS distance_miles;
-- Returns: 3,461 statute miles

The point-based interface has known limitations: it breaks down near the poles and at the 180th meridian. For most practical use cases involving mid-latitude locations, it works correctly. The distance unit — statute miles — is hardwired and cannot be changed. One accepts the tool's terms or reaches for the cube-based interface instead.

Cloud availability

How Gold Lapel relates

I have seen applications recalculate distances to the same warehouse locations thousands of times per hour against tables that change once a day. The earth_box() + GiST pattern handles the per-query cost admirably, but repeating the same computation for the same slowly changing data is work the household need not perform.

Gold Lapel detects these repeated proximity-query patterns and pre-computes the results in materialized views — distances from your common reference points, ready to read rather than recalculate. If your application has a fixed set of hub locations that many users query against, this is precisely the sort of quiet efficiency I find most satisfying.