'Why can't I use a with variable in select?
So I'm trying to calculate the population surround Atlantic Commons.
with atlantic_commons as (
select geom
from nyc_streets
where name = 'Atlantic Commons'
),
ac_nearby_pop_per_sqr_meter as (
select
sum(popn_total) / sum(st_area(ncb.geom)) as pop_per_sqr_meter
from nyc_census_blocks ncb
join atlantic_commons ac on
ST_DWithin(ncb.geom, ac.geom, 50)
)
select (ac_nearby_pop_per_sqr_meter.pop_per_sqr_meter *
power(50, 2) * 3.14159) +
(ac_nearby_pop_per_sqr_meter.pop_per_sqr_meter *
st_length(ac.geom) * 100) as estimated_pop_in_proximity_to_ac
from nyc_census_blocks ncb
join atlantic_commons ac on ST_DWithin(ncb.geom, ac.geom, 50)
group by ac.geom;
It gives me this error
SQL Error [42P01]: ERROR: missing FROM-clause entry for table "ac_nearby_pop_per_sqr_meter"
Position: 295
Error position: line: 8 pos: 294
Not sure why I cannot use ac_nearby_pop_per_sqr_meter.pop_per_sqr_meter variable directly in calculation
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
