large.sql

ALTER
  TABLE countries
    DROP COLUMN IF EXISTS width,
    DROP COLUMN IF EXISTS height,
    ADD COLUMN width float,
    ADD COLUMN height float;

WITH parts AS (
  select gid, (st_dump(geog::geometry)).geom AS geom FROM countries
),

min_max AS (
  SELECT 
    gid,
    st_area(geom::geography) as area,
    st_xmin(geom) as lon_min,
    st_xmax(geom) as lon_max,
    (st_xmax(geom) + st_xmin(geom)) / 2 as lon_avg,
    st_ymin(geom) as lat_min,
    st_ymax(geom) as lat_max,
    (st_ymax(geom) + st_ymin(geom)) / 2 as lat_avg
  FROM parts
),

dimensions AS (
  SELECT
    gid,
    area,
    st_makeline(st_makepoint(lon_min, lat_avg), st_makepoint(lon_max, lat_avg)) as h_line,
    st_makeline(st_makepoint(lon_avg, lat_min), st_makepoint(lon_avg, lat_max)) as v_line
  FROM min_max
),

weighted_sizes AS (
  SELECT
    gid,
    area,
    st_length(h_line::geography) as weighted_width,
    st_length(v_line::geography) as weighted_height
  FROM dimensions
),

sizes AS (
  SELECT
    gid,
    max(weighted_width) as width,
    max(weighted_height) as height
  FROM weighted_sizes
  GROUP BY gid
  )

UPDATE countries
  SET
    width = sizes.width / 1000,
    height = sizes.height / 1000
  FROM sizes
  WHERE sizes.gid = countries.gid