def get_distinct_locations_and_time_information():
sql = """
SELECT
AL.location_key, L.city, L.latitude, L.longitude, H.date, H.hour_start
FROM
relations.accident_hour_relation AH,
relations.accident_location_relation AL,
relations.weather_hour_relation WH,
dimension_pre_stage.location_dimension_pre_stage L,
dimension_pre_stage.hour_dimension_pre_stage H
WHERE
AH.accident_key = AL.accident_key
AND AH.hour_key = WH.hour_key
AND AL.location_key = L.location_key
AND AH.hour_key = H.hour_key
GROUP BY
AL.location_key, L.city, L.latitude, L.longitude, H.date, H.hour_start
"""
return DAL.fetch_all(sql)
def get_locations_count():
return DAL.get_count("""
SELECT COUNT(*) FROM (SELECT DISTINCT ON (longitude, latitude) *
FROM data_source.collision_data_toronto
GROUP BY id, longitude, latitude) a;""")
def fetch_all_unique_locations():
return DAL.fetch_all("""
SELECT DISTINCT ON (longitude, latitude) *
FROM data_source.collision_data_toronto
GROUP BY id, longitude, latitude""")
def get_count():
return DAL.get_count(
"""SELECT count(*) FROM data_source.collision_data_toronto""")
def fetch_all():
return DAL.fetch_all(
"""SELECT * FROM data_source.collision_data_toronto""")
def get_count():
return DAL.get_count("""SELECT count(*) FROM data_source.climate_data_toronto""")
def fetch_all():
return DAL.fetch_all("""SELECT * FROM data_source.climate_data_toronto""")
def get_count():
return DAL.get_count(
"""SELECT count(*) FROM data_source.climate_data_ottawa""")
def fetch_all():
return DAL.fetch_all(
"""SELECT * FROM data_source.climate_data_ottawa""")
def fetch_all():
return DAL.fetch_all("""SELECT * FROM data_source.station_inventory""")
def get_count():
return DAL.get_count(
"""SELECT count(*) FROM data_source.collision_data_ottawa""")
def fetch_all():
return DAL.fetch_all(
"""SELECT * FROM data_source.collision_data_ottawa""")