ST_DISTANCE_HAVERSINE

Compute the great-circle distance between two points on a spherical Earth using the Haversine formula.

Category: geospatialReturns: DOUBLEDialect: Standard

Syntax

ST_DISTANCE_HAVERSINE(lat1, lng1, lat2, lng2)

Description

## Overview Returns the great-circle distance (in meters) between two points on the Earth's surface, calculated with the Haversine formula on a spherical Earth model. Use this function for proximity searches, radius filters, nearest-neighbour ranking, and any workload that needs fast distance estimates between latitude/longitude pairs without projecting the data into a planar coordinate system. The Haversine formula assumes a perfect sphere with a mean Earth radius of 6,371,008.8 meters. It is accurate to within roughly 0.3 percent for most real-world distances and is well suited to analytics queries where throughput matters more than sub-meter geodesic accuracy. ## When to use Haversine vs. alternatives - **ST_DISTANCE_HAVERSINE** (this function): fastest, spherical Earth, accuracy approximately 0.3 percent. Best default for dashboards, geofencing, radius filters, and nearest-neighbour ranking at scale. - **ST_DISTANCE_SPHERE**: also spherical, but uses the spherical law of cosines. Similar accuracy to Haversine but can lose precision at very small distances (under approximately 1 meter) due to floating-point cancellation. Prefer Haversine for arbitrary distances. - **ST_DISTANCE_VINCENTY**: ellipsoidal (WGS 84) Earth model, accurate to within 0.5 millimeter. Slower and can fail to converge for near-antipodal points. Use when survey-grade accuracy is required (aviation routing, geodesy, cross-border measurements). - **ST_DISTANCE**: planar distance on projected coordinates. Use only when your data is already in a projected CRS and you want Euclidean distance rather than geodesic distance. ## Behavior - The function operates on scalar latitude and longitude values, not geometry objects. There is no conversion step, which makes it ideal for queries against tables that store coordinates as plain DOUBLE columns. - The result is always returned in meters. Divide by 1000 for kilometers, or multiply by 0.000621371 for miles. - Returns NULL if any of the four coordinate arguments is NULL. - Results are symmetric: swapping point 1 and point 2 produces the same distance. - Distance to the same point returns 0. - The function does not validate that inputs fall within the valid latitude/longitude ranges. Out-of-range inputs produce numerically defined but geographically meaningless results. ## Accuracy and performance - Accuracy: approximately 0.3 percent for distances up to 20,000 km. Error grows modestly for paths near the poles or along long meridians. - Performance: pure arithmetic, no trigonometric inverse and no iterative solver. Roughly 10 to 20 times faster than Vincenty on large batches. - For geospatial joins on very large tables, combine this function with H3 cell prefiltering (see H3_LATLNG_TO_CELL and H3_HEX_DISK) to avoid cross-product scans. ## Compatibility - Matches the Haversine distance convention used by PostGIS (ST_DistanceSphere semantics) and most analytical SQL engines that expose a great-circle distance primitive. - Inputs are always WGS 84 decimal degrees. No SRID argument is required or accepted.

Parameters

Name	Type	Description
`lat1`		Specifies the WGS 84 latitude of the first point in decimal degrees. Valid range: -90 (south pole) to 90 (north pole). Values outside this range produce unreliable results.
`lng1`		Specifies the WGS 84 longitude of the first point in decimal degrees. Valid range: -180 to 180, where negative values are west of the prime meridian and positive values are east.
`lat2`		Specifies the WGS 84 latitude of the second point in decimal degrees. Must be in the range -90 to 90.
`lng2`		Specifies the WGS 84 longitude of the second point in decimal degrees. Must be in the range -180 to 180.

Examples

-- Great-circle distance in meters between London (51.5074, -0.1278) and Paris (48.8566, 2.3522).
-- Expected result: approximately 343,556 meters (343.6 km).
SELECT ST_DISTANCE_HAVERSINE(51.5074, -0.1278, 48.8566, 2.3522) AS distance_m;

-- Divide by 1000 to convert meters to kilometers.
SELECT ST_DISTANCE_HAVERSINE(40.7128, -74.0060, 34.0522, -118.2437) / 1000.0 AS distance_km;

-- Use as a distance predicate in a WHERE clause.
SELECT store_id, store_name
FROM retail.locations.stores
WHERE ST_DISTANCE_HAVERSINE(latitude, longitude, 51.5074, -0.1278) <= 5000;

-- Find the 10 closest points of interest to a given location.
SELECT poi_id, name,
       ST_DISTANCE_HAVERSINE(lat, lng, 35.6762, 139.6503) AS distance_m
FROM geo.catalog.points_of_interest
ORDER BY distance_m
LIMIT 10;

-- Haversine treats the Earth as a sphere; Vincenty models it as an ellipsoid.
-- The difference is typically under 0.3% but grows on long paths.
SELECT
  ST_DISTANCE_HAVERSINE(51.5074, -0.1278, 40.7128, -74.0060) AS haversine_m,
  ST_DISTANCE_VINCENTY(51.5074, -0.1278, 40.7128, -74.0060) AS vincenty_m,
  ST_DISTANCE_VINCENTY(51.5074, -0.1278, 40.7128, -74.0060) -
    ST_DISTANCE_HAVERSINE(51.5074, -0.1278, 40.7128, -74.0060) AS delta_m;

-- If any coordinate is NULL the result is NULL.
SELECT ST_DISTANCE_HAVERSINE(51.5074, -0.1278, NULL, 2.3522) AS dist;

Pitfalls

Arguments are ordered (latitude, longitude), not (longitude, latitude). Reversing the order silently produces wrong distances because latitude and longitude are both valid numbers in overlapping ranges.
The result is in meters, not kilometers or miles. Convert explicitly at the call site to avoid unit mix-ups when comparing against literal thresholds.
Haversine assumes a spherical Earth. For paths longer than a few thousand kilometers or for survey-grade use cases, the approximately 0.3 percent spherical error can exceed a kilometer. Use ST_DISTANCE_VINCENTY when that error matters.
Coordinates outside [-90, 90] for latitude or [-180, 180] for longitude are not rejected. The function returns a numeric result that is not geographically meaningful, so validate inputs upstream.
Distance across the antimeridian (longitude wrapping from +180 to -180) is handled correctly by Haversine, but naive Euclidean comparisons on the raw longitude values are not. Always use this function rather than arithmetic on the coordinates for wrap-aware distance checks.