6. Database¶

6.1. Database Overview¶

The database is indeed the backbone of the system. The database documentation is hosted here. Fig. 6.1 shows the various tables and relations in the database.

Fig. 6.1 ChargEval Database Schema

Interactive diagram

6.2. Flyway¶

Flyway is used for to perform database migrations. This allows version control on database migration scripts, which is just plain sql. The migration scripts are located here. The containered flyway service can be run when any database schema changes are needed.

6.3. Tables¶

The database currently has the following tables in the public schema:

analysis_record: This is the key table in the database. Every time a user submits a request to perform analysis, a record is created in this table. The auto-increment primary key analysis_id is used to create a one-to-many relation with several tables - evtrip_scenarios, dest_charger, evse_charging_session, evse_evs_passed, evse_power_draw, evse_util, ev_stranded, ev_finished, ev_info, od_cd and new_evses. The table also has an associated trigger notify_new_order that generates a notification using pg_notify(), which can be used by processes listening for a notification. In the case of ChargEval, this notification is picked by the controller and an analysis request is queued. More details in the controller.
wa*:
- WA_roads: This table contains the geometry of the WA roads. The roads were made traversable for GAMA by using clean_network() and then transformed to the correct SRID as explained here. Further a topology is created using the table for finding the shortest path etc.
- WA_roads_vertices_pgr: This is a vertices table auto-generated by pgRouting when a topology is created.
- wa_evtrips: This table contains the output of the gravity model. For each origin and destination pair, the ret and dep column contain the number of returning and departing trips respectively. oevs and devs contain the count of EVs in origin and destination respectively, whereas ocars and dcars represent the count of total cars in the origin and destination respectively.
- wa_gas_prices: This table contains the average price of gas for each zip code and should be updated periodically to get the current prices.
- wa_bevs: This table contains the details about the BEVs registered in WA. This information is recieved from the WA DOL and columns like fuel_consumption and range_fe have been added by looking up these make and models from the fueleconomy.gov database. Column connector_code has been added after, based on a EV manufacturer’s charging standard affiliation. For example: Tesla uses Superchargers, so for all Tesla vehicles connector_code = 4, Japanese automakers use CHAdeMO, therfore for Nissan etc. connector_code = 1, American and German automakers use CCS, therefore for BMW etc. connector_code = 2.
ev*: These tables are generated by the agent-based model EVI-ABM for the EVs in the simulation - hence they use the foreign key analysis_id.
- ev_finished: For each analysis, these are the EVs that have finished their respective trips. fin_ts represents the timestamp when the trip was finished for the vehicle with ID veh_id. trip_distance is the length of the shortest path between origin_zip and destination_zip and the distance_travelled represents the actual distance travelled by the EV in the GAMA simulation which could include charging detours. Therefore, \(distance\_travelled >= trip\_distance\). Additional constraint is that combination of veh_id and analysis_id is unique, i.e. for analysis, a vehicle can make only one trip and hence appear in this table only once.
- ev_info: This table stores the EV info for each simulation at each timestamp. This is useful for debugging the simulation and writing to this table can be omitted once we have enough confidence in the simulation logic. This table can be deleted if the database is becoming too large. However, this will affect tabs “finished” and “stranded” in the result viewer. The EV info stored for the timestep simulation_ts includes - the latitude, longitude, SOC, state, probability of charging (calculated using the charging choice decision model), to_charge boolean (probability passed to a binomial draw ultimately deciding whether the EV will charge at a charging station), and speed of the vehicle in the simulation.
- ev_stranded: This table stores the record of all the EVs stranded during the simulation, i.e. they were out of charge. This could happen, if no charging stations were available when the charge was needed. This is an indication of insufficent charging infrastructure. stranded_ts is the timestamp when the EV was stranded. Redundant columns origin_zip and destination_zip added to ease the lookup, where as redundant columns stranded_lat and stranded_lng could be helpful to pin-point the exact location where the EV was out of charge, and useful if the table ev_info was eliminated.
evse*: These tables are generated by the agent-based model EVI-ABM for the EVSEs in the simulation - hence they use the foreign key analysis_id. The agent-based simulation (aka simulation in the documentation) treats all charging stations - built as well as new alike. The field evse_id is generated in GAMA using
- evse_charging_session: This table records all the charging sessions during a simulation. Additional constraint could be the combination of analysis_id, veh_id and evse_id should be unique, i.e. a vehicle may not charge at the same charging station twice during a particular simulation.
- evse_evs_passed: This table records all the EVs that passed a charging station since it was occupied. soc_val records the SOC of the vehicle when it passed the said charging station. This is an important statistic currrently to denote EV infrastructure insufficiency and may change as a more sophisticated queueing model is implemented in GAMA.
- evse_util: This table is redundant (and maybe deleted) but stores the EVSE utilization, i.e. the total energy used by a charging station durring a simulation. Additional constraint that the combination of analysis_id and evse_id should be unique can be enforced.
- evse_power_draw: This table stores the instantaneous power draw for all EVSEs in the simulation.
od_sp: This is a static table and stores the shortest path lengths for all the OD pairs.
built_evse: This table represents the charging stations that are built and operational. The charging station information is sourced from AFDC and will need to be updated regularly.
new_evses: This table stores the information about the location, type etc. of the new charging stations being planned.
evtrip_scenarios: This table stores the EV trips generated by the VCDM. For each analysis, based on the current infrastructure, the VCDM finds the number of EV trips between an origin and destination and determines if an EV if available in the origin zip, is likely to make a trip. If an EV is selected, then a random SOC (soc) and a trip start time (trip_start_time) is assigned such that the trip ends by 10pm. The veh_id should belong to wa_bevs and the combination of analysis_id and veh_id should be unique.
dest_charger: This table contains the booleans fields dc_chademo, dc_combo and dc_level2 to represent where a destination charger of the respective type exists at the zip code. Since, this is based on the charging infrastructure, a base value is calculated for all zip codes (and this will need to be updated periodically as the as-built condition changes), with analysis_id = -1 representing the as-built condition. For every analysis request, if the charging station acts a destination charger for a zip code, a record is added to the table with the respective analysis_id.
zipcode_record: This table contains details about the location of centroid of all zip codes.
user_details: This table contains the details about the users logging onto the ChargEval.

Besides the above tables in the public schema, the database also has an “audit” schema, that is responsible for capturing the changes on certain field. This is implemented using the Audit Trigger. For example, when implemented on the analysis_record table, the trigger captures when the status of the particular column changes. This way the time taken for a particular step (tripgen, eviabm) can be calculated and monitored over time to deduce performance trends.

6.4. Triggers¶

notify_new_order(): The trigger notify_new_order() on the table analysis_record which notifies the listeners that a new record has been added to the table. It also converts the record to JSON and sends it along as a payload. The listener in the Simulation Manager (simman) upon receiving the notification begins the execution process. The first step is the EC2 instance launch to perform trip generation.
notify_trips_generated(): The trigger notify_trips_generated() notifies its listener that the status for the analysis record row has been updated to “trips_generated”. Upon this notification the simman terminates the tripgen EC2 instance and launches another EC2 instance to simulate the agent-based model eviabm.
notify_solved(): The trigger notify_solved() notifies that the agent-based model has solved and the simman then terminates the said EC2 instance.

6.5. Functions¶

The database has several functions that facilitate code re-use and modularity.

sp_len(orig, dest): The function sp_len(orig, dest) takes the origin zip code and destination zip code as arguments and returns the shortest path length in miles between the origin and destination along the WA state road network. The shortest path is calculated using pgr_dijkstra() between the WA_roads source vertices closest to the origin and destination zip centroids (from the zipcode_record table)
sp_od2(orig, dest): The function sp_od2(orig, dest) takes the origin and destination zip code and returns the geometry of the shortest path using pgr_dijkstra(). Of special note is the case-when-end clause that ensures a shortest path made of segments in the correct orientation. For details and solution, refer to the discussion.