Skip to main content

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.

distance.png

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                  |
+---------------------+----------------------+
In this section: