Skip to content

Instantly share code, notes, and snippets.

@wboykinm

wboykinm/osm_building_anomalies.sql

Last active July 22, 2022 22:46
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save wboykinm/ef6d805c4f47e1dd9b89bd08d5ac5826 to your computer and use it in GitHub Desktop.
Save wboykinm/ef6d805c4f47e1dd9b89bd08d5ac5826 to your computer and use it in GitHub Desktop.
Download ZIP
OSM building height anomaly detection, using BigQuery snapshot (https://console.cloud.google.com/marketplace/product/openstreetmap/geo-openstreetmap)
Raw
osm_building_anomalies.sql
-- 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
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment