forked from musa-509-spring-2023/assignment02
-
Notifications
You must be signed in to change notification settings - Fork 0
/
query01.sql
34 lines (30 loc) · 906 Bytes
/
query01.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
/*
Which bus stop has the largest population within 800 meters? As a rough
estimation, consider any block group that intersects the buffer as being part
of the 800 meter buffer.
*/
with
septa_bus_stop_blockgroups as (
select
stops.stop_id,
'1500000US' || bg.geoid as geoid
from septa.bus_stops as stops
inner join census.blockgroups_2020 as bg
on st_dwithin(stops.geog, bg.geog, 800)
),
septa_bus_stop_surrounding_population as (
select
stops.stop_id,
sum(pop.total) as estimated_pop_800m
from septa_bus_stop_blockgroups as stops
inner join census.population_2020 as pop using (geoid)
group by stops.stop_id
)
select
stops.stop_name,
pop.estimated_pop_800m,
stops.geog
from septa_bus_stop_surrounding_population as pop
inner join septa.bus_stops as stops using (stop_id)
order by pop.estimated_pop_800m desc
limit 8