gistfile1.txt

WITH weights AS (
  SELECT 
    0.24 AS w_adm0_crime,
    0.18 AS w_adm1_violent,
    0.12 AS w_adm1_property,
    0.10 AS w_structure,
    0.04 AS w_population,
    0.08 AS w_property_value
),
base_and_range AS (
  SELECT
    100.0 - (
      (AVG(DISTINCT legatum_prosperity_score) +
       AVG(DISTINCT legatum_safety_and_security) +
       AVG(DISTINCT legatum_property_crime)) / 3.0
    ) AS base,
    
    MIN((
      ((COALESCE(geosure_adm0_crime_class, 6.5) - 1) / 11.0) * 0.20 +
      ((COALESCE(geosure_adm1_violent_crime_class, 6.5) - 1) / 11.0) * 0.12 +
      ((COALESCE(geosure_adm1_property_crime_class, 6.5) - 1) / 11.0) * 0.12 +
      ((COALESCE(geosure_structure_class, 6.5) - 1) / 11.0) * 0.05 +
      ((COALESCE(geosure_population_class, 6.5) - 1) / 11.0) * 0.05 +
      ((12.0 - COALESCE(geosure_adm1_property_value_class, 6.5)) / 11.0) * 0.06
    )) AS min_risk,

    MAX((
      ((COALESCE(geosure_adm0_crime_class, 6.5) - 1) / 11.0) * 0.20 +
      ((COALESCE(geosure_adm1_violent_crime_class, 6.5) - 1) / 11.0) * 0.12 +
      ((COALESCE(geosure_adm1_property_crime_class, 6.5) - 1) / 11.0) * 0.12 +
      ((COALESCE(geosure_structure_class, 6.5) - 1) / 11.0) * 0.05 +
      ((COALESCE(geosure_population_class, 6.5) - 1) / 11.0) * 0.05 +
      ((12.0 - COALESCE(geosure_adm1_property_value_class, 6.5)) / 11.0) * 0.06
    )) AS max_risk
  FROM h3_l8_union_cities_urban
  WHERE adm0_iso = 'USA'
),
normalized AS (
  SELECT
    t.h3,
    (
      ((COALESCE(t.geosure_adm0_crime_class, 6.5) - 1) / 11.0) * w.w_adm0_crime +
      ((COALESCE(t.geosure_adm1_violent_crime_class, 6.5) - 1) / 11.0) * w.w_adm1_violent +
      ((COALESCE(t.geosure_adm1_property_crime_class, 6.5) - 1) / 11.0) * w.w_adm1_property +
      ((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 +
      ((12.0 - COALESCE(t.geosure_adm1_property_value_class, 6.5)) / 11.0) * w.w_property_value
    ) AS us_risk_raw
  FROM h3_l8_union_cities_urban t, weights w
  WHERE t.adm0_iso = 'USA'
),
rescaled AS (
  SELECT 
    n.h3,
    ROUND(
      LEAST(GREATEST(
        b.base + ((n.us_risk_raw - b.min_risk) / NULLIF(b.max_risk - b.min_risk, 0) * 12.0 - 4.0),
        0), 100)
    )::integer AS geosure_adm0_risk
  FROM normalized n
  CROSS JOIN base_and_range b
)
UPDATE h3_l8_union_cities_urban t
SET geosure_adm0_risk = r.geosure_adm0_risk
FROM rescaled r
WHERE t.h3 = r.h3;