Closed emmastorm closed 4 years ago
@gresbtim @teoulas can you guys wrap this up and close the ticket once done?
OK I see, instead of 2 tables (one for asset characteristics and another for annual data) we now have one table with everything. Maybe it's better to keep them separate instead? We have to add the "certifications" field regardless.
I merged everything into one table because I don't actually know which variables belong in asset characteristics and which can be submitted for annual data...is there a list somewhere that details this?
You can check the database:
or API - but the API filters null values on the annual data so you won't get the full list:
{
"gresb_asset_id": 512103,
"country": "GB",
"state_province": "UK",
"city": "West Malling",
"address": "1 Kings Hill Avenue",
"lat": null,
"lng": null,
"partners_id": null,
"construction_year": 2007,
"certifications": [],
"annual_data": [{
"year": 2018,
"asset_name": "1 Kings Hill Avenue",
"asset_size": 43717,
"asset_vacancy": 0,
"en_abs_lc_we": 0,
"en_cov_lc_we": 0,
"en_tot_lc_we": 43717,
"ghg_cov_s2_lb_w": 0,
"ghg_tot_s2_lb_w": 43717,
"property_type_code": "ROFF",
"was_pcov_lc": 0,
"wat_abs_lc_w": 0,
"wat_cov_lc_w": 0,
"wat_tot_lc_w": 43717,
"whole_building": true
}]
}
for annual_data this is what i have right now
BuildingData::BuildingRow.api_column_names.keys
[
[ 0] "asset_gav",
[ 1] "asset_name",
[ 2] "asset_size_common",
[ 3] "asset_size_shared",
[ 4] "asset_size_tenant_landlord",
[ 5] "asset_size_tenant_tenant",
[ 6] "asset_size_tenant",
[ 7] "asset_size",
[ 8] "asset_vacancy",
[ 9] "en_abs_lc_bcd",
[ 10] "en_abs_lc_bce",
[ 11] "en_abs_lc_bcf",
[ 12] "en_abs_lc_bsd",
[ 13] "en_abs_lc_bse",
[ 14] "en_abs_lc_bsf",
[ 15] "en_abs_lc_oe",
[ 16] "en_abs_lc_of",
[ 17] "en_abs_lc_td",
[ 18] "en_abs_lc_te",
[ 19] "en_abs_lc_tf",
[ 20] "en_abs_tc_oe",
[ 21] "en_abs_tc_of",
[ 22] "en_abs_tc_td",
[ 23] "en_abs_tc_te",
[ 24] "en_abs_tc_tf",
[ 25] "en_abs_wd",
[ 26] "en_abs_we",
[ 27] "en_abs_wf",
[ 28] "en_cov_lc_bcd",
[ 29] "en_cov_lc_bce",
[ 30] "en_cov_lc_bcf",
[ 31] "en_cov_lc_bsd",
[ 32] "en_cov_lc_bse",
[ 33] "en_cov_lc_bsf",
[ 34] "en_cov_lc_td",
[ 35] "en_cov_lc_te",
[ 36] "en_cov_lc_tf",
[ 37] "en_cov_tc_td",
[ 38] "en_cov_tc_te",
[ 39] "en_cov_tc_tf",
[ 40] "en_cov_wd",
[ 41] "en_cov_we",
[ 42] "en_cov_wf",
[ 43] "en_data_from",
[ 44] "en_data_to",
[ 45] "en_emr_amr",
[ 46] "en_emr_asur",
[ 47] "en_emr_ihee",
[ 48] "en_emr_iren",
[ 49] "en_emr_msur",
[ 50] "en_emr_oce",
[ 51] "en_emr_sbt",
[ 52] "en_emr_src",
[ 53] "en_emr_tba",
[ 54] "en_emr_wdr",
[ 55] "en_emr_wri",
[ 56] "en_ren_ofs_pbl",
[ 57] "en_ren_ofs_pbt",
[ 58] "en_ren_ons_con",
[ 59] "en_ren_ons_exp",
[ 60] "en_ren_ons_tpt",
[ 61] "en_tot_lc_bcd",
[ 62] "en_tot_lc_bce",
[ 63] "en_tot_lc_bcf",
[ 64] "en_tot_lc_bsd",
[ 65] "en_tot_lc_bse",
[ 66] "en_tot_lc_bsf",
[ 67] "en_tot_lc_td",
[ 68] "en_tot_lc_te",
[ 69] "en_tot_lc_tf",
[ 70] "en_tot_tc_td",
[ 71] "en_tot_tc_te",
[ 72] "en_tot_tc_tf",
[ 73] "en_tot_wd",
[ 74] "en_tot_we",
[ 75] "en_tot_wf",
[ 76] "energy_rating_id",
[ 77] "energy_rating_size",
[ 78] "ghg_abs_offset",
[ 79] "ghg_abs_s1_o",
[ 80] "ghg_abs_s1_w",
[ 81] "ghg_abs_s2_lb_o",
[ 82] "ghg_abs_s2_lb_w",
[ 83] "ghg_abs_s2_mb_o",
[ 84] "ghg_abs_s2_mb_w",
[ 85] "ghg_abs_s3_o",
[ 86] "ghg_abs_s3_w",
[ 87] "ghg_cov_s1_w",
[ 88] "ghg_cov_s2_lb_w",
[ 89] "ghg_cov_s3_w",
[ 90] "ghg_tot_s1_w",
[ 91] "ghg_tot_s2_lb_w",
[ 92] "ghg_tot_s3_w",
[ 93] "ncmr_from",
[ 94] "ncmr_status",
[ 95] "ncmr_to",
[ 96] "owned_entire_period",
[ 97] "ownership_from",
[ 98] "ownership_to",
[ 99] "property_type_code",
[100] "tenant_ctrl",
[101] "was_abs_haz",
[102] "was_abs_nhaz",
[103] "was_data_from",
[104] "was_data_to",
[105] "was_emr_clfw",
[106] "was_emr_opm",
[107] "was_emr_rec",
[108] "was_emr_tba",
[109] "was_emr_wsa",
[110] "was_emr_wsm",
[111] "was_pabs_in",
[112] "was_pabs_lf",
[113] "was_pabs_oth",
[114] "was_pabs_rec",
[115] "was_pabs_wte",
[116] "was_pcov",
[117] "wat_abs_lc_bc",
[118] "wat_abs_lc_bs",
[119] "wat_abs_lc_o",
[120] "wat_abs_lc_t",
[121] "wat_abs_tc_o",
[122] "wat_abs_tc_t",
[123] "wat_abs_w",
[124] "wat_cov_lc_bc",
[125] "wat_cov_lc_bs",
[126] "wat_cov_lc_t",
[127] "wat_cov_tc_t",
[128] "wat_cov_w",
[129] "wat_data_from",
[130] "wat_data_to",
[131] "wat_emr_amr",
[132] "wat_emr_clt",
[133] "wat_emr_dsi",
[134] "wat_emr_dtnl",
[135] "wat_emr_hedf",
[136] "wat_emr_lds",
[137] "wat_emr_mws",
[138] "wat_emr_owwt",
[139] "wat_emr_rsgw",
[140] "wat_emr_tba",
[141] "wat_rec_ofs_pur",
[142] "wat_rec_ons_cap",
[143] "wat_rec_ons_ext",
[144] "wat_rec_ons_reu",
[145] "wat_tot_lc_bc",
[146] "wat_tot_lc_bs",
[147] "wat_tot_lc_t",
[148] "wat_tot_tc_t",
[149] "wat_tot_w",
[150] 'whole_building',
So it looks like certifications are not annual, then? And energy ratings are? Is that correct?
yes, that's correct.
That's correct. I know it seems weird, but ask @joeyhorst
For spreadsheet data, neither certifications nor energy ratings are annual, just fyi.
Anyway, I will separate out the data dictionary into 3 tables then: asset characteristics, certifications, and annual data.
for the list above i missed + 'was_pabs_ru' => 'number',
For certifications, the variables are:
id
certification_id
name
level
size
correct, @teoulas @gresbtim ?
I assume level == scheme
, but what is name
? And what's the difference between id
and certification_id
?
We have Certification
and Building
. The link of them we can call BuildingCertification
the id
is from the building_certification.id
, the record that we actually save.
name
is the name of the Certification
which comes automatically.
level
is the former outcome
, so whatever result it was (AAA or AAAA or AAAAA+).
certification_id
is actually building_certification.certification_id
ok updated the data dictionary with certifications, so now all the variables should be in the docs.
Add variables for certifications and energy ratings tables to data dictionary once those tables/API endpoints are set up.
The table markdown file will have to be correspondingly updated.