Coverage for tests/test_data.py: 99%

1# SPDX-FileCopyrightText: 2021 Forschungszentrum Jülich GmbH 

2# SPDX-License-Identifier: MIT 

3 

4import pytest 

5import json 

6import pandas as pd 

7import csv 

8from sqlalchemy import insert, MetaData 

9from fastapi import Request 

10from toardb.toardb import app 

11from toardb.data.models import Data 

12#from toardb.utils.timeseries_filtering import select_provider, merge_sequences, process_eea_data, format_eea_time_series, format_time_series 

13from toardb.timeseries.models import Timeseries, timeseries_timeseries_roles_table 

14from toardb.timeseries.models_programme import TimeseriesProgramme 

15from toardb.timeseries.models_role import TimeseriesRole 

16from toardb.stationmeta.models import StationmetaCore, StationmetaGlobal 

17from toardb.stationmeta.schemas import get_geom_from_coordinates, Coordinates 

18from toardb.variables.models import Variable 

19from toardb.contacts.models import Person, Organisation, Contact 

20from toardb.auth_user.models import AuthUser 

21from toardb.test_base import ( 

22 client, 

23 get_test_db, 

24 override_dependency, 

25 create_test_database, 

26 url, 

27 get_test_engine, 

28 test_db_session as db, 

29) 

30# for mocking datetime.now(timezone.utc) 

31import datetime 

32from unittest.mock import patch 

33 

34# only datetime.now needs to be overridden because otherwise daterange-arguments would be provided as MagicMock-objects! 

35class FixedDatetime(datetime.datetime): 

36 @classmethod 

37 def now(cls, tz=None): 

38 return datetime.datetime(2023, 7, 28, 12, 0, 0) 

39 

40 

41class TestApps: 

42 def setup(self): 

43 self.application_url = "/data/" 

44 

45 """Set up all the data before each test 

46 If you want the setup only once (per test module), 

47 the scope argument is not working in the expected way, as discussed here: 

48 https://stackoverflow.com/questions/45817153/py-test-fixture-use-function-fixture-in-scope-fixture 

49 """ 

50 @pytest.fixture(autouse=True) 

51 def setup_db_data(self, db): 

52 _db_conn = get_test_engine() 

53 fake_conn = _db_conn.raw_connection() 

54 fake_cur = fake_conn.cursor() 

55 # id_seq will not be reset automatically between tests! 

56 fake_cur.execute("ALTER SEQUENCE auth_user_id_seq RESTART WITH 1") 

57 fake_conn.commit() 

58 fake_cur.execute("ALTER SEQUENCE variables_id_seq RESTART WITH 1") 

59 fake_conn.commit() 

60 fake_cur.execute("ALTER SEQUENCE stationmeta_core_id_seq RESTART WITH 1") 

61 fake_conn.commit() 

62 fake_cur.execute("ALTER SEQUENCE stationmeta_global_id_seq RESTART WITH 1") 

63 fake_conn.commit() 

64 fake_cur.execute("ALTER SEQUENCE stationmeta_roles_id_seq RESTART WITH 1") 

65 fake_conn.commit() 

66 fake_cur.execute("ALTER SEQUENCE stationmeta_annotations_id_seq RESTART WITH 1") 

67 fake_conn.commit() 

68 fake_cur.execute("ALTER SEQUENCE stationmeta_aux_doc_id_seq RESTART WITH 1") 

69 fake_conn.commit() 

70 fake_cur.execute("ALTER SEQUENCE stationmeta_aux_image_id_seq RESTART WITH 1") 

71 fake_conn.commit() 

72 fake_cur.execute("ALTER SEQUENCE stationmeta_aux_url_id_seq RESTART WITH 1") 

73 fake_conn.commit() 

74 fake_cur.execute("ALTER SEQUENCE persons_id_seq RESTART WITH 1") 

75 fake_conn.commit() 

76 fake_cur.execute("ALTER SEQUENCE organisations_id_seq RESTART WITH 1") 

77 fake_conn.commit() 

78 fake_cur.execute("ALTER SEQUENCE contacts_id_seq RESTART WITH 1") 

79 fake_conn.commit() 

80 fake_cur.execute("ALTER SEQUENCE timeseries_annotations_id_seq RESTART WITH 1") 

81 fake_conn.commit() 

82 fake_cur.execute("ALTER SEQUENCE timeseries_roles_id_seq RESTART WITH 3") 

83 fake_conn.commit() 

84 fake_cur.execute("ALTER SEQUENCE timeseries_programmes_id_seq RESTART WITH 1") 

85 fake_conn.commit() 

86 fake_cur.execute("ALTER SEQUENCE timeseries_id_seq RESTART WITH 1") 

87 fake_conn.commit() 

88 infilename = "tests/fixtures/auth_user/auth.json" 

89 with open(infilename) as f: 

90 metajson=json.load(f) 

91 for entry in metajson: 

92 new_auth_user = AuthUser(**entry) 

93 db.add(new_auth_user) 

94 db.commit() 

95 db.refresh(new_auth_user) 

96 infilename = "tests/fixtures/contacts/persons.json" 

97 with open(infilename) as f: 

98 metajson=json.load(f) 

99 for entry in metajson: 

100 new_person = Person(**entry) 

101 db.add(new_person) 

102 db.commit() 

103 db.refresh(new_person) 

104 infilename = "tests/fixtures/contacts/organisations.json" 

105 with open(infilename) as f: 

106 metajson=json.load(f) 

107 for entry in metajson: 

108 new_organisation = Organisation(**entry) 

109 db.add(new_organisation) 

110 db.commit() 

111 db.refresh(new_organisation) 

112 infilename = "tests/fixtures/contacts/contacts.json" 

113 with open(infilename) as f: 

114 metajson=json.load(f) 

115 for entry in metajson: 

116 new_contact = Contact(**entry) 

117 db.add(new_contact) 

118 db.commit() 

119 db.refresh(new_contact) 

120 infilename = "tests/fixtures/variables/variables.json" 

121 with open(infilename) as f: 

122 metajson=json.load(f) 

123 for entry in metajson: 

124 new_variable = Variable(**entry) 

125 db.add(new_variable) 

126 db.commit() 

127 db.refresh(new_variable) 

128 infilename = "tests/fixtures/stationmeta/stationmeta_core.json" 

129 with open(infilename) as f: 

130 metajson=json.load(f) 

131 for entry in metajson: 

132 new_stationmeta_core = StationmetaCore(**entry) 

133 # there's a mismatch with coordinates --> how to automatically switch back and forth?! 

134 new_stationmeta_core.coordinates = get_geom_from_coordinates(Coordinates(**new_stationmeta_core.coordinates)) 

135 db.add(new_stationmeta_core) 

136 db.commit() 

137 db.refresh(new_stationmeta_core) 

138 infilename = "tests/fixtures/stationmeta/stationmeta_global.json" 

139 with open(infilename) as f: 

140 metajson=json.load(f) 

141 for entry in metajson: 

142 new_stationmeta_global = StationmetaGlobal(**entry) 

143 db.add(new_stationmeta_global) 

144 db.commit() 

145 db.refresh(new_stationmeta_global) 

146 infilename = "tests/fixtures/timeseries/timeseries_programmes.json" 

147 with open(infilename) as f: 

148 metajson=json.load(f) 

149 for entry in metajson: 

150 new_timeseries_programme = TimeseriesProgramme(**entry) 

151 db.add(new_timeseries_programme) 

152 db.commit() 

153 db.refresh(new_timeseries_programme) 

154 infilename = "tests/fixtures/timeseries/timeseries.json" 

155 with open(infilename) as f: 

156 metajson=json.load(f) 

157 for entry in metajson: 

158 new_timeseries = Timeseries(**entry) 

159 db.add(new_timeseries) 

160 db.commit() 

161 db.refresh(new_timeseries) 

162 infilename = "tests/fixtures/timeseries/timeseries_roles.json" 

163 with open(infilename) as f: 

164 metajson=json.load(f) 

165 for entry in metajson: 

166 new_timeseries_role = TimeseriesRole(**entry) 

167 db.add(new_timeseries_role) 

168 db.commit() 

169 db.refresh(new_timeseries_role) 

170 infilename = "tests/fixtures/timeseries/timeseries_timeseries_roles.json" 

171 with open(infilename) as f: 

172 metajson=json.load(f) 

173 for entry in metajson: 

174 db.execute(insert(timeseries_timeseries_roles_table).values(timeseries_id=entry["timeseries_id"], role_id=entry["role_id"])) 

175 db.execute("COMMIT") 

176 infilename = "tests/fixtures/data/data.json" 

177 with open(infilename) as f: 

178 metajson=json.load(f) 

179 for entry in metajson: 

180 new_data = Data (**entry) 

181 db.add(new_data) 

182 db.commit() 

183 db.refresh(new_data) 

184 infilename = "tests/fixtures/data/staging_data.json" 

185 with open(infilename) as f: 

186 metajson=json.load(f) 

187 for entry in metajson: 

188 new_data = Data (**entry) 

189 fake_cur.execute(("INSERT INTO staging.data(datetime, value, flags, timeseries_id, version) " 

190 f" VALUES('{new_data.datetime}', {new_data.value}, {new_data.flags}, " 

191 f"{new_data.timeseries_id}, '{new_data.version}');")) 

192 fake_conn.commit() 

193 with open("tests/fixtures/data/yearly_coverage.csv", newline="", encoding="utf-8") as f: 

194 metadata = MetaData() 

195 metadata.reflect(bind=_db_conn) 

196 table = metadata.tables["yearly_coverage"] 

197 reader = csv.DictReader(f) 

198 rows = list(reader) 

199 db.execute(insert(table), rows) 

200 db.commit() 

201 

202 

203 def test_get_special(self, client, db): 

204 fixed_time = datetime.datetime(2023, 7, 28, 12, 0, 0) 

205 with patch('toardb.timeseries.crud.dt.datetime') as mock_datetime: 

206 mock_datetime.now.return_value = fixed_time 

207 response = client.get("/data/timeseries/1") 

208 expected_status_code = 200 

209 assert response.status_code == expected_status_code 

210 expected_resp = {'metadata': {'id': 1, 'label': 'CMA', 'order': 1, 'sampling_frequency': 'hourly', 

211 'aggregation': 'mean', 

212 'license': 'This data is published under a Creative Commons Attribution 4.0 International (CC BY 4.0). https://creativecommons.org/licenses/by/4.0/', 

213 'doi': '', 

214 'data_start_date': '2003-09-07T15:30:00+00:00', 'data_end_date': '2016-12-31T14:30:00+00:00', 'coverage': -1.0, 

215 'data_origin': 'instrument', 'data_origin_type': 'measurement', 'provider_version': 'N/A', 

216 'sampling_height': 7.0, 'additional_metadata': {'original_units': 'ppb'}, 

217 'station': {'id': 2, 'codes': ['SDZ54421'], 'name': 'Shangdianzi', 'coordinates': {'lat': 40.65, 'lng': 117.106, 'alt': 293.9}, 

218 'coordinate_validation_status': 'not checked', 'country': 'China', 'state': 'Beijing Shi', 'type': 'unknown', 

219 'type_of_area': 'unknown', 'timezone': 'Asia/Shanghai', 

220 'additional_metadata': {'add_type': 'nature reservation'}, 

221 'aux_images': [], 'aux_docs': [], 'aux_urls': [], 'changelog': [], 

222 'globalmeta': {'climatic_zone_year2016': '6 (warm temperate dry)', 

223 'distance_to_major_road_year2020': -999.0, 

224 'dominant_ecoregion_year2017': '-1 (undefined)', 

225 'dominant_landcover_year2012': '11 (Cropland, rainfed, herbaceous cover)', 

226 'ecoregion_description_25km_year2017': '', 

227 'htap_region_tier1_year2010': '11 (MDE Middle East: S. Arabia, Oman, etc, Iran, Iraq)', 

228 'landcover_description_25km_year2012': '', 

229 'max_population_density_25km_year1990': -1.0, 

230 'max_population_density_25km_year2015': -1.0, 

231 'max_stable_nightlights_25km_year1992': -999.0, 

232 'max_stable_nightlights_25km_year2013': -999.0, 

233 'max_topography_srtm_relative_alt_5km_year1994': -999.0, 

234 'mean_nox_emissions_10km_year2000': -999.0, 

235 'mean_nox_emissions_10km_year2015': -999.0, 

236 'mean_population_density_250m_year1990': -1.0, 

237 'mean_population_density_250m_year2015': -1.0, 

238 'mean_population_density_5km_year1990': -1.0, 

239 'mean_population_density_5km_year2015': -1.0, 

240 'mean_stable_nightlights_1km_year2013': -999.0, 

241 'mean_stable_nightlights_5km_year2013': -999.0, 

242 'mean_topography_srtm_alt_1km_year1994': -999.0, 

243 'mean_topography_srtm_alt_90m_year1994': -999.0, 

244 'min_topography_srtm_relative_alt_5km_year1994': -999.0, 

245 'stddev_topography_srtm_relative_alt_5km_year1994': -999.0, 

246 'toar1_category': 'unclassified', 

247 'toar2_category': 'suburban', 

248 }}, 

249 'variable': {'name': 'toluene', 'longname': 'toluene', 'displayname': 'Toluene', 

250 'cf_standardname': 'mole_fraction_of_toluene_in_air', 'units': 'nmol mol-1', 'chemical_formula': 'C7H8', 'id': 7}, 

251 'programme': {'id': 0, 'name': '', 'longname': '', 'homepage': '', 'description': ''}, 

252 'roles': [{'id': 2, 'role': 'resource provider', 'status': 'active', 

253 'contact': {'id': 4, 'organisation': {'id': 1, 'name': 'UBA', 'longname': 'Umweltbundesamt', 

254 'kind': 'government', 'city': 'Dessau-Roßlau', 'postcode': '06844', 'street_address': 'Wörlitzer Platz 1', 

255 'country': 'Germany', 'homepage': 'https://www.umweltbundesamt.de', 'contact_url': 'mailto:immission@uba.de'}}}, 

256 {'id': 3, 'role': 'principal investigator', 'status': 'active', 

257 'contact': {'id': 3, 'person': {'email': 's.schroeder@fz-juelich.de','id': 3, 'isprivate': False, 

258 'name': 'Sabine Schröder', 'orcid': '0000-0002-0309-8010', 'phone': '+49-2461-61-6397'}}} 

259 ], 

260 'citation': 'Sabine Schröder: time series of toluene at Shangdianzi, accessed from the TOAR database on 2023-07-28 12:00:00'}, 

261 'data': [{'datetime': '2012-12-16T21:00:00+00:00', 'value': 21.581, 'flags': 'OK validated verified', 'version': '1.0', 'timeseries_id': 1}, 

262 {'datetime': '2012-12-16T22:00:00+00:00', 'value': 13.734, 'flags': 'OK validated verified', 'version': '1.0', 'timeseries_id': 1}, 

263 {'datetime': '2012-12-16T23:00:00+00:00', 'value': 13.734, 'flags': 'OK validated verified', 'version': '1.0', 'timeseries_id': 1}, 

264 {'datetime': '2012-12-17T00:00:00+00:00', 'value': 7.848, 'flags': 'OK validated QC passed', 'version': '1.0', 'timeseries_id': 1}, 

265 {'datetime': '2012-12-17T01:00:00+00:00', 'value': 15.696, 'flags': 'OK validated QC passed', 'version': '1.0', 'timeseries_id': 1}, 

266 {'datetime': '2012-12-17T02:00:00+00:00', 'value': 11.772, 'flags': 'OK validated verified', 'version': '1.0', 'timeseries_id': 1}, 

267 {'datetime': '2012-12-17T03:00:00+00:00', 'value': 13.734, 'flags': 'OK validated modified', 'version': '1.0', 'timeseries_id': 1}, 

268 {'datetime': '2012-12-17T04:00:00+00:00', 'value': 19.62, 'flags': 'OK validated QC passed', 'version': '1.0', 'timeseries_id': 1}, 

269 {'datetime': '2012-12-17T05:00:00+00:00', 'value': 15.696, 'flags': 'OK validated modified', 'version': '1.0', 'timeseries_id': 1}, 

270 {'datetime': '2012-12-17T06:00:00+00:00', 'value': 5.886, 'flags': 'OK validated verified', 'version': '1.0', 'timeseries_id': 1}] 

271 } 

272 assert response.json() == expected_resp 

273 

274 

275 def test_get_timeseries_merged(self, client, db): 

276 with patch('toardb.timeseries.crud.dt.datetime', FixedDatetime): 

277 response = client.get("/data/timeseries_merged/?station_code=SDZ54421&variable_id=5") 

278 expected_status_code = 200 

279 assert response.status_code == expected_status_code 

280 expected_resp = {'metadata': [{'id': 18763, 

281 'data_end_date': '2025-02-25T14:00:00+00:00', 

282 'data_origin_type': 'measurement', 

283 'label': '', 'data_origin': 'instrument', 

284 'sampling_height': 7.0, 'doi': '', 'order': 1, 'provider_version': 'N/A', 

285 'coverage': -1.0, 'sampling_frequency': 'hourly', 

286 'additional_metadata': {"original_units": "ppb"}, 

287 'aggregation': 'mean', 'data_start_date': '1991-01-01T00:00:00+00:00', 

288 'station': {'codes': ['SDZ54421'], 'name': 'Shangdianzi', 'coordinate_validation_status': 'not checked', 

289 'state': 'Beijing Shi', 'type_of_area': 'unknown', 'additional_metadata': {"add_type": "nature reservation"}, 

290 'id': 2, 'coordinates': {'lat': 40.65, 'lng': 117.106, 'alt': 293.9}, 'country': 'China', 'type': 'unknown', 

291 'timezone': 'Asia/Shanghai', 'aux_docs': [], 'aux_images': [], 'aux_urls': [], 'changelog': [], 

292 'globalmeta': {'climatic_zone_year2016': '6 (warm temperate dry)', 

293 'distance_to_major_road_year2020': -999.0, 

294 'dominant_ecoregion_year2017': '-1 (undefined)', 

295 'dominant_landcover_year2012': '11 (Cropland, rainfed, herbaceous cover)', 

296 'ecoregion_description_25km_year2017': '', 

297 'htap_region_tier1_year2010': '11 (MDE Middle East: S. Arabia, Oman, etc, Iran, Iraq)', 

298 'landcover_description_25km_year2012': '', 

299 'max_population_density_25km_year1990': -1.0, 

300 'max_population_density_25km_year2015': -1.0, 

301 'max_stable_nightlights_25km_year1992': -999.0, 

302 'max_stable_nightlights_25km_year2013': -999.0, 

303 'max_topography_srtm_relative_alt_5km_year1994': -999.0, 

304 'mean_nox_emissions_10km_year2000': -999.0, 

305 'mean_nox_emissions_10km_year2015': -999.0, 

306 'mean_population_density_250m_year1990': -1.0, 

307 'mean_population_density_250m_year2015': -1.0, 

308 'mean_population_density_5km_year1990': -1.0, 

309 'mean_population_density_5km_year2015': -1.0, 

310 'mean_stable_nightlights_1km_year2013': -999.0, 

311 'mean_stable_nightlights_5km_year2013': -999.0, 

312 'mean_topography_srtm_alt_1km_year1994': -999.0, 

313 'mean_topography_srtm_alt_90m_year1994': -999.0, 

314 'min_topography_srtm_relative_alt_5km_year1994': -999.0, 

315 'stddev_topography_srtm_relative_alt_5km_year1994': -999.0, 

316 'toar1_category': 'unclassified', 

317 'toar2_category': 'suburban', 

318 }}, 

319 'variable': {'id': 5, 'displayname': 'Ozone', 'units': 'nmol mol-1', 'longname': 'ozone', 

320 'name': 'o3', 'cf_standardname': 'mole_fraction_of_ozone_in_air', 'chemical_formula': 'O3'}, 

321 'programme': {'longname': '', 'homepage': '', 'name': '', 'id': 0, 'description': ''}, 

322 'roles': [{'role': 'resource provider', 

323 'status': 'active', 

324 'id': 1, 

325 'contact': {'id': 5, 

326 'organisation': {'city': 'Jülich', 

327 'contact_url': 'mailto:toar-data@fz-juelich.de', 

328 'country': 'Germany', 

329 'homepage': 'https://www.fz-juelich.de', 

330 'id': 2, 

331 'kind': 'research', 

332 'longname': 'Forschungszentrum Jülich', 

333 'name': 'FZJ', 

334 'postcode': '52425', 

335 'street_address': 'Wilhelm-Johnen-Straße', 

336 }, 

337 },}], 

338 'license': 'This data is published under a Creative Commons Attribution 4.0 International (CC BY 4.0). https://creativecommons.org/licenses/by/4.0/', 

339 'citation': 'Forschungszentrum Jülich: time series of o3 at Shangdianzi, accessed from the TOAR database on 2023-07-28 12:00:00', 

340 'attribution': 'Test-Attributions to be announced'}, 

341 {'id': 434870, 

342 'data_end_date': '2025-02-25T14:00:00+00:00', 

343 'data_origin_type': 'measurement', 

344 'label': '', 'data_origin': 'instrument', 

345 'sampling_height': 7.0, 'doi': '', 'order': 2, 'provider_version': 'N/A', 

346 'coverage': -1.0, 'sampling_frequency': 'hourly', 

347 'additional_metadata': {"original_units": "ppb"}, 

348 'aggregation': 'mean', 'data_start_date': '1991-01-01T00:00:00+00:00', 

349 'station': {'codes': ['SDZ54421'], 'name': 'Shangdianzi', 'coordinate_validation_status': 'not checked', 

350 'state': 'Beijing Shi', 'type_of_area': 'unknown', 'additional_metadata': {"add_type": "nature reservation"}, 

351 'id': 2, 'coordinates': {'lat': 40.65, 'lng': 117.106, 'alt': 293.9}, 'country': 'China', 'type': 'unknown', 

352 'timezone': 'Asia/Shanghai', 'aux_docs': [], 'aux_images': [], 'aux_urls': [], 'changelog': [], 

353 'globalmeta': {'climatic_zone_year2016': '6 (warm temperate dry)', 

354 'distance_to_major_road_year2020': -999.0, 

355 'dominant_ecoregion_year2017': '-1 (undefined)', 

356 'dominant_landcover_year2012': '11 (Cropland, rainfed, herbaceous cover)', 

357 'ecoregion_description_25km_year2017': '', 

358 'htap_region_tier1_year2010': '11 (MDE Middle East: S. Arabia, Oman, etc, Iran, Iraq)', 

359 'landcover_description_25km_year2012': '', 

360 'max_population_density_25km_year1990': -1.0, 

361 'max_population_density_25km_year2015': -1.0, 

362 'max_stable_nightlights_25km_year1992': -999.0, 

363 'max_stable_nightlights_25km_year2013': -999.0, 

364 'max_topography_srtm_relative_alt_5km_year1994': -999.0, 

365 'mean_nox_emissions_10km_year2000': -999.0, 

366 'mean_nox_emissions_10km_year2015': -999.0, 

367 'mean_population_density_250m_year1990': -1.0, 

368 'mean_population_density_250m_year2015': -1.0, 

369 'mean_population_density_5km_year1990': -1.0, 

370 'mean_population_density_5km_year2015': -1.0, 

371 'mean_stable_nightlights_1km_year2013': -999.0, 

372 'mean_stable_nightlights_5km_year2013': -999.0, 

373 'mean_topography_srtm_alt_1km_year1994': -999.0, 

374 'mean_topography_srtm_alt_90m_year1994': -999.0, 

375 'min_topography_srtm_relative_alt_5km_year1994': -999.0, 

376 'stddev_topography_srtm_relative_alt_5km_year1994': -999.0, 

377 'toar1_category': 'unclassified', 

378 'toar2_category': 'suburban', 

379 }}, 

380 'variable': {'id': 5, 'displayname': 'Ozone', 'units': 'nmol mol-1', 'longname': 'ozone', 

381 'name': 'o3', 'cf_standardname': 'mole_fraction_of_ozone_in_air', 'chemical_formula': 'O3'}, 

382 'programme': {'longname': '', 'homepage': '', 'name': '', 'id': 0, 'description': ''}, 

383 'roles': [{'role': 'resource provider', 

384 'status': 'active', 

385 'id': 2, 

386 'contact': {'id': 4, 

387 'organisation': {'city': 'Dessau-Roßlau', 

388 'contact_url': 'mailto:immission@uba.de', 

389 'country': 'Germany', 

390 'homepage': 'https://www.umweltbundesamt.de', 

391 'id': 1, 

392 'kind': 'government', 

393 'longname': 'Umweltbundesamt', 

394 'name': 'UBA', 

395 'postcode': '06844', 

396 'street_address': 'Wörlitzer Platz 1', 

397 }, 

398 }, 

399 }], 

400 'license': 'This data is published under a Creative Commons Attribution 4.0 International (CC BY 4.0). https://creativecommons.org/licenses/by/4.0/', 

401 'citation': 'Umweltbundesamt: time series of o3 at Shangdianzi, accessed from the TOAR database on 2023-07-28 12:00:00'}, 

402 {'id': 30890, 

403 'data_end_date': '2025-02-25T14:00:00+00:00', 

404 'data_origin_type': 'measurement', 

405 'label': '', 'data_origin': 'instrument', 

406 'sampling_height': 7.0, 'doi': '', 'order': 2, 'provider_version': 'N/A', 

407 'coverage': -1.0, 'sampling_frequency': 'hourly', 

408 'additional_metadata': {"original_units": "ppb"}, 

409 'aggregation': 'mean', 'data_start_date': '1991-01-01T00:00:00+00:00', 

410 'station': {'codes': ['SDZ54421'], 'name': 'Shangdianzi', 'coordinate_validation_status': 'not checked', 

411 'state': 'Beijing Shi', 'type_of_area': 'unknown', 'additional_metadata': {"add_type": "nature reservation"}, 

412 'id': 2, 'coordinates': {'lat': 40.65, 'lng': 117.106, 'alt': 293.9}, 'country': 'China', 'type': 'unknown', 

413 'timezone': 'Asia/Shanghai', 'aux_docs': [], 'aux_images': [], 'aux_urls': [], 'changelog': [], 

414 'globalmeta': {'climatic_zone_year2016': '6 (warm temperate dry)', 

415 'distance_to_major_road_year2020': -999.0, 

416 'dominant_ecoregion_year2017': '-1 (undefined)', 

417 'dominant_landcover_year2012': '11 (Cropland, rainfed, herbaceous cover)', 

418 'ecoregion_description_25km_year2017': '', 

419 'htap_region_tier1_year2010': '11 (MDE Middle East: S. Arabia, Oman, etc, Iran, Iraq)', 

420 'landcover_description_25km_year2012': '', 

421 'max_population_density_25km_year1990': -1.0, 

422 'max_population_density_25km_year2015': -1.0, 

423 'max_stable_nightlights_25km_year1992': -999.0, 

424 'max_stable_nightlights_25km_year2013': -999.0, 

425 'max_topography_srtm_relative_alt_5km_year1994': -999.0, 

426 'mean_nox_emissions_10km_year2000': -999.0, 

427 'mean_nox_emissions_10km_year2015': -999.0, 

428 'mean_population_density_250m_year1990': -1.0, 

429 'mean_population_density_250m_year2015': -1.0, 

430 'mean_population_density_5km_year1990': -1.0, 

431 'mean_population_density_5km_year2015': -1.0, 

432 'mean_stable_nightlights_1km_year2013': -999.0, 

433 'mean_stable_nightlights_5km_year2013': -999.0, 

434 'mean_topography_srtm_alt_1km_year1994': -999.0, 

435 'mean_topography_srtm_alt_90m_year1994': -999.0, 

436 'min_topography_srtm_relative_alt_5km_year1994': -999.0, 

437 'stddev_topography_srtm_relative_alt_5km_year1994': -999.0, 

438 'toar1_category': 'unclassified', 

439 'toar2_category': 'suburban', 

440 }}, 

441 'variable': {'id': 5, 'displayname': 'Ozone', 'units': 'nmol mol-1', 'longname': 'ozone', 

442 'name': 'o3', 'cf_standardname': 'mole_fraction_of_ozone_in_air', 'chemical_formula': 'O3'}, 

443 'programme': {'longname': '', 'homepage': '', 'name': '', 'id': 0, 'description': ''}, 

444 'roles': [{'role': 'resource provider', 

445 'status': 'active', 

446 'id': 1, 

447 'contact': {'id': 5, 

448 'organisation': {'city': 'Jülich', 

449 'contact_url': 'mailto:toar-data@fz-juelich.de', 

450 'country': 'Germany', 

451 'homepage': 'https://www.fz-juelich.de', 

452 'id': 2, 

453 'kind': 'research', 

454 'longname': 'Forschungszentrum Jülich', 

455 'name': 'FZJ', 

456 'postcode': '52425', 

457 'street_address': 'Wilhelm-Johnen-Straße', 

458 }, 

459 },}], 

460 'license': 'This data is published under a Creative Commons Attribution 4.0 International (CC BY 4.0). https://creativecommons.org/licenses/by/4.0/', 

461 'citation': 'Forschungszentrum Jülich: time series of o3 at Shangdianzi, accessed from the TOAR database on 2023-07-28 12:00:00', 

462 'attribution': 'Test-Attributions to be announced'}], 

463 'data': [{'datetime': '1991-03-17T04:00:00+00:00', 'value': 19.62, 'flags': 'OK validated verified', 'timeseries_id': 18763, 'version': '1.0'}, 

464 {'datetime': '1992-05-21T17:00:00+00:00', 'value': 31.42, 'flags': 'OK validated verified', 'timeseries_id': 18763, 'version': '1.0'}, 

465 {'datetime': '1999-09-01T12:00:00+00:00', 'value': 11.42, 'flags': 'OK validated verified', 'timeseries_id': 18763, 'version': '1.0'}, 

466 {'datetime': '2001-09-13T08:00:00+00:00', 'value': 11.42, 'flags': 'OK validated verified', 'timeseries_id': 434870, 'version': '1.0'}, 

467 {'datetime': '2005-11-03T08:00:00+00:00', 'value': 27.87, 'flags': 'OK validated verified', 'timeseries_id': 434870, 'version': '1.0'}, 

468 {'datetime': '2009-04-01T02:00:00+00:00', 'value': 7.87, 'flags': 'OK validated verified', 'timeseries_id': 434870, 'version': '1.0'}, 

469 {'datetime': '2012-12-01T23:00:00+00:00', 'value': 14.73, 'flags': 'OK validated verified', 'timeseries_id': 434870, 'version': '1.0'}, 

470 {'datetime': '2012-12-02T00:00:00+00:00', 'value': 14.74, 'flags': 'OK validated verified', 'timeseries_id': 434870, 'version': '1.0'}, 

471 {'datetime': '2013-12-31T23:00:00+00:00', 'value': 12.65, 'flags': 'OK validated verified', 'timeseries_id': 30890, 'version': '1.0'}, 

472 {'datetime': '2015-08-16T15:00:00+00:00', 'value': 87.19, 'flags': 'OK validated verified', 'timeseries_id': 30890, 'version': '1.0'}, 

473 {'datetime': '2019-07-24T16:00:00+00:00', 'value': 67.43, 'flags': 'OK validated verified', 'timeseries_id': 30890, 'version': '1.0'}, 

474 {'datetime': '2023-01-18T22:00:00+00:00', 'value': 5.18, 'flags': 'OK validated verified', 'timeseries_id': 18763, 'version': '1.0'}, 

475 {'datetime': '2025-01-18T22:00:00+00:00', 'value': 5.18, 'flags': 'OK validated verified', 'timeseries_id': 18763, 'version': '1.0'}]} 

476 assert response.json() == expected_resp 

477 

478 

479 def test_get_timeseries_merged_with_fields(self, client, db): 

480 with patch('toardb.timeseries.crud.dt.datetime', FixedDatetime): 

481 response = client.get("/data/timeseries_merged/?station_code=SDZ54421&variable_id=5&fields=datetime,value,timeseries_id") 

482 expected_status_code = 200 

483 assert response.status_code == expected_status_code 

484 expected_resp = {'metadata': [{'id': 18763, 

485 'data_end_date': '2025-02-25T14:00:00+00:00', 

486 'data_origin_type': 'measurement', 

487 'label': '', 'data_origin': 'instrument', 

488 'sampling_height': 7.0, 'doi': '', 'order': 1, 'provider_version': 'N/A', 

489 'coverage': -1.0, 'sampling_frequency': 'hourly', 

490 'additional_metadata': {"original_units": "ppb"}, 

491 'aggregation': 'mean', 'data_start_date': '1991-01-01T00:00:00+00:00', 

492 'station': {'codes': ['SDZ54421'], 'name': 'Shangdianzi', 'coordinate_validation_status': 'not checked', 

493 'state': 'Beijing Shi', 'type_of_area': 'unknown', 'additional_metadata': {"add_type": "nature reservation"}, 

494 'id': 2, 'coordinates': {'lat': 40.65, 'lng': 117.106, 'alt': 293.9}, 'country': 'China', 'type': 'unknown', 

495 'timezone': 'Asia/Shanghai', 'aux_docs': [], 'aux_images': [], 'aux_urls': [], 'changelog': [], 

496 'globalmeta': {'climatic_zone_year2016': '6 (warm temperate dry)', 

497 'distance_to_major_road_year2020': -999.0, 

498 'dominant_ecoregion_year2017': '-1 (undefined)', 

499 'dominant_landcover_year2012': '11 (Cropland, rainfed, herbaceous cover)', 

500 'ecoregion_description_25km_year2017': '', 

501 'htap_region_tier1_year2010': '11 (MDE Middle East: S. Arabia, Oman, etc, Iran, Iraq)', 

502 'landcover_description_25km_year2012': '', 

503 'max_population_density_25km_year1990': -1.0, 

504 'max_population_density_25km_year2015': -1.0, 

505 'max_stable_nightlights_25km_year1992': -999.0, 

506 'max_stable_nightlights_25km_year2013': -999.0, 

507 'max_topography_srtm_relative_alt_5km_year1994': -999.0, 

508 'mean_nox_emissions_10km_year2000': -999.0, 

509 'mean_nox_emissions_10km_year2015': -999.0, 

510 'mean_population_density_250m_year1990': -1.0, 

511 'mean_population_density_250m_year2015': -1.0, 

512 'mean_population_density_5km_year1990': -1.0, 

513 'mean_population_density_5km_year2015': -1.0, 

514 'mean_stable_nightlights_1km_year2013': -999.0, 

515 'mean_stable_nightlights_5km_year2013': -999.0, 

516 'mean_topography_srtm_alt_1km_year1994': -999.0, 

517 'mean_topography_srtm_alt_90m_year1994': -999.0, 

518 'min_topography_srtm_relative_alt_5km_year1994': -999.0, 

519 'stddev_topography_srtm_relative_alt_5km_year1994': -999.0, 

520 'toar1_category': 'unclassified', 

521 'toar2_category': 'suburban', 

522 }}, 

523 'variable': {'id': 5, 'displayname': 'Ozone', 'units': 'nmol mol-1', 'longname': 'ozone', 

524 'name': 'o3', 'cf_standardname': 'mole_fraction_of_ozone_in_air', 'chemical_formula': 'O3'}, 

525 'programme': {'longname': '', 'homepage': '', 'name': '', 'id': 0, 'description': ''}, 

526 'roles': [{'role': 'resource provider', 

527 'status': 'active', 

528 'id': 1, 

529 'contact': {'id': 5, 

530 'organisation': {'city': 'Jülich', 

531 'contact_url': 'mailto:toar-data@fz-juelich.de', 

532 'country': 'Germany', 

533 'homepage': 'https://www.fz-juelich.de', 

534 'id': 2, 

535 'kind': 'research', 

536 'longname': 'Forschungszentrum Jülich', 

537 'name': 'FZJ', 

538 'postcode': '52425', 

539 'street_address': 'Wilhelm-Johnen-Straße', 

540 }, 

541 },}], 

542 'license': 'This data is published under a Creative Commons Attribution 4.0 International (CC BY 4.0). https://creativecommons.org/licenses/by/4.0/', 

543 'citation': 'Forschungszentrum Jülich: time series of o3 at Shangdianzi, accessed from the TOAR database on 2023-07-28 12:00:00', 

544 'attribution': 'Test-Attributions to be announced'}, 

545 {'id': 434870, 

546 'data_end_date': '2025-02-25T14:00:00+00:00', 

547 'data_origin_type': 'measurement', 

548 'label': '', 'data_origin': 'instrument', 

549 'sampling_height': 7.0, 'doi': '', 'order': 2, 'provider_version': 'N/A', 

550 'coverage': -1.0, 'sampling_frequency': 'hourly', 

551 'additional_metadata': {"original_units": "ppb"}, 

552 'aggregation': 'mean', 'data_start_date': '1991-01-01T00:00:00+00:00', 

553 'station': {'codes': ['SDZ54421'], 'name': 'Shangdianzi', 'coordinate_validation_status': 'not checked', 

554 'state': 'Beijing Shi', 'type_of_area': 'unknown', 'additional_metadata': {"add_type": "nature reservation"}, 

555 'id': 2, 'coordinates': {'lat': 40.65, 'lng': 117.106, 'alt': 293.9}, 'country': 'China', 'type': 'unknown', 

556 'timezone': 'Asia/Shanghai', 'aux_docs': [], 'aux_images': [], 'aux_urls': [], 'changelog': [], 

557 'globalmeta': {'climatic_zone_year2016': '6 (warm temperate dry)', 

558 'distance_to_major_road_year2020': -999.0, 

559 'dominant_ecoregion_year2017': '-1 (undefined)', 

560 'dominant_landcover_year2012': '11 (Cropland, rainfed, herbaceous cover)', 

561 'ecoregion_description_25km_year2017': '', 

562 'htap_region_tier1_year2010': '11 (MDE Middle East: S. Arabia, Oman, etc, Iran, Iraq)', 

563 'landcover_description_25km_year2012': '', 

564 'max_population_density_25km_year1990': -1.0, 

565 'max_population_density_25km_year2015': -1.0, 

566 'max_stable_nightlights_25km_year1992': -999.0, 

567 'max_stable_nightlights_25km_year2013': -999.0, 

568 'max_topography_srtm_relative_alt_5km_year1994': -999.0, 

569 'mean_nox_emissions_10km_year2000': -999.0, 

570 'mean_nox_emissions_10km_year2015': -999.0, 

571 'mean_population_density_250m_year1990': -1.0, 

572 'mean_population_density_250m_year2015': -1.0, 

573 'mean_population_density_5km_year1990': -1.0, 

574 'mean_population_density_5km_year2015': -1.0, 

575 'mean_stable_nightlights_1km_year2013': -999.0, 

576 'mean_stable_nightlights_5km_year2013': -999.0, 

577 'mean_topography_srtm_alt_1km_year1994': -999.0, 

578 'mean_topography_srtm_alt_90m_year1994': -999.0, 

579 'min_topography_srtm_relative_alt_5km_year1994': -999.0, 

580 'stddev_topography_srtm_relative_alt_5km_year1994': -999.0, 

581 'toar1_category': 'unclassified', 

582 'toar2_category': 'suburban', 

583 }}, 

584 'variable': {'id': 5, 'displayname': 'Ozone', 'units': 'nmol mol-1', 'longname': 'ozone', 

585 'name': 'o3', 'cf_standardname': 'mole_fraction_of_ozone_in_air', 'chemical_formula': 'O3'}, 

586 'programme': {'longname': '', 'homepage': '', 'name': '', 'id': 0, 'description': ''}, 

587 'roles': [{'role': 'resource provider', 

588 'status': 'active', 

589 'id': 2, 

590 'contact': {'id': 4, 

591 'organisation': {'city': 'Dessau-Roßlau', 

592 'contact_url': 'mailto:immission@uba.de', 

593 'country': 'Germany', 

594 'homepage': 'https://www.umweltbundesamt.de', 

595 'id': 1, 

596 'kind': 'government', 

597 'longname': 'Umweltbundesamt', 

598 'name': 'UBA', 

599 'postcode': '06844', 

600 'street_address': 'Wörlitzer Platz 1', 

601 }, 

602 }, 

603 }], 

604 'license': 'This data is published under a Creative Commons Attribution 4.0 International (CC BY 4.0). https://creativecommons.org/licenses/by/4.0/', 

605 'citation': 'Umweltbundesamt: time series of o3 at Shangdianzi, accessed from the TOAR database on 2023-07-28 12:00:00'}, 

606 {'id': 30890, 

607 'data_end_date': '2025-02-25T14:00:00+00:00', 

608 'data_origin_type': 'measurement', 

609 'label': '', 'data_origin': 'instrument', 

610 'sampling_height': 7.0, 'doi': '', 'order': 2, 'provider_version': 'N/A', 

611 'coverage': -1.0, 'sampling_frequency': 'hourly', 

612 'additional_metadata': {"original_units": "ppb"}, 

613 'aggregation': 'mean', 'data_start_date': '1991-01-01T00:00:00+00:00', 

614 'station': {'codes': ['SDZ54421'], 'name': 'Shangdianzi', 'coordinate_validation_status': 'not checked', 

615 'state': 'Beijing Shi', 'type_of_area': 'unknown', 'additional_metadata': {"add_type": "nature reservation"}, 

616 'id': 2, 'coordinates': {'lat': 40.65, 'lng': 117.106, 'alt': 293.9}, 'country': 'China', 'type': 'unknown', 

617 'timezone': 'Asia/Shanghai', 'aux_docs': [], 'aux_images': [], 'aux_urls': [], 'changelog': [], 

618 'globalmeta': {'climatic_zone_year2016': '6 (warm temperate dry)', 

619 'distance_to_major_road_year2020': -999.0, 

620 'dominant_ecoregion_year2017': '-1 (undefined)', 

621 'dominant_landcover_year2012': '11 (Cropland, rainfed, herbaceous cover)', 

622 'ecoregion_description_25km_year2017': '', 

623 'htap_region_tier1_year2010': '11 (MDE Middle East: S. Arabia, Oman, etc, Iran, Iraq)', 

624 'landcover_description_25km_year2012': '', 

625 'max_population_density_25km_year1990': -1.0, 

626 'max_population_density_25km_year2015': -1.0, 

627 'max_stable_nightlights_25km_year1992': -999.0, 

628 'max_stable_nightlights_25km_year2013': -999.0, 

629 'max_topography_srtm_relative_alt_5km_year1994': -999.0, 

630 'mean_nox_emissions_10km_year2000': -999.0, 

631 'mean_nox_emissions_10km_year2015': -999.0, 

632 'mean_population_density_250m_year1990': -1.0, 

633 'mean_population_density_250m_year2015': -1.0, 

634 'mean_population_density_5km_year1990': -1.0, 

635 'mean_population_density_5km_year2015': -1.0, 

636 'mean_stable_nightlights_1km_year2013': -999.0, 

637 'mean_stable_nightlights_5km_year2013': -999.0, 

638 'mean_topography_srtm_alt_1km_year1994': -999.0, 

639 'mean_topography_srtm_alt_90m_year1994': -999.0, 

640 'min_topography_srtm_relative_alt_5km_year1994': -999.0, 

641 'stddev_topography_srtm_relative_alt_5km_year1994': -999.0, 

642 'toar1_category': 'unclassified', 

643 'toar2_category': 'suburban', 

644 }}, 

645 'variable': {'id': 5, 'displayname': 'Ozone', 'units': 'nmol mol-1', 'longname': 'ozone', 

646 'name': 'o3', 'cf_standardname': 'mole_fraction_of_ozone_in_air', 'chemical_formula': 'O3'}, 

647 'programme': {'longname': '', 'homepage': '', 'name': '', 'id': 0, 'description': ''}, 

648 'roles': [{'role': 'resource provider', 

649 'status': 'active', 

650 'id': 1, 

651 'contact': {'id': 5, 

652 'organisation': {'city': 'Jülich', 

653 'contact_url': 'mailto:toar-data@fz-juelich.de', 

654 'country': 'Germany', 

655 'homepage': 'https://www.fz-juelich.de', 

656 'id': 2, 

657 'kind': 'research',