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