Enrich Roof Detections with Buildings and Canopy Height

matched_table = f"examples_temp.sam3_db.roof_footprints_{AOI_NAME}"

sedona.sql(f"""
    WITH detections AS (
      SELECT
        ROW_NUMBER() OVER (ORDER BY bbox_score DESC, area_m2 DESC, ST_AsText(geometry)) AS detection_id,
        geometry,
        area_m2,
        bbox_score
      FROM examples_temp.sam3_db.sam3_{AOI_NAME}
      WHERE layer = 'roofs'
    ),
    candidates AS (
      SELECT
        d.detection_id,
        b.id                                                     AS building_id,
        b.class                                                  AS building_class,
        b.height                                                 AS building_height_m,
        b.roof_material,
        b.roof_shape,
        ST_AreaSpheroid(ST_Intersection(d.geometry, b.geometry))  AS overlap_m2
      FROM detections d
      JOIN wherobots_open_data.overture_maps_foundation.buildings_building b
        ON ST_Intersects(d.geometry, b.geometry)
    ), ranked AS (
      SELECT *,
        COUNT(*) OVER (PARTITION BY detection_id)                 AS footprints_touched,
        ROW_NUMBER() OVER (
          PARTITION BY detection_id
          ORDER BY overlap_m2 DESC, building_id
        )                                                         AS overlap_rank
      FROM candidates
      WHERE overlap_m2 > 0
    ), best AS (
      SELECT * FROM ranked WHERE overlap_rank = 1
    )
    SELECT
      d.detection_id, d.geometry, d.area_m2, d.bbox_score,
      b.building_id, b.building_class, b.building_height_m,
      b.roof_material, b.roof_shape, b.overlap_m2,
      COALESCE(b.footprints_touched, 0) AS footprints_touched
    FROM detections d
    LEFT JOIN best b ON d.detection_id = b.detection_id
""").writeTo(matched_table).createOrReplace()

print(f"{matched_table}: {sedona.table(matched_table).count():,} roof detections matched")

sedona.sql(f"""
    SELECT
      COUNT(*)                                                AS roof_detections,
      COUNT(DISTINCT building_id)                             AS distinct_buildings,
      SUM(CASE WHEN building_id IS NULL THEN 1 ELSE 0 END)    AS no_footprint,
      SUM(CASE WHEN footprints_touched > 1 THEN 1 ELSE 0 END) AS spans_several_footprints,
      ROUND(100.0 * COUNT(DISTINCT CASE WHEN roof_material IS NOT NULL THEN building_id END)
            / NULLIF(COUNT(DISTINCT building_id), 0), 1)
                                                              AS roof_material_pct,
      ROUND(100.0 * COUNT(DISTINCT CASE WHEN roof_shape IS NOT NULL THEN building_id END)
            / NULLIF(COUNT(DISTINCT building_id), 0), 1)
                                                              AS roof_shape_pct
    FROM {matched_table}
""").show()

sedona.sql("""
    WITH roofs AS (
      SELECT
        policy_id,
        roof_area_m2,
        ST_AsText(geometry) AS roof_wkt,
        ST_Buffer(geometry, 10, true) AS zone  -- 10 m around the roof, in meters
      FROM scored_roofs
    ), canopy_by_roof AS (
      SELECT r.roof_wkt,
             MAX(RS_ZonalStats(c.rast, r.zone, 1, 'max', true)) AS max_canopy_height_m
      FROM roofs r
      JOIN wherobots_open_data.meta_canopy_height.global_v2 c
        ON RS_Intersects(c.rast, r.zone)
      GROUP BY r.roof_wkt
    )
    SELECT r.policy_id, r.roof_area_m2, c.max_canopy_height_m
    FROM roofs r
    LEFT JOIN canopy_by_roof c ON r.roof_wkt = c.roof_wkt
    ORDER BY c.max_canopy_height_m DESC
""").show()