GEOGRAPHY_DISTANCE

This function takes in two geospatial objects and measures the shortest distance between them, in meters, using the standard metric for distance on a sphere.

Syntax

GEOGRAPHY_DISTANCE ( geo1, geo2 )

Arguments

  • geo1, geo2: any valid geospatial object or WKT string: path, point or polygon.

Return Type

A double. The measured distance, in meters.

Examples

CREATE ROWSTORE TABLE neighborhoods (
id INT UNSIGNED NOT NULL PRIMARY KEY,
name VARCHAR(64) NOT NULL,
population INT UNSIGNED NOT NULL,
shape GEOGRAPHY NOT NULL,
centroid GEOGRAPHYPOINT NOT NULL,
index (shape) WITH (resolution = 8),
index (centroid)
);
INSERT INTO neighborhoods VALUES
(1, "Hamilton", 12346, "POLYGON((1 1,2 1,2 2, 1 2, 1 1))","POINT(1.5 1.5)"),
(2, "Probability Bay", 263951, "POLYGON((5 1,6 1,6 2,5 2,5 1))", "POINT(5.5 1.5)"),
(3, "Decentralized Park", 29265, "POLYGON((5 5,6 5,6 6,5 6,5 5))", "POINT(5.5 5.5)"),
(4, "Axiom Township", 845696, "POLYGON((1 5,2 5,2 6,1 6,1 5))", "POINT(1.5 5.5)"),
(5, "Elegant Island ", 987654, "POLYGON((3 3,4 3,4 4,3 4,3 3))", "POINT(3.5 3.5)");
SELECT b.name AS neighbor,
ROUND(GEOGRAPHY_DISTANCE(a.shape, b.shape), 0) AS distance_from_border
FROM neighborhoods a, neighborhoods b
WHERE a.id = 2
ORDER BY 1;
+---------------------+----------------------+
| neighbor            | distance_from_border |
+---------------------+----------------------+
| Axiom Township      | 471030.0             |
| Decentralized Park  | 333399.0             |
| Elegant Island      | 157090.0             |
| Hamilton            | 333195.0             |
| Probability Bay     | 0.0                  |
+---------------------+----------------------+

For more information about working with geospatial data, see Working with Geospatial Features.

Last modified: February 23, 2023

Was this article helpful?