Calculating the metric distance between latitudes with PostGIS

Postgres’ WITH clauses and window functions are so awesome.

-- generate values from -90 to 90, increment by 1
WITH values AS (
  SELECT generate_series AS latitude
  FROM generate_series(-90, 90)
),
-- create a geographic point each
points AS (
  SELECT ST_MakePoint(0, latitude)::geography AS point FROM values
)
SELECT
  -- latitude values of subsequent points
  format(
    '%s° to %s°',
    ST_Y(point::geometry),
    ST_Y(lag(point::geometry) OVER ())
  ) AS latitudes,
  -- geographic distance between subsequent points, formatted to kilometers
  format(
    '%s km',
    to_char(ST_Distance(point, lag(point) OVER ())/1000, '999D99')
  ) AS distance
FROM points
OFFSET 1  -- skip the first row, no lag there
;

    latitudes  |  distance  
 --------------+------------
  -89° to -90° |  111.69 km
  -88° to -89° |  111.69 km
  -87° to -88° |  111.69 km
  -86° to -87° |  111.69 km
  -85° to -86° |  111.69 km
  -84° to -85° |  111.68 km
  -83° to -84° |  111.68 km
  -82° to -83° |  111.67 km
  -81° to -82° |  111.67 km
  -80° to -81° |  111.66 km
  -79° to -80° |  111.66 km
  -78° to -79° |  111.65 km
  -77° to -78° |  111.64 km
  -76° to -77° |  111.63 km
  -75° to -76° |  111.62 km
  -74° to -75° |  111.61 km
  -73° to -74° |  111.60 km
  -72° to -73° |  111.59 km
  -71° to -72° |  111.58 km
  -70° to -71° |  111.57 km
  -69° to -70° |  111.56 km
  -68° to -69° |  111.54 km
  -67° to -68° |  111.53 km
  -66° to -67° |  111.51 km
  -65° to -66° |  111.50 km
  -64° to -65° |  111.49 km
  -63° to -64° |  111.47 km
  -62° to -63° |  111.45 km
  -61° to -62° |  111.44 km
  -60° to -61° |  111.42 km
  -59° to -60° |  111.40 km
  -58° to -59° |  111.39 km
  -57° to -58° |  111.37 km
  -56° to -57° |  111.35 km
  -55° to -56° |  111.33 km
  -54° to -55° |  111.31 km
  -53° to -54° |  111.30 km
  -52° to -53° |  111.28 km
  -51° to -52° |  111.26 km
  -50° to -51° |  111.24 km
  -49° to -50° |  111.22 km
  -48° to -49° |  111.20 km
  -47° to -48° |  111.18 km
  -46° to -47° |  111.16 km
  -45° to -46° |  111.14 km
  -44° to -45° |  111.12 km
  -43° to -44° |  111.10 km
  -42° to -43° |  111.08 km
  -41° to -42° |  111.06 km
  -40° to -41° |  111.04 km
  -39° to -40° |  111.03 km
  -38° to -39° |  111.01 km
  -37° to -38° |  110.99 km
  -36° to -37° |  110.97 km
  -35° to -36° |  110.95 km
  -34° to -35° |  110.93 km
  -33° to -34° |  110.91 km
  -32° to -33° |  110.90 km
  -31° to -32° |  110.88 km
  -30° to -31° |  110.86 km
  -29° to -30° |  110.84 km
  -28° to -29° |  110.83 km
  -27° to -28° |  110.81 km
  -26° to -27° |  110.80 km
  -25° to -26° |  110.78 km
  -24° to -25° |  110.77 km
  -23° to -24° |  110.75 km
  -22° to -23° |  110.74 km
  -21° to -22° |  110.72 km
  -20° to -21° |  110.71 km
  -19° to -20° |  110.70 km
  -18° to -19° |  110.69 km
  -17° to -18° |  110.67 km
  -16° to -17° |  110.66 km
  -15° to -16° |  110.65 km
  -14° to -15° |  110.64 km
  -13° to -14° |  110.63 km
  -12° to -13° |  110.63 km
  -11° to -12° |  110.62 km
  -10° to -11° |  110.61 km
  -9° to -10°  |  110.60 km
  -8° to -9°   |  110.60 km
  -7° to -8°   |  110.59 km
  -6° to -7°   |  110.59 km
  -5° to -6°   |  110.58 km
  -4° to -5°   |  110.58 km
  -3° to -4°   |  110.58 km
  -2° to -3°   |  110.58 km
  -1° to -2°   |  110.58 km
  0° to -1°    |  110.57 km
  1° to 0°     |  110.57 km
  2° to 1°     |  110.58 km
  3° to 2°     |  110.58 km
  4° to 3°     |  110.58 km
  5° to 4°     |  110.58 km
  6° to 5°     |  110.58 km
  7° to 6°     |  110.59 km
  8° to 7°     |  110.59 km
  9° to 8°     |  110.60 km
  10° to 9°    |  110.60 km
  11° to 10°   |  110.61 km
  12° to 11°   |  110.62 km
  13° to 12°   |  110.63 km
  14° to 13°   |  110.63 km
  15° to 14°   |  110.64 km
  16° to 15°   |  110.65 km
  17° to 16°   |  110.66 km
  18° to 17°   |  110.67 km
  19° to 18°   |  110.69 km
  20° to 19°   |  110.70 km
  21° to 20°   |  110.71 km
  22° to 21°   |  110.72 km
  23° to 22°   |  110.74 km
  24° to 23°   |  110.75 km
  25° to 24°   |  110.77 km
  26° to 25°   |  110.78 km
  27° to 26°   |  110.80 km
  28° to 27°   |  110.81 km
  29° to 28°   |  110.83 km
  30° to 29°   |  110.84 km
  31° to 30°   |  110.86 km
  32° to 31°   |  110.88 km
  33° to 32°   |  110.90 km
  34° to 33°   |  110.91 km
  35° to 34°   |  110.93 km
  36° to 35°   |  110.95 km
  37° to 36°   |  110.97 km
  38° to 37°   |  110.99 km
  39° to 38°   |  111.01 km
  40° to 39°   |  111.03 km
  41° to 40°   |  111.04 km
  42° to 41°   |  111.06 km
  43° to 42°   |  111.08 km
  44° to 43°   |  111.10 km
  45° to 44°   |  111.12 km
  46° to 45°   |  111.14 km
  47° to 46°   |  111.16 km
  48° to 47°   |  111.18 km
  49° to 48°   |  111.20 km
  50° to 49°   |  111.22 km
  51° to 50°   |  111.24 km
  52° to 51°   |  111.26 km
  53° to 52°   |  111.28 km
  54° to 53°   |  111.30 km
  55° to 54°   |  111.31 km
  56° to 55°   |  111.33 km
  57° to 56°   |  111.35 km
  58° to 57°   |  111.37 km
  59° to 58°   |  111.39 km
  60° to 59°   |  111.40 km
  61° to 60°   |  111.42 km
  62° to 61°   |  111.44 km
  63° to 62°   |  111.45 km
  64° to 63°   |  111.47 km
  65° to 64°   |  111.49 km
  66° to 65°   |  111.50 km
  67° to 66°   |  111.51 km
  68° to 67°   |  111.53 km
  69° to 68°   |  111.54 km
  70° to 69°   |  111.56 km
  71° to 70°   |  111.57 km
  72° to 71°   |  111.58 km
  73° to 72°   |  111.59 km
  74° to 73°   |  111.60 km
  75° to 74°   |  111.61 km
  76° to 75°   |  111.62 km
  77° to 76°   |  111.63 km
  78° to 77°   |  111.64 km
  79° to 78°   |  111.65 km
  80° to 79°   |  111.66 km
  81° to 80°   |  111.66 km
  82° to 81°   |  111.67 km
  83° to 82°   |  111.67 km
  84° to 83°   |  111.68 km
  85° to 84°   |  111.68 km
  86° to 85°   |  111.69 km
  87° to 86°   |  111.69 km
  88° to 87°   |  111.69 km
  89° to 88°   |  111.69 km
  90° to 89°   |  111.69 km

					

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.