healthcare_min_distance.sql

WITH nearby_health AS (
SELECT pop.geo_id, pop.population, CAST(ST_DISTANCE(ST_CENTROID(pop.geog),layer.geometry)/1000 AS INT64) AS distance
FROM
`bigquery-public-data.worldpop.population_grid_1km` AS pop,
`bigquery-public-data.geo_openstreetmap.planet_layers` AS layer
WHERE TRUE
AND pop.country_name = 'Singapore' 
AND layer_name IN ('hospital','doctors')
AND ST_INTERSECTSBOX(layer.geometry, longitude_centroid-5, latitude_centroid-5, longitude_centroid+5, latitude_centroid+5)
AND longitude_centroid > -175 AND longitude_centroid < 175 AND latitude_centroid > -85 AND latitude_centroid < 85
),

pd AS (
SELECT geo_id, population, MIN(distance) AS distance,
ST_GEOGPOINTFROMGEOHASH(geo_id) AS centroid, 
FROM nearby_health
GROUP BY geo_id, population
),

maxes AS (
SELECT SUM(population) AS sum_population, MAX(population) AS max_population, MAX(distance) AS max_distance FROM pd
)

SELECT pd.distance, SUM(pd.population)/maxes.sum_population AS pct_population
FROM pd, maxes
GROUP BY distance, sum_population
ORDER BY distance