Scenario Polygons Data Cleaning and Analysis
JKH
2024-04-24
This notebook takes us from the point of having distance rasters and gardens summarized at the polygon level to the point of having summary tables for all five cities in the scaling analysis.
First, we have to import the garden polygon layers. This is a long bit of code, but it can be suppressed in setup when everything is already imported.
if (MetabolismImport == TRUE) {
Dortmund_gardens = st_read("U:/Jake/Urban Gardens/Scenarios/Dortmund/Parcels/Nutzung/ALKIS_ADV_NutzungPolygon.shp",
# If you want to inspect the data to get variables names, etc. you can just pull the first row.
## Still slow because it reads the whole thing, but it takes up less space and is less likely to crash the computer.
# query = 'SELECT * FROM "ALKIS_ADV_NutzungPolygon" WHERE FID = 1')
# Once you're ready, you can import the whole thing and reorganize the columns in the process.
query = 'SELECT aktualit, nutzart, LC_num, LU_NUM,
IndBase_su, CollBase_s, FarmBase_s, RoofBase_s,
IndSlp_sum, CollSlp_su, FarmSlp_su, RoofSlp_su,
IndSun_sum, CollSun_su, FarmSun_su, RoofSun_su,
IndTree_su, CollTree_s, FarmTree_s, RoofTree_s,
Parks_sum, Parking_su,
BaseType, SlpType, SunType, TreeType
FROM "ALKIS_ADV_NutzungPolygon"')
# WHERE FIELD44 = \'EAST JORDAN\'' ## if you want to filter to just one type or something like that.
London_gardens = st_read("U:/Jake/Urban Gardens/Scenarios/London/Parcels/UKMapBaseLayer_forAnalysis.shp",
# If you want to inspect the data to get variables names, etc. you can just pull the first row.
## Still slow because it reads the whole thing, but it takes up less space and is less likely to crash the computer.
# query = 'SELECT * FROM "UKMapBaseLayer_forAnalysis" WHERE FID = 1')
# Once you're ready, you can import the whole thing and reorganize the columns in the process.
query = 'SELECT Feature_Ty, FCC1, FCC2, FCC3, FCC4, Landuse_Or, FEWM_LC, FEWM_LU,
IndBase_su, CollBase_s, FarmBase_s, RoofBase_s,
IndSlp_sum, CollSlp_su, FarmSlp_su, RoofSlp_su,
IndSun_sum, CollSun_su, FarmSun_su, RoofSun_su,
IndTree_su, CollTree_s, FarmTree_s, RoofTree_s,
Parks_sum, Parking_su,
BaseType, SlpType, SunType, TreeType
FROM "UKMapBaseLayer_forAnalysis"')
# WHERE FIELD44 = \'EAST JORDAN\'' ## if you want to filter to just one type or something like that.
Paris_gardens = st_read("U:/Jake/Urban Gardens/Scenarios/Paris/Parcels/PARCELLE_CADASTRALE/PARCELLE_CADASTRALE.shp",
# If you want to inspect the data to get variables names, etc. you can just pull the first row.
## Still slow because it reads the whole thing, but it takes up less space and is less likely to crash the computer.
# query = 'SELECT * FROM "PARCELLE_CADASTRALE" WHERE FID = 1')
# Once you're ready, you can import the whole thing and reorganize the columns in the process.
query = 'SELECT LU_majorit,
IndBase_su, CollBase_s, FarmBase_s, RoofBase_s,
IndSlp_sum, CollSlp_su, FarmSlp_su, RoofSlp_su,
IndSun_sum, CollSun_su, FarmSun_su, RoofSun_su,
IndTree_su, CollTree_s, FarmTree_s, RoofTree_s,
Parks_sum, Parking_su,
BaseType, SlpType, SunType, TreeType
FROM "PARCELLE_CADASTRALE"')
# WHERE FIELD44 = \'EAST JORDAN\'' ## if you want to filter to just one type or something like that.
NYC_gardens = st_read("U:/Jake/Urban Gardens/Scenarios/NYC/Parcels - new/PLUTO 102003.shp",
# If you want to inspect the data to get variables names, etc. you can just pull the first row.
## Still slow because it reads the whole thing, but it takes up less space and is less likely to crash the computer.
# query = 'SELECT * FROM "PLUTO 102003" WHERE FID = 1')
# Once you're ready, you can import the whole thing and reorganize the columns in the process.
query = 'SELECT LandUse, LotArea, BldgArea, NumBldgs, UnitsTotal,
IndBase_su, CollBase_s, FarmBase_s, RoofBase_s,
IndSlp_sum, CollSlp_su, FarmSlp_su, RoofSlp_su,
IndSun_sum, CollSun_su, FarmSun_su, RoofSun_su,
IndTree_su, CollTree_s, FarmTree_s, RoofTree_s,
Parks_sum, Parking_su,
BaseType, SlpType, SunType, TreeType
FROM "PLUTO 102003"')
# IndBase__1, CollBase_1, FarmBase_1, RoofBase_1,
# IndSlp_s_1, CollSlp__1, FarmSlp__1, RoofSlp__1,
# IndSun_s_1, CollSun__1, FarmSun__1, RoofSun__1,
# IndTree__1, CollTree_1, FarmTree_1, RoofTree_1,
# Parks_sum, Parking_su,
# BaseType, SlpType, SunType, TreeType
# WHERE FIELD44 = \'EAST JORDAN\'' ## if you want to filter to just one type or something like that.
Gorzow_gardens = st_read("U:/Jake/Urban Gardens/Scenarios/Gorzow/Parcels/Gorzow parcels - dzialki - LU.shp",
# If you want to inspect the data to get variables names, etc. you can just pull the first row.
## Still slow because it reads the whole thing, but it takes up less space and is less likely to crash the computer.
# query = 'SELECT * FROM "Gorzow parcels - dzialki - LU" WHERE FID = 1')
# Once you're ready, you can import the whole thing and reorganize the columns in the process.
query = 'SELECT LU_majorit,
IndBase__1, CollBase_1, FarmBase_1, RoofBase_1,
IndSlp_s_1, CollSlp__1, FarmSlp__1, RoofSlp__1,
IndSun_s_1, CollSun__1, FarmSun__1, RoofSun__1,
IndTree__1, CollTree_1, FarmTree_1, RoofTree_1,
Parks_su_1, Parking__1,
BaseType, SlpType, SunType, TreeType
FROM "Gorzow parcels - dzialki - LU"')
# WHERE FIELD44 = \'EAST JORDAN\'' ## if you want to filter to just one type or something like that.
Gorzow_gardens %<>% rename(
IndBase_su = IndBase__1,
CollBase_s = CollBase_1,
FarmBase_s = FarmBase_1,
RoofBase_s = RoofBase_1,
IndSlp_sum = IndSlp_s_1,
CollSlp_su = CollSlp__1,
FarmSlp_su = FarmSlp__1,
RoofSlp_su = RoofSlp__1,
IndSun_sum = IndSun_s_1,
CollSun_su = CollSun__1,
FarmSun_su = FarmSun__1,
RoofSun_su = RoofSun__1,
IndTree_su = IndTree__1,
CollTree_s = CollTree_1,
FarmTree_s = FarmTree_1,
RoofTree_s = RoofTree_1,
Parks_sum = Parks_su_1,
Parking_su = Parking__1
)
}## Reading query `SELECT aktualit, nutzart, LC_num, LU_NUM,
## IndBase_su, CollBase_s, FarmBase_s, RoofBase_s,
## IndSlp_sum, CollSlp_su, FarmSlp_su, RoofSlp_su,
## IndSun_sum, CollSun_su, FarmSun_su, RoofSun_su,
## IndTree_su, CollTree_s, FarmTree_s, RoofTree_s,
## Parks_sum, Parking_su,
## BaseType, SlpType, SunType, TreeType
## FROM "ALKIS_ADV_NutzungPolygon"'
## from data source `U:\Jake\Urban Gardens\Scenarios\Dortmund\Parcels\Nutzung\ALKIS_ADV_NutzungPolygon.shp'
## using driver `ESRI Shapefile'
## Simple feature collection with 65027 features and 26 fields
## Geometry type: POLYGON
## Dimension: XY
## Bounding box: xmin: 382191.9 ymin: 5697134 xmax: 405527.3 ymax: 5717741
## Projected CRS: ETRS89 / UTM zone 32N
## Reading query `SELECT Feature_Ty, FCC1, FCC2, FCC3, FCC4, Landuse_Or, FEWM_LC, FEWM_LU,
## IndBase_su, CollBase_s, FarmBase_s, RoofBase_s,
## IndSlp_sum, CollSlp_su, FarmSlp_su, RoofSlp_su,
## IndSun_sum, CollSun_su, FarmSun_su, RoofSun_su,
## IndTree_su, CollTree_s, FarmTree_s, RoofTree_s,
## Parks_sum, Parking_su,
## BaseType, SlpType, SunType, TreeType
## FROM "UKMapBaseLayer_forAnalysis"'
## from data source `U:\Jake\Urban Gardens\Scenarios\London\Parcels\UKMapBaseLayer_forAnalysis.shp'
## using driver `ESRI Shapefile'
## Simple feature collection with 13483341 features and 30 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: 491000 ymin: 152000 xmax: 562000 ymax: 204000
## Projected CRS: OSGB36 / British National Grid
## Reading query `SELECT LU_majorit,
## IndBase_su, CollBase_s, FarmBase_s, RoofBase_s,
## IndSlp_sum, CollSlp_su, FarmSlp_su, RoofSlp_su,
## IndSun_sum, CollSun_su, FarmSun_su, RoofSun_su,
## IndTree_su, CollTree_s, FarmTree_s, RoofTree_s,
## Parks_sum, Parking_su,
## BaseType, SlpType, SunType, TreeType
## FROM "PARCELLE_CADASTRALE"'
## from data source `U:\Jake\Urban Gardens\Scenarios\Paris\Parcels\PARCELLE_CADASTRALE\PARCELLE_CADASTRALE.shp'
## using driver `ESRI Shapefile'
## Simple feature collection with 717551 features and 23 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: 637302.1 ymin: 6838640 xmax: 671752.1 ymax: 6879246
## Projected CRS: RGF93 v1 / Lambert-93
## Reading query `SELECT LandUse, LotArea, BldgArea, NumBldgs, UnitsTotal,
## IndBase_su, CollBase_s, FarmBase_s, RoofBase_s,
## IndSlp_sum, CollSlp_su, FarmSlp_su, RoofSlp_su,
## IndSun_sum, CollSun_su, FarmSun_su, RoofSun_su,
## IndTree_su, CollTree_s, FarmTree_s, RoofTree_s,
## Parks_sum, Parking_su,
## BaseType, SlpType, SunType, TreeType
## FROM "PLUTO 102003"'
## from data source `U:\Jake\Urban Gardens\Scenarios\NYC\Parcels - new\PLUTO 102003.shp'
## using driver `ESRI Shapefile'
## Simple feature collection with 856813 features and 27 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: 1810874 ymin: 544053 xmax: 1851221 ymax: 596833.3
## Projected CRS: USA_Contiguous_Albers_Equal_Area_Conic
## Reading query `SELECT LU_majorit,
## IndBase__1, CollBase_1, FarmBase_1, RoofBase_1,
## IndSlp_s_1, CollSlp__1, FarmSlp__1, RoofSlp__1,
## IndSun_s_1, CollSun__1, FarmSun__1, RoofSun__1,
## IndTree__1, CollTree_1, FarmTree_1, RoofTree_1,
## Parks_su_1, Parking__1,
## BaseType, SlpType, SunType, TreeType
## FROM "Gorzow parcels - dzialki - LU"'
## from data source `U:\Jake\Urban Gardens\Scenarios\Gorzow\Parcels\Gorzow parcels - dzialki - LU.shp'
## using driver `ESRI Shapefile'
## Simple feature collection with 28773 features and 23 fields
## Geometry type: POLYGON
## Dimension: XY
## Bounding box: xmin: 238427.6 ymin: 541925.1 xmax: 250938 ymax: 552705.7
## Projected CRS: ETRS_1989_UWPP_1992Next, we do a bit of data cleaning to make sure the land use codes are correct. This code block is suppressed in the Markdown, it doesn’t do anything of note for the results.
Next, we summarize all five cities’ data. I’ve included Dortmund as an example and suppressed the rest, since they’re all identical.
Dortmund_summary <- rbind(
Dortmund_gardens %>% as_tibble() %>% group_by(Industrial_lot) %>% summarise(
City = "Dortmund",
Scenario = "Base",
Ind_all = sum(IndBase_su, na.rm = TRUE),
Ind_100 = sum(IndBase_su[BaseType==1], na.rm = TRUE),
Ind_10 = sum(IndBase_su[IndBase_su>=10], na.rm = TRUE),
IndWithRoofs_10 = sum(IndBase_su[IndBase_su>=10], na.rm = TRUE) + sum(RoofBase_s[IndBase_su>=10], na.rm = TRUE),
Coll_all = sum(CollBase_s, na.rm = TRUE),
Coll_100 = sum(CollBase_s[BaseType==2], na.rm = TRUE),
CollWithRoofs_100 = sum(CollBase_s[BaseType==2], na.rm = TRUE) + sum(RoofBase_s[BaseType == 2], na.rm = TRUE),
Farm_all = sum(FarmBase_s, na.rm = TRUE),
Farm_100 = sum(FarmBase_s[BaseType==3], na.rm = TRUE),
FarmWithRoofs_100 = sum(FarmBase_s[BaseType==3], na.rm = TRUE) + sum(RoofBase_s[BaseType == 3], na.rm = TRUE),
UncategorizedRoof = sum(RoofBase_s[BaseType == 4], na.rm = TRUE),
IndWithRoofs_LUFixForRoofs = sum(IndBase_su[IndBase_su>=10], na.rm = TRUE) + sum(RoofBase_s[IndBase_su>=10 |LU_NUM == 10 | LU_NUM == 11], na.rm = TRUE),
CollWithRoofs_LUFixForRoofs = sum(CollBase_s[BaseType==2], na.rm = TRUE) + sum(RoofBase_s[BaseType == 2 |LU_NUM == 12 | LU_NUM == 13 | LU_NUM == 21 |LU_NUM == 24 |LU_NUM == 34 |LU_NUM == 35 |LU_NUM == 70], na.rm = TRUE),
FarmWithRoofs_LUFixForRoofs = sum(FarmBase_s[BaseType==3], na.rm = TRUE) + sum(RoofBase_s[BaseType == 3 |LU_NUM == 22 | LU_NUM == 23], na.rm = TRUE),
ParksUA = sum(Parks_sum, na.rm = TRUE),
ParkingUA = sum(Parking_su, na.rm = TRUE)
),
Dortmund_gardens %>% as_tibble() %>% group_by(Industrial_lot) %>% summarise(
City = "Dortmund",
Scenario = "Ignore slope",
Ind_all = sum(IndSlp_sum, na.rm = TRUE),
Ind_100 = sum(IndSlp_sum[SlpType==1], na.rm = TRUE),
Ind_10 = sum(IndSlp_sum[IndSlp_sum>=10], na.rm = TRUE),
IndWithRoofs_10 = sum(IndSlp_sum[IndSlp_sum>=10], na.rm = TRUE) + sum(RoofSlp_su[IndSlp_sum>=10], na.rm = TRUE),
Coll_all = sum(CollSlp_su, na.rm = TRUE),
Coll_100 = sum(CollSlp_su[SlpType==2], na.rm = TRUE),
CollWithRoofs_100 = sum(CollSlp_su[SlpType==2], na.rm = TRUE) + sum(RoofSlp_su[SlpType == 2], na.rm = TRUE),
Farm_all = sum(FarmSlp_su, na.rm = TRUE),
Farm_100 = sum(FarmSlp_su[SlpType==3], na.rm = TRUE),
FarmWithRoofs_100 = sum(FarmSlp_su[SlpType==3], na.rm = TRUE) + sum(RoofSlp_su[SlpType == 3], na.rm = TRUE),
UncategorizedRoof = sum(RoofSlp_su[SlpType == 4], na.rm = TRUE),
IndWithRoofs_LUFixForRoofs = sum(IndSlp_sum[IndSlp_sum>=10], na.rm = TRUE) + sum(RoofSlp_su[IndSlp_sum>=10 | LU_NUM == 10 | LU_NUM == 11], na.rm = TRUE),
CollWithRoofs_LUFixForRoofs = sum(CollSlp_su[SlpType==2], na.rm = TRUE) + sum(RoofSlp_su[SlpType == 2 | LU_NUM == 12 | LU_NUM == 13 | LU_NUM == 21 |LU_NUM == 24 |LU_NUM == 34 |LU_NUM == 35 |LU_NUM == 70], na.rm = TRUE),
FarmWithRoofs_LUFixForRoofs = sum(FarmSlp_su[SlpType==3], na.rm = TRUE) + sum(RoofSlp_su[SlpType == 3 | LU_NUM == 22 | LU_NUM == 23], na.rm = TRUE),
ParksUA = 0,
ParkingUA = 0
),
Dortmund_gardens %>% as_tibble() %>% group_by(Industrial_lot) %>% summarise(
City = "Dortmund",
Scenario = "Ignore sunlight availability",
Ind_all = sum(IndSun_sum, na.rm = TRUE),
Ind_100 = sum(IndSun_sum[SunType==1], na.rm = TRUE),
Ind_10 = sum(IndSun_sum[IndSun_sum>=10], na.rm = TRUE),
IndWithRoofs_10 = sum(IndSun_sum[IndSun_sum>=10], na.rm = TRUE) + sum(RoofSun_su[IndSun_sum>=10], na.rm = TRUE),
Coll_all = sum(CollSun_su, na.rm = TRUE),
Coll_100 = sum(CollSun_su[SunType==2], na.rm = TRUE),
CollWithRoofs_100 = sum(CollSun_su[SunType==2], na.rm = TRUE) + sum(RoofSun_su[SunType == 2], na.rm = TRUE),
Farm_all = sum(FarmSun_su, na.rm = TRUE),
Farm_100 = sum(FarmSun_su[SunType==3], na.rm = TRUE),
FarmWithRoofs_100 = sum(FarmSun_su[SunType==3], na.rm = TRUE) + sum(RoofSun_su[SunType == 3], na.rm = TRUE),
UncategorizedRoof = sum(RoofSun_su[SunType == 4], na.rm = TRUE),
IndWithRoofs_LUFixForRoofs = sum(IndSun_sum[IndSun_sum>=10], na.rm = TRUE) + sum(RoofSun_su[IndSun_sum>=10 | LU_NUM == 10 | LU_NUM == 11], na.rm = TRUE),
CollWithRoofs_LUFixForRoofs = sum(CollSun_su[SunType==2], na.rm = TRUE) + sum(RoofSun_su[SunType == 2 | LU_NUM == 12 | LU_NUM == 13 | LU_NUM == 21 |LU_NUM == 24 |LU_NUM == 34 |LU_NUM == 35 |LU_NUM == 70], na.rm = TRUE),
FarmWithRoofs_LUFixForRoofs = sum(FarmSun_su[SunType==3], na.rm = TRUE) + sum(RoofSun_su[SunType == 3 | LU_NUM == 22 | LU_NUM == 23], na.rm = TRUE),
ParksUA = 0,
ParkingUA = 0
),
Dortmund_gardens %>% as_tibble() %>% group_by(Industrial_lot) %>% summarise(
City = "Dortmund",
Scenario = "Trees obstruct",
Ind_all = sum(IndTree_su, na.rm = TRUE),
Ind_100 = sum(IndTree_su[TreeType==1], na.rm = TRUE),
Ind_10 = sum(IndTree_su[IndTree_su>=10], na.rm = TRUE),
IndWithRoofs_10 = sum(IndTree_su[IndTree_su>=10], na.rm = TRUE) + sum(RoofTree_s[IndTree_su>=10], na.rm = TRUE),
Coll_all = sum(CollTree_s, na.rm = TRUE),
Coll_100 = sum(CollTree_s[TreeType==2], na.rm = TRUE),
CollWithRoofs_100 = sum(CollTree_s[TreeType==2], na.rm = TRUE) + sum(RoofTree_s[TreeType == 2], na.rm = TRUE),
Farm_all = sum(FarmTree_s, na.rm = TRUE),
Farm_100 = sum(FarmTree_s[TreeType==3], na.rm = TRUE),
FarmWithRoofs_100 = sum(FarmTree_s[TreeType==3], na.rm = TRUE) + sum(RoofTree_s[TreeType == 3], na.rm = TRUE),
UncategorizedRoof = sum(RoofTree_s[TreeType == 4], na.rm = TRUE),
IndWithRoofs_LUFixForRoofs = sum(IndTree_su[IndTree_su>=10], na.rm = TRUE) + sum(RoofTree_s[IndTree_su>=10 | LU_NUM == 10 | LU_NUM == 11], na.rm = TRUE),
CollWithRoofs_LUFixForRoofs = sum(CollTree_s[TreeType==2], na.rm = TRUE) + sum(RoofTree_s[TreeType == 2 | LU_NUM == 12 | LU_NUM == 13 | LU_NUM == 21 |LU_NUM == 24 |LU_NUM == 34 |LU_NUM == 35 |LU_NUM == 70], na.rm = TRUE),
FarmWithRoofs_LUFixForRoofs = sum(FarmTree_s[TreeType==3], na.rm = TRUE) + sum(RoofTree_s[TreeType == 3 | LU_NUM == 22 | LU_NUM == 23], na.rm = TRUE),
ParksUA = 0,
ParkingUA = 0
)
)Finally we get to actually produce the metabolism results.
# Bind them all together so we can just run the transformations once.
IndustrialSummary <- rbind(Dortmund_summary,Gorzow_summary, London_summary, NYC_summary, Paris_summary)
# Import metabolism data from Dorr as relevant.
Appendix_A <- read_excel("Appendix A.xlsx", sheet = "Appendix A")
Appendix_A %<>% mutate(
yield.kg.m2.total.area = harvest.kilograms / Total.farm.area..m2.,
water.use.L.m2.total.area = water.use.L / Total.farm.area..m2.,
energy.kWh.m2.total.area = EnergyUse_kWh / Total.farm.area..m2.,
compost.kg.m2.total.area = compost.use.kg / Total.farm.area..m2.,
econValue.USD.m2.total.area = TotalValue_USD / Total.farm.area..m2.,
PercentSiteForFood = percent.cultivated.area * percent.food.area
)
Metabolism <- Appendix_A %>% group_by(category_short) %>% summarise(
FoodProduction.mean = mean(yield.kg.m2.total.area),
FoodProduction.max = max(yield.kg.m2.total.area),
FoodProduction.min = min(yield.kg.m2.total.area),
WaterUse.mean = mean(water.use.L.m2.total.area),
WaterUse.max = max(water.use.L.m2.total.area),
WaterUse.min = min(water.use.L.m2.total.area),
EnergyUse.mean = mean(energy.kWh.m2.total.area),
EnergyUse.max = max(energy.kWh.m2.total.area),
EnergyUse.min = min(energy.kWh.m2.total.area),
CompostUse.mean = mean(compost.kg.m2.total.area),
CompostUse.max = max(compost.kg.m2.total.area),
CompostUse.min = min(compost.kg.m2.total.area),
EconomicValue.mean = mean(econValue.USD.m2.total.area),
EconomicValue.max = max(econValue.USD.m2.total.area),
EconomicValue.min = min(econValue.USD.m2.total.area),
PercentSiteForFood = mean(PercentSiteForFood, na.rm = TRUE)
) %>% mutate(GardenType = ifelse(category_short == "Collective garden", "Collective",
ifelse(category_short == "Individual garden", "Individual",
ifelse(category_short == "Urban farm", "Farm", "Error"))))
# Import the needed data from other papers, including Hawes, CoDyre, etc. Worth noting that the participation results no longer rely on this code. It's still a useful bit of analysis, but I made it more nuanced in the full-on section below.
Metabolism_nonDorrPaper <- tibble(
GardenType = c("Farm", "Collective", "Individual"),
ServingsProduced = c(5229.4363, 1245.1850, 164.4493),
ServingsPerm2 = c(9.6759697, 5.5355278, 3.2679242), # Servings per m2
CarbonFootprint.avg = c(0.3425121, 0.8145467, 0.3380896), # kg co2 per serving
CarbonFootprint.secondQuartile = c(0.0557, 0.3, 0.0703),
NFootprint = c(0.18, 0, 0.05), # g per serving
PFootprint = c(0.14, 0, 0.03), # g per serving
KFootprint = c(0.23, 0, 0.04), # g per serving
NSavings = c(0.70, 0.88, 0.83), # g savings per serving
PSavings = c(1.26, 1.40, 1.37), # g savings per serving
KSavings = c(0.76, 0.99, 0.95), # g savings per serving
VolunteersNeededMin = c(0, 0.00163, 0.00163), # min persons per m2 gardens
VolunteersNeededMax = c(0, 0.22, 0.22), # max persons per m2 gardens
EmployeesNeededMin = c(0.000163, 0, 0), # min persons per m2 urban farm
EmployeesNeededMax = c(0.022, 0, 0) # max persons per m2 urban farm
)
Metabolism %<>% left_join(Metabolism_nonDorrPaper)
# Creating the actual metabolism results. Still in industrial binary format, will summarize it later when we want the full scenario results.
IndustrialSummary %<>% mutate(
TotalArea = IndWithRoofs_LUFixForRoofs + CollWithRoofs_LUFixForRoofs + FarmWithRoofs_LUFixForRoofs,
TotalAreaGrnd = Ind_10 + Coll_100 + Farm_100,
TotalAreaRoof = TotalArea - TotalAreaGrnd,
FoodProductionGrnd = ((Ind_10 * Metabolism[Metabolism$GardenType == "Individual",]$FoodProduction.mean) +
(Coll_100 * Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.mean) +
(Farm_100 * Metabolism[Metabolism$GardenType == "Farm",]$FoodProduction.mean)),
ServingsTotal = ((IndWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Individual",]$ServingsPerm2) +
(CollWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Collective",]$ServingsPerm2) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$ServingsPerm2)),
FoodProductionTotal = ((IndWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Individual",]$FoodProduction.mean) +
(CollWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.mean) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$FoodProduction.mean)),
FoodProductionRoof = FoodProductionTotal - FoodProductionGrnd,
FoodProductionGrnd.max = ((Ind_10 * Metabolism[Metabolism$GardenType == "Individual",]$FoodProduction.max) +
(Coll_100 * Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.max) +
(Farm_100 * Metabolism[Metabolism$GardenType == "Farm",]$FoodProduction.max)),
FoodProductionTotal.max = ((IndWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Individual",]$FoodProduction.max) +
(CollWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.max) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$FoodProduction.max)),
FoodProductionRoof.max = FoodProductionTotal.max - FoodProductionGrnd.max,
WaterUseGrnd = ((Ind_10 * Metabolism[Metabolism$GardenType == "Individual",]$WaterUse.mean) +
(Coll_100 * Metabolism[Metabolism$GardenType == "Collective",]$WaterUse.mean) +
(Farm_100 * Metabolism[Metabolism$GardenType == "Farm",]$WaterUse.mean)),
WaterUseTotal = ((IndWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Individual",]$WaterUse.mean) +
(CollWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Collective",]$WaterUse.mean) +
(FarmWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Farm",]$WaterUse.mean)),
WaterUseRoof = WaterUseTotal - WaterUseGrnd,
EnergyUseGrnd = ((Ind_10 * Metabolism[Metabolism$GardenType == "Individual",]$EnergyUse.mean) +
(Coll_100 * Metabolism[Metabolism$GardenType == "Collective",]$EnergyUse.mean) +
(Farm_100 * Metabolism[Metabolism$GardenType == "Farm",]$EnergyUse.mean)),
EnergyUseTotal = ((IndWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Individual",]$EnergyUse.mean) +
(CollWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Collective",]$EnergyUse.mean) +
(FarmWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Farm",]$EnergyUse.mean)),
EnergyUseRoof = EnergyUseTotal - EnergyUseGrnd,
CompostUseGrnd = ((Ind_10 * Metabolism[Metabolism$GardenType == "Individual",]$CompostUse.mean) +
(Coll_100 * Metabolism[Metabolism$GardenType == "Collective",]$CompostUse.mean) +
(Farm_100 * Metabolism[Metabolism$GardenType == "Farm",]$CompostUse.mean)),
CompostUseTotal = ((IndWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Individual",]$CompostUse.mean) +
(CollWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Collective",]$CompostUse.mean) +
(FarmWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Farm",]$CompostUse.mean)),
CompostUseRoof = CompostUseTotal - CompostUseGrnd,
CarbonFootprintGrnd.avg = ((Ind_10 * Metabolism[Metabolism$GardenType == "Individual",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Individual",]$CarbonFootprint.avg) +
(Coll_100 * Metabolism[Metabolism$GardenType == "Collective",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Collective",]$CarbonFootprint.avg) +
(Farm_100 * Metabolism[Metabolism$GardenType == "Farm",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Farm",]$CarbonFootprint.avg)),
CarbonFootprintTotal.avg = ((IndWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Individual",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Individual",]$CarbonFootprint.avg) +
(CollWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Collective",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Collective",]$CarbonFootprint.avg) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Farm",]$CarbonFootprint.avg)),
CarbonFootprintRoof.avg = CarbonFootprintTotal.avg - CarbonFootprintGrnd.avg,
CarbonFootprintGrnd.secondQuartile = ((Ind_10 * Metabolism[Metabolism$GardenType == "Individual",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Individual",]$CarbonFootprint.secondQuartile) +
(Coll_100 * Metabolism[Metabolism$GardenType == "Collective",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Collective",]$CarbonFootprint.secondQuartile) +
(Farm_100 * Metabolism[Metabolism$GardenType == "Farm",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Farm",]$CarbonFootprint.secondQuartile)),
CarbonFootprintTotal.secondQuartile = ((IndWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Individual",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Individual",]$CarbonFootprint.secondQuartile) +
(CollWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Collective",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Collective",]$CarbonFootprint.secondQuartile) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Farm",]$CarbonFootprint.secondQuartile)),
CarbonFootprintRoof.secondQuartile = CarbonFootprintTotal.secondQuartile - CarbonFootprintGrnd.secondQuartile,
NSavingsTotal = ((IndWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Individual",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Individual",]$NSavings) +
(CollWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Collective",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Collective",]$NSavings) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Farm",]$NSavings)),
PSavingsTotal = ((IndWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Individual",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Individual",]$PSavings) +
(CollWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Collective",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Collective",]$PSavings) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Farm",]$PSavings)),
KSavingsTotal = ((IndWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Individual",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Individual",]$KSavings) +
(CollWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Collective",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Collective",]$KSavings) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$ServingsPerm2 *
Metabolism[Metabolism$GardenType == "Farm",]$KSavings)),
EconValueGrnd = ((Ind_10 * Metabolism[Metabolism$GardenType == "Individual",]$EconomicValue.mean) +
(Coll_100 * Metabolism[Metabolism$GardenType == "Collective",]$EconomicValue.mean) +
(Farm_100 * Metabolism[Metabolism$GardenType == "Farm",]$EconomicValue.mean)),
EconValueTotal = ((IndWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Individual",]$EconomicValue.mean) +
(CollWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Collective",]$EconomicValue.mean) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$EconomicValue.mean)),
EconValueRoof = EconValueTotal - EconValueGrnd,
VolMinGrnd = ((Ind_10 * Metabolism[Metabolism$GardenType == "Individual",]$VolunteersNeededMin) +
(Coll_100 * Metabolism[Metabolism$GardenType == "Collective",]$VolunteersNeededMin) +
(Farm_100 * Metabolism[Metabolism$GardenType == "Farm",]$VolunteersNeededMin)),
VolMinTotal = ((IndWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Individual",]$VolunteersNeededMin) +
(CollWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Collective",]$VolunteersNeededMin) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$VolunteersNeededMin)),
VolMinRoof = VolMinTotal - VolMinGrnd,
VolMaxGrnd = ((Ind_10 * Metabolism[Metabolism$GardenType == "Individual",]$VolunteersNeededMax) +
(Coll_100 * Metabolism[Metabolism$GardenType == "Collective",]$VolunteersNeededMax) +
(Farm_100 * Metabolism[Metabolism$GardenType == "Farm",]$VolunteersNeededMax)),
VolMaxTotal = ((IndWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Individual",]$VolunteersNeededMax) +
(CollWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Collective",]$VolunteersNeededMax) +
(FarmWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Farm",]$VolunteersNeededMax)),
VolMaxRoof = VolMaxTotal - VolMaxGrnd,
JobsMinGrnd = ((Ind_10 * Metabolism[Metabolism$GardenType == "Individual",]$EmployeesNeededMin) +
(Coll_100 * Metabolism[Metabolism$GardenType == "Collective",]$EmployeesNeededMin) +
(Farm_100 * Metabolism[Metabolism$GardenType == "Farm",]$EmployeesNeededMin)),
JobsMinTotal = ((IndWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Individual",]$EmployeesNeededMin) +
(CollWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Collective",]$EmployeesNeededMin) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$EmployeesNeededMin)),
JobsMinRoof = JobsMinTotal - JobsMinGrnd,
JobsMaxGrnd = ((Ind_10 * Metabolism[Metabolism$GardenType == "Individual",]$EmployeesNeededMax) +
(Coll_100 * Metabolism[Metabolism$GardenType == "Collective",]$EmployeesNeededMax) +
(Farm_100 * Metabolism[Metabolism$GardenType == "Farm",]$EmployeesNeededMax)),
JobsMaxTotal = ((IndWithRoofs_LUFixForRoofs * Metabolism[Metabolism$GardenType == "Individual",]$EmployeesNeededMax) +
(CollWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Collective",]$EmployeesNeededMax) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$EmployeesNeededMax)),
JobsMaxRoof = JobsMaxTotal - JobsMaxGrnd
)
## City metabolism data
CityMetabolism <- tibble(
City = c("Dortmund","Gorzow","London","NYC","Paris"),
CityArea = c(280.676, 85.72, 1575.72, 789.4284, 815.1), #km2
CityPopulation = c(587696, 118011, 8799800, 8622467, 6962961),
PerCapitaVegConsumption = c(193.2224, 178.689, 117.4299, 128.5984, 155.4262), # g / day / person
PerCapitaWaterUse = c(142.92, 147.123, 187.94, 454.2494, 120), # L / day / person
PerCapitaEnergyUse = c(5839.26, 3999.08, 4033.622, 17967.288, 4743.55), #kWh / yr / person
PerCapitaFoodWaste = c(0.2364341080,0.2364341085,0.177,0.1963333333,0.2364341085), # proportion of food purchased
AnnualRainfall = c(80.64,50.61,61.498,125.73,63.43), # cm per year
PerCapitaGDP = c(44781.45,15183.78,71626.63,95334.14,65333.01), # USD per capita
ConvVegGHG = c(0.05672152, 0.05711523, 0.08314678, 0.08100616, 0.06802115) # kg Co2 per serving,
)
# Fold the industrial sites in for the basic summary.
OverallSummary <- IndustrialSummary %>% select(-Industrial_lot) %>% group_by(City,Scenario) %>% summarise_if(is.numeric, sum, na.rm = TRUE)
# Function for writing the variables in a format we want for the final tables. Proportions for sheets to turn into %, km2 instead of m2, etc.
SummaryFunction <- function(SummaryTibble) {
SummaryTibble %<>% left_join(CityMetabolism) %>% mutate(
TotalArea_km2 = TotalArea / 1000000, # convert to square kilometers
TotalAreaGrnd_km2 = TotalAreaGrnd / 1000000,
TotalAreaRoof_km2 = TotalAreaRoof / 1000000,
# IndWithRoofs_km2 = IndWithRoofs_10 / 1000000, # old, missed roofs without a type code.
IndWithRoofs_km2 = IndWithRoofs_LUFixForRoofs / 1000000,
# CollWithRoofs_km2 = CollWithRoofs_100 / 1000000, # old, missed roofs without a type code.
CollWithRoofs_km2 = CollWithRoofs_LUFixForRoofs / 1000000,
# FarmWithRoofs_km2 = FarmWithRoofs_100 / 1000000, # old, missed roofs without a type code.
FarmWithRoofs_km2 = FarmWithRoofs_LUFixForRoofs / 1000000,
UncategorizedRoofArea_km2 = UncategorizedRoof / 1000000,
PropTotalArea = TotalArea_km2 / CityArea,
PropGardenAreaOnRoofs = TotalAreaRoof_km2 / TotalArea_km2,
PropIndGardens = IndWithRoofs_km2 / TotalArea_km2,
PropCollGardens = CollWithRoofs_km2 / TotalArea_km2,
PropFarms = FarmWithRoofs_km2 / TotalArea_km2,
PropVegSupply = FoodProductionTotal / (CityPopulation * PerCapitaVegConsumption / 1000 * 365),
PropVegSupply.max = FoodProductionTotal.max / (CityPopulation * PerCapitaVegConsumption / 1000 * 365),
PropVegSupply.Roof = FoodProductionRoof / (CityPopulation * PerCapitaVegConsumption / 1000 * 365),
PropVegSupply.max.Roof = FoodProductionRoof.max / (CityPopulation * PerCapitaVegConsumption / 1000 * 365),
PropFoodProd.Roof = FoodProductionRoof / FoodProductionTotal,
PropFoodProd.max.Roof = FoodProductionRoof.max / FoodProductionTotal.max,
PropWaterSupply = WaterUseTotal / (CityPopulation * PerCapitaWaterUse * 365),
PropEnergySupply = EnergyUseTotal / (CityPopulation * PerCapitaEnergyUse),
PropFoodWaste = (CompostUseTotal / 0.3) / (CityPopulation * PerCapitaVegConsumption * PerCapitaFoodWaste), # 0.3 converts compost by mass to food waste by mass - 30% yield from food to compost
PropGDP = EconValueTotal / (CityPopulation * PerCapitaGDP),
IndGardenAreaPerCapita = IndWithRoofs_LUFixForRoofs / CityPopulation,
CollGardenAreaPerCapita = CollWithRoofs_LUFixForRoofs / CityPopulation,
FarmAreaPerCapita = FarmWithRoofs_LUFixForRoofs / CityPopulation,
VolunteersPerCapitaMin = VolMinTotal / CityPopulation,
VolunteersPerCapitaMax = VolMaxTotal / CityPopulation,
JobsPerCapitaMin = JobsMinTotal / CityPopulation,
JobsPerCapitaMax = JobsMaxTotal / CityPopulation,
CarbonFootprintPerServing.avg = CarbonFootprintTotal.avg / ServingsTotal,
CarbonFootprintPerServing.secondQuartile = CarbonFootprintTotal.secondQuartile / ServingsTotal,
ExcessCarbonPerServing.avg = CarbonFootprintPerServing.avg - ConvVegGHG,
ExcessCarbonPerServing.secondQuartile = CarbonFootprintPerServing.secondQuartile - ConvVegGHG,
ExcessCarbon.avg = CarbonFootprintTotal.avg - (FoodProductionTotal * ConvVegGHG),
ExcessCarbon.secondQuartile = CarbonFootprintTotal.secondQuartile - (FoodProductionTotal * ConvVegGHG),
ExcessCarbonPerUAParticipant.avg = ExcessCarbon.avg / ((VolunteersPerCapitaMin * CityPopulation) + (JobsPerCapitaMin * CityPopulation)),
ExcessCarbonPerUAParticipant.secondQuartile = ExcessCarbon.secondQuartile / ((VolunteersPerCapitaMin * CityPopulation) + (JobsPerCapitaMin * CityPopulation)),
PropCityParticipatingForBreakevenAt200.avg = ExcessCarbon.avg / 200 / CityPopulation,
PropCityParticipatingForBreakevenAt200.secondQuartile = ExcessCarbon.secondQuartile / 200 / CityPopulation,
NSavingsTotal_kg = NSavingsTotal / 1000,
PSavingsTotal_kg = PSavingsTotal / 1000,
KSavingsTotal_kg = KSavingsTotal / 1000,
RainfallAvail = (AnnualRainfall / 100) * TotalArea * 1000, # L rainfall avail
PropRainfed = RainfallAvail / WaterUseTotal
)
return(SummaryTibble)
}
OverallSummary %<>% SummaryFunction()
IndustrialSummary %<>% SummaryFunction()Just tables for diplay and to make copy-pasting easier.
BaseTable <- OverallSummary %>% ungroup() %>% filter(Scenario == "Base") %>% select(City, CityPopulation, CityArea, TotalArea_km2, TotalAreaGrnd_km2, TotalAreaRoof_km2, PropTotalArea, PropGardenAreaOnRoofs, PropIndGardens, PropCollGardens, PropFarms, PropVegSupply, PropVegSupply.max, PropWaterSupply, PropEnergySupply, PropFoodWaste, PropGDP, IndGardenAreaPerCapita, CollGardenAreaPerCapita, FarmAreaPerCapita, VolunteersPerCapitaMin, VolunteersPerCapitaMax, JobsPerCapitaMin, JobsPerCapitaMax, ConvVegGHG, CarbonFootprintPerServing.avg, ExcessCarbonPerServing.avg, ExcessCarbon.avg, ExcessCarbonPerUAParticipant.avg, PropCityParticipatingForBreakevenAt200.avg, CarbonFootprintPerServing.secondQuartile, ExcessCarbonPerServing.secondQuartile, ExcessCarbon.secondQuartile, ExcessCarbonPerUAParticipant.secondQuartile, PropCityParticipatingForBreakevenAt200.secondQuartile, NSavingsTotal_kg, PSavingsTotal_kg, KSavingsTotal_kg,PropRainfed)
SlpTable <- OverallSummary %>% ungroup() %>% filter(Scenario == "Ignore slope") %>% select(City, CityPopulation, CityArea, TotalArea_km2, TotalAreaGrnd_km2, TotalAreaRoof_km2, PropTotalArea, PropGardenAreaOnRoofs, PropIndGardens, PropCollGardens, PropFarms, PropVegSupply, PropVegSupply.max, PropWaterSupply, PropEnergySupply, PropFoodWaste, PropGDP, IndGardenAreaPerCapita, CollGardenAreaPerCapita, FarmAreaPerCapita, VolunteersPerCapitaMin, VolunteersPerCapitaMax, JobsPerCapitaMin, JobsPerCapitaMax, ConvVegGHG, CarbonFootprintPerServing.avg, ExcessCarbonPerServing.avg, ExcessCarbon.avg, ExcessCarbonPerUAParticipant.avg, PropCityParticipatingForBreakevenAt200.avg, CarbonFootprintPerServing.secondQuartile, ExcessCarbonPerServing.secondQuartile, ExcessCarbon.secondQuartile, ExcessCarbonPerUAParticipant.secondQuartile, PropCityParticipatingForBreakevenAt200.secondQuartile, NSavingsTotal_kg, PSavingsTotal_kg, KSavingsTotal_kg,PropRainfed)
SunTable <- OverallSummary %>% ungroup() %>% filter(Scenario == "Ignore sunlight availability") %>% select(City, CityPopulation, CityArea, TotalArea_km2, TotalAreaGrnd_km2, TotalAreaRoof_km2, PropTotalArea, PropGardenAreaOnRoofs, PropIndGardens, PropCollGardens, PropFarms, PropVegSupply, PropVegSupply.max, PropWaterSupply, PropEnergySupply, PropFoodWaste, PropGDP, IndGardenAreaPerCapita, CollGardenAreaPerCapita, FarmAreaPerCapita, VolunteersPerCapitaMin, VolunteersPerCapitaMax, JobsPerCapitaMin, JobsPerCapitaMax, ConvVegGHG, CarbonFootprintPerServing.avg, ExcessCarbonPerServing.avg, ExcessCarbon.avg, ExcessCarbonPerUAParticipant.avg, PropCityParticipatingForBreakevenAt200.avg, CarbonFootprintPerServing.secondQuartile, ExcessCarbonPerServing.secondQuartile, ExcessCarbon.secondQuartile, ExcessCarbonPerUAParticipant.secondQuartile, PropCityParticipatingForBreakevenAt200.secondQuartile, NSavingsTotal_kg, PSavingsTotal_kg, KSavingsTotal_kg,PropRainfed)
TreeTable <- OverallSummary %>% ungroup() %>% filter(Scenario == "Trees obstruct") %>% select(City, CityPopulation, CityArea, TotalArea_km2, TotalAreaGrnd_km2, TotalAreaRoof_km2, PropTotalArea, PropGardenAreaOnRoofs, PropIndGardens, PropCollGardens, PropFarms, PropVegSupply, PropVegSupply.max, PropWaterSupply, PropEnergySupply, PropFoodWaste, PropGDP, IndGardenAreaPerCapita, CollGardenAreaPerCapita, FarmAreaPerCapita, VolunteersPerCapitaMin, VolunteersPerCapitaMax, JobsPerCapitaMin, JobsPerCapitaMax, ConvVegGHG, CarbonFootprintPerServing.avg, ExcessCarbonPerServing.avg, ExcessCarbon.avg, ExcessCarbonPerUAParticipant.avg, PropCityParticipatingForBreakevenAt200.avg, CarbonFootprintPerServing.secondQuartile, ExcessCarbonPerServing.secondQuartile, ExcessCarbon.secondQuartile, ExcessCarbonPerUAParticipant.secondQuartile, PropCityParticipatingForBreakevenAt200.secondQuartile, NSavingsTotal_kg, PSavingsTotal_kg, KSavingsTotal_kg,PropRainfed)
IndustrialLotsTable <- IndustrialSummary %>% ungroup() %>% filter(Scenario == "Base", Industrial_lot == 1) %>% select(City, CityPopulation, CityArea, TotalArea_km2, TotalAreaGrnd_km2, TotalAreaRoof_km2, PropTotalArea, PropGardenAreaOnRoofs, PropIndGardens, PropCollGardens, PropFarms, PropVegSupply, PropVegSupply.max, PropWaterSupply, PropEnergySupply, PropFoodWaste, PropGDP, IndGardenAreaPerCapita, CollGardenAreaPerCapita, FarmAreaPerCapita, VolunteersPerCapitaMin, VolunteersPerCapitaMax, JobsPerCapitaMin, JobsPerCapitaMax, ConvVegGHG, CarbonFootprintPerServing.avg, ExcessCarbonPerServing.avg, ExcessCarbon.avg, ExcessCarbonPerUAParticipant.avg, PropCityParticipatingForBreakevenAt200.avg, CarbonFootprintPerServing.secondQuartile, ExcessCarbonPerServing.secondQuartile, ExcessCarbon.secondQuartile, ExcessCarbonPerUAParticipant.secondQuartile, PropCityParticipatingForBreakevenAt200.secondQuartile, NSavingsTotal_kg, PSavingsTotal_kg, KSavingsTotal_kg,PropRainfed)
IndustrialPropTable <- (IndustrialLotsTable %>% select(-City) / BaseTable %>% select(-City)) %>% cbind(IndustrialLotsTable$City)
BaseRoofTopTable <- OverallSummary %>% ungroup() %>% filter(Scenario == "Base") %>% select(City, CityPopulation, CityArea, TotalArea_km2, TotalAreaGrnd_km2, TotalAreaRoof_km2, PropTotalArea, PropGardenAreaOnRoofs, PropIndGardens, PropCollGardens, PropFarms, PropVegSupply, PropVegSupply.max, PropVegSupply.Roof, PropVegSupply.max.Roof, PropFoodProd.Roof, PropFoodProd.max.Roof)Import the shapefiles for the access data using queries to grab the variables of interest. Very similar to the import code above. Here’s an example: London_access = st_read(“U:/Jake/Urban Gardens/Scenarios/London/Parcels/Residential Parcels_updated Ind Gardens.shp”, # If you want to inspect the data to get variables names, etc. you can just pull the first row. ## Still slow because it reads the whole thing, but it takes up less space and is less likely to crash the computer. # query = ‘SELECT * FROM “UKMapBaseLayer_forAnalysis” WHERE FID = 1’) # Once you’re ready, you can import the whole thing and reorganize the columns in the process. query = ‘SELECT Feature_Ty, FCC1, FCC2, FCC3, FCC4, Landuse_Or, FEWM_LC, FEWM_LU, IBase_mean, CBase_mean, FBase_mean, RBase_mean, ISlp_mean, CSlp_mean, FSlp_mean, RSlp_mean, ISun_mean, CSun_mean, FSun_mean, RSun_mean, ITree_mean, CTree_mean, FTree_mean, RTree_mean FROM “Residential Parcels_updated Ind Gardens”’)
## Reading query `SELECT Feature_Ty, FCC1, FCC2, FCC3, FCC4, Landuse_Or, FEWM_LC, FEWM_LU,
## IBase_mean, CBase_mean, FBase_mean, RBase_mean,
## ISlp_mean, CSlp_mean, FSlp_mean, RSlp_mean,
## ISun_mean, CSun_mean, FSun_mean, RSun_mean,
## ITree_mean, CTree_mean, FTree_mean, RTree_mean
## FROM "Residential Parcels_updated Ind Gardens"'
## from data source `U:\Jake\Urban Gardens\Scenarios\London\Parcels\Residential Parcels_updated Ind Gardens.shp'
## using driver `ESRI Shapefile'
## Simple feature collection with 10663207 features and 24 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: 491000 ymin: 152000 xmax: 561502.9 ymax: 204000
## Projected CRS: OSGB36 / British National Grid
## Reading query `SELECT LU_majorit,
## IBase_mean, CBase_mean, FBase_mean, RBase_mean,
## ISlp_mean, CSlp_mean, FSlp_mean, RSlp_mean,
## ISun_mean, CSun_mean, FSun_mean, RSun_mean,
## ITree_mean, CTree_mean, FTree_mean, RTree_mean
## FROM "Residential Parcels_sizes corrected"'
## from data source `U:\Jake\Urban Gardens\Scenarios\Paris\Parcels\Residential Parcels_sizes corrected.shp'
## using driver `ESRI Shapefile'
## Simple feature collection with 605064 features and 17 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: 637302.1 ymin: 6838640 xmax: 670888.7 ymax: 6879126
## Projected CRS: RGF93 v1 / Lambert-93
## Reading query `SELECT LandUse, LotArea, BldgArea, NumBldgs, UnitsTotal,
## IBase_mean, CBase_mean, FBase_mean, RBase_mean,
## ISlp_mean, CSlp_mean, FSlp_mean, RSlp_mean,
## ISun_mean, CSun_mean, FSun_mean, RSun_mean,
## ITree_mean, CTree_mean, FTree_mean, RTree_mean
## FROM "Residential 102003"'
## from data source `U:\Jake\Urban Gardens\Scenarios\NYC\Parcels - new\Residential 102003.shp'
## using driver `ESRI Shapefile'
## Simple feature collection with 766432 features and 21 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: 1811140 ymin: 544436.2 xmax: 1851212 ymax: 596547.6
## Projected CRS: USA_Contiguous_Albers_Equal_Area_Conic
## Reading query `SELECT LU_majorit,
## IBase_mean, CBase_mean, FBase_mean, RBase_mean,
## ISlp_mean, CSlp_mean, FSlp_mean, RSlp_mean,
## ISun_mean, CSun_mean, FSun_mean, RSun_mean,
## ITree_mean, CTree_mean, FTree_mean, RTree_mean
## FROM "Residential"'
## from data source `U:\Jake\Urban Gardens\Scenarios\Gorzow\Parcels - Corrected Ind Garden Size\Residential.shp'
## using driver `ESRI Shapefile'
## Simple feature collection with 16343 features and 17 fields
## Geometry type: POLYGON
## Dimension: XY
## Bounding box: xmin: 238848 ymin: 542021.2 xmax: 250430.6 ymax: 551656.9
## Projected CRS: ETRS_1989_UWPP_1992Again, very similar to the summary functions above, but all in one block this time. Takes the access indicators (distance) and summarizes them as things we actually want (binary indicators, followed by proportion of residences qualifying).
Summarizing the participation rates which lead to carbon savings or costs. Quick bit of data parsing, but mostly committed to building plots.
Participation <- tibble(
Study = c("CoDyre Min", "CoDyre Max", "CoDyre Mean", "Sydney Min", "Sydney Max", "Sydney Mean"),
HoursPerM2 = c(0.16, 22, 3.03, 1.34, 10.53, 6)
)
GrowingSeason <- Appendix_A %>% select(City, Growing.Season..Frost) %>% unique() %>% mutate(
GrowingSeasonLength.Weeks = Growing.Season..Frost / 7,
GrowingSeasonLength.VolunteerHours = GrowingSeasonLength.Weeks * 4,
GrowingSeasonLength.EmployeeHours = GrowingSeasonLength.Weeks * 40,
City = ifelse(City == "Gorzow Wlkp", "Gorzow", City),
City = ifelse(City == "New York", "NYC", City))
# Available from the OverallSummary.
# Old results: VolunteersPerCapitaMin, VolunteersPerCapitaMax, JobsPerCapitaMin, JobsPerCapitaMax,
## Can graph all participation up to 100%.
HoursSummary <- OverallSummary %>% select(City, CityPopulation, Scenario, IndWithRoofs_km2, CollWithRoofs_km2, FarmWithRoofs_km2, ConvVegGHG, CarbonFootprintPerServing.avg, CarbonFootprintPerServing.secondQuartile, ExcessCarbonPerServing.avg, ExcessCarbonPerServing.secondQuartile, ExcessCarbon.avg, ExcessCarbon.secondQuartile, ExcessCarbonPerUAParticipant.avg, ExcessCarbonPerUAParticipant.secondQuartile) %>% left_join(GrowingSeason)
Participation_seq <- seq(min(Participation$HoursPerM2),max(Participation$HoursPerM2), length.out = 1000)
for (hrs in Participation_seq) {
vol.hrs.name <- paste("VolHrsReqd - ",hrs)
emply.hrs.name <- paste("EmplHrsReqd - ",hrs)
HoursSummary %<>% mutate("{vol.hrs.name}" := (IndWithRoofs_km2 * 1000000 * hrs) + (CollWithRoofs_km2 * 1000000 * hrs),
"{emply.hrs.name}" := (FarmWithRoofs_km2 * 1000000 * hrs))
# HoursSummary %<>% rename()
}
# # Raw hrs reqd
# HoursSummary %>% pivot_longer(cols = starts_with("VolHrsReqd"), names_to = "HrsPerm2", values_to = "VolHrsReqd") %>%
# mutate(HrsPerm2 = str_replace(HrsPerm2, 'VolHrsReqd - ','')) %>%
# select(-starts_with("EmplHrsReqd")) %>% filter(Scenario == "Base") %>%
# ggplot(aes(x=as.numeric(HrsPerm2), y=VolHrsReqd, color=City)) +
# geom_line() +
# geom_point()
#Hrs as % of available time + incorporating carbon footprint and employees
ParticipationAndCarbon <- HoursSummary %>% filter(Scenario == "Base") %>%
pivot_longer(cols = starts_with("VolHrsReqd"), names_to = "HrsPerm2", values_to = "VolHrsReqd") %>%
select(-starts_with("EmplHrsReqd")) %>%
mutate(HrsPerm2 = str_replace(HrsPerm2, 'VolHrsReqd - ','')) %>%
left_join(HoursSummary %>% filter(Scenario == "Base") %>%
pivot_longer(cols = starts_with("EmplHrsReqd"), names_to = "HrsPerm2", values_to = "EmplHrsReqd") %>%
select(-starts_with("VolHrsReqd")) %>% mutate(HrsPerm2 = str_replace(HrsPerm2, 'EmplHrsReqd - ',''))) %>%
mutate(VolHrsAvail = CityPopulation * GrowingSeasonLength.VolunteerHours,
EmplHrsAvail = CityPopulation * GrowingSeasonLength.VolunteerHours,
PropPopulationNeeded.Vol = ifelse((VolHrsReqd / VolHrsAvail) <= 1, VolHrsReqd / VolHrsAvail, 1.0000000001),
PropPopulationNeeded.Empl = ifelse((EmplHrsReqd / EmplHrsAvail) <= 1, EmplHrsReqd / EmplHrsAvail, 1.0000000001),
PropPopulationNeeded.Total = PropPopulationNeeded.Vol + PropPopulationNeeded.Empl,
NumberVolNeeded = CityPopulation * PropPopulationNeeded.Vol,
NumberEmplNeeded = CityPopulation * PropPopulationNeeded.Empl,
NumberPartNeeded = NumberVolNeeded + NumberEmplNeeded,
CarbonSavingsPerCapitaReqd.avg = ExcessCarbon.avg / NumberPartNeeded,
CarbonSavingsPerCapitaReqd.secondQuartile = ExcessCarbon.secondQuartile / NumberPartNeeded,
HrsPerm2 = as.numeric(HrsPerm2)) %>%
select(-starts_with("EmplHrsReqd"))
ParticipationAndCarbon %>%
ggplot(aes(x=HrsPerm2)) +
geom_line(aes(y=PropPopulationNeeded.Total, color = City), size = 1) +
theme_classic() +
scale_colour_viridis_d() +
scale_y_continuous(limits = c(0,1), labels = scales::percent) +
scale_x_continuous(limits = c(0,10)) +
xlab("Hours spent per m2 of garden (growing season)") +
ylab("Percent of population who \n would have to farm or garden")
ParticipationAndCarbon %>%
ggplot(aes(x=PropPopulationNeeded.Total)) +
geom_line(aes(y=CarbonSavingsPerCapitaReqd.avg, color = City), size = 1) +
theme_classic() +
scale_colour_viridis_d() +
scale_y_continuous(limits = c(0,400)) +
scale_x_continuous(limits = c(0,1), labels = scales::percent) +
xlab("Percent of population farming or gardening") +
ylab("Dietary change per UA participant to \n offset UA excess emissions (kg CO2 eq")
ParticipationAndCarbon %>%
ggplot(aes(x=PropPopulationNeeded.Total)) +
geom_line(aes(y=CarbonSavingsPerCapitaReqd.secondQuartile, color = City), size = 1) +
theme_classic() +
scale_colour_viridis_d() +
scale_y_continuous(limits = c(0,400)) +
scale_x_continuous(limits = c(0,1), labels = scales::percent) +
xlab("Percent of population farming or gardening") +
ylab("Dietary change per UA participant to \n offset UA excess emissions (kg CO2 eq")
# Identifies the breakeven point where the dietary change per participant drops below 200.
print("Breakeven assuming avg garden for 200 kg figure")## [1] "Breakeven assuming avg garden for 200 kg figure"ParticipationAndCarbon %>% filter(Scenario == "Base", CarbonSavingsPerCapitaReqd.avg <= 200) %>%
group_by(City) %>% summarise(BreakevenParticipation = min(PropPopulationNeeded.Total))## # A tibble: 5 × 2
## City BreakevenParticipation
## <chr> <dbl>
## 1 Dortmund 1.01
## 2 Gorzow 1.24
## 3 London 0.397
## 4 NYC 0.159
## 5 Paris 0.319print("Breakeven assuming second quartile garden for 200 kg figure")## [1] "Breakeven assuming second quartile garden for 200 kg figure"ParticipationAndCarbon %>% filter(Scenario == "Base", CarbonSavingsPerCapitaReqd.secondQuartile <= 200) %>%
group_by(City) %>% summarise(BreakevenParticipation = min(PropPopulationNeeded.Total))## # A tibble: 5 × 2
## City BreakevenParticipation
## <chr> <dbl>
## 1 Dortmund 0.210
## 2 Gorzow 0.309
## 3 London 0.0866
## 4 NYC 0.0453
## 5 Paris 0.0963print("Breakeven assuming second quartile garden for Meatless Mondays (130 kg Co2 per yr)")## [1] "Breakeven assuming second quartile garden for Meatless Mondays (130 kg Co2 per yr)"ParticipationAndCarbon %>% filter(Scenario == "Base", CarbonSavingsPerCapitaReqd.secondQuartile <= 130) %>%
group_by(City) %>% summarise(BreakevenParticipation = min(PropPopulationNeeded.Total))## # A tibble: 5 × 2
## City BreakevenParticipation
## <chr> <dbl>
## 1 Dortmund 0.333
## 2 Gorzow 0.472
## 3 London 0.137
## 4 NYC 0.0676
## 5 Paris 0.147Sensitivity analysis of the role of parks and parking
ParksIngSummary <- rbind(Dortmund_summary,Gorzow_summary, London_summary, NYC_summary, Paris_summary) %>% filter(ParksUA != 0) %>% select(-Industrial_lot) %>% group_by(City,Scenario) %>% summarise_if(is.numeric, sum, na.rm = TRUE)
ParksIngSummary %<>% mutate(
TotalArea_km2 = (IndWithRoofs_LUFixForRoofs + CollWithRoofs_LUFixForRoofs + FarmWithRoofs_LUFixForRoofs) / 1000000,
FoodProductionTotal = ((IndWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Individual",]$FoodProduction.mean) +
(CollWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.mean) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$FoodProduction.mean)),
FoodProductionTotal.max = ((IndWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Individual",]$FoodProduction.max) +
(CollWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.max) +
(FarmWithRoofs_LUFixForRoofs *
Metabolism[Metabolism$GardenType == "Farm",]$FoodProduction.max)),
ParkArea_km2 = ParksUA / 1000000,
ParkingArea_km2 = ParkingUA / 1000000,
FoodProductionParksIng.avg = ((ParksUA * Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.mean) +
(ParkingUA * Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.mean)),
ServingsTotalParkIngs = ((ParksUA * Metabolism[Metabolism$GardenType == "Collective",]$ServingsPerm2) +
(ParkingUA * Metabolism[Metabolism$GardenType == "Collective",]$ServingsPerm2)),
FoodProductionParksIng.max = ((ParksUA * Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.max) +
(ParkingUA * Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.max)),
FoodProductionParksIng.avghalf = ((0.5 * ParksUA *
Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.mean) +
(0.5 * ParkingUA * Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.mean)),
ServingsTotal.half = ((0.5 * ParksUA * Metabolism[Metabolism$GardenType == "Collective",]$ServingsPerm2) +
(0.5 * ParkingUA * Metabolism[Metabolism$GardenType == "Collective",]$ServingsPerm2)),
FoodProductionParksIng.halfmax = ((0.5 * ParksUA *
Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.max) +
(0.5 * ParkingUA * Metabolism[Metabolism$GardenType == "Collective",]$FoodProduction.max)),
ParksPropTotal = ParkArea_km2 / TotalArea_km2,
ParkingPropTotal = ParkingArea_km2 / TotalArea_km2,
AvgPercentProd = FoodProductionParksIng.avg / FoodProductionTotal,
MaxPercentProd = FoodProductionParksIng.max / FoodProductionTotal.max,
AvgPercentProd.half = FoodProductionParksIng.avghalf / FoodProductionTotal,
MaxPercentProd.half = FoodProductionParksIng.halfmax / FoodProductionTotal.max
)Actual file closes with some scrap code that may be useful for varying the analysis, excluded from this display version.