gistfile1.txt

WITH weights AS (
  SELECT 
    0.8 AS w_structure,
    0.4 AS w_population
),
base AS (
  SELECT
    100.0 - (
      (AVG(DISTINCT legatum_prosperity_score) +
       AVG(DISTINCT legatum_safety_and_security) +
       AVG(DISTINCT legatum_property_crime)) / 3.0
    ) AS base
  FROM h3_l8_union_cities_urban
  WHERE adm0_iso = 'SWE'
),
normalized AS (
  SELECT
    t.h3,
    (
      ((COALESCE(t.geosure_structure_class, 6.5) - 1) / 11.0) * w.w_structure +
      ((COALESCE(t.geosure_population_class, 6.5) - 1) / 11.0) * w.w_population
    ) AS risk_raw
  FROM h3_l8_union_cities_urban t, weights w
  WHERE t.adm0_iso = 'SWE'
),
rescaled AS (
  SELECT 
    n.h3,
    ROUND(LEAST(GREATEST(b.base + (n.risk_raw * 12.0 - 4.0), 0), 100))::integer AS geosure_adm0_risk
  FROM normalized n
  CROSS JOIN base b
)
UPDATE h3_l8_union_cities_urban t
SET geosure_adm0_risk = r.geosure_adm0_risk
FROM rescaled r
WHERE t.h3 = r.h3;