{"id":1265,"date":"2019-05-30T23:00:01","date_gmt":"2019-05-30T21:00:01","guid":{"rendered":"https:\/\/hannes.enjoys.it\/blog\/?p=1265"},"modified":"2019-05-30T23:03:02","modified_gmt":"2019-05-30T21:03:02","slug":"calculating-the-metric-distance-between-latitudes-with-postgis","status":"publish","type":"post","link":"https:\/\/hannes.enjoys.it\/blog\/2019\/05\/calculating-the-metric-distance-between-latitudes-with-postgis\/","title":{"rendered":"Calculating the metric distance between latitudes with PostGIS"},"content":{"rendered":"\n<p>Postgres&#8217; WITH clauses and window functions are <em>so<\/em> awesome.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- generate values from -90 to 90, increment by 1\nWITH values AS (\n  SELECT generate_series AS latitude\n  FROM generate_series(-90, 90)\n),\n-- create a geographic point each\npoints AS (\n  SELECT ST_MakePoint(0, latitude)::geography AS point FROM values\n)\nSELECT\n  -- latitude values of subsequent points\n  format(\n    '%s\u00b0 to %s\u00b0',\n    ST_Y(point::geometry),\n    ST_Y(lag(point::geometry) OVER ())\n  ) AS latitudes,\n  -- geographic distance between subsequent points, formatted to kilometers\n  format(\n    '%s km',\n    to_char(ST_Distance(point, lag(point) OVER ())\/1000, '999D99')\n  ) AS distance\nFROM points\nOFFSET 1  -- skip the first row, no lag there\n;\n\n    latitudes  |  distance  \n --------------+------------\n  -89\u00b0 to -90\u00b0 |  111.69 km\n  -88\u00b0 to -89\u00b0 |  111.69 km\n  -87\u00b0 to -88\u00b0 |  111.69 km\n  -86\u00b0 to -87\u00b0 |  111.69 km\n  -85\u00b0 to -86\u00b0 |  111.69 km\n  -84\u00b0 to -85\u00b0 |  111.68 km\n  -83\u00b0 to -84\u00b0 |  111.68 km\n  -82\u00b0 to -83\u00b0 |  111.67 km\n  -81\u00b0 to -82\u00b0 |  111.67 km\n  -80\u00b0 to -81\u00b0 |  111.66 km\n  -79\u00b0 to -80\u00b0 |  111.66 km\n  -78\u00b0 to -79\u00b0 |  111.65 km\n  -77\u00b0 to -78\u00b0 |  111.64 km\n  -76\u00b0 to -77\u00b0 |  111.63 km\n  -75\u00b0 to -76\u00b0 |  111.62 km\n  -74\u00b0 to -75\u00b0 |  111.61 km\n  -73\u00b0 to -74\u00b0 |  111.60 km\n  -72\u00b0 to -73\u00b0 |  111.59 km\n  -71\u00b0 to -72\u00b0 |  111.58 km\n  -70\u00b0 to -71\u00b0 |  111.57 km\n  -69\u00b0 to -70\u00b0 |  111.56 km\n  -68\u00b0 to -69\u00b0 |  111.54 km\n  -67\u00b0 to -68\u00b0 |  111.53 km\n  -66\u00b0 to -67\u00b0 |  111.51 km\n  -65\u00b0 to -66\u00b0 |  111.50 km\n  -64\u00b0 to -65\u00b0 |  111.49 km\n  -63\u00b0 to -64\u00b0 |  111.47 km\n  -62\u00b0 to -63\u00b0 |  111.45 km\n  -61\u00b0 to -62\u00b0 |  111.44 km\n  -60\u00b0 to -61\u00b0 |  111.42 km\n  -59\u00b0 to -60\u00b0 |  111.40 km\n  -58\u00b0 to -59\u00b0 |  111.39 km\n  -57\u00b0 to -58\u00b0 |  111.37 km\n  -56\u00b0 to -57\u00b0 |  111.35 km\n  -55\u00b0 to -56\u00b0 |  111.33 km\n  -54\u00b0 to -55\u00b0 |  111.31 km\n  -53\u00b0 to -54\u00b0 |  111.30 km\n  -52\u00b0 to -53\u00b0 |  111.28 km\n  -51\u00b0 to -52\u00b0 |  111.26 km\n  -50\u00b0 to -51\u00b0 |  111.24 km\n  -49\u00b0 to -50\u00b0 |  111.22 km\n  -48\u00b0 to -49\u00b0 |  111.20 km\n  -47\u00b0 to -48\u00b0 |  111.18 km\n  -46\u00b0 to -47\u00b0 |  111.16 km\n  -45\u00b0 to -46\u00b0 |  111.14 km\n  -44\u00b0 to -45\u00b0 |  111.12 km\n  -43\u00b0 to -44\u00b0 |  111.10 km\n  -42\u00b0 to -43\u00b0 |  111.08 km\n  -41\u00b0 to -42\u00b0 |  111.06 km\n  -40\u00b0 to -41\u00b0 |  111.04 km\n  -39\u00b0 to -40\u00b0 |  111.03 km\n  -38\u00b0 to -39\u00b0 |  111.01 km\n  -37\u00b0 to -38\u00b0 |  110.99 km\n  -36\u00b0 to -37\u00b0 |  110.97 km\n  -35\u00b0 to -36\u00b0 |  110.95 km\n  -34\u00b0 to -35\u00b0 |  110.93 km\n  -33\u00b0 to -34\u00b0 |  110.91 km\n  -32\u00b0 to -33\u00b0 |  110.90 km\n  -31\u00b0 to -32\u00b0 |  110.88 km\n  -30\u00b0 to -31\u00b0 |  110.86 km\n  -29\u00b0 to -30\u00b0 |  110.84 km\n  -28\u00b0 to -29\u00b0 |  110.83 km\n  -27\u00b0 to -28\u00b0 |  110.81 km\n  -26\u00b0 to -27\u00b0 |  110.80 km\n  -25\u00b0 to -26\u00b0 |  110.78 km\n  -24\u00b0 to -25\u00b0 |  110.77 km\n  -23\u00b0 to -24\u00b0 |  110.75 km\n  -22\u00b0 to -23\u00b0 |  110.74 km\n  -21\u00b0 to -22\u00b0 |  110.72 km\n  -20\u00b0 to -21\u00b0 |  110.71 km\n  -19\u00b0 to -20\u00b0 |  110.70 km\n  -18\u00b0 to -19\u00b0 |  110.69 km\n  -17\u00b0 to -18\u00b0 |  110.67 km\n  -16\u00b0 to -17\u00b0 |  110.66 km\n  -15\u00b0 to -16\u00b0 |  110.65 km\n  -14\u00b0 to -15\u00b0 |  110.64 km\n  -13\u00b0 to -14\u00b0 |  110.63 km\n  -12\u00b0 to -13\u00b0 |  110.63 km\n  -11\u00b0 to -12\u00b0 |  110.62 km\n  -10\u00b0 to -11\u00b0 |  110.61 km\n  -9\u00b0 to -10\u00b0  |  110.60 km\n  -8\u00b0 to -9\u00b0   |  110.60 km\n  -7\u00b0 to -8\u00b0   |  110.59 km\n  -6\u00b0 to -7\u00b0   |  110.59 km\n  -5\u00b0 to -6\u00b0   |  110.58 km\n  -4\u00b0 to -5\u00b0   |  110.58 km\n  -3\u00b0 to -4\u00b0   |  110.58 km\n  -2\u00b0 to -3\u00b0   |  110.58 km\n  -1\u00b0 to -2\u00b0   |  110.58 km\n  0\u00b0 to -1\u00b0    |  110.57 km\n  1\u00b0 to 0\u00b0     |  110.57 km\n  2\u00b0 to 1\u00b0     |  110.58 km\n  3\u00b0 to 2\u00b0     |  110.58 km\n  4\u00b0 to 3\u00b0     |  110.58 km\n  5\u00b0 to 4\u00b0     |  110.58 km\n  6\u00b0 to 5\u00b0     |  110.58 km\n  7\u00b0 to 6\u00b0     |  110.59 km\n  8\u00b0 to 7\u00b0     |  110.59 km\n  9\u00b0 to 8\u00b0     |  110.60 km\n  10\u00b0 to 9\u00b0    |  110.60 km\n  11\u00b0 to 10\u00b0   |  110.61 km\n  12\u00b0 to 11\u00b0   |  110.62 km\n  13\u00b0 to 12\u00b0   |  110.63 km\n  14\u00b0 to 13\u00b0   |  110.63 km\n  15\u00b0 to 14\u00b0   |  110.64 km\n  16\u00b0 to 15\u00b0   |  110.65 km\n  17\u00b0 to 16\u00b0   |  110.66 km\n  18\u00b0 to 17\u00b0   |  110.67 km\n  19\u00b0 to 18\u00b0   |  110.69 km\n  20\u00b0 to 19\u00b0   |  110.70 km\n  21\u00b0 to 20\u00b0   |  110.71 km\n  22\u00b0 to 21\u00b0   |  110.72 km\n  23\u00b0 to 22\u00b0   |  110.74 km\n  24\u00b0 to 23\u00b0   |  110.75 km\n  25\u00b0 to 24\u00b0   |  110.77 km\n  26\u00b0 to 25\u00b0   |  110.78 km\n  27\u00b0 to 26\u00b0   |  110.80 km\n  28\u00b0 to 27\u00b0   |  110.81 km\n  29\u00b0 to 28\u00b0   |  110.83 km\n  30\u00b0 to 29\u00b0   |  110.84 km\n  31\u00b0 to 30\u00b0   |  110.86 km\n  32\u00b0 to 31\u00b0   |  110.88 km\n  33\u00b0 to 32\u00b0   |  110.90 km\n  34\u00b0 to 33\u00b0   |  110.91 km\n  35\u00b0 to 34\u00b0   |  110.93 km\n  36\u00b0 to 35\u00b0   |  110.95 km\n  37\u00b0 to 36\u00b0   |  110.97 km\n  38\u00b0 to 37\u00b0   |  110.99 km\n  39\u00b0 to 38\u00b0   |  111.01 km\n  40\u00b0 to 39\u00b0   |  111.03 km\n  41\u00b0 to 40\u00b0   |  111.04 km\n  42\u00b0 to 41\u00b0   |  111.06 km\n  43\u00b0 to 42\u00b0   |  111.08 km\n  44\u00b0 to 43\u00b0   |  111.10 km\n  45\u00b0 to 44\u00b0   |  111.12 km\n  46\u00b0 to 45\u00b0   |  111.14 km\n  47\u00b0 to 46\u00b0   |  111.16 km\n  48\u00b0 to 47\u00b0   |  111.18 km\n  49\u00b0 to 48\u00b0   |  111.20 km\n  50\u00b0 to 49\u00b0   |  111.22 km\n  51\u00b0 to 50\u00b0   |  111.24 km\n  52\u00b0 to 51\u00b0   |  111.26 km\n  53\u00b0 to 52\u00b0   |  111.28 km\n  54\u00b0 to 53\u00b0   |  111.30 km\n  55\u00b0 to 54\u00b0   |  111.31 km\n  56\u00b0 to 55\u00b0   |  111.33 km\n  57\u00b0 to 56\u00b0   |  111.35 km\n  58\u00b0 to 57\u00b0   |  111.37 km\n  59\u00b0 to 58\u00b0   |  111.39 km\n  60\u00b0 to 59\u00b0   |  111.40 km\n  61\u00b0 to 60\u00b0   |  111.42 km\n  62\u00b0 to 61\u00b0   |  111.44 km\n  63\u00b0 to 62\u00b0   |  111.45 km\n  64\u00b0 to 63\u00b0   |  111.47 km\n  65\u00b0 to 64\u00b0   |  111.49 km\n  66\u00b0 to 65\u00b0   |  111.50 km\n  67\u00b0 to 66\u00b0   |  111.51 km\n  68\u00b0 to 67\u00b0   |  111.53 km\n  69\u00b0 to 68\u00b0   |  111.54 km\n  70\u00b0 to 69\u00b0   |  111.56 km\n  71\u00b0 to 70\u00b0   |  111.57 km\n  72\u00b0 to 71\u00b0   |  111.58 km\n  73\u00b0 to 72\u00b0   |  111.59 km\n  74\u00b0 to 73\u00b0   |  111.60 km\n  75\u00b0 to 74\u00b0   |  111.61 km\n  76\u00b0 to 75\u00b0   |  111.62 km\n  77\u00b0 to 76\u00b0   |  111.63 km\n  78\u00b0 to 77\u00b0   |  111.64 km\n  79\u00b0 to 78\u00b0   |  111.65 km\n  80\u00b0 to 79\u00b0   |  111.66 km\n  81\u00b0 to 80\u00b0   |  111.66 km\n  82\u00b0 to 81\u00b0   |  111.67 km\n  83\u00b0 to 82\u00b0   |  111.67 km\n  84\u00b0 to 83\u00b0   |  111.68 km\n  85\u00b0 to 84\u00b0   |  111.68 km\n  86\u00b0 to 85\u00b0   |  111.69 km\n  87\u00b0 to 86\u00b0   |  111.69 km\n  88\u00b0 to 87\u00b0   |  111.69 km\n  89\u00b0 to 88\u00b0   |  111.69 km\n  90\u00b0 to 89\u00b0   |  111.69 km<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\"><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Postgres&#8217; WITH clauses and window functions are so awesome.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,49,51,50],"tags":[],"class_list":["post-1265","post","type-post","status-publish","format-standard","hentry","category-gis","category-postgis","category-postgresql","category-sql"],"_links":{"self":[{"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/posts\/1265","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/comments?post=1265"}],"version-history":[{"count":2,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/posts\/1265\/revisions"}],"predecessor-version":[{"id":1267,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/posts\/1265\/revisions\/1267"}],"wp:attachment":[{"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/media?parent=1265"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/categories?post=1265"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/tags?post=1265"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}