Medium

### Does the “Catchment Premium” Really Exist? Unmasking Spatial Reality in Shrewsbury with BigQuery GIS & Looker Studio ### Introduction: The Catchment Myth vs. Regional Reality In the UK housing market, few narratives are as deeply entrenched as the “Catchment Area Premium.” Across London and major metropolitan hubs, property listings routinely trumpet proximity to “Outstanding” primary schools, commanding eye-watering price premiums. Families stretch their borrowing capacity simply to fall on the fortunate side of an administrative line. In my previous article on mapping Defra flood risks, I explored the physical geography of Shrewsbury — a historic county town bounded and periodically besieged by the unembanked River Severn. Having engineered a fast GeoParquet pipeline to map Flood Zones 2 and 3 in Looker Studio, a natural economic question emerged: > In a historic regional town facing recurring flood risks, does the primary school catchment premium still reign supreme, or is it eclipsed by environmental vulnerability and urban geography? To answer this, I extended our spatial architecture in Google BigQuery GIS, joining HM Land Registry transaction data across 22 primary school catchments, 3 secondary school zones, and localized flood boundaries. What the data revealed was not a triumph of school branding, but a masterclass in urban economics and topography. ### Data Architecture: Extending the Spatial Pipeline Rather than visualizing individual, noisy property sales on a raw map, my objective was to design a clean, high-performance analytical model in BigQuery that preserves both property-type distinctions and environmental context. Our data stack incorporates four foundational layers: 1. HM Land Registry (Price Paid Data): Transaction records including sale prices, transfer dates, and property classifications: Detached (D), Semi-detached (S), Terraced (T), and Flat (F). 2. ONS Postcode Directory (ONSPD): Providing precise geographic coordinates (ST_GEOGPOINT) for postcode centroids. 3. Shropshire Council School Catchments: Shapefiles defining 22 localized primary catchments and 3 broader secondary school zones. 4. Environment Agency Flood Zones 2 & 3: Our pre-optimized flood boundary table (shrewsbury_flood_zones), processed via QGIS and GeoParquet in the previous project. #### Balancing Performance and Granularity Directly piping hundreds of thousands of individual transactions into Looker Studio introduces latency and skews aggregate calculations. Conversely, rolling up metrics strictly by school name discards vital property characteristics. To solve this, I engineered an intermediate aggregation layer grouped by postcode × property_type × is_flood_area, retaining sample size (total_transactions) and baseline prices to enable dynamic weighted averaging downstream. Figure 1: Final table schema in BigQuery (shrewsbury_property_primary_secondary_final) balancing transaction sample sizes with spatial dimensions. WITH property_points AS ( -- 1. Geocode transaction records to Point geometries SELECT lr.price, lr.property_type, lr.postcode, ST_GEOGPOINT(ons.longitude, ons.latitude) AS geom_point FROM `your_project.land_registry.price_paid` AS lr JOIN `your_project.geo_reference.onspd` AS ons ON lr.postcode = ons.postcode WHERE ons.outcode IN ('SY1', 'SY2', 'SY3') ), joined_catchments AS ( -- 2. Spatial Join with Primary & Secondary boundaries SELECT p.price, p.property_type, p.postcode, p.geom_point, pri.school_name AS School_name, sec.school_name AS secondary_school_name FROM property_points AS p LEFT JOIN `your_project.catchments.primary_schools` AS pri ON ST_CONTAINS(pri.geometry, p.geom_point) LEFT JOIN `your_project.catchments.secondary_schools` AS sec ON ST_CONTAINS(sec.geometry, p.geom_point) ) -- 3. Intersect with Flood Zones and generate pre-aggregated analytical table SELECT jc.School_name, jc.secondary_school_name, jc.property_type, jc.postcode, CASE WHEN fz.geometry IS NOT NULL THEN TRUE ELSE FALSE END AS is_flood_area, ROUND(AVG(jc.price), 0) AS average_sale_price, COUNT(1) AS total_transactions FROM joined_catchments AS jc LEFT JOIN `your_project.environment_agency.shrewsbury_flood_zones` AS fz ON ST_INTERSECTS(fz.geometry, jc.geom_point) WHERE jc.School_name IS NOT NULL GROUP BY 1, 2, 3, 4, 5; ### Visualization in Looker Studio: Uncovering the Baseline To prevent sampling skew across postcodes with unequal turnover, I configured a custom calculated metric in Looker Studio for weighted average pricing: Weighted Average Price = SUM(average_sale_price * total_transactions) / SUM(total_transactions) When rendered across all 22 primary school catchments, the property hierarchy behaves impeccably: Detached houses sit firmly at the top, followed by Semi-detached, Terraced, and Flats. Figure 2: Distribution of property values across primary catchments in Shrewsbury by housing typology (Detached, Semi-Detached, Terraced, Flat). At first glance, significant price gaps appear between catchments. Rural and edge-of-town schools like Longden CofE show massive detached averages (£650k+) driven by expansive rural parcels. Within town boundaries, St George’s Junior and Coleham Primary lead the urban pack, maintaining semi-detached averages near £400k. ### The Reality Check: Flood Risk as the Ultimate Arbitrator The dynamic shifts dramatically the moment we apply our is_flood_area boolean filter. Figure 3: Valuation impact of EA Flood Zones 2 & 3 on core housing typologies (Semi-detached & Terraced) across primary school catchments. By isolating properties directly touching EA Flood Zones 2 and 3, an unyielding market penalty surfaces: Coleham Primary School: • Non-Flood Semi-detached: ~£390k • Flood Zone Semi-detached: ~£300k • Net Discount: -£89,854 (-23%) St George’s Junior School (Frankwell Lowlands): • Non-Flood Semi-detached: ~£400k • Flood Zone Semi-detached: ~£295k • Net Discount: -£105,022 (-26%) Greenfields Primary School: • Non-Flood Semi-detached: ~£274k • Flood Zone Semi-detached: ~£226k • Net Discount: -£47,336 (-17%) The Martin Wilson School (Castlefields): • Non-Flood Terraced: ~£222k • Flood Zone Terraced: ~£192k • Net Discount: -£29,965 (-13%) Regardless of academic accolades or community reputation, crossing the threshold into the River Severn’s flood path imposes an immediate, non-negotiable 20% to 25% price discount within the very same school boundary. ### Deconstructing the Myth: Why Geography Beats Catchments Seeing Coleham’s flooded semi-detached homes hold a £300k baseline — still higher than non-flooded homes in outer suburbs like Mereside (~£270k) — one might be tempted to argue: “The catchment premium is so strong it cushions the flood hit!” However, looking at the structural geography of Shrewsbury completely disproves this assumption. #### 1. Urban Gravity vs. Academic Prestige Coleham sits immediately across the English Bridge from the town center. Its price floor is sustained not by school gates, but by walkable lifestyle amenities, independent shops, and direct pedestrian access to the town core. When comparing Coleham to Castlefields (The Martin Wilson School catchment) — which shares an identical urban-edge location and River Severn flood exposure — flooded terraced homes in Castlefields trade around £190k, compared to £280k in Coleham. This £90k chasm is rooted in housing typology: Castlefields comprises compact, industrial 19th-century worker terraces, whereas Belle Vue and Coleham boast wider Victorian and Edwardian villas with substantial gardens. #### 2. The Absence of Feeder Pressure In London, primary catchments matter immensely because admissions funnel into competitive secondary schools. In Shrewsbury, secondary education across the urban core is unified under a single shared catchment (Shrewsbury Schools) encompassing The Priory, Meole Brace, Belvidere, and Shrewsbury Academy. With no hyper-localized secondary gating mechanism, the primary school “feeder race” largely evaporates. #### 3. Stock Eras Across Suburban Rings Comparing adjacent southwestern residential suburbs further confirms the absence of school-driven pricing: - Radbrook Primary vs. Meole Brace CE Primary: Detached homes in both catchments trade almost identically at ~£460k–£470k. Yet, semi-detached properties in Meole Brace trade ~£70k higher than Radbrook. This reflects architectural epochs: Radbrook’s housing stock is dominated by mature, uniform 1980s–90s suburban estates, whereas Meole Brace’s catchment captures a significant concentration of recent high-spec new-build developments (particularly along the southern urban extensions) alongside historic village plots, commanding a notable new-build premium. ### Conclusion: Spatial Intelligence Over Conventional Wisdom When I set out to analyze the relationship between school catchments and property values, conventional wisdom suggested I would find a distinct educational premium. Instead, my BigQuery GIS pipeline uncovered an entirely different truth: - Physical hazard is absolute: Flood risk enforces an undeniable 20–25% valuation cut within identical school boundaries. - School catchments are secondary to urban structure: In regional towns with unified secondary systems, micro-catchment premiums are practically non-existent. Market prices are driven overwhelmingly by walkability to town, architectural epoch, and topography. Relying on high-level averages often reinforces market myths. By joining open geospatial datasets at granular resolution, spatial data analysts can cut through generalized assumptions and illuminate the true mechanics of local real estate. ### Looking Ahead: The Final Synthesis Over the course of my spatial data projects, I have dissected Shrewsbury from multiple angles: • Environmental Risk: Engineering optimized Defra Flood Zone boundaries via GeoParquet. • Healthcare Infrastructure: Mapping travel-time access to GP clinics and hospitals. ・Urban Development: Tracking planning permissions and heritage conservation in real time. ・Social Economics: Evaluating school catchments against property market fundamentals. In the upcoming capstone article of my spatial analytics work, I will bring all these disparate spatial layers together into a comprehensive Urban Spatial Intelligence Framework — demonstrating how modern data engineering can build a holistic, 360-degree digital twin of a regional town. Stay tuned! --- Does the “Catchment Premium” Really Exist? was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Cloud Run product logo You can now debug for a Cloud Run service or Cloud Run instance* by ssh’ing into a container instance. Make sure you have 1. required roles and 2. have enabled ssh to the service (or cloud run instance). Let’s go through a few examples: “I want to troubleshoot a Cloud Run service. I don’t care which instance id I ssh into.” You can use ssh by just specifying the service name and region. It’ll route you to a container instance. gcloud beta run services ssh hello-ssh \ --region=<YOUR_REGION> --project=<YOUR_PROJECT_ID> You can also specify a revision. “I’ve deployed a Cloud Run service and I want to troubleshoot a specific instance. I have the instance id.” Let’s say you went to cloud logging and got an instance id from the logs. instance id in Cloud Logging You can ssh into that specific instance. gcloud beta run services ssh hello-ssh \ --region=<YOUR_REGION> \ --project=<YOUR_PROJECT_ID> \ --instance=<YOUR_INSTANCE_ID> \ --revision=<YOUR_REVISION_NAME> Got questions? Join the discussion on reddit. *Note: Cloud Run instances is referring to the new resource type alongside services, jobs, and worker pools. --- It’s here! You can now ssh into a cloud run container instance was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.