Screening NSW for data-centre land with a solar co-location check

python3 - <<'EOF'
import zn
con=zn.lake_con()
PP=zn.read('parcel_profile')
# ranked survivors and funnel summary
screen_sql = f"""
WITH screened AS (
 SELECT parcel_id, lot, plan_label, suburb, lga_name, zone_code, zone_name,
        round(area_m2 / 10000.0, 1) AS area_ha, lat, lng, h3_r9,
        tx_330kv_nearest_km, tx_nearest_kv, substation_nearest_km,
        road_arterial_nearest_km, road_arterial_nearest_name,
        persons_2km, persons_2km_fill,
        town_5k_50k_nearest_km, town_5k_50k_nearest_name,
        remoteness, slope_deg, elevation_m, ghi_kwh_m2_day,
        bushfire_prone,
        CASE WHEN flood_planning IS FALSE THEN 'assessed: outside mapped flood planning land'
             ELSE 'unassessed by this flood planning layer' END AS flood_status,
        alum_label
 FROM {PP}
 WHERE zone_group IN ('Rural','Industrial')
   AND area_m2 >= 200000
   AND lot <> ''
   AND tx_330kv_nearest_km <= 5
   AND substation_nearest_km <= 15
   AND road_arterial_nearest_km <= 3
   AND persons_2km < 200
   AND town_5k_50k_nearest_km <= 30
   AND slope_deg <= 5
   AND flood_planning IS NOT TRUE
)
SELECT * FROM screened ORDER BY area_ha DESC, parcel_id LIMIT 25
"""
con.execute(f"COPY ({screen_sql}) TO '/workspace/out/datacentre_top25.csv' (HEADER)")
summary_sql=f"""
WITH c AS (
 SELECT zone_group IN ('Rural','Industrial') AND area_m2 >= 200000 AND lot <> '' AS c1,
        tx_330kv_nearest_km <= 5 AS c2,
        substation_nearest_km <= 15 AS c3,
        road_arterial_nearest_km <= 3 AS c4,
        persons_2km < 200 AS c5,
        town_5k_50k_nearest_km <= 30 AS c6,
        slope_deg <= 5 AND flood_planning IS NOT TRUE AS c7,
        flood_planning, bushfire_prone, area_m2, ghi_kwh_m2_day
 FROM {PP}
), k AS (SELECT *, c1 AND c2 AND c3 AND c4 AND c5 AND c6 AND c7 AS kept FROM c)
SELECT 'NSW parcels in source' AS stage, count(*) AS parcels, NULL::DOUBLE AS median_area_ha, NULL::DOUBLE AS solar_min, NULL::DOUBLE AS solar_max, NULL::BIGINT AS bushfire_flagged, NULL::BIGINT AS flood_unassessed FROM k
UNION ALL SELECT 'Rural or industrial, >=20 ha, non-strata', count(*) FILTER(c1), NULL, NULL, NULL, NULL, NULL FROM k
UNION ALL SELECT 'Also within 5 km of 330 kV+ line', count(*) FILTER(c1 AND c2), NULL, NULL, NULL, NULL, NULL FROM k
UNION ALL SELECT 'Also within 15 km of transmission substation', count(*) FILTER(c1 AND c2 AND c3), NULL, NULL, NULL, NULL, NULL FROM k
UNION ALL SELECT 'Also within 3 km of sealed arterial/highway', count(*) FILTER(c1 AND c2 AND c3 AND c4), NULL, NULL, NULL, NULL, NULL FROM k
UNION ALL SELECT 'Also under 200 residents in 2 km screen', count(*) FILTER(c1 AND c2 AND c3 AND c4 AND c5), NULL, NULL, NULL, NULL, NULL FROM k
UNION ALL SELECT 'Also within 30 km of 5,000-50,000 town', count(*) FILTER(c1 AND c2 AND c3 AND c4 AND c5 AND c6), NULL, NULL, NULL, NULL, NULL FROM k
UNION ALL SELECT 'Final screened lots', count(*) FILTER(kept), median(area_m2) FILTER(kept)/10000.0, min(ghi_kwh_m2_day) FILTER(kept), max(ghi_kwh_m2_day) FILTER(kept), count(*) FILTER(kept AND bushfire_prone), count(*) FILTER(kept AND flood_planning IS NULL) FROM k
"""
con.execute(f"COPY ({summary_sql}) TO '/workspace/out/datacentre_funnel.csv' (HEADER)")
# grid context: 330kV+ line cells overlapping top25 extent, plus substations within 15 km of any top25 lot
base_lines=zn.read('transmission',file='lines_h3r9')
base_lines_dim=zn.read('transmission',file='lines')
base_sub=zn.read('transmission',file='substations')
grid_sql=f"""
WITH top AS (SELECT h3_r9 FROM read_csv_auto('/workspace/out/datacentre_top25.csv')),
line_cells AS (
 SELECT l.h3_r9, max(l.voltage_kv) AS voltage_kv,
        'transmission line' AS feature, string_agg(DISTINCT coalesce(d.name,'unnamed line'), '; ') AS label
 FROM {base_lines} l LEFT JOIN {base_lines_dim} d USING(line_id)
 JOIN top t USING(h3_r9)
 WHERE l.voltage_kv >= 330
 GROUP BY l.h3_r9
), sub AS (
 SELECT s.h3_r9, max(coalesce(s.voltage_kv,0)) AS voltage_kv,
        'substation' AS feature, string_agg(DISTINCT s.name, '; ') AS label
 FROM {base_sub} s
 JOIN (SELECT DISTINCT lat, lng FROM read_csv_auto('/workspace/out/datacentre_top25.csv')) p
   ON h3_great_circle_distance(s.lat,s.lng,p.lat,p.lng,'km') <= 15
 GROUP BY s.h3_r9
), both AS (
 SELECT * FROM line_cells UNION ALL SELECT * FROM sub
)
SELECT h3_r9, max(voltage_kv) AS voltage_kv, string_agg(DISTINCT feature, ' + ') AS feature, string_agg(DISTINCT label, '; ') AS label
FROM both GROUP BY h3_r9
"""
con.execute(f"COPY ({grid_sql}) TO '/workspace/out/datacentre_grid_context.csv' (HEADER)")
print(con.sql("SELECT * FROM '/workspace/out/datacentre_top25.csv' LIMIT 2"))
print(con.sql("SELECT * FROM '/workspace/out/datacentre_funnel.csv' ORDER BY parcels DESC LIMIT 1"))
print(con.sql("SELECT * FROM '/workspace/out/datacentre_grid_context.csv' LIMIT 2"))
EOF

python3 - <<'EOF'
import zn
con=zn.lake_con()
lines=zn.read('transmission',file='lines_h3r9')
dim=zn.read('transmission',file='lines')
sub=zn.read('transmission',file='substations')
sql=f"""
WITH top_sites AS (SELECT h3_r9 FROM read_csv_auto('/workspace/out/datacentre_top25.csv')),
line_cells AS (
 SELECT l.h3_r9, max(l.voltage_kv) AS voltage_kv,
        'transmission line' AS feature,
        string_agg(DISTINCT coalesce(d.name,'unnamed line'), '; ') AS label
 FROM {lines} l LEFT JOIN {dim} d USING(line_id)
 JOIN top_sites t USING(h3_r9)
 WHERE l.voltage_kv >= 330
 GROUP BY l.h3_r9
), substation_cells AS (
 SELECT s.h3_r9, max(coalesce(s.voltage_kv,0)) AS voltage_kv,
        'substation' AS feature,
        string_agg(DISTINCT s.name, '; ') AS label
 FROM {sub} s
 JOIN (SELECT DISTINCT lat, lng FROM read_csv_auto('/workspace/out/datacentre_top25.csv')) p
   ON h3_great_circle_distance(s.lat,s.lng,p.lat,p.lng,'km') <= 15
 GROUP BY s.h3_r9
), combined_features AS (
 SELECT * FROM line_cells
 UNION ALL
 SELECT * FROM substation_cells
)
SELECT h3_r9, max(voltage_kv) AS voltage_kv,
       string_agg(DISTINCT feature, ' + ') AS feature,
       string_agg(DISTINCT label, '; ') AS label
FROM combined_features
GROUP BY h3_r9
"""
con.execute(f"COPY ({sql}) TO '/workspace/out/datacentre_grid_context.csv' (HEADER)")
print(con.sql("SELECT count(*) AS cells FROM '/workspace/out/datacentre_grid_context.csv'"))
EOF

python3 - <<'EOF'
import duckdb
con=duckdb.connect()
con.execute("COPY (SELECT * EXCLUDE (h3_r9, lat, lng) FROM read_csv_auto('/workspace/out/datacentre_top25.csv')) TO '/workspace/out/datacentre_top25_reader.csv' (HEADER)
")
print(con.sql("SELECT * FROM read_csv_auto('/workspace/out/datacentre_funnel.csv')"))
print(con.sql("SELECT count(*) AS n, min(area_ha) AS min_ha, max(area_ha) AS max_ha, min(ghi_kwh_m2_day) AS min_ghi, max(ghi_kwh_m2_day) AS max_ghi, sum(CASE WHEN bushfire_prone THEN 1 ELSE 0 END) AS bushfire FROM read_csv_auto('/workspace/out/datacentre_top25.csv')"))
print(con.sql("SELECT parcel_id, suburb, lga_name, area_ha, zone_code, ghi_kwh_m2_day, bushfire_prone FROM read_csv_auto('/workspace/out/datacentre_top25.csv') LIMIT 5"))
EOF

python3 - <<'EOF'
import duckdb
con=duckdb.connect()
q="""COPY (SELECT * EXCLUDE (h3_r9, lat, lng) FROM read_csv_auto('/workspace/out/datacentre_top25.csv')) TO '/workspace/out/datacentre_top25_reader.csv' (HEADER)"""
con.execute(q)
print(con.sql("SELECT * FROM read_csv_auto('/workspace/out/datacentre_funnel.csv')"))
print(con.sql("SELECT count(*) AS n, min(area_ha) AS min_ha, max(area_ha) AS max_ha, min(ghi_kwh_m2_day) AS min_ghi, max(ghi_kwh_m2_day) AS max_ghi, sum(CASE WHEN bushfire_prone THEN 1 ELSE 0 END) AS bushfire FROM read_csv_auto('/workspace/out/datacentre_top25.csv')"))
EOF

python3 - <<'EOF'
import duckdb
con=duckdb.connect()
q="""COPY (SELECT suburb, lga_name, zone_code, zone_name, area_ha, tx_330kv_nearest_km, substation_nearest_km, road_arterial_nearest_km, persons_2km, town_5k_50k_nearest_name, town_5k_50k_nearest_km, ghi_kwh_m2_day, bushfire_prone, flood_status, parcel_id AS "Lot/DP" FROM read_csv_auto('/workspace/out/datacentre_top25.csv') ORDER BY area_ha DESC, parcel_id LIMIT 12) TO '/workspace/out/datacentre_top12_reader.csv' (HEADER)"""
con.execute(q)
print(con.sql("SELECT count(*) AS rows, min(area_ha) AS smallest_ha, max(area_ha) AS largest_ha FROM read_csv_auto('/workspace/out/datacentre_top12_reader.csv')"))
EOF