Queries that need to be supported

[jvanulde]

The following queries need to be supported in the API's. Elasticsearch is very flexible so we can send range queries etc. Our OGC Features API has limited query capability at the moment but we can send an exact value for a property and/or send a bounding box.

Suggest we look at what needs to be supported in the database Views first. Then we can look at how the datasets will flow to Elasticsearch.

• [ ] Entrapments by DA: sC_CasDayL3 aggregated at dauid
• [ ] Hospital demand by neighbourhood: (sC_CasDayL3 + sC_CasDayL2) aggregated at the adauid
• [ ] Road-blocking debris by DA: sCt_DebrisTotal aggregated at the dauid
• [ ] Mass care by neighbourhood: sC_DisplRes3 aggregated at the adauid
• [ ] Mass care by municipality: sC_DisplRes3 aggregated at the csdname level
• [ ] Economic impact by municpality: I think sC_AssetLoss (? not sure which variable represents the total loss) aggregated at the csdname level
• [ ] Total economic impact: I think sC_AssetLoss aggregated at the provincial or national level

[wkhchow]

Economic impact by municpality: I think sC_AssetLoss sL_Asset (? not sure which variable represents the total loss) aggregated at the csdname level

[tieganh]

Quick comment, I'd like to change the sC_DisplRes3 to sC_DisplRes90. This means it's counting most of the people who have extensive-complete damage, versus everyone with any damage. I think it's a more accurate portrayal of who might actual need a place to sleep in the early response phase. Here is the script I used to query my csv's:

import pandas as pd

# Read in data loss = pd.read_csv('s_lossesbyasset_IDM6p8_Sidney_b0_212.csv') cons = pd.read_csv('s_consequences_IDM6p8_Sidney_b0_210.csv') damg = pd.read_csv('s_dmgbyasset_IDM6p8_Sidney_b0_210.csv')

#Merge consequences to damage and create calculated fields data = damg.merge(cons,left_on='asset_id', right_on='asset_ref') data['hospital'] = data['casualties_day_severity_2']+data['casualties_day_severity_3'] data['debris'] = data['debris_brick_wood_tons']+data['debris_concrete_steel_tons']

# Group by DAUID, ADAUID, etc. lossout = loss.groupby('csduid')['totalLoss'].sum().reset_index() entrapmentsout = data.groupby('dauid')['casualties_day_severity_3'].sum().reset_index() hospitalout = data.groupby('adauid')['hospital'].sum().reset_index() debrisout = data.groupby('dauid')['debris'].sum().reset_index() carelocalout = data.groupby('dauid')['sc_Displ90'].sum().reset_index() caremuniout = data.groupby('csduid')['sc_Displ90'].sum().reset_index() print('Total cost is {:,d}' % loss['totalLoss'].sum())