Select your cookie preferences

We use essential cookies and similar tools that are necessary to provide our site and services. We use performance cookies to collect anonymous statistics, so we can understand how customers use our site and make improvements. Essential cookies cannot be deactivated, but you can choose “Customize” or “Decline” to decline performance cookies.

If you agree, AWS and approved third parties will also use cookies to provide useful site features, remember your preferences, and display relevant content, including relevant advertising. To accept or decline all non-essential cookies, choose “Accept” or “Decline.” To make more detailed choices, choose “Customize.”

ST_DistanceSphere - Amazon Redshift

ST_DistanceSphere

ST_DistanceSphere returns the distance between two point geometries lying on a sphere.

Syntax

ST_DistanceSphere(geom1, geom2)
ST_DistanceSphere(geom1, geom2, radius)

Arguments

geom1

A point value in degrees of data type GEOMETRY lying on a sphere. The first coordinate of the point is the longitude value. The second coordinate of the point is the latitude value. For 3DZ, 3DM, or 4D geometries, only the first two coordinates are used.

geom2

A point value in degrees of data type GEOMETRY lying on a sphere. The first coordinate of the point is the longitude value. The second coordinate of the point is the latitude value. For 3DZ, 3DM, or 4D geometries, only the first two coordinates are used.

radius

The radius of a sphere of data type DOUBLE PRECISION. If no radius is provided, the sphere defaults to Earth and the radius is computed from the World Geodetic System (WGS) 84 representation of the ellipsoid.

Return type

DOUBLE PRECISION in the same units as the radius. If no radius is provided, the distance is in meters.

If geom1 or geom2 is null or empty, then null is returned.

If no radius is provided, then the result is in meters along the Earth's surface.

If radius is a negative number, then an error is returned.

If geom1 and geom2 don't have the same value for the spatial reference system identifier (SRID), then an error is returned.

If geom1 or geom2 is not a point, then an error is returned.

Examples

The following example SQL computes the distance in kilometers between two points on Earth.

SELECT ROUND(ST_DistanceSphere(ST_Point(-122, 47), ST_Point(-122.1, 47.1))/ 1000, 0);
round ----------- 13

The following example SQL computes the distances in kilometers between three airport locations in Germany: Berlin Tegel (TXL), Munich International (MUC), and Frankfurt International (FRA).

WITH airports_raw(code,lon,lat) AS ( (SELECT 'MUC', 11.786111, 48.353889) UNION (SELECT 'FRA', 8.570556, 50.033333) UNION (SELECT 'TXL', 13.287778, 52.559722)), airports1(code,location) AS (SELECT code, ST_Point(lon, lat) FROM airports_raw), airports2(code,location) AS (SELECT * from airports1) SELECT (airports1.code || ' <-> ' || airports2.code) AS airports, round(ST_DistanceSphere(airports1.location, airports2.location) / 1000, 0) AS distance_in_km FROM airports1, airports2 WHERE airports1.code < airports2.code ORDER BY 1;
airports | distance_in_km -------------+---------------- FRA <-> MUC | 299 FRA <-> TXL | 432 MUC <-> TXL | 480
PrivacySite termsCookie preferences
© 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved.