def populate():
db = DatabaseConnection()
sql = """INSERT INTO accidents_weather_data_mart.accident_fact(
hour_key,
location_key,
accident_key,
weather_key,
is_fatal,
is_intersection
)
SELECT AH.hour_key,
AL.location_key,
AH.accident_key,
WL.weather_key,
A.collision_classification = 'fatal' as is_fatal,
L.is_intersection
FROM relations.accident_hour_relation AH,
relations.accident_location_relation AL,
relations.weather_hour_relation WH,
relations.weather_location_relation WL,
accidents_weather_data_mart.accident_dimension A,
accidents_weather_data_mart.location_dimension L
WHERE AL.accident_key = AH.accident_key
AND A.accident_key = AL.accident_key
AND AH.hour_key = WH.hour_key
AND WH.weather_key = WL.weather_key
AND AL.location_key = WL.location_key
AND L.location_key = AL.location_key"""
with db.get_connection().cursor() as cursor:
cursor.execute(sql)
def get_count(sql):
db = DatabaseConnection()
with db.get_connection().cursor(
cursor_factory=psycopg2.extras.DictCursor) as cursor:
cursor.execute(sql)
return cursor.fetchone()['count']
def insert_many(entities):
"""
Inserts a single entity to the database.
:param entities: a tuple of the form -> ([
street_name,
intersection_1,
intersection_2,
longitude,
latitude,
city,
neighbourhood,
is_intersection])
:return: None
"""
db = DatabaseConnection()
sql_insert = """INSERT INTO dimension_pre_stage.location_dimension_pre_stage (
street_name,
intersection_1,
intersection_2,
longitude,
latitude,
city,
neighbourhood,
is_intersection)
VALUES %s;"""
with db.get_connection().cursor() as cursor:
execute_values(cur=cursor, sql=sql_insert, argslist=entities)
def insert_many(entities):
"""
Insert many entities to the database.
:param entities: a list of tuples of the form -> [(
station_name,
longitude,
latitude,
elevation,
date,
time,
temperature,
dew_point_temp,
relative_humidity,
wind_direction,
wind_speed,
wind_speed_flag,
visibility,
station_pressure,
humidex,
wind_chill,
wind_chill_flag,
weather)]
:return: None
"""
db = DatabaseConnection()
sql_insert = """INSERT INTO dimension_pre_stage.weather_dimension_pre_stage (
station_name,
longitude,
latitude,
elevation,
date,
time,
temperature,
temperature_flag,
dew_point_temp,
dew_point_temp_flag,
relative_humidity,
relative_humidity_flag,
wind_direction,
wind_direction_flag,
wind_speed,
wind_speed_flag,
visibility,
visibility_flag,
station_pressure,
station_pressure_flag,
humidex,
humidex_flag,
wind_chill,
wind_chill_flag,
weather,
weather_flag)
VALUES %s"""
with db.get_connection().cursor() as cursor:
execute_values(cur=cursor, sql=sql_insert, argslist=entities)
def insert_many_temp(entities):
db = DatabaseConnection()
sql_insert = """INSERT INTO relations.weather_location_temp_relation (station_name, location_key, date, time)
VALUES %s;"""
with db.get_connection().cursor() as cursor:
execute_values(cur=cursor, sql=sql_insert, argslist=entities)
def connect_accident_hour_dimension():
db = DatabaseConnection()
sql = """INSERT INTO relations.accident_hour_relation (accident_key, hour_key)
SELECT A.accident_key, H.hour_key
FROM dimension_pre_stage.accident_dimension_pre_stage A, dimension_pre_stage.hour_dimension_pre_stage H
WHERE date_trunc('hour', A.date + A.time + INTERVAL '30 minute') = H.date + H.hour_start;"""
with db.get_connection().cursor() as cursor:
cursor.execute(sql)
def connect_weather_to_location_dimension():
db = DatabaseConnection()
sql = """INSERT INTO relations.weather_location_relation (weather_key, location_key)
SELECT W.weather_key, T.location_key
FROM dimension_pre_stage.weather_dimension_pre_stage W, relations.weather_location_temp_relation T
WHERE W.station_name = T.station_name AND W.date = T.date AND W.time = T.time;"""
with db.get_connection().cursor() as cursor:
cursor.execute(sql)
def insert_many(entities):
"""
Insert many entities to the database.
:param entities: a list of tuples of the form -> [(
id,
collision_id,
location,
longitude,
latitude,
date,
time,
environment,
environment_flag,
light,
light_flag,
surface_condition,
surface_condition,
traffic_control,
traffic_control_flag,
traffic_control_condition,
traffic_control_condition_flag,
collision_classification,
collision_classification_flag,
impact_type,
impact_type_flag,
no_of_pedestrians)]
:return: None
"""
db = DatabaseConnection()
sql_insert = """INSERT INTO dimension_pre_stage.accident_dimension_pre_stage (
longitude,
latitude,
date,
time,
street_name,
street1,
street2,
environment,
environment_flag,
road_surface,
road_surface_flag,
traffic_control,
traffic_control_flag,
visibility,
visibility_flag,
collision_classification,
collision_classification_flag,
impact_type,
impact_type_flag)
VALUES %s"""
with db.get_connection().cursor() as cursor:
execute_values(cur=cursor, sql=sql_insert, argslist=entities)
def connect_event_hour_dimension():
db = DatabaseConnection()
sql = """INSERT INTO relations.event_hour_relation (event_key, hour_key)
SELECT E.event_key, H.hour_key
FROM dimension_pre_stage.event_dimension_pre_stage E,
dimension_pre_stage.hour_dimension_pre_stage H
WHERE E.start_date = H.date OR E.end_date = H.date"""
with db.get_connection().cursor() as cursor:
cursor.execute(sql)
def connect_accident_location_dimension():
db = DatabaseConnection()
sql = """INSERT INTO relations.accident_location_relation (accident_key, location_key)
SELECT A.accident_key, L.location_key
FROM dimension_pre_stage.accident_dimension_pre_stage A,
dimension_pre_stage.location_dimension_pre_stage L
WHERE L.longitude = A.longitude AND L.latitude = A.latitude"""
with db.get_connection().cursor() as cursor:
cursor.execute(sql)
def connect_weather_hour_dimension():
db = DatabaseConnection()
sql = """INSERT INTO relations.weather_hour_relation (weather_key, hour_key)
SELECT W.weather_key, H.hour_key
FROM dimension_pre_stage.weather_dimension_pre_stage W,
dimension_pre_stage.hour_dimension_pre_stage H
WHERE W.date = H.date AND H.hour_start <= W.time AND W.time <= H.hour_end"""
with db.get_connection().cursor() as cursor:
cursor.execute(sql)
def connect_event_location_dimension():
db = DatabaseConnection()
sql = """INSERT INTO relations.event_location_relation (event_key, location_key)
SELECT E.event_key, L.location_key
FROM dimension_pre_stage.event_dimension_pre_stage E,
dimension_pre_stage.location_dimension_pre_stage L
WHERE E.city = L.city"""
with db.get_connection().cursor() as cursor:
cursor.execute(sql)
def fetch_all(sql):
db = DatabaseConnection()
with db.get_connection().cursor(
cursor_factory=psycopg2.extras.DictCursor) as cursor:
cursor.execute(sql)
while True:
results = cursor.fetchmany(500)
if not results:
break
for result in results:
yield result
def insert_many(entities):
db = DatabaseConnection()
sql_insert = """INSERT INTO dimension_pre_stage.event_dimension_pre_stage (
name,
start_date,
end_date,
city)
VALUES %s;"""
with db.get_connection().cursor() as cursor:
execute_values(cur=cursor, sql=sql_insert, argslist=entities)
def update_temperature_nulls():
"""
Update all temperature pre-stage values
"""
db = DatabaseConnection()
sql_update = \
"""
UPDATE dimension_pre_stage.weather_dimension_pre_stage as W
SET temperature_flag = 'estimated',
temperature = av.temp
FROM (select date, AVG(temperature) as temp
from dimension_pre_stage.weather_dimension_pre_stage
group by date) as av
WHERE
W.date = av.date AND W.temperature IS NULL
"""
with db.get_connection().cursor() as cursor:
cursor.execute(sql_update)
def update_weather_nulls():
"""
Update all weather values
"""
db = DatabaseConnection()
sql_update = \
"""
UPDATE dimension_pre_stage.weather_dimension_pre_stage as W
SET weather_flag = 'estimated',
weather = av.weath
FROM (SELECT date, weather as weath, COUNT(weather) AS cnt
FROM dimension_pre_stage.weather_dimension_pre_stage
GROUP BY date, weather
ORDER BY cnt DESC
LIMIT 1) as av
WHERE
W.date = av.date AND W.weather IS NULL
"""
with db.get_connection().cursor() as cursor:
cursor.execute(sql_update)
def copy_data_from_accident_pre_stage():
db = DatabaseConnection()
sql = """INSERT INTO accidents_weather_data_mart.accident_dimension (
accident_key,
time,
environment,
environment_flag,
road_surface,
road_surface_flag,
traffic_control,
traffic_control_flag,
visibility,
visibility_flag,
collision_classification,
collision_classification_flag,
impact_type,
impact_type_flag )
SELECT accident_key,
time,
environment,
environment_flag,
road_surface,
road_surface_flag,
traffic_control,
traffic_control_flag,
visibility,
visibility_flag,
collision_classification,
collision_classification_flag,
impact_type,
impact_type_flag
FROM dimension_pre_stage.accident_dimension_pre_stage"""
with db.get_connection().cursor() as cursor:
cursor.execute(sql)
