-- Isolate building features and height attributes
WITH buildings AS (
SELECT 
  feature_type, 
  osm_id, 
  osm_timestamp, 
  ST_Centroid(geometry) AS centroid, 
  (
    SELECT 
      value 
    FROM UNNEST(all_tags) 
    WHERE key='name'
  ) AS name,
  (
    SELECT 
      value 
    FROM UNNEST(all_tags) 
    WHERE key='height'
  ) AS height
FROM `bigquery-public-data.geo_openstreetmap.planet_features`
WHERE ('building') IN (
  SELECT 
    (key) 
  FROM UNNEST(all_tags))
)--,

-- Calculate local ratios
--ratios AS (
SELECT
  b1.osm_id,
  b1.height,
  MAX(ST_Y(b1.centroid)) AS lat,
  MAX(ST_X(b1.centroid)) AS lon,
  count(b2.height) AS count_local_heights,
  AVG(SAFE_CAST(b2.height AS FLOAT64)) AS avg_local_height,
  SAFE_CAST(b1.height AS FLOAT64)/AVG(SAFE_CAST(b2.height AS FLOAT64)) AS local_ratio
FROM buildings b1
-- Set average from all buildings within 2km
JOIN buildings b2 ON ST_Distance(b1.centroid,b2.centroid) < 2000
WHERE SAFE_CAST(b1.height AS FLOAT64) > 0
AND SAFE_CAST(b2.height AS FLOAT64) > 0
GROUP BY b1.osm_id,b1.height
--)

-- Select only buildings that are 10x+ taller than the local average
-- (Obv tweak as needed)
/*
SELECT
  *
FROM ratios
WHERE local_ratio >= 10
*/