Category Archives: postgis

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

					

QGIS: “no result set” with PostGIS

If QGIS tells you “no result set” while you are playing around with PostGIS query layers. If your query works fine in the DB Manager but “Load as Layer” fails (and not silently with “invalid PostgreSQL layer”).

Try turning it off and on again. Restart QGIS. It just might save you many minutes of unreasonable frustration.