1 Versions

  • V.1.1: 2021-10-18: Datatables for waste statistics have been moved from G to V drive

  • V.1.1: 2021-10-18: Names in px table UMH04105.px had been changed so all values for waste were zero, repaired this

  • v.2.0: 2021-10-26: Added a few source tables to fill in voids.

    • Used the last year in CLRTAP data to build estimate for next year. CLRTAP does not change much between years, but I only use wastestream values from this module
    • Use the newest data in NIR report to build estimates for next year. NIR is slightly varying between years, but I am otherwise using AEA to calculate values for t emissions.
    • Reviewed the quantity of waste, these values go into MF.7.2 MEMO
    • Collect all data connections in paths list. Simplifies managing of data location
    • Build data collection from px-web where applicable

  • v.2.1: 2023-05-24: Collect information from the food-flow model to get quantities of waste water from fish industry and added this value to DPO

    • The food production compliation is in the process MFA-Table_BUILD matarflæði. This data needs to be available on the db.
    • use PMDB to store conversion factors

  • v.2.1.1: 2023-05-24: Broke the processing into more managable sub-processes. Data is stored in resouce list that is then zipped together and stored on the db in the end. I prefer this over having a single build-function for the entire compilation.

  • v.3.0: Re-coding of the compilation and translation of documentation into English.

    • Added “compare to last year” graphics to monitor changes between submissions.

2 What is the project?

Table F in EW-MFA collects unretcoverable emission of material into the environment. This can be air emission, wastewater outputs or garbage that goes to unmonitored landfill sites.

The primary complexity of processing the data here is:

  1. EW-MFA has a “residence principle” like the AEA. This means that emissions from non-nationals must be subtracted, and emissions from nationals abroad must be added. This is the same method used in AEA accounting.
  2. Some pollutants, e.g. N\(_2\)O, HFC, NMVOC, NH\(_3\) and PM that are in air emissions are reported in other streams (see Table 22 in the guidelines), but should not be included in the climate figures here. For example: a) Emission of NMVOCs in point 2D3b: Road paving with asphalt from the CLRTAP report should go to class MF_DPO_48 (Dissipative use of products-solvents, laughing gas and other). This quantity should therefore not be included in MF_DPO_19 (Emissions to air : non-methane volatile organic compounds (NMVOC)) b) The emission of NMVOCs in point 2D3A: Domestic solvent use including fungicides from the CLRTAP report should be entered in point MF_DPO_45: Pesticides. This quantity should therefore not be included in MF_DPO_19 (Emissions to air : non-methane volatile organic compounds (NMVOC))

3 Processing and design

The questionnaire has a relatively simple structure for data. Here, we are only looking for the total amount of material released, and not for the categories responsible for the release. The classes of emissions are:

  • MF.7.1 Emissions to air: Release of greenhouse gases: This is where AEA accounting comes in the strongest. EW-MFA follows the same boundaries as AEA accounting. We can fill in with part of the data from CLRTAP and the NIR data from the Environment Agency. Here the following method is used:

    1. All quantities of substances are taken from the AEA accounting as it is published in its entirety
    2. Where data is missing (between 1990-1995) it is taken from the NIR report and “Break in series” is used to mark the division
    3. The amounts not to come from the AEA accounting are found in the CLRTAP and NIR reports and entered as deductions. In some cases, this same amount is re-entered under the correct DPO heading

  • MF.7.2 Waste disposal to the environment: Data here comes largely from published web-tables of information sent to Eurostat. There are more in-depth data available on the internal database, but the raw-data needs a lot of verification before they become official. At the current design of the MFA-compliation I find it unnessecary to build a full set of data cleaning and verification for a single cell in the MFA questionnaire.

  • MF.7.3 Emissions to water: The amount of nitrogen, metals and phosphorus released into lakes and seas is being requested here. We have almost no data on this, and getting the data in has been a challenge for many years.

  • MF.7.4 Dissipative use of products: This includes material use in agriculture and other sector. The data is not accessible in the AEA stack, but the guidelines indicate several points that could benefit from the CLRTAP and NIR data: Here the following procedure is used

    1. If we have material usage data in px tables, they are used
    2. If no data is available in the px tables, numbers are taken from the CLRAP and NIR data. This processing is partly designed into the processing for point 7.1, so here you have to be careful or check whether numbers are double counted.

  • MF.7.5 Dissipative losses: This is supposedly a “shit-happended” category, where I could report spills from industrial sites to nature, abration of breaks and roads (which is a lot in Iceland) and other leaks of material into the environment that can not be consistently attributed to the above categories.

The sub-sections in each section then deal with specific topics. Here you have to read carefully exactly which quantities are being called for. We can assume that the amount should be tons, but whether the amount in “Mineral Fertilizer” is only the amount of nitrogen (e.g. N\(_2\)O and NOx) or whether the amount of the substances is being called for, which can be NH$ _4\(NO\)_3$ or (NH\(_4\))\(_2\)HPO\(_4\)

The following sub-processes in the data collection and compilation are available/run

Sub processes in compilation of Table F
sub_proc skjal_nafn skjal_fannst
table_f_mf71 MFA-Table_F.biti.71.rmd TRUE
table_f_mf72 MFA-Table_F.biti.72.rmd TRUE
table_f_mf73 MFA-Table_F.biti.73.rmd TRUE
table_f_mf74 MFA-Table_F.biti.74.rmd TRUE
table_f_mf75 MFA-Table_F.biti.75.rmd TRUE
table_f_store MFA-Table_F.db_storage.rmd TRUE

4 Data for MF.7.1: Emission to air

Here, the idea is to primarily use the AEA accounting to obtain the emission figures. Where there is a lack, I retrieve figures from the CLRTAP and NIR reports

4.1 Data from AEA accounting

The AEA accounting only goes back to 1995 (it would be good to build the timeseries back to 1990 in the future). However, the figures are well connected to the MFA accounting and come in fairly unchanged. We still need to do a sanity check on the data. Here I first build a mapping table for aea_data column to mfa_cd key. This table may live in the database, but is fine here in the function map.aea_dpo(). The columns in this table are:

  • mfa_cd: the material category for EW-MFA
  • lysing: description
  • efni: Name of compound
  • efni_class: Material class, this is used in later joins
  • aea_gagnadalkur: column from the AEA data
Vörpun úr AEA bókhaldi yfir í MFA
aea_gagnadalkur mfa_cd lysing
losun_biomassCO2 MF_DPO_111 MF.7.1.1.1 - Carbon dioxide (CO2) from biomass combustion
losun_CO2 MF_DPO_112 MF.7.1.1.2 - Carbon dioxide (CO2) excluding biomass combustion
losun_SOX MF_DPO_11A MF.7.1.A - Sulfur dioxide (SO2)
losun_NH3 MF_DPO_11B MF.7.1.B - Ammonia (NH3)
losun_PM10 MF_DPO_11E MF.7.1.E - Particles (e.g. PM10, Dust)
losun_CH4 MF_DPO_12 MF.7.1.2 - Methane (CH4)
losun_N2O MF_DPO_13 MF.7.1.3 - Dinitrogen oxide (N2O)
losun_NOX MF_DPO_14 MF.7.1.4 - Nitrous oxides (NOx)
losun_HFC MF_DPO_15 MF.7.1.5 - Hydroflourcarbons (HFCs)
losun_PFC MF_DPO_16 MF.7.1.6 - Perflourocarbons (PFCs)
losun_SF6NF3 MF_DPO_17 MF.7.1.7 - Sulfur hexaflouride
losun_CO MF_DPO_18 MF.7.1.8 - Carbon monoxide (CO)
losun_NMVOC MF_DPO_19 MF.7.1.9 - Non-methane volatile organic compounds (NMVOC)

For update 05-2023, the data from AEA was retrieved from the processing database from the table aea.gogn_fyrir_skyrslu_long. This data is used in compiling the AEA account, but the account is then reviewed and corrected if there are obvious flaws, or missing data, sometimes to be able to submit preliminary data for the newest year. With update 05-2023, I went to the view aea.v_skilaskjol_gogn_nyjast to get the published data. This is in many ways an easier query:


select 
  ar, 
  efni_cd, 
  SUM(gildi) gildi_blondud_eining 
from aea.v_skilaskjol_gogn_nyjast
where nace_cd = 'BRIDGING_ITEMS'
GROUP BY ar, efni_cd

In order to process the data, I do the following:

  1. Select mappings between materials in AEA and materials in MFA
Varpanir úr efnum í AEA yfir í mfa kerfið
ID lind_texti eining_inn vara_cd mfa_cd
3772 CO2 KILOTONN EFNI:CO2 MF_DPO_112
3773 CO2(bio) KILOTONN EFNI:CO2 MF_DPO_111
3774 CH4 TONN EFNI:CH4 MF_DPO_12
3775 HFC TONN EFNI:HFC MF_DPO_15
3776 PFC TONN EFNI:PFC MF_DPO_16
3777 SF6_NF3 TONN EFNI:SF6NF3 MF_DPO_17
3778 SOX TONN EFNI:SOX MF_DPO_11A
3779 NH3 TONN EFNI:NH3 MF_DPO_11B
3780 PM10 TONN EFNI:PM10 MF_DPO_11E
3781 N2O TONN EFNI:N2O MF_DPO_13
3782 NOX TONN EFNI:NOX MF_DPO_14
3783 CO TONN EFNI:CO MF_DPO_18
3784 NMVOC TONN EFNI:NMVOC MF_DPO_19

Get GWP values the database to calculate the amount of F-gases instead of using CO2 equivalents

  1. Retrieve the AEA data according to the query above
  1. Join the data with the GWP and calculate the weight of F-gasses

4.1.1 Data for the assembly

##                  Length Class  Mode
## aea.tolur        10     tbl_df list
## aea.gwp.leidrett 10     tbl_df list
Few lines from 2020
ar mfa_cd vara_cd gildi
2000 MF_DPO_112 EFNI:CO2 3530676.066
2000 MF_DPO_18 EFNI:CO 54378.414
2000 MF_DPO_14 EFNI:NOX 36344.151
2000 MF_DPO_11A EFNI:SOX 34190.260
2000 MF_DPO_12 EFNI:CH4 26982.413
2000 MF_DPO_19 EFNI:NMVOC 11816.429
2000 MF_DPO_11B EFNI:NH3 5290.142
2000 MF_DPO_11E EFNI:PM10 3318.398
2000 MF_DPO_13 EFNI:N2O 1033.205

The downside here is that the AEA accounting does not go back further than 1995, but the mfa is supposed to go back to 1990. The data is therefore extedned using the NIR accounting

4.2 Data from NIR accounting

NIR includes emissions from animals, which the MFA should not cover. Here again, a projection table (map.nir_dpo()) needs to be set up in order to assign the correct category to the pollutants.

Vörpun úr efnum í NIR bókhaldi yfir í MFA
efni mfa_cd lysing
CO2 MF_DPO_11 MF.7.1.1 - Carbon dioxide (CO2)
SOX MF_DPO_11A MF.7.1.A - Sulfur dioxide (SO2)
CH4 MF_DPO_12 MF.7.1.2 - Methane (CH4)
N2O MF_DPO_13 MF.7.1.3 - Dinitrogen oxide (N2O)
NOX MF_DPO_14 MF.7.1.4 - Nitrous oxides (NOx)
HFC MF_DPO_15 MF.7.1.5 - Hydroflourcarbons (HFCs)
PFC MF_DPO_16 MF.7.1.6 - Perflourocarbons (PFCs)
SF6 MF_DPO_17 MF.7.1.7 - Sulfur hexaflouride
CO MF_DPO_18 MF.7.1.8 - Carbon monoxide (CO)
NMVOC MF_DPO_19 MF.7.1.9 - Non-methane volatile organic compounds (NMVOC)
CO2 MF_DPO_111 MF.7.1.1.2 - Carbon dioxide (CO2)

Next, I retrieve some key sequences from the NIR data, or:

  • ID=303: Total emissions from energy production (Table1s1: Total Energy)
  • ID=353: Total emissions from industry (Table2(I)s1: Total industry processes)
  • ID=401: Total emissions from agriculture (Table3s1: Total agriculture)
    • ID=369: Total emissions from waste processing (Table5: Total Waste)
    • ID=349: Total emissions from the use of biomass (Table1s2: Multilateral operations: CO2 emissions from biomass)

(update 4/11/2019):

  • Created a view: umherfisstofnun.v_unfccc_sector_data on the base which analyzes keys in the parent table.
  • Moved the map.nir_dpo() table for the materials into the mfa.gagnalindir_mfa_varpanir table. However, the pollutants in the unfccc-sector table are marked with an ID value, not a substance. The materials are then connected to the efni_cd which are in the umhverfisstofnun.v_unfccc_sector_data view table.
  • Moved the row_id keys into mfa.databases_mfa_projects. The keys are however marked as ID_unfccc_sheet_key where the sectors in the unfccc table are sometimes numbers, but the ID is unique

The data is retrieved by year using the sql query:


select
  k.row_title_in_sheet,
  case ID_unfccc_sheet_key 
    when 349 THEN 'BIOMASS' -- sérstaklega biomass
    else 'TOTAL' 
  END efni_class,
  ID_unfccc_mengunarefni,
  ar, 
  losun*1000 gildi
from umhverfisstofnun.unfccc_sector_data d 
join umhverfisstofnun.unfccc_sheet_keys k on k.ID = d.ID_unfccc_sheet_key
where d.ID_unfccc_sheet_key IN (303, 353, 469, 349, 401); -- hér tek ég inn ID lyklana

The processing of the values is then:

  1. Collect information about what material I intend to grab from NIR
Efni sem eru valin
mfa_cd vara_cd lind_texti
MF_DPO_19 EFNI:NMVOC NMVOC
MF_DPO_18 EFNI:CO CO
MF_DPO_13 EFNI:N2O N2O
MF_DPO_112 EFNI:CO2 CO2
MF_DPO_17 EFNI:SF6NF3 SF6
MF_DPO_16 EFNI:PFC PFCs(CO2 eq)
MF_DPO_11A EFNI:SOX SO2
MF_DPO_12 EFNI:CH4 CH4
MF_DPO_15 EFNI:HFC HFCs(CO2 eq)
MF_DPO_14 EFNI:NOX NOx
  1. Retrieve the lines from the NIR-sector data that I am going to work with:
Sectorar sem eru valdir. ID gildin tilheyra ákveðnum skráningum
mfa_cd lind_texti
MF_DPO_1 ID_unfccc_sheet_key:303
MF_DPO_1 ID_unfccc_sheet_key:349
MF_DPO_1 ID_unfccc_sheet_key:353
MF_DPO_1 ID_unfccc_sheet_key:469
MF_DPO_1 ID_unfccc_sheet_key:401

These definitions are then used to collect data from the database.

NIR gildi fyrir 1995
efni_cd 1 - Energy 1.Memo.3 - CO2 emissions from biomass 2 - Industrial Processes and Product Use 3 - Agriculture 5 - Waste management
NMVOC 5.288751 NA 1.6130202 1.8974273 0.5223922
CO 46.569970 NA 12.2522800 NA 1.4786720
N2O 0.112121 NA 0.1510386 0.7981238 0.0214652
CO2 2017.883092 3.901866 444.1428880 2.4370043 4.8740187
SO2 19.223606 NA 2.7326268 NA 0.0029112
CH4 0.342893 NA 0.0732405 15.3398209 10.3733084
NOx 29.768300 NA 1.3070405 0.9591451 0.0841593
SF6 NA NA 0.0000545 NA NA
HFCs(CO2 eq) NA NA 3.1483538 NA NA
PFCs(CO2 eq) NA NA 62.3756299 NA NA

  1. Use this information to filter the NIR data I download to the database:

  2. All lines for CO2 in the NIR begin with the label DPO_111 (ie not Biomass). I expect to have to fill in the data for 1995, which means I don’t have to worry too much about biomass carbon, but the solution here is pretty simple; I subtract the biomass material from DPO_111 and add it to DPO_112. A little brutal, but not a problem

4.2.1 Data for the assembly

##                  Length Class  Mode
## aea.tolur        10     tbl_df list
## aea.gwp.leidrett 10     tbl_df list
## nir              10     tbl_df list

4.3 Data from CLRTAP accounting

The CLRTAP accounting goes back to the 1990s, the breakdown is also quite complex. The release numbers are set up in a view on the database under umferfisstofnun.v_clrtap_data_magn, where IDs in the base data table are replaced by names.

The CLRTAP accounting includes information on POPs and heavy metals, which are not included in the AEA or NIR data. The mapping is again stored in the function map.clrtap_dpo() (update 5/11/2019: Added the keys to mfa.databases_mfa_varpanir.

Vörpun úr efnum í CLRTAP bókhaldi yfir í MFA
efni mfa_cd lysing
Pb MF_DPO_11C MF.7.1.C - Heavy metals
Cd MF_DPO_11C MF.7.1.C - Heavy metals
Hg MF_DPO_11C MF.7.1.C - Heavy metals
As MF_DPO_11C MF.7.1.C - Heavy metals-voluntary
Cr MF_DPO_11C MF.7.1.C - Heavy metals-voluntary
Cu MF_DPO_11C MF.7.1.C - Heavy metals-voluntary
Ni MF_DPO_11C MF.7.1.C - Heavy metals-voluntary
Se MF_DPO_11C MF.7.1.C - Heavy metals-voluntary
Zn MF_DPO_11C MF.7.1.C - Heavy metals-voluntary
PCDD/PCDF MF_DPO_11D MF.7.1.D - Persistent organic pollutants (POPs)
benzo(a)pyrene MF_DPO_11D MF.7.1.D - Persistent organic pollutants (POPs)
benzo(b)fluoranthene MF_DPO_11D MF.7.1.D - Persistent organic pollutants (POPs)
benzo(k)fluoranthrene MF_DPO_11D MF.7.1.D - Persistent organic pollutants (POPs)
Indeno(1,2,3-cd)pyrene MF_DPO_11D MF.7.1.D - Persistent organic pollutants (POPs)
HCB MF_DPO_11D MF.7.1.D - Persistent organic pollutants (POPs)
PCBs MF_DPO_11D MF.7.1.D - Persistent organic pollutants (POPs)
CO MF_DPO_18 MF.7.1.8 - Carbon monoxide (CO)
SOX MF_DPO_11A MF.7.1.A - Sulfur dioxide (SO2)
PM10 MF_DPO_11E MF.7.1.E - Particles (e.g. PM10, Dust)
NH3 MF_DPO_11B MF.7.1.B - Ammonia (NH3)
NOX MF_DPO_14 MF.7.1.4 - Nitrous oxides (NOx)
NMVOC MF_DPO_19 MF.7.1.9 - Non-methane volatile organic compounds (NMVOC)

The data in CLRTAP is somewhat different from the NIR and AEA table, where the data is placed in NFR-aggregation categories. This means that it is easy to extract the total emission by choosing an nfr-aggregation that matches The categories are sixteen (stored in mfa.datasources_mfa_varpanir under the NFR-AGGREGATION flag).

Flokkar af tölum í CLRTAP gögnum
ID nfr_aggregation
11 K_AgriLivestock
12 L_AgriOther
13 N_Natural
14 O_AviCruise
15 P_IntShipping
16 TOTAL

The SQL query that retrieves the data contacts umhverfisstofnun.v_clrtap_data_magn, where all units have been converted to tons. Here sum-aggregate is run to reduce the number of rows that need to be fetched. If lines are fetched unnecessarily, they are taken out with correction entries later. The basic script is:


SELECT TOP (1000)
    ar,
    efni_cd, 
    sum(magn_tonn) gildi

  FROM umhverfisstofnun.v_clrtap_data_magn
where nfr_aggregation IN ('A_PublicPower', 'B_Industry', 'other')
group by ar, efni_cd;

The processing here is similar to the one above for the NIR data

  1. Retrieve information and mappings for materials from CLRTAP to MFA

  2. Retrieve which classes and “aggregation” I intend to use from the CLRTAP data

  3. Retrieve the CLRTAP data from the database

4.3.1 Data for the assembly

##                  Length Class  Mode
## aea.tolur        10     tbl_df list
## aea.gwp.leidrett 10     tbl_df list
## nir              10     tbl_df list
## clrtap           10     tbl_df list

4.4 Combining data from different emission accounting systems and choosing the “right number”

The NIR, CLRTAP and AEA figures have “overlap” in many pollutants, as NIR and CLRTAP have land-accounts, while AEA has economy limits. Moreover, there is an endless damn chase to find the correct pollution constant between these accounts.

Some of these substances (for example heavy metals) occur only in the CLRTAP data. I can therefore “accept” these topics without further problems. Other topics I need to think more about.

4.4.1 CO2 Values

Here there is an understandable difference in values as flight is not included in the NIR data but it is in the AEA data. Here I am mainly interested in finding numbers for 1990-1995, but less interested in other discrepancies. Here I take the period 1995-2000 and calculate the ratio between AEA and NIR. Then I use that ratio to scale the NIR numbers from 1990-1994 and add them to the dataset. After that I can remove the NIR numbers

4.4.2 CH4 values

Here there is no noticeable difference between NIR and AEA, so I can take the NIR numbers straight in

4.4.3 N2O values

Here there is a small difference between NIR and AEA, so I can take the NIR numbers directly

4.4.4 CO values

Here there is a small difference between the AEA and CLRTAP data, so I include the CLRTAP data in the collection, but omit the NIR data

4.4.5 NMVOC values

Here is a surprisingly big difference between AEA and the other accounts. I suspect here I need to revise the accounts for AEA. The amount in CLRTAP is probably closer to what it should be in the collection, so I remove that data from the AEA and NIR collections but keep the CLRTAP data

4.4.6 NH3 values

Here is a surprisingly big difference between AEA and CLRTAP I suspect I need to revise the calculations for AEA here, but this probably comes from animal husbandry. The amount in CLRTAP is probably closer to what should be in the collection, so I remove this data from the AEA collection but keep the CLRTAP data

4.4.7 NOx values

Here there is a frustratingly large difference between the CLRTAP and AEA data. However, I am quite satisfied with the AEA data and therefore intend to scale up the CLRTAP data instead

4.4.8 PM10 values

Here is a strange difference between AEA and CLRTAP. However, the data is almost exactly the same, so I’m going to use the clrtap data

4.4.9 SOX values

Here, there is little difference between the CLRTAP and AEA data. I therefore extend the line with this data

##                        Length Class  Mode
## aea.tolur              10     tbl_df list
## aea.gwp.leidrett       10     tbl_df list
## nir                    10     tbl_df list
## clrtap                 10     tbl_df list
## nir_framlenging_CO2    10     tbl_df list
## nir_framlenging_CH4    10     tbl_df list
## nir_framlenging_N2O    10     tbl_df list
## clrtap_framlenging_CO  10     tbl_df list
## clrtap_framlenging_NOX 10     tbl_df list
## clrtap_framlenging_SOX 10     tbl_df list

Here the NIR dataset is empty, but instead I’ve used it to extend CO2, CH4 and N2O

4.5 Final assembly

Here I should be done selecting data for my collection. This should be only one line per graph, and only different colors depending on the data used to extend the series we have.

4.6 Gas emissions - first impression

Here I should have as accurate data on emissions from the economy as I have access to. This data is all marked with an MFA category

4.7 Corrections according to table 22 in the instructions

The total emissions according to the AEA include the emissions of substances that should be listed elsewhere in the EW-MFA. Several items are summarized in Table 22. This table specifically summarizes items in the NIR and CLRTAP data that should be subtracted from the emission figures here, as they appear in other items.

4.7.1 Data from CLRTAP data

Here is the process

  1. Get information about which item to deduct
Efni sem á að færa á milli í DPO flokkum
mfa_cd.efni vara_cd hlutur.efni
MF_DPO_42 EFNI:NH3 1
MF_DPO_14 EFNI:NOX -1
MF_DPO_42 EFNI:NOX 1
MF_DPO_19 EFNI:NMVOC -1
MF_DPO_4 EFNI:NMVOC 1
MF_DPO_11E EFNI:PM10 -1
MF_DPO_4 EFNI:PM10 1
MF_DPO_11C EFNI:As -1
MF_DPO_11C EFNI:Cd -1
MF_DPO_11C EFNI:Cr -1
MF_DPO_11C EFNI:Cu -1
MF_DPO_11C EFNI:Hg -1
MF_DPO_11C EFNI:Ni -1
MF_DPO_11C EFNI:Pb -1
MF_DPO_11C EFNI:Se -1
MF_DPO_11C EFNI:Zn -1
MF_DPO_5 EFNI:As 1
MF_DPO_5 EFNI:Cd 1
MF_DPO_5 EFNI:Cr 1
MF_DPO_5 EFNI:Cu 1
MF_DPO_5 EFNI:Hg 1
MF_DPO_5 EFNI:Ni 1
MF_DPO_5 EFNI:Pb 1
MF_DPO_5 EFNI:Se 1
MF_DPO_5 EFNI:Zn 1
MF_DPO_11D EFNI:Total 1-4 -1
MF_DPO_48 EFNI:Total 1-4 1

Here, for example, NOX is being moved from category DPO-14 to category DPO-42

  1. Retrieves which team to include from the CLRTAP table
liðir sem er verið að flytja gögn fyrir
ID_sector nfr_sector mfa_cd.lidur hlutur.lidur
2560 3Da2b MF_DPO_43 1
2561 3Da2c MF_DPO_44 1
2562 3Da3 MF_DPO_44 1
2563 3Da4 MF_DPO_44 1
2565 2D3b MF_DPO_48 1
2566 2D3c MF_DPO_48 1
2567 2D3d MF_DPO_48 1
2568 2D3e MF_DPO_48 1
2569 2D3f MF_DPO_48 1
2570 2D3g MF_DPO_48 1
2571 2D3h MF_DPO_48 1
2573 2D3i MF_DPO_48 1
2574 2G MF_DPO_48 1
2575 1A3bvi MF_DPO_5 1
2576 1A3bvii MF_DPO_5 1
  1. Retrieve the data tagged in these categories. This is then processed. Here, deductions and additions are taken separately together and marked with a process
## Warning in inner_join(tmp_clrtap.0, tmp_efni, by = "efni_cd", multiple = "all"): Detected an unexpected many-to-many relationship between `x` and `y`.
## ℹ Row 3 of `x` matches multiple rows in `y`.
## ℹ Row 4 of `y` matches multiple rows in `x`.
## ℹ If a many-to-many relationship is expected, set `relationship =
##   "many-to-many"` to silence this warning.
##                              Length Class  Mode
## aea.tolur                    10     tbl_df list
## aea.gwp.leidrett             10     tbl_df list
## nir                          10     tbl_df list
## clrtap                       10     tbl_df list
## nir_framlenging_CO2          10     tbl_df list
## nir_framlenging_CH4          10     tbl_df list
## nir_framlenging_N2O          10     tbl_df list
## clrtap_framlenging_CO        10     tbl_df list
## clrtap_framlenging_NOX       10     tbl_df list
## clrtap_framlenging_SOX       10     tbl_df list
## clrtap_tafla22_leidrettingar 10     tbl_df list

The subtraction must not be greater than the value present in the data.

The processing here also returns data that fits in elsewhere

4.7.2 Corrections from NIR data

Here again data is being transferred from one MFA-DPO team to other teams.

There should be no “overlap” between substances to be corrected from the NIR report and those to be corrected from the CLRTAP report. I am therefore unlikely to end up with negative numbers due to the deductions.

##                              Length Class  Mode
## aea.tolur                    10     tbl_df list
## aea.gwp.leidrett             10     tbl_df list
## nir                          10     tbl_df list
## clrtap                       10     tbl_df list
## nir_framlenging_CO2          10     tbl_df list
## nir_framlenging_CH4          10     tbl_df list
## nir_framlenging_N2O          10     tbl_df list
## clrtap_framlenging_CO        10     tbl_df list
## clrtap_framlenging_NOX       10     tbl_df list
## clrtap_framlenging_SOX       10     tbl_df list
## clrtap_tafla22_leidrettingar 10     tbl_df list
## nir_tafla22_leidrettingar    10     tbl_df list

## MFA-Table_F.biti.71.rmd finished

The data for this element comes from px tables. These tables are a rather poor supply, but in reality only a few numbers are being called for here. Here I use the following projections:

  • UMH04102.px; Landfilling in places without a work permit or with an older permit -> MF_DPO_21M
  • UMH04102.px; For disposal in places with a work permit -> MF_DPO_21M
  • UMH04105.px; Waste: Total disposal -> MF_DPO_21M

5 Data for MF.7.2: Waste disposal to the environment

According to the guidelines, the category MF_DPO_2 should only be used in the case of illegal disposal, or disposal outside of organized landfills. All legal disposal should go to Memo-item as this amount is still considered “within the economy”

5.1 Processing of garbage volume from px tables

Garbage volume is listed in several px tables:

  • UMH04102.px: Waste disposal
  • UMH04105.px: Waste disposal

The tables are slightly different

5.1.1 Data from UMH04102.px: Waste disposal

This processing is a bit fragile as the disposal table is changing frequently. I start by gathering the data from the px table

The processing is done in the function Table_E.UMH04102.data(url, this.connection). Keys in the px table are processed in the function Table_F.UMH04102.check_keys(url, this.connection). Lines marked mfa_cd marked in the database table mfa.gangalindir_mfa_varpanir.

5.1.2 Data from UMH04105.px: Waste disposal designation

The processing is done in the function Table_E.UMH04105.data(path). Keys in the px table are processed in the function Table_F.UMH04105.check_keys(path, this.connection). Lines marked mfa_cd marked in the database table mfa.gangalindir_mfa_varpanir.

##   Downloading large query (in 3 batches):
## 
  |                                                                            
  |                                                                      |   0%
  |                                                                            
  |=======================                                               |  33%
  |                                                                            
  |===============================================                       |  67%
  |                                                                            
  |======================================================================| 100%

5.2 Data for the assembly

Older designs relied on a function that combined the numbers into a single function. In the 05-2023 update I dismantled this function.

##                              Length Class  Mode
## aea.tolur                    10     tbl_df list
## aea.gwp.leidrett             10     tbl_df list
## nir                          10     tbl_df list
## clrtap                       10     tbl_df list
## nir_framlenging_CO2          10     tbl_df list
## nir_framlenging_CH4          10     tbl_df list
## nir_framlenging_N2O          10     tbl_df list
## clrtap_framlenging_CO        10     tbl_df list
## clrtap_framlenging_NOX       10     tbl_df list
## clrtap_framlenging_SOX       10     tbl_df list
## clrtap_tafla22_leidrettingar 10     tbl_df list
## nir_tafla22_leidrettingar    10     tbl_df list
## UMH04102.data                10     tbl_df list
## UMH04105.data                10     tbl_df list

## MFA-Table_F.biti.72.rmd finished

6 Data for MF.7.3: Emissions to water

Skip this element as there is little or no data

7 Data for MF.7.4 Dissipative use of products

Here are a several values I need to compile from different tables, which we might need to dig up from activity tables from the NIR or CLRTAP data

  1. MF.7.4.1 Organic fertilizer (manure)
  2. MF.7.4.2 Mineral fertiliser
  3. MF.7.4.3 Sewage sludge
  4. MF.7.4.4 Compost
  5. MF.7.4.5 Pesticides
  6. MF.7.4.6 Seeds
  7. MF.7.4.7 Salt and other thawing materials spread on roads (including grit)
  8. MF.7.4.8 Solvents, laughing gas and other

7.1 Dissipative use of products

The categories i am searching for are:

  • MF.7.4.1: Organic fertilizer (manure). The amount of livestock droppings is summarized in the NIR report in detail Table3.B(a)s1 in NIR. Total amount of maneure is estimated according to

  • MF.7.4.2: Mineral fertilizer. Amounts of nitrogen, phosphorus and potassium can be found on px tables. Here you can also get data on the Eurostat Database under aei_fm_usefert.

  • MF.7.4.3: Sewage sludge: Something used, but need to check if it is accessible. Some of this may come from the CLRTAP data.

  • MF.7.4.4: Compost: No numbers available, but some could come in from the CLRTAP and NIR data

  • MF.7.4.5: Pesticides: Pesticides - get data from Jóni You can also find data at Eurostat: aei_fm_salpest09

  • MF.7.4.6: Seeds: Seed - Jón could have some numbers

  • MF.7.4.7: Salt and other thawing materials spread on roads (including grit): No figures available, but we may need to contact the Road Administration and the City of Reykjavík.

  • MF.7.4.8: Solvents, laughing gas and other. Here I go into the CLRTAP data to retrieve information. This information comes in as correction entries in the table mfa.sub_gogn_losun_loftgetduga

7.1.1 Organic fertilizer (manure) - from NIR model

The National Inventory Report calculates emissions of CH4 and other material from maneure by first estimating the amount based on the nubmer of animals in the country. The formula they use is:

\[ M = n_s(\textrm{animals}) \times V_s(\textrm{daily excretion})\times 365 \] Where n is in thousands, and \(V_s\) has the unit kg dm/head/day, the final unit is 1000 kg dm (dm=digested matter). The numbers are retrieved directly from the UmhverfisTolfraedi database according to the call:


select
  d.sheet_name,
  d.dyr_cd,
  d.ar, 
  d.vs_dagleg_losun * d.fjoldi_dyra_thusund * 365 saur_tonn
from umhverfisstofnun.landbunadur_sheet_keys k
join umhverfisstofnun.landbunadur_gogn_table3_Bas1 d on d.ID_landbunadur_sheet_key = k.ID
where d.vs_dagleg_losun IS NOT NULL and d.fjoldi_dyra_thusund IS NOT NULL ;

The processing is exactly:

  1. Download the data from the NIR agricultural tables
  2. Retrieves mappings from data sources_mfa_maps where lind_cd is ‘UMHVERFISTOLFRAEDI.TABLE3_BAS1’
  3. Returns tables in the form that fits into the data table mfa.sub_gogn_tafla_f

7.1.1.1 Data for the assembly

##                              Length Class  Mode
## aea.tolur                    10     tbl_df list
## aea.gwp.leidrett             10     tbl_df list
## nir                          10     tbl_df list
## clrtap                       10     tbl_df list
## nir_framlenging_CO2          10     tbl_df list
## nir_framlenging_CH4          10     tbl_df list
## nir_framlenging_N2O          10     tbl_df list
## clrtap_framlenging_CO        10     tbl_df list
## clrtap_framlenging_NOX       10     tbl_df list
## clrtap_framlenging_SOX       10     tbl_df list
## clrtap_tafla22_leidrettingar 10     tbl_df list
## nir_tafla22_leidrettingar    10     tbl_df list
## UMH04102.data                10     tbl_df list
## UMH04105.data                10     tbl_df list
## husdyrasaur_ur_nir           10     tbl_df list

7.1.2 Use of inorganic fertilizer in farming (LAN10001)

This table shows the amount of nitrogen, phosphorus and potassium used. The figures are based on import volumes.

The processing here is:

  • Fetch the data from the px table
  • check if titles/keys are mapped
  • map values to keys
  • calculate material amount using coefficient for each key.
content of LAN10001.px
value.Áburður value.Ár value.value
Köfnunarefni (N):47 1977 : 3 Min. : 1119
Fosfór (P) :47 1978 : 3 1st Qu.: 2403
Kalí (K) :47 1979 : 3 Median : 3543
NA 1980 : 3 Mean : 6053
NA 1981 : 3 3rd Qu.:11211
NA 1982 : 3 Max. :15778
NA (Other):123 NA
  • Fertilizer - mapped keys: 3
  • Fertilizer - unmapped keys: 0

The data is summarized by the function LAN10001.Table_F.olifraenn_aburdur

7.1.2.1 Data for the assembly

The values from the px table are used to calculate N and P amount

##                              Length Class  Mode
## aea.tolur                    10     tbl_df list
## aea.gwp.leidrett             10     tbl_df list
## nir                          10     tbl_df list
## clrtap                       10     tbl_df list
## nir_framlenging_CO2          10     tbl_df list
## nir_framlenging_CH4          10     tbl_df list
## nir_framlenging_N2O          10     tbl_df list
## clrtap_framlenging_CO        10     tbl_df list
## clrtap_framlenging_NOX       10     tbl_df list
## clrtap_framlenging_SOX       10     tbl_df list
## clrtap_tafla22_leidrettingar 10     tbl_df list
## nir_tafla22_leidrettingar    10     tbl_df list
## UMH04102.data                10     tbl_df list
## UMH04105.data                10     tbl_df list
## husdyrasaur_ur_nir           10     tbl_df list
## olifraenn_aburdur            10     tbl_df list

7.1.3 Data on pesticides (retrieved from Jón)

The data on toxins is in the document IcelandPesticides2017 in the _GognInn folder. The data is retrieved and cleaned with the function Table_F.gogn_eiturefni_saekja_ur_excel_skjali()

## Warning: Using an external vector in selections was deprecated in tidyselect 1.1.0.
## ℹ Please use `all_of()` or `any_of()` instead.
##   # Was:
##   data %>% select(tmp_artol)
## 
##   # Now:
##   data %>% select(all_of(tmp_artol))
## 
## See <https://tidyselect.r-lib.org/reference/faq-external-vector.html>.
## This warning is displayed once every 8 hours.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.

7.1.3.1 Data for the assembly

##                                       Length Class  Mode
## aea.tolur                             10     tbl_df list
## aea.gwp.leidrett                      10     tbl_df list
## nir                                   10     tbl_df list
## clrtap                                10     tbl_df list
## nir_framlenging_CO2                   10     tbl_df list
## nir_framlenging_CH4                   10     tbl_df list
## nir_framlenging_N2O                   10     tbl_df list
## clrtap_framlenging_CO                 10     tbl_df list
## clrtap_framlenging_NOX                10     tbl_df list
## clrtap_framlenging_SOX                10     tbl_df list
## clrtap_tafla22_leidrettingar          10     tbl_df list
## nir_tafla22_leidrettingar             10     tbl_df list
## UMH04102.data                         10     tbl_df list
## UMH04105.data                         10     tbl_df list
## husdyrasaur_ur_nir                    10     tbl_df list
## olifraenn_aburdur                     10     tbl_df list
## gogn_eiturefni_saekja_ur_excel_skjali 10     tbl_df list

7.1.4 For other collections

  • MF.7.4.3: Sewage sludge: Something used, but need to check if it is accessible. It would be good to compile some wastewater statistics for this part. The waste statistics have some information on sludge liquids from waste treatment, but as far as I can tell, none of this material is used in Iceland

7.1.5 Data on MF.7.4.4: Compost.

NEW 2024: The waste staitistics table UMH04105.px: Waste collection and allocation shows the quantities of waste (tonnes) that are collected, as well as the allocated route for disposal or material recovery for the waste. Here I choose to select materials that goes to mulch or aneroubic breakdown treadment. For some of the material types (garden waste, kitchen scraps and paper) the resulting material is used as lawn compost material (since 2015). Other material that is composted, such as municipal waste ends up in landfill since it contains too much plastics, glass and other contaminants. The data is available from 2014.

The following mapping of waste types to mfa-category is used:

Waste types that are allocated to “mulch or anerobic breakdown”
waste_name use mfa_cd
Total FALSE NA
03.3 Sludges liquid wastes from waste treatment FALSE MF_DPO_43
07.23 Paper and cardboard (not packaging) TRUE MF_DPO_44
07.51 Wood (packaging) TRUE MF_DPO_44
07.53 Wood (not packaging) TRUE MF_DPO_44
09.11 Animal carcases, fish and slaughted wastes FALSE NA
09.12 Food waste from industrial kitchens and stores TRUE MF_DPO_44
09.21 Garden waste and organic wastes from horticulture and forestry TRUE MF_DPO_44
09.31 Animal wastes TRUE MF_DPO_41
10.11 Municipal wastes (from general collection and from collection sites) FALSE MF_DPO_43
11.4 Sludge from sewage filtration and treatment FALSE NA
12.3 Soils (e.g. pebbles, gravel and sand) FALSE NA
12.6 Soils from land FALSE NA

The question here is if items 03.3 and 11.4 should be assigned to MF.7.3.5: Dumping of materials at sea, although I am not familiar with the practice in Iceland.

7.1.5.1 Data for the assembly

  • MF.7.4.6: Seeds: Seed - Jón could have some numbers

  • MF.7.4.7: Salt and other thawing materials spread on roads (including grit): No figures available, but we may need to contact the Road Administration and the City of Reykjavík.

  • MF.7.4.8: Solvents, laughing gas and other. Here I go into the CLRTAP data to retrieve information. This information comes in as correction entries in the table mfa.sub_gogn_losun_loftgetduga

## MFA-Table_F.biti.74.rmd finished

8 Data for MF.7.5 Dissipative losses

These numbers were entered in the submission in 2022, but redacted back in submission of 2023. In 2022 I included information from the Food Flow Calculations (experimental statistics) that I completed in 2022. A key part of this calculation was to estimate the amount of water that is removed from imported and wild fish and meat products during the processing of the material. This emission of extracted water could be reported in several different categories.

  • If I knew how much food-waste is flushed to the sea (e.g. from poultry production) I could report quantity in MF.7.3.4.
  • The water that is extracted from biomass has a reporting category MF.8.2.3 - Excoprorated water from biomass products, which is in Table G.

From reading the MFA guidelines it seems, however, that the dissipative losses should be a combination of singular events (e.g. accidents and spills) and road-abration, tire wear, break wear and other wear that I could estimate.

No data was collected or compiled for this item in 2022-2024. Following are some ideas for how to improve this.

8.1 Incidental spills

This information needs to be collected separately from envrironmental reports as no database is available about these issues

8.2 Road abration

  • I do have some information from the road authority (Vegagerðin) on how much overcoating material they need to apply to the roads each year. These values are sporatic and “noisy”,
  • I have information from road counters that gives traffic volume each month on key locations around the country. I could possibly model the road wear from these figures based seasonal use of studded road tires. The model output could, however, be hard to verify

8.3 Tire abration

  • I have information on tire import and disposal of tires. This could give me information about how much tire material may be lost each year to the environment. The information would be hard to validate

8.4 Break abration

  • I have relatively poor idea on the weight of break pads that are installed/removed, but the number of vehicles could be used as a proxy here. This would be a model output that is hard to validate
## MFA-Table_F.biti.75.rmd finished

9 Final storage on the database

9.1

This bit changed quite a bit in update 05-2023. Before modification, all design was done in the bits above and the data was fetched by functions designed for each part. Then these functions were called in this “final processing” and the data was inserted into the base. In the 05-2023 update, I took all the subprocesses out of these “invisible functions” and put them into the process. Each child then returns data into a list, which I then insert into the base. I can do this automatically in the script.

Samtekt
name dalkar radir gildi
aea.tolur 10 180 1.257712e+08
aea.gwp.leidrett 10 84 1.488203e+03
nir 10 0 0.000000e+00
clrtap 10 5088 4.426994e+05
nir_framlenging_CO2 10 24 1.271424e+07
nir_framlenging_CH4 10 20 1.285207e+05
nir_framlenging_N2O 10 20 5.243394e+03
clrtap_framlenging_CO 10 55 2.828089e+05
clrtap_framlenging_NOX 10 60 1.751116e+05
clrtap_framlenging_SOX 10 60 1.106844e+05
clrtap_tafla22_leidrettingar 10 2223 2.566221e+04
nir_tafla22_leidrettingar 10 384 0.000000e+00
UMH04102.data 10 38 5.276000e+06
UMH04105.data 10 7 1.454760e+06
husdyrasaur_ur_nir 10 224 6.137920e+06
olifraenn_aburdur 10 102 1.485509e+06
gogn_eiturefni_saekja_ur_excel_skjali 10 105 8.337579e+01
sorpurvinnsla 10 42 1.299490e+05
Samtals 10 8716 1.541419e+08

In older processing, the data was stored in two different database tables; mfa.sub_gogn_gas_emissions and mfa.sub_gogn_table_f. In the updated process all data is saved in. This data is all saved in mfa.sub_gogn_tafla_f and I can empty and get rid of the other data table. The data is always saved in the database and sql processing is executed to update the table.

9.1.1 Results = 2024-04-09

## Setti inn töfluna 'tmp_sub_gogn_tafla_f' með 8716 línum
##  [1] "table_cd"                "mfa_cd"                 
##  [3] "ID_gagnalind_mfa_vorpun" "ar"                     
##  [5] "gildi"                   "eining_inn"             
##  [7] "vara_cd"                 "lind_texti"             
##  [9] "vinnsla"                 "reiknad_dags"

The following queries is then executed

truncate table mfa.sub_gogn_tafla_f
insert into mfa.sub_gogn_tafla_f(
  table_cd, mfa_cd, ar, gildi, ID_gagnalind_mfa_vorpun,
  --ID_gagnalind_mfa_varpanir, 
  lind_texti, vinnsla, reiknad_dags
) 
select 
  table_cd, mfa_cd, ar, gildi, ID_gagnalind_mfa_vorpun,
  --ID_gagnalind_mfa_varpanir, 
  vara_cd, vinnsla, reiknad_dags
from tmp_sub_gogn_tafla_f
drop table tmp_sub_gogn_tafla_f
## MFA-Table_F.db_storage.rmd finished

10 Comparison with previous submission

## Warning: Removed 672 rows containing missing values (`geom_text()`).