import%20marimo%0A%0A__generated_with%20%3D%20%220.23.9%22%0Aapp%20%3D%20marimo.App(width%3D%22full%22)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_()%3A%0A%20%20%20%20import%20marimo%20as%20mo%0A%0A%20%20%20%20return%20(mo%2C)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%20Crime%20CSV%20-%3E%20addresses%20-%3E%20H3%3A%20the%20actual%20ELT%0A%0A%20%20%20%20This%20notebook%20walks%20the%20production%20Z%C3%BCrich%20crime%20H3%20pipeline%20against%20the%20local%0A%20%20%20%20Postgres%20database%3A%0A%0A%20%20%20%20%60dbt%20seed%20crime_stats_raw.csv%60%20-%3E%20%60staging.stg_zurich__crime_stats%60%20-%3E%0A%20%20%20%20address%2Fstreet%20geometry%20matching%20-%3E%20weighted%20H3%20aggregation%20-%3E%0A%20%20%20%20%60mobility.zurich_crime_h3%60%20and%20%60tiles.crime_hexes%60.%0A%0A%20%20%20%20The%20important%20bit%3A%20the%20source%20is%20not%20incident%20points.%20It%20is%20a%20CSV%20of%20counts%20per%0A%20%20%20%20street%20section%20and%20daypart%2C%20so%20the%20ELT%20has%20to%20place%20each%20section%20onto%20real%20address%0A%20%20%20%20points%20when%20possible%2C%20fall%20back%20to%20whole-street%20geometry%20when%20needed%2C%20and%20then%20emit%0A%20%20%20%20area-level%20H3%20values%20rather%20than%20pretending%20every%20count%20belongs%20to%20one%20point.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_()%3A%0A%20%20%20%20import%20os%0A%0A%20%20%20%20import%20folium%0A%20%20%20%20import%20geopandas%20as%20gpd%0A%20%20%20%20import%20pandas%20as%20pd%0A%20%20%20%20from%20sqlalchemy%20import%20create_engine%2C%20text%0A%0A%20%20%20%20pw%20%3D%20os.getenv(%22POSTGRES_PASSWORD%22%2C%20%22%22)%0A%20%20%20%20auth%20%3D%20os.getenv(%22POSTGRES_USER%22%2C%20%22postgres%22)%20%2B%20(f%22%3A%7Bpw%7D%22%20if%20pw%20else%20%22%22)%0A%20%20%20%20DB_URL%20%3D%20(%0A%20%20%20%20%20%20%20%20f%22postgresql%2Bpsycopg%3A%2F%2F%7Bauth%7D%40%22%0A%20%20%20%20%20%20%20%20f%22%7Bos.getenv('POSTGRES_HOST'%2C%20'localhost')%7D%3A%7Bos.getenv('POSTGRES_PORT'%2C%20'5432')%7D%22%0A%20%20%20%20%20%20%20%20f%22%2F%7Bos.getenv('POSTGRES_DB'%2C%20'livemap')%7D%22%0A%20%20%20%20)%0A%20%20%20%20ENGINE%20%3D%20create_engine(DB_URL)%0A%0A%20%20%20%20def%20q(sql%2C%20**params)%3A%0A%20%20%20%20%20%20%20%20return%20pd.read_sql(text(sql)%2C%20ENGINE%2C%20params%3Dparams)%0A%0A%20%20%20%20def%20qg(sql%2C%20crs%3D4326%2C%20**params)%3A%0A%20%20%20%20%20%20%20%20return%20gpd.read_postgis(%0A%20%20%20%20%20%20%20%20%20%20%20%20text(sql)%2C%20ENGINE%2C%20geom_col%3D%22geometry%22%2C%20crs%3Dcrs%2C%20params%3Dparams%0A%20%20%20%20%20%20%20%20)%0A%0A%20%20%20%20RES%20%3D%209%0A%20%20%20%20STEP_M%20%3D%2010%0A%20%20%20%20return%20RES%2C%20STEP_M%2C%20folium%2C%20q%2C%20qg%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%200.%20Database%20contract%20check%0A%0A%20%20%20%20The%20notebook%20expects%20dbt-built%20relations%20in%20Postgres%2C%20including%20the%20%60h3%60%20and%0A%20%20%20%20%60h3_postgis%60%20extensions%20enabled%20by%20migrations.%20The%20first%20query%20locates%20the%20seeded%0A%20%20%20%20CSV%20table%20because%20dbt%20seeds%20live%20in%20the%20active%20target%20schema%2C%20usually%20%60public%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(q)%3A%0A%20%20%20%20seed_relation%20%3D%20q(%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20format('%25I.%25I'%2C%20table_schema%2C%20table_name)%20AS%20relation%0A%20%20%20%20%20%20%20%20FROM%20information_schema.tables%0A%20%20%20%20%20%20%20%20WHERE%20table_name%20%3D%20'crime_stats_raw'%0A%20%20%20%20%20%20%20%20ORDER%20BY%0A%20%20%20%20%20%20%20%20%20%20CASE%20table_schema%0A%20%20%20%20%20%20%20%20%20%20%20%20WHEN%20current_schema()%20THEN%200%0A%20%20%20%20%20%20%20%20%20%20%20%20WHEN%20'public'%20THEN%201%0A%20%20%20%20%20%20%20%20%20%20%20%20ELSE%202%0A%20%20%20%20%20%20%20%20%20%20END%0A%20%20%20%20%20%20%20%20LIMIT%201%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20).at%5B0%2C%20%22relation%22%5D%0A%0A%20%20%20%20required%20%3D%20q(%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20%20%20%20%20WITH%20expected(relation)%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20VALUES%0A%20%20%20%20%20%20%20%20%20%20%20%20('staging.stg_zurich__crime_stats')%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20('staging.stg_zurich__adressen')%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20('staging.stg_zurich__strassennamen')%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20('mobility.zurich_crime_h3')%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20('tiles.crime_hexes')%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20e.relation%2C%0A%20%20%20%20%20%20%20%20%20%20to_regclass(e.relation)%20IS%20NOT%20NULL%20AS%20exists%0A%20%20%20%20%20%20%20%20FROM%20expected%20e%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20%3Aseed_relation%2C%20to_regclass(%3Aseed_relation)%20IS%20NOT%20NULL%0A%20%20%20%20%20%20%20%20ORDER%20BY%20relation%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20seed_relation%3Dseed_relation%2C%0A%20%20%20%20)%0A%20%20%20%20required%0A%20%20%20%20return%20(seed_relation%2C)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20extname%2C%20extversion%0A%20%20%20%20%20%20%20%20FROM%20pg_extension%0A%20%20%20%20%20%20%20%20WHERE%20extname%20IN%20('postgis'%2C%20'h3'%2C%20'h3_postgis')%0A%20%20%20%20%20%20%20%20ORDER%20BY%20extname%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%201.%20Extract%2Fload%3A%20the%20input%20CSV%20as%20a%20Postgres%20seed%0A%0A%20%20%20%20The%20file%20%60dbt%2Fseeds%2Fcrime_stats_raw.csv%60%20is%20loaded%20by%20dbt%20as%20%60crime_stats_raw%60.%0A%20%20%20%20These%20are%20the%20raw%20delivered%20rows%3A%20one%20row%20names%20a%20street%20section%2C%20and%20the%20measure%0A%20%20%20%20columns%20are%20counts%20by%20daypart.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(q%2C%20seed_relation)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20*%0A%20%20%20%20%20%20%20%20FROM%20%7Bseed_relation%7D%0A%20%20%20%20%20%20%20%20ORDER%20BY%20total%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%2015%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(q%2C%20seed_relation)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20COUNT(*)%20AS%20csv_rows%2C%0A%20%20%20%20%20%20%20%20%20%20SUM(am)%20AS%20morning%2C%0A%20%20%20%20%20%20%20%20%20%20SUM(pm)%20AS%20afternoon%2C%0A%20%20%20%20%20%20%20%20%20%20SUM(night)%20AS%20night%2C%0A%20%20%20%20%20%20%20%20%20%20SUM(total)%20AS%20total%0A%20%20%20%20%20%20%20%20FROM%20%7Bseed_relation%7D%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%202.%20Staging%3A%20parse%20the%20CSV%20row%20label%0A%0A%20%20%20%20%60staging.stg_zurich__crime_stats%60%20is%20the%20meaning-preserving%20dbt%20staging%20model.%0A%20%20%20%20It%20extracts%20%60street_name%60%20and%20%60abschnitt%60%20from%20values%20like%20%60Langstrasse%20Abschnitt%203%60%0A%20%20%20%20and%20keeps%20the%20source%20counts%20intact.%0A%0A%20%20%20%20The%20source%20legend%20is%20essential%3A%0A%0A%20%20%20%20-%20%60abschnitt%20%3D%200%60%20means%20unknown%20location%20on%20that%20street.%0A%20%20%20%20-%20%60abschnitt%20%3D%201%60%20means%20house%20numbers%201-50.%0A%20%20%20%20-%20%60abschnitt%20%3D%202%60%20means%2051-100%2C%20and%20so%20on.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20street_name%2C%20abschnitt%2C%20morning%2C%20afternoon%2C%20night%2C%20total%0A%20%20%20%20%20%20%20%20FROM%20staging.stg_zurich__crime_stats%0A%20%20%20%20%20%20%20%20ORDER%20BY%20total%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%2015%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20COUNT(*)%20AS%20staged_rows%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(*)%20FILTER%20(WHERE%20abschnitt%20%3D%200)%20AS%20unknown_section_rows%2C%0A%20%20%20%20%20%20%20%20%20%20SUM(total)%20AS%20incidents%2C%0A%20%20%20%20%20%20%20%20%20%20SUM(total)%20FILTER%20(WHERE%20abschnitt%20%3D%200)%20AS%20incidents_unknown_section%0A%20%20%20%20%20%20%20%20FROM%20staging.stg_zurich__crime_stats%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%203.%20Match%20crime%20sections%20to%20address%20points%0A%0A%20%20%20%20Address%20staging%20computes%20address%20sections%20as%20%60(house_number%20-%201)%20%2F%2050%60%2C%20where%0A%20%20%20%20%600%20%3D%20houses%201-50%60.%20The%20crime%20CSV%20uses%20%601%20%3D%20houses%201-50%60%2C%20so%20the%20production%20join%0A%20%20%20%20intentionally%20shifts%20the%20address%20section%20by%20one%3A%0A%0A%20%20%20%20%60crime.abschnitt%20%3D%20(address.house_number%20-%201)%20%2F%2050%20%2B%201%60%0A%0A%20%20%20%20That%20gives%20precise%20multipoint%20geometry%20for%20sections%20with%20real%20addresses.%20Rows%20with%0A%20%20%20%20%60abschnitt%20%3D%200%60%2C%20and%20sections%20that%20still%20do%20not%20match%20addresses%2C%20fall%20back%20to%20the%0A%20%20%20%20whole%20street%20centerline%20from%20%60staging.stg_zurich__strassennamen%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20%20%20%20%20WITH%20crime%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20SELECT%20street_name%2C%20abschnitt%2C%20total%0A%20%20%20%20%20%20%20%20%20%20FROM%20staging.stg_zurich__crime_stats%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20address_sections%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20SELECT%20DISTINCT%20street_name%2C%20(house_number%20-%201)%20%2F%2050%20%2B%201%20AS%20abschnitt%0A%20%20%20%20%20%20%20%20%20%20FROM%20staging.stg_zurich__adressen%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20streets%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20SELECT%20DISTINCT%20street_name%0A%20%20%20%20%20%20%20%20%20%20FROM%20staging.stg_zurich__strassennamen%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20SUM(total)%20FILTER%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20c.abschnitt%20%3E%3D%201%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20AND%20EXISTS%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20SELECT%201%20FROM%20address_sections%20a%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20a.street_name%20%3D%20c.street_name%20AND%20a.abschnitt%20%3D%20c.abschnitt%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20)%20AS%20address_matched_incidents%2C%0A%20%20%20%20%20%20%20%20%20%20SUM(total)%20FILTER%20(WHERE%20c.abschnitt%20%3D%200)%20AS%20whole_street_unknown_incidents%2C%0A%20%20%20%20%20%20%20%20%20%20SUM(total)%20FILTER%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20c.abschnitt%20%3E%3D%201%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20AND%20NOT%20EXISTS%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20SELECT%201%20FROM%20address_sections%20a%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20a.street_name%20%3D%20c.street_name%20AND%20a.abschnitt%20%3D%20c.abschnitt%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20AND%20EXISTS%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20SELECT%201%20FROM%20streets%20s%20WHERE%20s.street_name%20%3D%20c.street_name%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20)%20AS%20whole_street_unmatched_section_incidents%2C%0A%20%20%20%20%20%20%20%20%20%20SUM(total)%20FILTER%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20NOT%20EXISTS%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20SELECT%201%20FROM%20streets%20s%20WHERE%20s.street_name%20%3D%20c.street_name%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20)%20AS%20unplaceable_incidents%2C%0A%20%20%20%20%20%20%20%20%20%20SUM(total)%20AS%20total_incidents%0A%20%20%20%20%20%20%20%20FROM%20crime%20c%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20%20%20%20%20WITH%20crime%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20SELECT%20street_name%2C%20abschnitt%2C%20total%0A%20%20%20%20%20%20%20%20%20%20FROM%20staging.stg_zurich__crime_stats%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20addr%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20SELECT%20street_name%2C%20(house_number%20-%201)%20%2F%2050%20%2B%201%20AS%20abschnitt%0A%20%20%20%20%20%20%20%20%20%20FROM%20staging.stg_zurich__adressen%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20c.street_name%2C%0A%20%20%20%20%20%20%20%20%20%20c.abschnitt%2C%0A%20%20%20%20%20%20%20%20%20%20c.total%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(a.abschnitt)%20AS%20matching_address_points%0A%20%20%20%20%20%20%20%20FROM%20crime%20c%0A%20%20%20%20%20%20%20%20LEFT%20JOIN%20addr%20a%20USING%20(street_name%2C%20abschnitt)%0A%20%20%20%20%20%20%20%20WHERE%20c.abschnitt%20%3E%3D%201%0A%20%20%20%20%20%20%20%20GROUP%20BY%20c.street_name%2C%20c.abschnitt%2C%20c.total%0A%20%20%20%20%20%20%20%20ORDER%20BY%20c.total%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%2020%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%204.%20Build%20the%20geometry%20that%20gets%20aggregated%0A%0A%20%20%20%20This%20CTE%20block%20is%20the%20model%20logic%20from%20%60zurich_crime_h3.sql%60%20plus%20the%20reusable%0A%20%20%20%20H3%20aggregation%20macro%20expanded%20inline%20so%20the%20intermediate%20tables%20are%20inspectable.%0A%0A%20%20%20%20The%20next%20summary%20groups%20the%20source%20rows%20by%20**placement**%3A%20how%20each%20parsed%20crime%0A%20%20%20%20section%20gets%20a%20geometry%20before%20H3%20aggregation.%0A%0A%20%20%20%20-%20%60address_points%60%3A%20the%20best%20case.%20The%20source%20row%20has%20%60abschnitt%20%3E%3D%201%60%2C%20and%20that%0A%20%20%20%20%20%20street%2Fsection%20matches%20real%20address%20points%20after%20applying%20the%20off-by-one%20section%0A%20%20%20%20%20%20correction.%20The%20geometry%20is%20a%20%60MULTIPOINT%60%20of%20those%20addresses.%0A%20%20%20%20-%20%60whole_street_unknown%60%3A%20the%20source%20row%20has%20%60abschnitt%20%3D%200%60.%20In%20the%20CSV%20legend%20that%0A%20%20%20%20%20%20means%20%22unknown%20location%20on%20this%20street%22%2C%20not%20house%20numbers%201-50%2C%20so%20the%20geometry%20is%0A%20%20%20%20%20%20the%20whole%20street%20centerline.%0A%20%20%20%20-%20%60whole_street_unmatched_section%60%3A%20the%20source%20row%20names%20a%20numbered%20section%2C%20but%20no%0A%20%20%20%20%20%20address%20points%20matched%20that%20section.%20If%20the%20street%20centerline%20exists%2C%20we%20still%20place%0A%20%20%20%20%20%20the%20count%20on%20the%20whole%20street%20rather%20than%20dropping%20it.%0A%20%20%20%20-%20%60unplaceable%60%3A%20neither%20address%20points%20nor%20a%20street%20centerline%20could%20be%20found.%20These%0A%20%20%20%20%20%20rows%20are%20visible%20in%20the%20placement%20summary%2C%20but%20they%20do%20not%20enter%20the%20H3%20aggregation%0A%20%20%20%20%20%20because%20there%20is%20no%20geometry%20to%20sample.%0A%0A%20%20%20%20%60source_sections%60%20counts%20distinct%20%60(street_name%2C%20abschnitt)%60%20pairs.%20The%20pipeline%20then%0A%20%20%20%20pivots%20each%20section%20into%20three%20daypart%20rows%2C%20so%20%60section_daypart_rows%60%20is%20usually%0A%20%20%20%20three%20times%20the%20section%20count.%20%60daypart_incident_sum%60%20is%20the%20total%20count%20represented%0A%20%20%20%20by%20those%20long-form%20rows.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(RES%2C%20STEP_M)%3A%0A%20%20%20%20PIPELINE_CTES%20%3D%20f%22%22%22%0A%20%20%20%20WITH%20crime%20AS%20(%0A%20%20%20%20%20%20SELECT%20street_name%2C%20abschnitt%2C%20morning%2C%20afternoon%2C%20night%2C%20total%0A%20%20%20%20%20%20FROM%20staging.stg_zurich__crime_stats%0A%20%20%20%20)%2C%0A%20%20%20%20crime_long%20AS%20(%0A%20%20%20%20%20%20SELECT%20street_name%2C%20abschnitt%2C%20'morning'%3A%3Atext%20AS%20daypart%2C%20morning%3A%3Afloat%20AS%20incident_count%20FROM%20crime%0A%20%20%20%20%20%20UNION%20ALL%20SELECT%20street_name%2C%20abschnitt%2C%20'afternoon'%2C%20afternoon%3A%3Afloat%20FROM%20crime%0A%20%20%20%20%20%20UNION%20ALL%20SELECT%20street_name%2C%20abschnitt%2C%20'night'%2C%20night%3A%3Afloat%20FROM%20crime%0A%20%20%20%20)%2C%0A%20%20%20%20addr%20AS%20(%0A%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20(house_number%20-%201)%20%2F%2050%20%2B%201%20AS%20abschnitt%2C%0A%20%20%20%20%20%20%20%20geometry%0A%20%20%20%20%20%20FROM%20staging.stg_zurich__adressen%0A%20%20%20%20)%2C%0A%20%20%20%20section_points%20AS%20(%0A%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20a.street_name%2C%0A%20%20%20%20%20%20%20%20a.abschnitt%2C%0A%20%20%20%20%20%20%20%20ST_Collect(a.geometry)%20AS%20geometry%2C%0A%20%20%20%20%20%20%20%20COUNT(*)%20AS%20address_points%0A%20%20%20%20%20%20FROM%20addr%20a%0A%20%20%20%20%20%20INNER%20JOIN%20(%0A%20%20%20%20%20%20%20%20SELECT%20DISTINCT%20street_name%2C%20abschnitt%0A%20%20%20%20%20%20%20%20FROM%20crime_long%0A%20%20%20%20%20%20%20%20WHERE%20abschnitt%20%3E%3D%201%0A%20%20%20%20%20%20)%20c%20USING%20(street_name%2C%20abschnitt)%0A%20%20%20%20%20%20GROUP%20BY%20a.street_name%2C%20a.abschnitt%0A%20%20%20%20)%2C%0A%20%20%20%20streets%20AS%20(%0A%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20ST_LineMerge(ST_Collect(geometry))%20AS%20geometry%0A%20%20%20%20%20%20FROM%20staging.stg_zurich__strassennamen%0A%20%20%20%20%20%20GROUP%20BY%20street_name%0A%20%20%20%20)%2C%0A%20%20%20%20crime_geom%20AS%20(%0A%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20cl.street_name%2C%0A%20%20%20%20%20%20%20%20cl.abschnitt%2C%0A%20%20%20%20%20%20%20%20cl.daypart%2C%0A%20%20%20%20%20%20%20%20cl.incident_count%2C%0A%20%20%20%20%20%20%20%20COALESCE(sp.geometry%2C%20st.geometry)%20AS%20geometry%2C%0A%20%20%20%20%20%20%20%20CASE%0A%20%20%20%20%20%20%20%20%20%20WHEN%20sp.geometry%20IS%20NOT%20NULL%20THEN%20'address_points'%0A%20%20%20%20%20%20%20%20%20%20WHEN%20st.geometry%20IS%20NOT%20NULL%20AND%20cl.abschnitt%20%3D%200%20THEN%20'whole_street_unknown'%0A%20%20%20%20%20%20%20%20%20%20WHEN%20st.geometry%20IS%20NOT%20NULL%20THEN%20'whole_street_unmatched_section'%0A%20%20%20%20%20%20%20%20%20%20ELSE%20'unplaceable'%0A%20%20%20%20%20%20%20%20END%20AS%20placement%2C%0A%20%20%20%20%20%20%20%20COALESCE(sp.address_points%2C%200)%20AS%20address_points%0A%20%20%20%20%20%20FROM%20crime_long%20cl%0A%20%20%20%20%20%20LEFT%20JOIN%20section_points%20sp%0A%20%20%20%20%20%20%20%20ON%20sp.street_name%20%3D%20cl.street_name%0A%20%20%20%20%20%20%20AND%20sp.abschnitt%20%3D%20cl.abschnitt%0A%20%20%20%20%20%20%20AND%20cl.abschnitt%20%3E%3D%201%0A%20%20%20%20%20%20LEFT%20JOIN%20streets%20st%0A%20%20%20%20%20%20%20%20ON%20st.street_name%20%3D%20cl.street_name%0A%20%20%20%20)%2C%0A%20%20%20%20dumped%20AS%20(%0A%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20abschnitt%2C%0A%20%20%20%20%20%20%20%20daypart%2C%0A%20%20%20%20%20%20%20%20incident_count%2C%0A%20%20%20%20%20%20%20%20placement%2C%0A%20%20%20%20%20%20%20%20address_points%2C%0A%20%20%20%20%20%20%20%20dp.geom%20AS%20pt%0A%20%20%20%20%20%20FROM%20crime_geom%0A%20%20%20%20%20%20CROSS%20JOIN%20LATERAL%20ST_DumpPoints(ST_Segmentize(geometry%2C%20%7BSTEP_M%7D))%20AS%20dp%0A%20%20%20%20%20%20WHERE%20geometry%20IS%20NOT%20NULL%20AND%20NOT%20ST_IsEmpty(geometry)%0A%20%20%20%20)%2C%0A%20%20%20%20samples%20AS%20(%0A%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20*%2C%0A%20%20%20%20%20%20%20%20COUNT(*)%20OVER%20(PARTITION%20BY%20street_name%2C%20abschnitt%2C%20daypart)%20AS%20n_pts%0A%20%20%20%20%20%20FROM%20dumped%0A%20%20%20%20)%2C%0A%20%20%20%20celled%20AS%20(%0A%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20h3_lat_lng_to_cell(ST_Transform(pt%2C%204326)%2C%20%7BRES%7D)%20AS%20cell%2C%0A%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20abschnitt%2C%0A%20%20%20%20%20%20%20%20daypart%2C%0A%20%20%20%20%20%20%20%20incident_count%20%2F%20n_pts%20AS%20incident_share%2C%0A%20%20%20%20%20%20%20%20placement%2C%0A%20%20%20%20%20%20%20%20n_pts%0A%20%20%20%20%20%20FROM%20samples%0A%20%20%20%20)%2C%0A%20%20%20%20hexes%20AS%20(%0A%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20cell%3A%3Atext%20AS%20h3_cell%2C%0A%20%20%20%20%20%20%20%20daypart%2C%0A%20%20%20%20%20%20%20%20SUM(incident_share)%20AS%20crime_total%2C%0A%20%20%20%20%20%20%20%20bool_or(placement%20%3C%3E%20'address_points')%20AS%20includes_street_level%2C%0A%20%20%20%20%20%20%20%20ST_Transform(h3_cell_to_boundary_geometry(cell)%2C%202056)%20AS%20geometry%0A%20%20%20%20%20%20FROM%20celled%0A%20%20%20%20%20%20GROUP%20BY%20cell%2C%20daypart%0A%20%20%20%20)%0A%20%20%20%20%22%22%22%0A%20%20%20%20return%20(PIPELINE_CTES%2C)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(PIPELINE_CTES%2C%20q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20placement%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(DISTINCT%20(street_name%2C%20abschnitt))%20AS%20source_sections%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(*)%20AS%20section_daypart_rows%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(SUM(incident_count)%3A%3Anumeric%2C%201)%20AS%20daypart_incident_sum%0A%20%20%20%20%20%20%20%20FROM%20crime_geom%0A%20%20%20%20%20%20%20%20GROUP%20BY%20placement%0A%20%20%20%20%20%20%20%20ORDER%20BY%20daypart_incident_sum%20DESC%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(PIPELINE_CTES%2C%20q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20%20%20abschnitt%2C%0A%20%20%20%20%20%20%20%20%20%20daypart%2C%0A%20%20%20%20%20%20%20%20%20%20incident_count%2C%0A%20%20%20%20%20%20%20%20%20%20placement%2C%0A%20%20%20%20%20%20%20%20%20%20address_points%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(ST_Length(geometry)%3A%3Anumeric%2C%201)%20AS%20line_length_m%2C%0A%20%20%20%20%20%20%20%20%20%20ST_GeometryType(geometry)%20AS%20geometry_type%0A%20%20%20%20%20%20%20%20FROM%20crime_geom%0A%20%20%20%20%20%20%20%20WHERE%20incident_count%20%3E%200%0A%20%20%20%20%20%20%20%20ORDER%20BY%20incident_count%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%2020%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%205.%20Aggregate%20into%20H3%0A%0A%20%20%20%20At%20this%20point%20each%20crime%20row%20has%20a%20geometry%2C%20but%20it%20is%20still%20a%20street-section%20signal%2C%0A%20%20%20%20not%20an%20area%20signal.%20H3%20aggregation%20turns%20those%20geometries%20into%20a%20regular%20hex%20surface.%0A%20%20%20%20The%20reusable%20macro%20%60h3_aggregate_value%60%20does%20the%20same%20mechanical%20steps%20shown%20here%3A%0A%0A%20%20%20%201.%20**Densify%20the%20geometry**%20every%2010%20m%20with%20%60ST_Segmentize%60.%20A%20street%20line%20gets%20more%0A%20%20%20%20%20%20%20vertices%20along%20its%20length%3B%20a%20%60MULTIPOINT%60%20address%20geometry%20is%20effectively%20already%0A%20%20%20%20%20%20%20sampled%20at%20the%20address%20locations.%0A%20%20%20%202.%20**Explode%20the%20geometry%20to%20sample%20points**%20with%20%60ST_DumpPoints%60.%20These%20points%20are%0A%20%20%20%20%20%20%20the%20carriers%20of%20the%20source%20row's%20count.%0A%20%20%20%203.%20**Count%20sample%20points%20per%20source%20feature**%3A%20one%20feature%20is%20one%0A%20%20%20%20%20%20%20%60(street_name%2C%20abschnitt%2C%20daypart)%60%20row.%0A%20%20%20%204.%20**Assign%20every%20sample%20point%20to%20an%20H3%20cell**%20at%20resolution%209.%0A%20%20%20%205.%20**Split%20the%20source%20row's%20count%20across%20its%20sample%20points**%2C%20then%20sum%20those%20shares%0A%20%20%20%20%20%20%20by%20%60(h3_cell%2C%20daypart)%60.%0A%0A%20%20%20%20Example%3A%20if%20%60Langstrasse%20Abschnitt%203%20%2F%20night%60%20has%2030%20incidents%20and%20its%20matched%0A%20%20%20%20address%20geometry%20produces%2012%20sample%20points%2C%20each%20sample%20carries%20%6030%20%2F%2012%20%3D%202.5%60%0A%20%20%20%20incidents.%20If%20those%2012%20samples%20fall%20into%20three%20H3%20cells%2C%20the%20cells%20receive%20the%20sum%20of%0A%20%20%20%20the%20sample%20shares%20that%20landed%20inside%20each%20cell.%20No%20incident%20count%20is%20created%20or%0A%20%20%20%20destroyed%3B%20it%20is%20redistributed%20from%20an%20irregular%20street-section%20geometry%20onto%20a%0A%20%20%20%20regular%20H3%20grid.%0A%0A%20%20%20%20The%20first%20table%20below%20checks%20the%20sampling%20stage%3A%0A%0A%20%20%20%20-%20%60sample_points%60%3A%20how%20many%20point%20samples%20were%20produced%20from%20all%20placeable%20geometry.%0A%20%20%20%20-%20%60source_features%60%3A%20how%20many%20long-form%20%60(street%2C%20section%2C%20daypart)%60%20rows%20were%20sampled.%0A%20%20%20%20-%20%60h3_cells_touched%60%3A%20how%20many%20unique%20H3%20cells%20received%20at%20least%20one%20sample.%0A%20%20%20%20-%20%60distributed_incident_sum%60%3A%20the%20sum%20of%20all%20per-sample%20shares.%0A%0A%20%20%20%20The%20second%20table%20checks%20the%20emitted%20H3%20layer.%20%60emitted_crime_total%60%20should%20match%20the%0A%20%20%20%20placeable%20portion%20of%20the%20CSV%20total.%20The%20gap%20to%20%60csv_total%60%20is%20the%20unplaceable%20source%0A%20%20%20%20rows%20from%20Step%204.%0A%0A%20%20%20%20The%20separate%20%60dumped%60%20and%20%60samples%60%20CTEs%20are%20deliberate.%20Combining%20the%20set-returning%0A%20%20%20%20%60ST_DumpPoints%60%20call%20and%20the%20window%20count%20in%20one%20SELECT%20would%20inflate%20totals%20in%0A%20%20%20%20Postgres%20because%20the%20window%20count%20is%20evaluated%20before%20the%20set-returning%20function%0A%20%20%20%20expands%20rows.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(PIPELINE_CTES%2C%20q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20COUNT(*)%20AS%20sample_points%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(DISTINCT%20(street_name%2C%20abschnitt%2C%20daypart))%20AS%20source_features%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(DISTINCT%20cell)%20AS%20h3_cells_touched%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(SUM(incident_share)%3A%3Anumeric%2C%201)%20AS%20distributed_incident_sum%0A%20%20%20%20%20%20%20%20FROM%20celled%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(PIPELINE_CTES%2C%20q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20COUNT(DISTINCT%20h3_cell)%20AS%20h3_cells%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(*)%20AS%20h3_cell_daypart_rows%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(SUM(crime_total)%3A%3Anumeric%2C%201)%20AS%20emitted_crime_total%2C%0A%20%20%20%20%20%20%20%20%20%20(SELECT%20SUM(total)%20FROM%20crime)%20AS%20csv_total%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(100.0%20*%20SUM(crime_total)%3A%3Anumeric%20%2F%20(SELECT%20SUM(total)%20FROM%20crime)%2C%201)%20AS%20placed_pct%0A%20%20%20%20%20%20%20%20FROM%20hexes%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%206.%20Visual%20checks%0A%0A%20%20%20%20These%20maps%20are%20not%20the%20final%20proof%20of%20correctness%3B%20they%20are%20visual%20sanity%20checks%20for%0A%20%20%20%20the%20placement%20and%20aggregation%20choices.%0A%0A%20%20%20%20The%20first%20map%20overlays%20the%20two%20kinds%20of%20geometry%20that%20feed%20the%20H3%20aggregation%3A%0A%0A%20%20%20%20-%20**Green%20points**%20are%20matched%20address%20points.%20These%20are%20the%20precise%20placements%20for%0A%20%20%20%20%20%20numbered%20crime%20sections%20where%20%60(street_name%2C%20abschnitt)%60%20matched%20real%20addresses.%0A%20%20%20%20%20%20Each%20point%20is%20one%20address%20coordinate%20from%20%60staging.stg_zurich__adressen%60.%0A%20%20%20%20-%20**Orange%20lines**%20are%20fallback%20whole-street%20geometries.%20These%20appear%20when%20the%20crime%0A%20%20%20%20%20%20row%20is%20%60abschnitt%20%3D%200%60%20(%22unknown%20location%20on%20this%20street%22)%20or%20when%20a%20numbered%0A%20%20%20%20%20%20section%20could%20not%20be%20matched%20to%20address%20points%20but%20the%20street%20centerline%20exists.%0A%0A%20%20%20%20This%20map%20should%20make%20the%20trade-off%20visible%3A%20most%20high-signal%20numbered%20sections%20are%0A%20%20%20%20anchored%20to%20address%20points%2C%20while%20uncertain%20rows%20are%20deliberately%20spread%20along%20a%0A%20%20%20%20street%20instead%20of%20being%20forced%20into%20a%20fake%20point.%0A%0A%20%20%20%20The%20next%20maps%20show%20the%20H3%20output%20surface%3A%0A%0A%20%20%20%20-%20Each%20polygon%20is%20one%20H3%20cell%20at%20resolution%209.%0A%20%20%20%20-%20Darker%20%2F%20warmer%20cells%20have%20a%20higher%20%60crime_total%60%20for%20the%20selected%20daypart.%0A%20%20%20%20-%20Tooltips%20expose%20the%20%60h3_cell%60%2C%20%60crime_total%60%2C%20and%20whether%20the%20cell%20includes%20any%0A%20%20%20%20%20%20street-level%20fallback%20contribution.%0A%0A%20%20%20%20Use%20the%20daypart%20picker%20to%20compare%20%60morning%60%2C%20%60afternoon%60%2C%20and%20%60night%60.%20The%20night%20map%0A%20%20%20%20should%20usually%20show%20the%20strongest%20concentration%20because%20the%20source%20CSV%20is%0A%20%20%20%20night-heavy%20around%20central%20nightlife%20%2F%20station%20areas.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(PIPELINE_CTES%2C%20folium%2C%20qg)%3A%0A%20%20%20%20_overview_matched_points%20%3D%20qg(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20%20%20abschnitt%2C%0A%20%20%20%20%20%20%20%20%20%20placement%2C%0A%20%20%20%20%20%20%20%20%20%20ST_Transform((ST_DumpPoints(geometry)).geom%2C%204326)%20AS%20geometry%0A%20%20%20%20%20%20%20%20FROM%20crime_geom%0A%20%20%20%20%20%20%20%20WHERE%20placement%20%3D%20'address_points'%20AND%20daypart%20%3D%20'night'%20AND%20incident_count%20%3E%200%0A%20%20%20%20%20%20%20%20LIMIT%20750%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20crs%3D4326%2C%0A%20%20%20%20)%0A%20%20%20%20_overview_fallback_lines%20%3D%20qg(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20%20%20abschnitt%2C%0A%20%20%20%20%20%20%20%20%20%20placement%2C%0A%20%20%20%20%20%20%20%20%20%20incident_count%2C%0A%20%20%20%20%20%20%20%20%20%20ST_Transform(geometry%2C%204326)%20AS%20geometry%0A%20%20%20%20%20%20%20%20FROM%20crime_geom%0A%20%20%20%20%20%20%20%20WHERE%20placement%20%3C%3E%20'address_points'%20AND%20daypart%20%3D%20'night'%20AND%20incident_count%20%3E%200%0A%20%20%20%20%20%20%20%20ORDER%20BY%20incident_count%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%2025%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20crs%3D4326%2C%0A%20%20%20%20)%0A%0A%20%20%20%20_overview_map%20%3D%20folium.Map(%0A%20%20%20%20%20%20%20%20location%3D%5B47.3769%2C%208.5417%5D%2C%20zoom_start%3D13%2C%20tiles%3D%22CartoDB%20positron%22%0A%20%20%20%20)%0A%20%20%20%20folium.GeoJson(%0A%20%20%20%20%20%20%20%20_overview_fallback_lines%2C%0A%20%20%20%20%20%20%20%20name%3D%22fallback%20whole-street%20geometry%22%2C%0A%20%20%20%20%20%20%20%20style_function%3Dlambda%20_%3A%20%7B%22color%22%3A%20%22%23d95f02%22%2C%20%22weight%22%3A%204%2C%20%22opacity%22%3A%200.7%7D%2C%0A%20%20%20%20%20%20%20%20tooltip%3Dfolium.GeoJsonTooltip(%0A%20%20%20%20%20%20%20%20%20%20%20%20fields%3D%5B%22street_name%22%2C%20%22abschnitt%22%2C%20%22incident_count%22%2C%20%22placement%22%5D%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20).add_to(_overview_map)%0A%20%20%20%20folium.GeoJson(%0A%20%20%20%20%20%20%20%20_overview_matched_points%2C%0A%20%20%20%20%20%20%20%20name%3D%22matched%20address%20points%22%2C%0A%20%20%20%20%20%20%20%20marker%3Dfolium.CircleMarker(%0A%20%20%20%20%20%20%20%20%20%20%20%20radius%3D3%2C%20color%3D%22%231b9e77%22%2C%20fill%3DTrue%2C%20fill_opacity%3D0.7%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20tooltip%3Dfolium.GeoJsonTooltip(fields%3D%5B%22street_name%22%2C%20%22abschnitt%22%2C%20%22placement%22%5D)%2C%0A%20%20%20%20).add_to(_overview_map)%0A%20%20%20%20folium.LayerControl().add_to(_overview_map)%0A%20%20%20%20_overview_map%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(PIPELINE_CTES%2C%20folium%2C%20qg)%3A%0A%20%20%20%20_hexes_night%20%3D%20qg(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20h3_cell%2C%0A%20%20%20%20%20%20%20%20%20%20crime_total%2C%0A%20%20%20%20%20%20%20%20%20%20includes_street_level%2C%0A%20%20%20%20%20%20%20%20%20%20ST_Transform(geometry%2C%204326)%20AS%20geometry%0A%20%20%20%20%20%20%20%20FROM%20hexes%0A%20%20%20%20%20%20%20%20WHERE%20daypart%20%3D%20'night'%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20crs%3D4326%2C%0A%20%20%20%20)%0A%0A%20%20%20%20_night_hex_map%20%3D%20_hexes_night.explore(%0A%20%20%20%20%20%20%20%20column%3D%22crime_total%22%2C%0A%20%20%20%20%20%20%20%20cmap%3D%22OrRd%22%2C%0A%20%20%20%20%20%20%20%20tiles%3D%22CartoDB%20positron%22%2C%0A%20%20%20%20%20%20%20%20tooltip%3D%5B%22h3_cell%22%2C%20%22crime_total%22%2C%20%22includes_street_level%22%5D%2C%0A%20%20%20%20%20%20%20%20legend%3DTrue%2C%0A%20%20%20%20%20%20%20%20style_kwds%3D%7B%22weight%22%3A%200.4%2C%20%22color%22%3A%20%22%23555555%22%2C%20%22fillOpacity%22%3A%200.65%7D%2C%0A%20%20%20%20%20%20%20%20name%3D%22night%20crime%20H3%22%2C%0A%20%20%20%20)%0A%20%20%20%20folium.LayerControl().add_to(_night_hex_map)%0A%20%20%20%20_night_hex_map%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20daypart_picker%20%3D%20mo.ui.dropdown(%0A%20%20%20%20%20%20%20%20options%3D%5B%22morning%22%2C%20%22afternoon%22%2C%20%22night%22%5D%2C%0A%20%20%20%20%20%20%20%20value%3D%22night%22%2C%0A%20%20%20%20%20%20%20%20label%3D%22H3%20daypart%22%2C%0A%20%20%20%20)%0A%20%20%20%20daypart_picker%0A%20%20%20%20return%20(daypart_picker%2C)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(PIPELINE_CTES%2C%20daypart_picker%2C%20folium%2C%20qg)%3A%0A%20%20%20%20daypart_hexes%20%3D%20qg(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20h3_cell%2C%0A%20%20%20%20%20%20%20%20%20%20daypart%2C%0A%20%20%20%20%20%20%20%20%20%20crime_total%2C%0A%20%20%20%20%20%20%20%20%20%20includes_street_level%2C%0A%20%20%20%20%20%20%20%20%20%20ST_Transform(geometry%2C%204326)%20AS%20geometry%0A%20%20%20%20%20%20%20%20FROM%20hexes%0A%20%20%20%20%20%20%20%20WHERE%20daypart%20%3D%20%3Adaypart%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20crs%3D4326%2C%0A%20%20%20%20%20%20%20%20daypart%3Ddaypart_picker.value%2C%0A%20%20%20%20)%0A%0A%20%20%20%20_daypart_map%20%3D%20daypart_hexes.explore(%0A%20%20%20%20%20%20%20%20column%3D%22crime_total%22%2C%0A%20%20%20%20%20%20%20%20cmap%3D%22YlOrRd%22%2C%0A%20%20%20%20%20%20%20%20tiles%3D%22CartoDB%20positron%22%2C%0A%20%20%20%20%20%20%20%20tooltip%3D%5B%22h3_cell%22%2C%20%22daypart%22%2C%20%22crime_total%22%2C%20%22includes_street_level%22%5D%2C%0A%20%20%20%20%20%20%20%20legend%3DTrue%2C%0A%20%20%20%20%20%20%20%20style_kwds%3D%7B%22weight%22%3A%200.4%2C%20%22color%22%3A%20%22%23555555%22%2C%20%22fillOpacity%22%3A%200.65%7D%2C%0A%20%20%20%20%20%20%20%20name%3Df%22%7Bdaypart_picker.value%7D%20crime%20H3%22%2C%0A%20%20%20%20)%0A%20%20%20%20folium.LayerControl().add_to(_daypart_map)%0A%20%20%20%20_daypart_map%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%207.%20Trace%20one%20source%20row%20into%20H3%0A%0A%20%20%20%20The%20CSV%20has%20aggregate%20counts%2C%20not%20incident%20IDs.%20That%20means%20there%20is%20no%20literal%0A%20%20%20%20single%20incident%20record%20to%20follow.%20What%20we%20can%20trace%20is%20a%20**notional%20one%20incident**%0A%20%20%20%20from%20a%20selected%20%60(street_name%2C%20abschnitt%2C%20daypart)%60%20source%20row.%0A%0A%20%20%20%20The%20dropdown%20starts%20with%20a%20high-signal%20default%20(%60Langstrasse%20Abschnitt%203%20%2F%20night%60)%2C%0A%20%20%20%20but%20you%20can%20pick%20any%20placeable%20source%20row.%20When%20you%20change%20the%20selection%2C%20every%20table%0A%20%20%20%20and%20map%20below%20recomputes%20from%20the%20same%20pipeline%20CTEs.%0A%0A%20%20%20%20The%20trace%20below%20shows%3A%0A%0A%20%20%20%20-%20**which%20source%20row%20we%20selected**%3A%20street%2C%20section%2C%20daypart%2C%20count%2C%20and%20placement%0A%20%20%20%20%20%20category%3B%0A%20%20%20%20-%20**where%20the%20row%20is%20placed**%3A%20matched%20address%20points%20when%20possible%2C%20otherwise%20a%0A%20%20%20%20%20%20street%20centerline%20fallback%3B%0A%20%20%20%20-%20**how%20the%20aggregation%20samples%20it**%3A%20densified%20points%20over%20the%20geometry%3B%0A%20%20%20%20-%20**which%20H3%20cells%20receive%20signal**%3A%20every%20cell%20touched%20by%20at%20least%20one%20sample%20point%3B%0A%20%20%20%20-%20**how%20one%20notional%20incident%20is%20split**%3A%20the%20fraction%20of%20a%20single%20source-row%0A%20%20%20%20%20%20incident%20that%20lands%20in%20each%20cell.%0A%0A%20%20%20%20Read%20the%20trace%20as%20a%20lineage%20view%3A%20it%20links%20one%20row%20in%20the%20input%20CSV%20to%20one%20or%20more%0A%20%20%20%20emitted%20H3%20rows%2C%20while%20keeping%20the%20caveat%20that%20the%20source%20data%20is%20already%20aggregated.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(PIPELINE_CTES%2C%20mo%2C%20q)%3A%0A%20%20%20%20trace_candidates%20%3D%20q(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20%20%20abschnitt%2C%0A%20%20%20%20%20%20%20%20%20%20daypart%2C%0A%20%20%20%20%20%20%20%20%20%20incident_count%2C%0A%20%20%20%20%20%20%20%20%20%20placement%2C%0A%20%20%20%20%20%20%20%20%20%20address_points%2C%0A%20%20%20%20%20%20%20%20%20%20concat(street_name%2C%20'%20%7C%20Abschnitt%20'%2C%20abschnitt%2C%20'%20%7C%20'%2C%20daypart)%20AS%20label%0A%20%20%20%20%20%20%20%20FROM%20crime_geom%0A%20%20%20%20%20%20%20%20WHERE%20incident_count%20%3E%200%20AND%20placement%20%3C%3E%20'unplaceable'%0A%20%20%20%20%20%20%20%20ORDER%20BY%0A%20%20%20%20%20%20%20%20%20%20CASE%20WHEN%20street_name%20%3D%20'Langstrasse'%20AND%20abschnitt%20%3D%203%20AND%20daypart%20%3D%20'night'%20THEN%200%20ELSE%201%20END%2C%0A%20%20%20%20%20%20%20%20%20%20incident_count%20DESC%2C%0A%20%20%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20%20%20abschnitt%2C%0A%20%20%20%20%20%20%20%20%20%20daypart%0A%20%20%20%20%20%20%20%20LIMIT%20200%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20trace_options%20%3D%20trace_candidates%5B%22label%22%5D.tolist()%0A%20%20%20%20trace_picker%20%3D%20mo.ui.dropdown(%0A%20%20%20%20%20%20%20%20options%3Dtrace_options%2C%0A%20%20%20%20%20%20%20%20value%3Dtrace_options%5B0%5D%20if%20trace_options%20else%20None%2C%0A%20%20%20%20%20%20%20%20label%3D%22Source%20crime%20row%22%2C%0A%20%20%20%20)%0A%20%20%20%20trace_picker%0A%20%20%20%20return%20trace_candidates%2C%20trace_picker%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(trace_candidates%2C%20trace_picker)%3A%0A%20%20%20%20trace_row%20%3D%20trace_candidates.loc%5B%0A%20%20%20%20%20%20%20%20trace_candidates%5B%22label%22%5D%20%3D%3D%20trace_picker.value%0A%20%20%20%20%5D.iloc%5B0%5D%0A%20%20%20%20trace_street%20%3D%20trace_row%5B%22street_name%22%5D%0A%20%20%20%20trace_abschnitt%20%3D%20int(trace_row%5B%22abschnitt%22%5D)%0A%20%20%20%20trace_daypart%20%3D%20trace_row%5B%22daypart%22%5D%0A%20%20%20%20trace_incident_count%20%3D%20float(trace_row%5B%22incident_count%22%5D)%0A%20%20%20%20trace_row.to_frame(%22selected%22).T%0A%20%20%20%20return%20trace_abschnitt%2C%20trace_daypart%2C%20trace_incident_count%2C%20trace_street%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%207.0%20Source%20section%20in%20context%0A%0A%20%20%20%20Start%20with%20the%20selected%20source%20row%20in%20its%20local%20street%20context.%0A%0A%20%20%20%20This%20map%20intentionally%20mixes%20three%20layers%3A%0A%0A%20%20%20%20-%20**Nearby%20grey%20street%20centerlines**%20give%20local%20orientation.%20They%20are%20not%20all%20used%20by%0A%20%20%20%20%20%20the%20selected%20row%3B%20they%20just%20show%20where%20the%20selected%20street%20sits%20in%20the%20network.%0A%20%20%20%20-%20**The%20blue%20selected%20geometry**%20is%20the%20geometry%20assigned%20to%20the%20source%20row%20before%0A%20%20%20%20%20%20sampling.%20For%20address-matched%20rows%20this%20may%20look%20like%20one%20or%20more%20points%3B%20for%0A%20%20%20%20%20%20fallback%20rows%20it%20is%20a%20street%20line.%0A%20%20%20%20-%20**Red%20H3%20outlines**%20preview%20the%20cells%20that%20will%20receive%20some%20share%20of%20this%20row's%0A%20%20%20%20%20%20count%20after%20sampling.%0A%0A%20%20%20%20This%20is%20the%20%22before%20aggregation%22%20view%3A%20it%20answers%20%22where%20does%20the%20selected%20CSV%20row%0A%20%20%20%20live%20spatially%3F%22%20before%20showing%20how%20its%20count%20is%20redistributed.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(PIPELINE_CTES%2C%20folium%2C%20qg%2C%20trace_abschnitt%2C%20trace_daypart%2C%20trace_street)%3A%0A%20%20%20%20context_streets%20%3D%20qg(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20%2C%20selected%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20SELECT%20geometry%0A%20%20%20%20%20%20%20%20%20%20FROM%20crime_geom%0A%20%20%20%20%20%20%20%20%20%20WHERE%20street_name%20%3D%20%3Astreet%0A%20%20%20%20%20%20%20%20%20%20%20%20AND%20abschnitt%20%3D%20%3Aabschnitt%0A%20%20%20%20%20%20%20%20%20%20%20%20AND%20daypart%20%3D%20%3Adaypart%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20s.street_name%2C%0A%20%20%20%20%20%20%20%20%20%20ST_Transform(s.geometry%2C%204326)%20AS%20geometry%0A%20%20%20%20%20%20%20%20FROM%20staging.stg_zurich__strassennamen%20s%0A%20%20%20%20%20%20%20%20CROSS%20JOIN%20selected%20x%0A%20%20%20%20%20%20%20%20WHERE%20ST_DWithin(s.geometry%2C%20x.geometry%2C%20250)%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20crs%3D4326%2C%0A%20%20%20%20%20%20%20%20street%3Dtrace_street%2C%0A%20%20%20%20%20%20%20%20abschnitt%3Dtrace_abschnitt%2C%0A%20%20%20%20%20%20%20%20daypart%3Dtrace_daypart%2C%0A%20%20%20%20)%0A%20%20%20%20context_selected%20%3D%20qg(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20%20%20abschnitt%2C%0A%20%20%20%20%20%20%20%20%20%20daypart%2C%0A%20%20%20%20%20%20%20%20%20%20incident_count%2C%0A%20%20%20%20%20%20%20%20%20%20placement%2C%0A%20%20%20%20%20%20%20%20%20%20ST_Transform(geometry%2C%204326)%20AS%20geometry%0A%20%20%20%20%20%20%20%20FROM%20crime_geom%0A%20%20%20%20%20%20%20%20WHERE%20street_name%20%3D%20%3Astreet%0A%20%20%20%20%20%20%20%20%20%20AND%20abschnitt%20%3D%20%3Aabschnitt%0A%20%20%20%20%20%20%20%20%20%20AND%20daypart%20%3D%20%3Adaypart%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20crs%3D4326%2C%0A%20%20%20%20%20%20%20%20street%3Dtrace_street%2C%0A%20%20%20%20%20%20%20%20abschnitt%3Dtrace_abschnitt%2C%0A%20%20%20%20%20%20%20%20daypart%3Dtrace_daypart%2C%0A%20%20%20%20)%0A%20%20%20%20context_cells%20%3D%20qg(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20cell%3A%3Atext%20AS%20h3_cell%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(SUM(incident_share)%3A%3Anumeric%2C%206)%20AS%20source_row_contribution%2C%0A%20%20%20%20%20%20%20%20%20%20ST_Transform(h3_cell_to_boundary_geometry(cell)%2C%204326)%20AS%20geometry%0A%20%20%20%20%20%20%20%20FROM%20celled%0A%20%20%20%20%20%20%20%20WHERE%20street_name%20%3D%20%3Astreet%0A%20%20%20%20%20%20%20%20%20%20AND%20abschnitt%20%3D%20%3Aabschnitt%0A%20%20%20%20%20%20%20%20%20%20AND%20daypart%20%3D%20%3Adaypart%0A%20%20%20%20%20%20%20%20GROUP%20BY%20cell%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20crs%3D4326%2C%0A%20%20%20%20%20%20%20%20street%3Dtrace_street%2C%0A%20%20%20%20%20%20%20%20abschnitt%3Dtrace_abschnitt%2C%0A%20%20%20%20%20%20%20%20daypart%3Dtrace_daypart%2C%0A%20%20%20%20)%0A%0A%20%20%20%20_context_minx%2C%20_context_miny%2C%20_context_maxx%2C%20_context_maxy%20%3D%20(%0A%20%20%20%20%20%20%20%20context_cells.total_bounds%0A%20%20%20%20)%0A%20%20%20%20_context_map%20%3D%20folium.Map(%0A%20%20%20%20%20%20%20%20location%3D%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20(_context_miny%20%2B%20_context_maxy)%20%2F%202%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(_context_minx%20%2B%20_context_maxx)%20%2F%202%2C%0A%20%20%20%20%20%20%20%20%5D%2C%0A%20%20%20%20%20%20%20%20zoom_start%3D16%2C%0A%20%20%20%20%20%20%20%20tiles%3D%22CartoDB%20positron%22%2C%0A%20%20%20%20)%0A%20%20%20%20folium.GeoJson(%0A%20%20%20%20%20%20%20%20context_streets%2C%0A%20%20%20%20%20%20%20%20name%3D%22nearby%20street%20centerlines%22%2C%0A%20%20%20%20%20%20%20%20style_function%3Dlambda%20_%3A%20%7B%22color%22%3A%20%22%239e9e9e%22%2C%20%22weight%22%3A%202%2C%20%22opacity%22%3A%200.45%7D%2C%0A%20%20%20%20%20%20%20%20tooltip%3Dfolium.GeoJsonTooltip(fields%3D%5B%22street_name%22%5D)%2C%0A%20%20%20%20).add_to(_context_map)%0A%20%20%20%20folium.GeoJson(%0A%20%20%20%20%20%20%20%20context_cells%2C%0A%20%20%20%20%20%20%20%20name%3D%22H3%20cells%20touched%20later%22%2C%0A%20%20%20%20%20%20%20%20style_function%3Dlambda%20_%3A%20%7B%22color%22%3A%20%22%23e41a1c%22%2C%20%22weight%22%3A%201%2C%20%22fillOpacity%22%3A%200.08%7D%2C%0A%20%20%20%20%20%20%20%20tooltip%3Dfolium.GeoJsonTooltip(fields%3D%5B%22h3_cell%22%2C%20%22source_row_contribution%22%5D)%2C%0A%20%20%20%20).add_to(_context_map)%0A%20%20%20%20folium.GeoJson(%0A%20%20%20%20%20%20%20%20context_selected%2C%0A%20%20%20%20%20%20%20%20name%3D%22selected%20source%20placement%20geometry%22%2C%0A%20%20%20%20%20%20%20%20style_function%3Dlambda%20_%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22color%22%3A%20%22%23386cb0%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22weight%22%3A%205%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22opacity%22%3A%200.9%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22fillOpacity%22%3A%200.35%2C%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20marker%3Dfolium.CircleMarker(%0A%20%20%20%20%20%20%20%20%20%20%20%20radius%3D6%2C%20color%3D%22%23386cb0%22%2C%20fill%3DTrue%2C%20fill_opacity%3D0.85%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20tooltip%3Dfolium.GeoJsonTooltip(%0A%20%20%20%20%20%20%20%20%20%20%20%20fields%3D%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22street_name%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22abschnitt%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22daypart%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22incident_count%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22placement%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%5D%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20).add_to(_context_map)%0A%20%20%20%20folium.LayerControl().add_to(_context_map)%0A%20%20%20%20_context_map%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%207.1%20Source%20row%20and%20placement%20geometry%0A%0A%20%20%20%20This%20is%20the%20geometry-building%20step%20for%20the%20selected%20source%20row.%0A%0A%20%20%20%20The%20table%20reports%20the%20exact%20placement%20metadata%3A%0A%0A%20%20%20%20-%20%60placement%60%20tells%20which%20rule%20was%20used%20(%60address_points%60%2C%20%60whole_street_unknown%60%2C%0A%20%20%20%20%20%20%60whole_street_unmatched_section%60%2C%20or%20%60unplaceable%60).%0A%20%20%20%20-%20%60address_points%60%20is%20the%20number%20of%20real%20address%20coordinates%20collected%20for%20an%0A%20%20%20%20%20%20address-matched%20section.%20It%20is%20zero%20for%20street-level%20fallback%20rows.%0A%20%20%20%20-%20%60geometry_type%60%20tells%20whether%20the%20aggregation%20input%20is%20point-like%20or%20line-like.%0A%20%20%20%20-%20%60length_m%60%20is%20meaningful%20for%20street%20fallback%20geometries.%20It%20is%20normally%20zero%20for%0A%20%20%20%20%20%20%60MULTIPOINT%60%20address%20geometry.%0A%20%20%20%20-%20%60bbox_area_m2%60%20is%20a%20quick%20scale%20check%20for%20how%20spatially%20spread%20the%20geometry%20is.%0A%0A%20%20%20%20The%20map%20below%20makes%20the%20same%20choice%20visible.%20Blue%20is%20the%20constructed%20aggregation%0A%20%20%20%20geometry.%20If%20the%20selected%20row%20is%20address-matched%2C%20green%20points%20show%20the%20actual%0A%20%20%20%20addresses%20that%20were%20collected%20into%20that%20geometry.%20If%20it%20is%20a%20fallback%20row%2C%20there%20may%0A%20%20%20%20be%20no%20green%20points%20because%20the%20geometry%20is%20the%20street%20centerline%20itself.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(PIPELINE_CTES%2C%20q%2C%20trace_abschnitt%2C%20trace_daypart%2C%20trace_street)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20%20%20abschnitt%2C%0A%20%20%20%20%20%20%20%20%20%20daypart%2C%0A%20%20%20%20%20%20%20%20%20%20incident_count%2C%0A%20%20%20%20%20%20%20%20%20%20placement%2C%0A%20%20%20%20%20%20%20%20%20%20address_points%2C%0A%20%20%20%20%20%20%20%20%20%20ST_GeometryType(geometry)%20AS%20geometry_type%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(ST_Length(geometry)%3A%3Anumeric%2C%201)%20AS%20length_m%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(ST_Area(ST_Envelope(geometry))%3A%3Anumeric%2C%201)%20AS%20bbox_area_m2%0A%20%20%20%20%20%20%20%20FROM%20crime_geom%0A%20%20%20%20%20%20%20%20WHERE%20street_name%20%3D%20%3Astreet%0A%20%20%20%20%20%20%20%20%20%20AND%20abschnitt%20%3D%20%3Aabschnitt%0A%20%20%20%20%20%20%20%20%20%20AND%20daypart%20%3D%20%3Adaypart%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20street%3Dtrace_street%2C%0A%20%20%20%20%20%20%20%20abschnitt%3Dtrace_abschnitt%2C%0A%20%20%20%20%20%20%20%20daypart%3Dtrace_daypart%2C%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(PIPELINE_CTES%2C%20folium%2C%20qg%2C%20trace_abschnitt%2C%20trace_daypart%2C%20trace_street)%3A%0A%20%20%20%20trace_geometry%20%3D%20qg(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20%20%20abschnitt%2C%0A%20%20%20%20%20%20%20%20%20%20daypart%2C%0A%20%20%20%20%20%20%20%20%20%20incident_count%2C%0A%20%20%20%20%20%20%20%20%20%20placement%2C%0A%20%20%20%20%20%20%20%20%20%20ST_Transform(geometry%2C%204326)%20AS%20geometry%0A%20%20%20%20%20%20%20%20FROM%20crime_geom%0A%20%20%20%20%20%20%20%20WHERE%20street_name%20%3D%20%3Astreet%0A%20%20%20%20%20%20%20%20%20%20AND%20abschnitt%20%3D%20%3Aabschnitt%0A%20%20%20%20%20%20%20%20%20%20AND%20daypart%20%3D%20%3Adaypart%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20crs%3D4326%2C%0A%20%20%20%20%20%20%20%20street%3Dtrace_street%2C%0A%20%20%20%20%20%20%20%20abschnitt%3Dtrace_abschnitt%2C%0A%20%20%20%20%20%20%20%20daypart%3Dtrace_daypart%2C%0A%20%20%20%20)%0A%20%20%20%20trace_address_points%20%3D%20qg(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20%20%20abschnitt%2C%0A%20%20%20%20%20%20%20%20%20%20ST_Transform((ST_DumpPoints(geometry)).geom%2C%204326)%20AS%20geometry%0A%20%20%20%20%20%20%20%20FROM%20section_points%0A%20%20%20%20%20%20%20%20WHERE%20street_name%20%3D%20%3Astreet%0A%20%20%20%20%20%20%20%20%20%20AND%20abschnitt%20%3D%20%3Aabschnitt%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20crs%3D4326%2C%0A%20%20%20%20%20%20%20%20street%3Dtrace_street%2C%0A%20%20%20%20%20%20%20%20abschnitt%3Dtrace_abschnitt%2C%0A%20%20%20%20)%0A%0A%20%20%20%20_trace_geometry_centroid%20%3D%20trace_geometry.geometry.iloc%5B0%5D.centroid%0A%20%20%20%20_trace_geometry_map%20%3D%20folium.Map(%0A%20%20%20%20%20%20%20%20location%3D%5B_trace_geometry_centroid.y%2C%20_trace_geometry_centroid.x%5D%2C%0A%20%20%20%20%20%20%20%20zoom_start%3D17%2C%0A%20%20%20%20%20%20%20%20tiles%3D%22CartoDB%20positron%22%2C%0A%20%20%20%20)%0A%20%20%20%20folium.GeoJson(%0A%20%20%20%20%20%20%20%20trace_geometry%2C%0A%20%20%20%20%20%20%20%20name%3D%22selected%20placement%20geometry%22%2C%0A%20%20%20%20%20%20%20%20style_function%3Dlambda%20_%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22color%22%3A%20%22%23386cb0%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22weight%22%3A%205%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22opacity%22%3A%200.85%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22fillOpacity%22%3A%200.2%2C%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20marker%3Dfolium.CircleMarker(%0A%20%20%20%20%20%20%20%20%20%20%20%20radius%3D6%2C%20color%3D%22%23386cb0%22%2C%20fill%3DTrue%2C%20fill_opacity%3D0.8%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20tooltip%3Dfolium.GeoJsonTooltip(%0A%20%20%20%20%20%20%20%20%20%20%20%20fields%3D%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22street_name%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22abschnitt%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22daypart%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22incident_count%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22placement%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%5D%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20).add_to(_trace_geometry_map)%0A%20%20%20%20if%20not%20trace_address_points.empty%3A%0A%20%20%20%20%20%20%20%20folium.GeoJson(%0A%20%20%20%20%20%20%20%20%20%20%20%20trace_address_points%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20name%3D%22matched%20address%20points%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20marker%3Dfolium.CircleMarker(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20radius%3D5%2C%20color%3D%22%231b9e77%22%2C%20fill%3DTrue%2C%20fill_opacity%3D0.85%0A%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20tooltip%3Dfolium.GeoJsonTooltip(fields%3D%5B%22street_name%22%2C%20%22abschnitt%22%5D)%2C%0A%20%20%20%20%20%20%20%20).add_to(_trace_geometry_map)%0A%20%20%20%20folium.LayerControl().add_to(_trace_geometry_map)%0A%20%20%20%20_trace_geometry_map%0A%20%20%20%20return%20(trace_geometry%2C)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%207.2%20Densify%2C%20sample%2C%20and%20assign%20samples%20to%20cells%0A%0A%20%20%20%20This%20is%20the%20core%20aggregation%20step%20for%20the%20selected%20source%20row.%0A%0A%20%20%20%20The%20selected%20geometry%20is%20converted%20into%20sample%20points.%20Each%20sample%20point%20carries%3A%0A%0A%20%20%20%20%60incident_count%20%2F%20n_pts%60%0A%0A%20%20%20%20where%20%60n_pts%60%20is%20the%20number%20of%20samples%20produced%20for%20this%20one%0A%20%20%20%20%60(street_name%2C%20abschnitt%2C%20daypart)%60%20feature.%20Every%20sample%20point%20is%20assigned%20to%20one%20H3%0A%20%20%20%20cell%2C%20and%20then%20the%20sample%20shares%20are%20summed%20by%20cell.%0A%0A%20%20%20%20The%20contribution%20table%20answers%20two%20related%20questions%3A%0A%0A%20%20%20%20-%20%60emitted_count_from_source_row%60%3A%20how%20much%20of%20the%20selected%20row's%20actual%20count%20lands%0A%20%20%20%20%20%20in%20that%20H3%20cell.%20If%20the%20source%20row%20has%2030%20incidents%2C%20the%20values%20across%20all%20touched%0A%20%20%20%20%20%20cells%20should%20sum%20back%20to%2030.%0A%20%20%20%20-%20%60notional_incident_share%60%3A%20how%20a%20single%20notional%20incident%20from%20that%20source%20row%20would%0A%20%20%20%20%20%20be%20split.%20Values%20across%20all%20touched%20cells%20should%20sum%20to%201.%0A%0A%20%20%20%20The%20map%20shows%20the%20same%20mechanics%20visually%3A%0A%0A%20%20%20%20-%20H3%20polygons%20are%20the%20cells%20touched%20by%20this%20one%20source%20row.%0A%20%20%20%20-%20Darker%20red%20fill%20means%20a%20larger%20share%20of%20the%20selected%20row%20lands%20in%20that%20cell.%0A%20%20%20%20-%20Black%20sample%20points%20are%20the%20actual%20sample%20carriers.%0A%20%20%20%20-%20The%20blue%20layer%20is%20the%20constructed%20aggregation%20geometry%20underneath%20the%20samples.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(%0A%20%20%20%20PIPELINE_CTES%2C%0A%20%20%20%20q%2C%0A%20%20%20%20trace_abschnitt%2C%0A%20%20%20%20trace_daypart%2C%0A%20%20%20%20trace_incident_count%2C%0A%20%20%20%20trace_street%2C%0A)%3A%0A%20%20%20%20trace_h3_contributions%20%3D%20q(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20cell%3A%3Atext%20AS%20h3_cell%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(*)%20AS%20sample_points%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(SUM(incident_share)%3A%3Anumeric%2C%206)%20AS%20emitted_count_from_source_row%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND((SUM(incident_share)%20%2F%20NULLIF(%3Aincident_count%2C%200))%3A%3Anumeric%2C%206)%0A%20%20%20%20%20%20%20%20%20%20%20%20AS%20notional_incident_share%0A%20%20%20%20%20%20%20%20FROM%20celled%0A%20%20%20%20%20%20%20%20WHERE%20street_name%20%3D%20%3Astreet%0A%20%20%20%20%20%20%20%20%20%20AND%20abschnitt%20%3D%20%3Aabschnitt%0A%20%20%20%20%20%20%20%20%20%20AND%20daypart%20%3D%20%3Adaypart%0A%20%20%20%20%20%20%20%20GROUP%20BY%20cell%0A%20%20%20%20%20%20%20%20ORDER%20BY%20notional_incident_share%20DESC%2C%20h3_cell%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20street%3Dtrace_street%2C%0A%20%20%20%20%20%20%20%20abschnitt%3Dtrace_abschnitt%2C%0A%20%20%20%20%20%20%20%20daypart%3Dtrace_daypart%2C%0A%20%20%20%20%20%20%20%20incident_count%3Dtrace_incident_count%2C%0A%20%20%20%20)%0A%20%20%20%20trace_h3_contributions%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(%0A%20%20%20%20PIPELINE_CTES%2C%0A%20%20%20%20RES%2C%0A%20%20%20%20folium%2C%0A%20%20%20%20qg%2C%0A%20%20%20%20trace_abschnitt%2C%0A%20%20%20%20trace_daypart%2C%0A%20%20%20%20trace_geometry%2C%0A%20%20%20%20trace_incident_count%2C%0A%20%20%20%20trace_street%2C%0A)%3A%0A%20%20%20%20trace_samples%20%3D%20qg(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20f%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20street_name%2C%0A%20%20%20%20%20%20%20%20%20%20abschnitt%2C%0A%20%20%20%20%20%20%20%20%20%20daypart%2C%0A%20%20%20%20%20%20%20%20%20%20h3_lat_lng_to_cell(ST_Transform(pt%2C%204326)%2C%20%7BRES%7D)%3A%3Atext%20AS%20h3_cell%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND((incident_count%20%2F%20n_pts)%3A%3Anumeric%2C%206)%20AS%20emitted_count%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(((incident_count%20%2F%20n_pts)%20%2F%20NULLIF(%3Aincident_count%2C%200))%3A%3Anumeric%2C%206)%0A%20%20%20%20%20%20%20%20%20%20%20%20AS%20notional_incident_share%2C%0A%20%20%20%20%20%20%20%20%20%20ST_Transform(pt%2C%204326)%20AS%20geometry%0A%20%20%20%20%20%20%20%20FROM%20samples%0A%20%20%20%20%20%20%20%20WHERE%20street_name%20%3D%20%3Astreet%0A%20%20%20%20%20%20%20%20%20%20AND%20abschnitt%20%3D%20%3Aabschnitt%0A%20%20%20%20%20%20%20%20%20%20AND%20daypart%20%3D%20%3Adaypart%0A%20%20%20%20%20%20%20%20LIMIT%202500%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20crs%3D4326%2C%0A%20%20%20%20%20%20%20%20street%3Dtrace_street%2C%0A%20%20%20%20%20%20%20%20abschnitt%3Dtrace_abschnitt%2C%0A%20%20%20%20%20%20%20%20daypart%3Dtrace_daypart%2C%0A%20%20%20%20%20%20%20%20incident_count%3Dtrace_incident_count%2C%0A%20%20%20%20)%0A%20%20%20%20trace_cells%20%3D%20qg(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20cell%3A%3Atext%20AS%20h3_cell%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(*)%20AS%20sample_points%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(SUM(incident_share)%3A%3Anumeric%2C%206)%20AS%20emitted_count_from_source_row%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND((SUM(incident_share)%20%2F%20NULLIF(%3Aincident_count%2C%200))%3A%3Anumeric%2C%206)%0A%20%20%20%20%20%20%20%20%20%20%20%20AS%20notional_incident_share%2C%0A%20%20%20%20%20%20%20%20%20%20ST_Transform(h3_cell_to_boundary_geometry(cell)%2C%204326)%20AS%20geometry%0A%20%20%20%20%20%20%20%20FROM%20celled%0A%20%20%20%20%20%20%20%20WHERE%20street_name%20%3D%20%3Astreet%0A%20%20%20%20%20%20%20%20%20%20AND%20abschnitt%20%3D%20%3Aabschnitt%0A%20%20%20%20%20%20%20%20%20%20AND%20daypart%20%3D%20%3Adaypart%0A%20%20%20%20%20%20%20%20GROUP%20BY%20cell%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20crs%3D4326%2C%0A%20%20%20%20%20%20%20%20street%3Dtrace_street%2C%0A%20%20%20%20%20%20%20%20abschnitt%3Dtrace_abschnitt%2C%0A%20%20%20%20%20%20%20%20daypart%3Dtrace_daypart%2C%0A%20%20%20%20%20%20%20%20incident_count%3Dtrace_incident_count%2C%0A%20%20%20%20)%0A%0A%20%20%20%20_trace_minx%2C%20_trace_miny%2C%20_trace_maxx%2C%20_trace_maxy%20%3D%20trace_cells.total_bounds%0A%20%20%20%20_trace_sample_map%20%3D%20folium.Map(%0A%20%20%20%20%20%20%20%20location%3D%5B(_trace_miny%20%2B%20_trace_maxy)%20%2F%202%2C%20(_trace_minx%20%2B%20_trace_maxx)%20%2F%202%5D%2C%0A%20%20%20%20%20%20%20%20zoom_start%3D17%2C%0A%20%20%20%20%20%20%20%20tiles%3D%22CartoDB%20positron%22%2C%0A%20%20%20%20)%0A%20%20%20%20folium.GeoJson(%0A%20%20%20%20%20%20%20%20trace_cells%2C%0A%20%20%20%20%20%20%20%20name%3D%22H3%20cells%20touched%20by%20selected%20row%22%2C%0A%20%20%20%20%20%20%20%20style_function%3Dlambda%20feature%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22color%22%3A%20%22%234d4d4d%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22weight%22%3A%201%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22fillColor%22%3A%20%22%23e41a1c%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22fillOpacity%22%3A%20max(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%200.18%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20min(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%200.75%2C%20float(feature%5B%22properties%22%5D%5B%22notional_incident_share%22%5D)%20*%201.5%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20tooltip%3Dfolium.GeoJsonTooltip(%0A%20%20%20%20%20%20%20%20%20%20%20%20fields%3D%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22h3_cell%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22sample_points%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22emitted_count_from_source_row%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22notional_incident_share%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%5D%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20).add_to(_trace_sample_map)%0A%20%20%20%20folium.GeoJson(%0A%20%20%20%20%20%20%20%20trace_geometry%2C%0A%20%20%20%20%20%20%20%20name%3D%22constructed%20aggregation%20geometry%22%2C%0A%20%20%20%20%20%20%20%20style_function%3Dlambda%20_%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22color%22%3A%20%22%23386cb0%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22weight%22%3A%205%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22opacity%22%3A%200.85%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22fillOpacity%22%3A%200.18%2C%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20marker%3Dfolium.CircleMarker(%0A%20%20%20%20%20%20%20%20%20%20%20%20radius%3D6%2C%20color%3D%22%23386cb0%22%2C%20fill%3DTrue%2C%20fill_opacity%3D0.85%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20tooltip%3Dfolium.GeoJsonTooltip(%0A%20%20%20%20%20%20%20%20%20%20%20%20fields%3D%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22street_name%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22abschnitt%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22daypart%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22incident_count%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22placement%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%5D%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20).add_to(_trace_sample_map)%0A%20%20%20%20folium.GeoJson(%0A%20%20%20%20%20%20%20%20trace_samples%2C%0A%20%20%20%20%20%20%20%20name%3D%22sample%20points%22%2C%0A%20%20%20%20%20%20%20%20marker%3Dfolium.CircleMarker(%0A%20%20%20%20%20%20%20%20%20%20%20%20radius%3D3%2C%20color%3D%22%23000000%22%2C%20fill%3DTrue%2C%20fill_opacity%3D0.8%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20tooltip%3Dfolium.GeoJsonTooltip(%0A%20%20%20%20%20%20%20%20%20%20%20%20fields%3D%5B%22h3_cell%22%2C%20%22emitted_count%22%2C%20%22notional_incident_share%22%5D%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20).add_to(_trace_sample_map)%0A%20%20%20%20folium.LayerControl().add_to(_trace_sample_map)%0A%20%20%20%20_trace_sample_map%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%207.3%20Final%20emitted%20cells%20for%20this%20source%20row%0A%0A%20%20%20%20This%20step%20compares%20the%20selected%20row's%20contribution%20with%20the%20final%20production%20H3%0A%20%20%20%20output.%0A%0A%20%20%20%20Up%20to%20now%2C%20we%20have%20only%20traced%20one%20source%20row.%20But%20%60mobility.zurich_crime_h3%60%20is%20the%0A%20%20%20%20sum%20of%20all%20source%20rows%20that%20touched%20the%20same%20%60(h3_cell%2C%20daypart)%60.%20A%20selected%20row%20may%0A%20%20%20%20dominate%20one%20cell%2C%20or%20it%20may%20be%20a%20small%20part%20of%20a%20busier%20cell%20that%20also%20receives%0A%20%20%20%20contributions%20from%20nearby%20sections.%0A%0A%20%20%20%20The%20table%20columns%20read%20as%3A%0A%0A%20%20%20%20-%20%60source_row_contribution%60%3A%20the%20selected%20row's%20emitted%20count%20in%20that%20H3%20cell.%0A%20%20%20%20-%20%60notional_incident_share%60%3A%20the%20selected%20row's%20one-incident%20split%20into%20that%20cell.%0A%20%20%20%20-%20%60final_h3_crime_total%60%3A%20the%20production%20H3%20value%20after%20all%20rows%20are%20aggregated.%0A%20%20%20%20-%20%60pct_of_final_cell_from_selected_row%60%3A%20how%20much%20of%20the%20final%20cell%20total%20comes%20from%0A%20%20%20%20%20%20the%20selected%20source%20row.%0A%0A%20%20%20%20The%20map%20shades%20the%20touched%20cells%20by%20%60final_h3_crime_total%60%2C%20not%20by%20the%20selected%20row's%0A%20%20%20%20contribution.%20Use%20the%20tooltip%20to%20compare%20the%20selected%20row's%20share%20against%20the%20final%0A%20%20%20%20cell%20value.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(%0A%20%20%20%20PIPELINE_CTES%2C%0A%20%20%20%20q%2C%0A%20%20%20%20trace_abschnitt%2C%0A%20%20%20%20trace_daypart%2C%0A%20%20%20%20trace_incident_count%2C%0A%20%20%20%20trace_street%2C%0A)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20%2C%20trace_contribution%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20cell%3A%3Atext%20AS%20h3_cell%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20daypart%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20SUM(incident_share)%20AS%20source_row_contribution%0A%20%20%20%20%20%20%20%20%20%20FROM%20celled%0A%20%20%20%20%20%20%20%20%20%20WHERE%20street_name%20%3D%20%3Astreet%0A%20%20%20%20%20%20%20%20%20%20%20%20AND%20abschnitt%20%3D%20%3Aabschnitt%0A%20%20%20%20%20%20%20%20%20%20%20%20AND%20daypart%20%3D%20%3Adaypart%0A%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20cell%2C%20daypart%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20t.h3_cell%2C%0A%20%20%20%20%20%20%20%20%20%20t.daypart%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(t.source_row_contribution%3A%3Anumeric%2C%206)%20AS%20source_row_contribution%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND((t.source_row_contribution%20%2F%20NULLIF(%3Aincident_count%2C%200))%3A%3Anumeric%2C%206)%0A%20%20%20%20%20%20%20%20%20%20%20%20AS%20notional_incident_share%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(h.crime_total%3A%3Anumeric%2C%206)%20AS%20final_h3_crime_total%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND((100.0%20*%20t.source_row_contribution%20%2F%20NULLIF(h.crime_total%2C%200))%3A%3Anumeric%2C%202)%0A%20%20%20%20%20%20%20%20%20%20%20%20AS%20pct_of_final_cell_from_selected_row%0A%20%20%20%20%20%20%20%20FROM%20trace_contribution%20t%0A%20%20%20%20%20%20%20%20JOIN%20mobility.zurich_crime_h3%20h%0A%20%20%20%20%20%20%20%20%20%20ON%20h.h3_cell%20%3D%20t.h3_cell%0A%20%20%20%20%20%20%20%20%20AND%20h.daypart%20%3D%20t.daypart%0A%20%20%20%20%20%20%20%20ORDER%20BY%20source_row_contribution%20DESC%2C%20h3_cell%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20street%3Dtrace_street%2C%0A%20%20%20%20%20%20%20%20abschnitt%3Dtrace_abschnitt%2C%0A%20%20%20%20%20%20%20%20daypart%3Dtrace_daypart%2C%0A%20%20%20%20%20%20%20%20incident_count%3Dtrace_incident_count%2C%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(%0A%20%20%20%20PIPELINE_CTES%2C%0A%20%20%20%20folium%2C%0A%20%20%20%20qg%2C%0A%20%20%20%20trace_abschnitt%2C%0A%20%20%20%20trace_daypart%2C%0A%20%20%20%20trace_incident_count%2C%0A%20%20%20%20trace_street%2C%0A)%3A%0A%20%20%20%20final_cell_context%20%3D%20qg(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20%2C%20trace_contribution%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20cell%3A%3Atext%20AS%20h3_cell%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20daypart%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20SUM(incident_share)%20AS%20source_row_contribution%0A%20%20%20%20%20%20%20%20%20%20FROM%20celled%0A%20%20%20%20%20%20%20%20%20%20WHERE%20street_name%20%3D%20%3Astreet%0A%20%20%20%20%20%20%20%20%20%20%20%20AND%20abschnitt%20%3D%20%3Aabschnitt%0A%20%20%20%20%20%20%20%20%20%20%20%20AND%20daypart%20%3D%20%3Adaypart%0A%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20cell%2C%20daypart%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20t.h3_cell%2C%0A%20%20%20%20%20%20%20%20%20%20t.daypart%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(t.source_row_contribution%3A%3Anumeric%2C%206)%20AS%20source_row_contribution%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND((t.source_row_contribution%20%2F%20NULLIF(%3Aincident_count%2C%200))%3A%3Anumeric%2C%206)%0A%20%20%20%20%20%20%20%20%20%20%20%20AS%20notional_incident_share%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(h.crime_total%3A%3Anumeric%2C%206)%20AS%20final_h3_crime_total%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND((100.0%20*%20t.source_row_contribution%20%2F%20NULLIF(h.crime_total%2C%200))%3A%3Anumeric%2C%202)%0A%20%20%20%20%20%20%20%20%20%20%20%20AS%20pct_of_final_cell_from_selected_row%2C%0A%20%20%20%20%20%20%20%20%20%20ST_Transform(h.geometry%2C%204326)%20AS%20geometry%0A%20%20%20%20%20%20%20%20FROM%20trace_contribution%20t%0A%20%20%20%20%20%20%20%20JOIN%20mobility.zurich_crime_h3%20h%0A%20%20%20%20%20%20%20%20%20%20ON%20h.h3_cell%20%3D%20t.h3_cell%0A%20%20%20%20%20%20%20%20%20AND%20h.daypart%20%3D%20t.daypart%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20crs%3D4326%2C%0A%20%20%20%20%20%20%20%20street%3Dtrace_street%2C%0A%20%20%20%20%20%20%20%20abschnitt%3Dtrace_abschnitt%2C%0A%20%20%20%20%20%20%20%20daypart%3Dtrace_daypart%2C%0A%20%20%20%20%20%20%20%20incident_count%3Dtrace_incident_count%2C%0A%20%20%20%20)%0A%0A%20%20%20%20_final_cell_map%20%3D%20final_cell_context.explore(%0A%20%20%20%20%20%20%20%20column%3D%22final_h3_crime_total%22%2C%0A%20%20%20%20%20%20%20%20cmap%3D%22PuRd%22%2C%0A%20%20%20%20%20%20%20%20tiles%3D%22CartoDB%20positron%22%2C%0A%20%20%20%20%20%20%20%20tooltip%3D%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22h3_cell%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22daypart%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22source_row_contribution%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22notional_incident_share%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22final_h3_crime_total%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22pct_of_final_cell_from_selected_row%22%2C%0A%20%20%20%20%20%20%20%20%5D%2C%0A%20%20%20%20%20%20%20%20legend%3DTrue%2C%0A%20%20%20%20%20%20%20%20style_kwds%3D%7B%22weight%22%3A%201%2C%20%22color%22%3A%20%22%23252525%22%2C%20%22fillOpacity%22%3A%200.65%7D%2C%0A%20%20%20%20%20%20%20%20name%3D%22final%20H3%20totals%20for%20touched%20cells%22%2C%0A%20%20%20%20)%0A%20%20%20%20folium.LayerControl().add_to(_final_cell_map)%0A%20%20%20%20_final_cell_map%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%208.%20Emit%3A%20production%20H3%20rows%20and%20tile%20rows%0A%0A%20%20%20%20The%20dbt%20model%20emits%20%60mobility.zurich_crime_h3%60%20as%20the%20queryable%20Mart%20table%3A%0A%20%20%20%20one%20row%20per%20%60(h3_cell%2C%20daypart)%60%20with%20%60crime_total%60%20and%20the%20H3%20polygon%20geometry.%0A%20%20%20%20%60tiles.crime_hexes%60%20is%20the%20Martin-facing%20materialized%20view%20derived%20from%20that%20same%0A%20%20%20%20H3%20output.%0A%0A%20%20%20%20These%20checks%20move%20from%20%22recomputed%20notebook%20logic%22%20to%20%22what%20the%20pipeline%20actually%0A%20%20%20%20published%22%3A%0A%0A%20%20%20%20-%20The%20first%20table%20lists%20the%20highest%20emitted%20H3%20values%20in%20the%20production%20Mart.%20These%0A%20%20%20%20%20%20are%20the%20cells%20a%20downstream%20consumer%20would%20read.%0A%20%20%20%20-%20The%20second%20table%20compares%20the%20Mart%20table%20and%20the%20tile%20materialized%20view%20at%20a%20coarse%0A%20%20%20%20%20%20level%3A%20distinct%20cells%2C%20row%20count%2C%20and%20total%20crime%20signal.%20They%20should%20agree%20unless%0A%20%20%20%20%20%20the%20tile%20view%20has%20intentionally%20changed%20shape.%0A%20%20%20%20-%20The%20third%20table%20recomputes%20the%20notebook%20CTE%20logic%20and%20compares%20it%20row-by-row%20against%0A%20%20%20%20%20%20%60mobility.zurich_crime_h3%60.%20A%20healthy%20result%20is%20%60differing_rows%20%3D%200%60%20and%0A%20%20%20%20%20%20%60absolute_total_delta%20%3D%200%60%2C%20or%20a%20tiny%20floating-point%20residue%20close%20to%20zero.%0A%0A%20%20%20%20This%20is%20the%20handoff%20point%3A%20before%20this%20section%2C%20the%20notebook%20is%20explaining%20the%0A%20%20%20%20transformation.%20From%20here%20on%2C%20it%20is%20verifying%20the%20emitted%20pipeline%20artifacts.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20h3_cell%2C%20daypart%2C%20ROUND(crime_total%3A%3Anumeric%2C%203)%20AS%20crime_total%0A%20%20%20%20%20%20%20%20FROM%20mobility.zurich_crime_h3%0A%20%20%20%20%20%20%20%20ORDER%20BY%20crime_total%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%2025%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20'mobility.zurich_crime_h3'%20AS%20relation%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(DISTINCT%20h3_cell)%20AS%20h3_cells%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(*)%20AS%20rows%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(SUM(crime_total)%3A%3Anumeric%2C%201)%20AS%20crime_total%0A%20%20%20%20%20%20%20%20FROM%20mobility.zurich_crime_h3%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20'tiles.crime_hexes'%20AS%20relation%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(DISTINCT%20h3_cell)%20AS%20h3_cells%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(*)%20AS%20rows%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(SUM(crime_total)%3A%3Anumeric%2C%201)%20AS%20crime_total%0A%20%20%20%20%20%20%20%20FROM%20tiles.crime_hexes%0A%20%20%20%20%20%20%20%20ORDER%20BY%20relation%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(PIPELINE_CTES%2C%20q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20PIPELINE_CTES%0A%20%20%20%20%20%20%20%20%2B%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20COUNT(*)%20AS%20differing_rows%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(COALESCE(SUM(ABS(recomputed.crime_total%20-%20emitted.crime_total))%2C%200)%3A%3Anumeric%2C%206)%0A%20%20%20%20%20%20%20%20%20%20%20%20AS%20absolute_total_delta%0A%20%20%20%20%20%20%20%20FROM%20hexes%20recomputed%0A%20%20%20%20%20%20%20%20FULL%20OUTER%20JOIN%20mobility.zurich_crime_h3%20emitted%0A%20%20%20%20%20%20%20%20%20%20ON%20emitted.h3_cell%20%3D%20recomputed.h3_cell%0A%20%20%20%20%20%20%20%20%20AND%20emitted.daypart%20%3D%20recomputed.daypart%0A%20%20%20%20%20%20%20%20WHERE%0A%20%20%20%20%20%20%20%20%20%20recomputed.h3_cell%20IS%20NULL%0A%20%20%20%20%20%20%20%20%20%20OR%20emitted.h3_cell%20IS%20NULL%0A%20%20%20%20%20%20%20%20%20%20OR%20ABS(recomputed.crime_total%20-%20emitted.crime_total)%20%3E%200.000001%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%209.%20Downstream%20consumption%0A%0A%20%20%20%20Routing%20does%20**not**%20consume%20the%20H3%20layer%20directly.%20The%20H3%20layer%20is%20an%20intermediate%0A%20%20%20%20spatial%20lookup%20surface.%20Before%20the%20GraphHopper%20export%2C%20the%20pipeline%20projects%20the%20H3%0A%20%20%20%20area%20signal%20back%20onto%20route%20graph%20edges%20as%20fixed%20OSM-style%20tags.%0A%0A%20%20%20%20The%20handoff%20path%20is%3A%0A%0A%20%20%20%201.%20%60mobility.zurich_crime_h3%60%20stores%20%60crime_total%60%20by%20%60(h3_cell%2C%20daypart)%60.%0A%20%20%20%202.%20%60zurich_routennetz_crime_scores_by_bucket%60%20takes%20each%20route%20segment%2C%20samples%20a%0A%20%20%20%20%20%20%20representative%20point%20with%20%60ST_PointOnSurface(geometry)%60%2C%20converts%20that%20point%20to%20the%0A%20%20%20%20%20%20%20same%20H3%20resolution%2C%20and%20joins%20to%20%60zurich_crime_h3%60.%0A%20%20%20%203.%20The%20joined%20%60crime_total%60%20is%20converted%20to%20an%20ordinal%20%60crime_score%60%20using%0A%20%20%20%20%20%20%20%60NTILE(router_score_max)%60.%20%600%60%20means%20no%20modeled%20signal%3B%20non-zero%20scores%20are%20ranked%0A%20%20%20%20%20%20%20into%20the%20configured%20score%20range.%0A%20%20%20%204.%20Because%20the%20crime%20source%20has%20daypart%20but%20not%20weekday%2Fweekend%2C%20the%20same%20daypart%0A%20%20%20%20%20%20%20signal%20is%20duplicated%20across%20%60weekday%60%20and%20%60weekend%60%2C%20with%0A%20%20%20%20%20%20%20%60is_day_type_inferred%20%3D%20true%60%20upstream.%0A%20%20%20%205.%20%60zurich_routennetz_temporal_context%60%20combines%20crime%2C%20accident%2C%20and%20transit%20presence%0A%20%20%20%20%20%20%20into%20one%20row%20per%20%60(objectid%2C%20day_type%2C%20daypart)%60.%0A%20%20%20%206.%20%60zurich_routennetz_enriched_temporal%60%20pivots%20those%20rows%20back%20to%20one%20row%20per%20route%0A%20%20%20%20%20%20%20segment%2C%20creating%20columns%20such%20as%20%60crime_score_weekday_night%60%20and%0A%20%20%20%20%20%20%20%60crime_score_weekend_night%60.%0A%20%20%20%207.%20%60graphhopper_foot_network.py%60%20exports%20those%20columns%20as%20OSM%20way%20tags%3A%0A%0A%20%20%20%20%20%20%20%60livemap%3Acrime_score%3A%3Cday_type%3E%3A%3Cdaypart%3E%60%0A%0A%20%20%20%20So%20a%20route%20edge%20does%20not%20carry%20%60h3_cell%60%20or%20%60crime_total%60.%20It%20carries%20the%20resulting%0A%20%20%20%20router-facing%20score%20tags%2C%20for%20example%3A%0A%0A%20%20%20%20%60livemap%3Acrime_score%3Aweekday%3Anight%3D4%60%0A%0A%20%20%20%20GraphHopper%20can%20then%20import%20those%20tags%20as%20encoded%20values%20and%20use%20them%20in%20custom%0A%20%20%20%20weighting.%20The%20summary%20below%20previews%20the%20**crime-score%20part**%20of%20the%20graph%20export%0A%20%20%20%20without%20requiring%20the%20full%20GraphHopper%20export%20view%20to%20exist%20locally.%20It%20derives%20the%0A%20%20%20%20same%20%60livemap%3Acrime_score%3A*%60%20tags%20from%20%60zurich_routennetz_crime_scores_by_bucket%60%0A%20%20%20%20and%20%60stg_zurich__routennetz%60.%0A%0A%20%20%20%20The%20final%20map%20returns%20to%20the%20emitted%20H3%20result.%20Use%20the%20daypart%20dropdown%20to%20inspect%0A%20%20%20%20the%20area%20signal%20behind%20the%20route-edge%20tags.%20Since%20the%20source%20crime%20data%20has%20daypart%0A%20%20%20%20but%20not%20weekday%2Fweekend%2C%20this%20H3%20surface%20has%20no%20weekday%2Fweekend%20selector%3B%0A%20%20%20%20weekday%2Fweekend%20only%20appears%20after%20the%20router%20bucket%20contract%20duplicates%20the%20daypart%0A%20%20%20%20value%20into%20both%20day%20types.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(RES%2C%20q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20WITH%20route_cells%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20objectid%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20h3_lat_lng_to_cell(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20ST_Transform(ST_PointOnSurface(geometry)%2C%204326)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7BRES%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20)%3A%3Atext%20AS%20h3_cell%0A%20%20%20%20%20%20%20%20%20%20FROM%20staging.stg_zurich__routennetz%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20COUNT(*)%20AS%20route_segments%2C%0A%20%20%20%20%20%20%20%20%20%20COUNT(*)%20FILTER%20(WHERE%20h.crime_total%20%3E%200)%20AS%20night_segments_with_crime_signal%2C%0A%20%20%20%20%20%20%20%20%20%20ROUND(100.0%20*%20COUNT(*)%20FILTER%20(WHERE%20h.crime_total%20%3E%200)%20%2F%20COUNT(*)%2C%201)%0A%20%20%20%20%20%20%20%20%20%20%20%20AS%20pct_with_night_signal%0A%20%20%20%20%20%20%20%20FROM%20route_cells%20rc%0A%20%20%20%20%20%20%20%20LEFT%20JOIN%20mobility.zurich_crime_h3%20h%0A%20%20%20%20%20%20%20%20%20%20ON%20h.h3_cell%20%3D%20rc.h3_cell%0A%20%20%20%20%20%20%20%20%20AND%20h.daypart%20%3D%20'night'%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(q)%3A%0A%20%20%20%20q(%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20%20%20%20%20WITH%20pivoted%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20objectid%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20MAX(CASE%20WHEN%20day_type%20%3D%20'weekday'%20AND%20daypart%20%3D%20'morning'%20THEN%20crime_score%20END)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20crime_score_weekday_morning%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20MAX(CASE%20WHEN%20day_type%20%3D%20'weekday'%20AND%20daypart%20%3D%20'afternoon'%20THEN%20crime_score%20END)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20crime_score_weekday_afternoon%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20MAX(CASE%20WHEN%20day_type%20%3D%20'weekday'%20AND%20daypart%20%3D%20'night'%20THEN%20crime_score%20END)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20crime_score_weekday_night%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20MAX(CASE%20WHEN%20day_type%20%3D%20'weekend'%20AND%20daypart%20%3D%20'morning'%20THEN%20crime_score%20END)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20crime_score_weekend_morning%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20MAX(CASE%20WHEN%20day_type%20%3D%20'weekend'%20AND%20daypart%20%3D%20'afternoon'%20THEN%20crime_score%20END)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20crime_score_weekend_afternoon%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20MAX(CASE%20WHEN%20day_type%20%3D%20'weekend'%20AND%20daypart%20%3D%20'night'%20THEN%20crime_score%20END)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20crime_score_weekend_night%0A%20%20%20%20%20%20%20%20%20%20FROM%20mobility.zurich_routennetz_crime_scores_by_bucket%0A%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20objectid%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20r.objectid%2C%0A%20%20%20%20%20%20%20%20%20%20r.name%2C%0A%20%20%20%20%20%20%20%20%20%20COALESCE(p.crime_score_weekday_morning%2C%200)%20AS%20%22livemap%3Acrime_score%3Aweekday%3Amorning%22%2C%0A%20%20%20%20%20%20%20%20%20%20COALESCE(p.crime_score_weekday_afternoon%2C%200)%20AS%20%22livemap%3Acrime_score%3Aweekday%3Aafternoon%22%2C%0A%20%20%20%20%20%20%20%20%20%20COALESCE(p.crime_score_weekday_night%2C%200)%20AS%20%22livemap%3Acrime_score%3Aweekday%3Anight%22%2C%0A%20%20%20%20%20%20%20%20%20%20COALESCE(p.crime_score_weekend_morning%2C%200)%20AS%20%22livemap%3Acrime_score%3Aweekend%3Amorning%22%2C%0A%20%20%20%20%20%20%20%20%20%20COALESCE(p.crime_score_weekend_afternoon%2C%200)%20AS%20%22livemap%3Acrime_score%3Aweekend%3Aafternoon%22%2C%0A%20%20%20%20%20%20%20%20%20%20COALESCE(p.crime_score_weekend_night%2C%200)%20AS%20%22livemap%3Acrime_score%3Aweekend%3Anight%22%0A%20%20%20%20%20%20%20%20FROM%20staging.stg_zurich__routennetz%20r%0A%20%20%20%20%20%20%20%20LEFT%20JOIN%20pivoted%20p%20USING%20(objectid)%0A%20%20%20%20%20%20%20%20WHERE%20r.fuss%20%3D%201%0A%20%20%20%20%20%20%20%20ORDER%20BY%20COALESCE(p.crime_score_weekday_night%2C%200)%20DESC%2C%20r.objectid%0A%20%20%20%20%20%20%20%20LIMIT%2020%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20final_h3_daypart_selector%20%3D%20mo.ui.dropdown(%0A%20%20%20%20%20%20%20%20options%3D%5B%22morning%22%2C%20%22afternoon%22%2C%20%22night%22%5D%2C%0A%20%20%20%20%20%20%20%20value%3D%22night%22%2C%0A%20%20%20%20%20%20%20%20label%3D%22Final%20H3%20daypart%22%2C%0A%20%20%20%20)%0A%20%20%20%20final_h3_daypart_selector%0A%20%20%20%20return%20(final_h3_daypart_selector%2C)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(final_h3_daypart_selector%2C%20folium%2C%20q%2C%20qg)%3A%0A%20%20%20%20_final_h3_scale%20%3D%20q(%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20MIN(crime_total)%20AS%20vmin%2C%0A%20%20%20%20%20%20%20%20%20%20MAX(crime_total)%20AS%20vmax%0A%20%20%20%20%20%20%20%20FROM%20mobility.zurich_crime_h3%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20)%0A%20%20%20%20_final_h3_hexes%20%3D%20qg(%0A%20%20%20%20%20%20%20%20%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20h3_cell%2C%0A%20%20%20%20%20%20%20%20%20%20daypart%2C%0A%20%20%20%20%20%20%20%20%20%20crime_total%2C%0A%20%20%20%20%20%20%20%20%20%20ST_Transform(geometry%2C%204326)%20AS%20geometry%0A%20%20%20%20%20%20%20%20FROM%20mobility.zurich_crime_h3%0A%20%20%20%20%20%20%20%20WHERE%20daypart%20%3D%20%3Adaypart%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20crs%3D4326%2C%0A%20%20%20%20%20%20%20%20daypart%3Dfinal_h3_daypart_selector.value%2C%0A%20%20%20%20)%0A%0A%20%20%20%20_hex_minx%2C%20_hex_miny%2C%20_hex_maxx%2C%20_hex_maxy%20%3D%20_final_h3_hexes.total_bounds%0A%20%20%20%20_final_h3_vmin%20%3D%20float(_final_h3_scale%5B%22vmin%22%5D.iloc%5B0%5D)%0A%20%20%20%20_final_h3_vmax%20%3D%20float(_final_h3_scale%5B%22vmax%22%5D.iloc%5B0%5D)%0A%20%20%20%20_final_h3_map%20%3D%20folium.Map(%0A%20%20%20%20%20%20%20%20location%3D%5B(_hex_miny%20%2B%20_hex_maxy)%20%2F%202%2C%20(_hex_minx%20%2B%20_hex_maxx)%20%2F%202%5D%2C%0A%20%20%20%20%20%20%20%20zoom_start%3D13%2C%0A%20%20%20%20%20%20%20%20tiles%3D%22CartoDB%20dark_matter%22%2C%0A%20%20%20%20)%0A%20%20%20%20_final_h3_layer%20%3D%20_final_h3_hexes.explore(%0A%20%20%20%20%20%20%20%20m%3D_final_h3_map%2C%0A%20%20%20%20%20%20%20%20column%3D%22crime_total%22%2C%0A%20%20%20%20%20%20%20%20cmap%3D%22YlOrRd%22%2C%0A%20%20%20%20%20%20%20%20vmin%3D_final_h3_vmin%2C%0A%20%20%20%20%20%20%20%20vmax%3D_final_h3_vmax%2C%0A%20%20%20%20%20%20%20%20tooltip%3D%5B%22h3_cell%22%2C%20%22daypart%22%2C%20%22crime_total%22%5D%2C%0A%20%20%20%20%20%20%20%20legend%3DTrue%2C%0A%20%20%20%20%20%20%20%20style_kwds%3D%7B%22weight%22%3A%200.7%2C%20%22color%22%3A%20%22%23f7f7f7%22%2C%20%22fillOpacity%22%3A%200.68%7D%2C%0A%20%20%20%20%20%20%20%20name%3Df%22final%20H3%20crime_total%3A%20%7Bfinal_h3_daypart_selector.value%7D%22%2C%0A%20%20%20%20)%0A%20%20%20%20_final_h3_legend_css%20%3D%20%22%22%22%0A%20%20%20%20%3Cstyle%3E%0A%20%20%20%20%20%20.legend%2C%20.legend%20*%20%7B%0A%20%20%20%20%20%20%20%20color%3A%20%23f7f7f7%20!important%3B%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20.legend%20%7B%0A%20%20%20%20%20%20%20%20background%3A%20rgba(20%2C%2020%2C%2020%2C%200.88)%20!important%3B%0A%20%20%20%20%20%20%20%20border%3A%201px%20solid%20%23555%20!important%3B%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%3C%2Fstyle%3E%0A%20%20%20%20%22%22%22%0A%20%20%20%20_final_h3_layer.get_root().html.add_child(folium.Element(_final_h3_legend_css))%0A%20%20%20%20folium.LayerControl().add_to(_final_h3_layer)%0A%20%20%20%20_final_h3_layer%0A%20%20%20%20return%0A%0A%0Aif%20__name__%20%3D%3D%20%22__main__%22%3A%0A%20%20%20%20app.run()%0A
a0e477e53fcbe233710038162facc1da