def rooftop_facilities() -> None:
session = SessionLocal()
for state_map in STATE_NETWORK_REGION_MAP:
state_rooftop_code = "{}_{}_{}".format(
ROOFTOP_CODE,
state_map["network"].upper(),
state_map["state"].upper(),
)
rooftop_station = session.query(Station).filter_by(
code=state_rooftop_code).one_or_none()
if not rooftop_station:
logger.info("Creating new station {}".format(state_rooftop_code))
rooftop_station = Station(code=state_rooftop_code, )
rooftop_station.name = "Rooftop Solar {}".format(state_map["state"])
rooftop_station.description = "Solar rooftop facilities for {}".format(
state_map["state"])
rooftop_station.approved = False
rooftop_station.approved_by = ""
rooftop_station.created_by = "opennem.importer.rooftop"
if not rooftop_station.location:
rooftop_station.location = Location(state=state_map["state"])
rooftop_fac = session.query(Facility).filter_by(
code=state_rooftop_code).one_or_none()
if not rooftop_fac:
logger.info("Creating new facility {}".format(state_rooftop_code))
rooftop_fac = Facility(code=state_rooftop_code)
network = state_map["network"]
# map to separate AEMO rooftop network
if network.upper() == "NEM":
network = "AEMO_ROOFTOP"
rooftop_fac.network_id = network
rooftop_fac.network_region = state_map["network_region"]
rooftop_fac.fueltech_id = "solar_rooftop"
rooftop_fac.status_id = "operating"
rooftop_fac.active = True
rooftop_fac.dispatch_type = DispatchType.GENERATOR
rooftop_fac.approved_by = "opennem.importer.rooftop"
rooftop_fac.created_by = "opennem.importer.rooftop"
rooftop_station.facilities.append(rooftop_fac)
session.add(rooftop_fac)
session.add(rooftop_station)
session.commit()
def registry_init():
registry = registry_import()
for station in registry:
station_dict = station.dict(exclude={"id"})
# pprint(station_dict)
# pylint:disable no-member
station_model = fromdict(Station(), station_dict)
station_model.approved = True
station_model.approved_at = datetime.now()
station_model.approved_by = "opennem.registry"
station_model.created_by = "opennem.registry"
# location
station_model.location = fromdict(
Location(),
station_dict["location"],
exclude=["id"],
)
if station.location.lat and station.location.lng:
station_model.location.geom = "SRID=4326;POINT({} {})".format(
station.location.lng, station.location.lat)
for fac in station.facilities:
f = Facility(**fac.dict(
exclude={
"id",
"fueltech",
"status",
"network",
"revision_ids",
"scada_power",
}))
f.network_id = fac.network.code
if fac.fueltech:
f.fueltech_id = fac.fueltech.code
f.status_id = fac.status.code
f.approved = True
f.approved_by = "opennem.registry"
f.created_by = "opennem.registry"
f.approved_at = datetime.now()
f.created_at = datetime.now()
station_model.facilities.append(f)
s.add(station_model)
s.commit()
def test_revisions() -> None:
registry = registry_import()
# mms = mms_import()
k = registry.get_code("KWINANA")
station = Station(
code=k.code,
name=k.name,
network_name=k.network_name,
)
s.add(station)
s.commit()
# pylint: disable=no-member
station_clone = station.asdict()
station_clone.pop("id")
pprint(station_clone)
station = create_revision(station)
station.location = Location(
address1=k.location.address1,
address2=k.location.address2,
locality=k.location.locality,
state=k.location.state,
postcode=k.location.postcode,
)
s.add(station)
s.commit()
station = create_revision(station)
station.name = "Kwinana Edited"
s.add(station)
s.commit()
def setup_network_flow_stations(network: NetworkSchema = NetworkNEM) -> None:
"""Creats a station for each network region and a facility for each of imports
exports so that energy values in facility_scada can be matched up per-region"""
session = SessionLocal()
network_regions = get_network_region_schema(network)
for network_region in network_regions:
logger.info(
f"Setting up for network {network.code} and region: {network_region.code}"
)
flow_station_id = generated_flow_station_id(network, network_region)
flow_station = (session.query(Station).filter_by(
code=flow_station_id).filter_by(
network_code=flow_station_id).one_or_none())
if not flow_station:
flow_station = Station(code=flow_station_id,
network_code=flow_station_id)
flow_station.approved = False
flow_station.created_by = IMPORTER_NAME
if not flow_station.location:
flow_station.location = Location(
state=state_from_network_region(network_region.code))
flow_station.name = "Flows for {} state {}".format(
network.code.upper(),
state_from_network_region(network_region.code.upper()))
flow_facilities = [(i,
generated_flow_station_id(network, network_region,
i))
for i in FlowDirection]
for (flow_direction, flow_facility_id) in flow_facilities:
flow_facility_model = (session.query(Facility).filter_by(
code=flow_facility_id).filter_by(
dispatch_type=DispatchType.GENERATOR).filter_by(
network_id=network.code).filter_by(
network_region=network_region.code).one_or_none())
if not flow_facility_model:
flow_facility_model = Facility( # type: ignore
code=flow_facility_id,
dispatch_type=DispatchType.GENERATOR,
network_id=network.code,
network_region=network_region.code,
)
flow_facility_model.status_id = "operating"
flow_facility_model.approved = False
flow_facility_model.created_by = IMPORTER_NAME
flow_facility_model.fueltech_id = flow_direction.value
flow_facility_model.updated_at = datetime.now()
flow_station.facilities.append(flow_facility_model)
session.add(flow_station)
logger.info(
"Created network trading flow station facility: {}".format(
flow_facility_id))
session.commit()
return None
def opennem_init() -> None:
session = SessionLocal()
station_data = load_data("opennem_stations.json", from_project=True)
stations = StationSet()
for s in station_data:
stations.add_dict(s)
for station in stations:
logger.debug("Adding station: {}".format(station.code))
station_model = session.query(Station).filter_by(
code=station.code).one_or_none()
if not station_model:
station_model = Station(code=station.code)
station_model.approved = True
station_model.approved_at = datetime.now()
station_model.approved_by = "opennem.init"
station_model.created_by = "opennem.init"
station_model.description = station.description
station_model.name = station.name
station_model.network_name = station.network_name
if not station_model.location:
station_model.location = Location()
if station.location:
station_model.location.locality = station.location.locality
station_model.location.state = station.location.state
station_model.location.postcode = station.location.postcode
station_model.location.country = station.location.country
if station.location.lat and station.location.lng:
station_model.location.geom = "SRID=4326;POINT({} {})".format(
station.location.lng, station.location.lat)
for fac in station.facilities:
facility_model = (session.query(Facility).filter_by(
code=fac.code).filter_by(
network_id=fac.network.code).one_or_none())
if not facility_model:
facility_model = Facility(code=fac.code,
network_id=fac.network.code)
facility_model.network_region = fac.network_region
facility_model.fueltech_id = fac.fueltech.code
facility_model.status_id = fac.status.code
facility_model.dispatch_type = fac.dispatch_type
facility_model.capacity_registered = fac.capacity_registered
facility_model.registered = fac.registered
facility_model.unit_id = fac.unit_id
facility_model.unit_number = fac.unit_number
facility_model.unit_alias = fac.unit_alias
facility_model.unit_capacity = fac.unit_capacity
facility_model.emissions_factor_co2 = fac.emissions_factor_co2
facility_model.approved = fac.approved
facility_model.approved_by = fac.approved_by
facility_model.created_by = "opennem.init"
facility_model.approved_by = "opennem.init"
session.add(facility_model)
station_model.facilities.append(facility_model)
logger.debug(" => Added facility {}".format(fac.code))
print(station_model)
session.add(station_model)
session.commit()
def registry_init() -> None:
registry = registry_import()
session = SessionLocal()
for station in registry:
station_dict = station.dict(exclude={"id"})
# pprint(station_dict)
station_model = session.query(Station).filter_by(
code=station.code).one_or_none()
if not station_model:
# pylint:disable no-member
station_model = fromdict(Station(), station_dict)
station_model.approved = True
station_model.approved_at = datetime.now()
station_model.approved_by = "opennem.registry"
station_model.created_by = "opennem.registry"
# location
station_model.location = fromdict(
Location(),
station_dict["location"],
exclude=["id"],
)
if station.location.lat and station.location.lng:
station_model.location.geom = "SRID=4326;POINT({} {})".format(
station.location.lng, station.location.lat)
session.add(station_model)
session.commit()
for fac in station.facilities:
f = (session.query(Facility).filter_by(code=fac.code).filter_by(
network_id=fac.network.code).one_or_none())
if not f:
print("new facility {} {}".format(fac.code, fac.network.code))
f = Facility(**fac.dict(
exclude={
"id",
"fueltech",
"status",
"network",
"revision_ids",
"scada_power",
}))
f.approved_by = "opennem.registry"
f.created_by = "opennem.registry"
f.approved_at = datetime.now()
f.created_at = datetime.now()
f.network_id = fac.network.code
if fac.fueltech:
f.fueltech_id = fac.fueltech.code
if fac.network.code:
f.network_code = fac.network.code
f.status_id = fac.status.code
f.approved = True
if station_model.id:
f.station_id = station_model.id
else:
station_model.facilities.append(f)
session.add(station_model)
session.add(f)
try:
session.commit()
except IntegrityError as e:
logger.error(e)
def load_revision(records, created_by):
logger.info("Running db test")
for station_record in records:
station_model = s.query(Station).filter(
Station.code == station_record.code).one_or_none()
if not station_model:
logger.info(
f"New station {station_record.name} {station_record.code}")
station_dict = station_record.dict(
include={"code", "network_name", "name", "location"})
# pprint(station_dict)
# pylint:disable no-member
station_model = fromdict(Station(), station_dict)
station_model.approved = False
station_model.created_by = created_by
# location
if "location" in station_dict and station_dict["location"]:
station_model.location = fromdict(Location(),
station_dict["location"],
exclude=["id"])
if station_record.location.lat and station_record.location.lng:
station_model.location.geom = station_record.location.geom
s.add(station_model)
s.commit()
station_record.id = station_model.id
# revision = revision_factory(
# station_record, ["code", "name", "network_name"], created_by,
# )
# if revision:
# station_model.revisions.append(revision)
# s.add(station_model)
# s.commit()
else:
for field in ["name"]:
if getattr(station_model, field) != getattr(
station_record, field):
revision_factory(station_record, field, created_by)
for facility in station_record.facilities:
facility_model = s.query(Facility).filter(
Facility.code == facility.code).first()
if not facility_model:
logger.info("New facility %s => %s", station_record.name,
facility.code)
facility_dict = facility.dict(
include={
"code",
"network",
# "network_id",
"dispatch_type",
"station",
# "station_id",
# "status",
"network_code",
"network_region",
"network_name",
})
# pprint(station_dict)
# pylint:disable no-member
facility_model = fromdict(Facility(), facility_dict)
facility_model.network_id = facility.network.code
facility_model.approved = False
facility_model.created_by = created_by
s.add(facility_model)
s.commit()
facility.id = facility_model.id
# @NOTE don't create revisions for new facilities
# revision = revision_factory(
# facility, ["code", "dispatch_type"], created_by
# )
# if revision:
# facility_model.revisions.append(revision)
# s.add(facility_model)
# s.commit()
else:
facility.id = facility_model.id
for field in [
"fueltech",
"status",
"capacity_registered",
]:
revision = None
if compare_record_differs(facility_model, facility, field):
# logger.info(
# "%s and %s differ",
# getattr(facility, field),
# getattr(facility_model, field),
# )
revision = revision_factory(facility, field, created_by)
if revision:
facility_model.revisions.append(revision)
s.add(facility_model)
station_model.facilities.append(facility_model)
s.add(station_model)
s.commit()
def process_item(self, item, spider):
s = SessionLocal()
records_added = 0
csvreader = csv.DictReader(item["content"].split("\n"))
for row in csvreader:
if "Participant Code" not in row:
logger.error("Invalid row")
continue
if row["Facility Type"] in [
"Demand Side Program",
"Non-Dispatchable Load",
"Network",
]:
continue
participant = None
participant_name = participant_name_filter(row["Participant Name"])
participant_network_name = normalize_string(
row["Participant Name"])
participant_code = normalize_duid(row["Participant Code"])
participant = (s.query(Participant).filter(
Participant.code == participant_code).one_or_none())
if not participant:
participant = Participant(
created_by=spider.name,
approved_at=datetime.now(),
code=participant_code,
name=participant_name,
network_name=participant_network_name,
)
s.add(participant)
s.commit()
logger.debug(
"Participant not found created new database entry: %s",
participant_code,
)
station = None
facility = None
facility_code = normalize_duid(row["Facility Code"])
station_code = parse_wem_facility_code(facility_code)
station = (s.query(Station).filter(
Station.code == station_code).one_or_none())
if not station:
station = Station(
created_by="opennem.wem.facilities",
approved_at=datetime.now(),
code=station_code,
network_code=station_code,
participant=participant,
)
location = Location(state="WA")
station.location = location
logger.debug("Added WEM station: {}".format(station_code))
facility = (s.query(Facility).filter(
Facility.code == facility_code).one_or_none())
if not facility:
facility = Facility(
created_by=spider.name,
approved_at=datetime.now(),
code=facility_code,
network_id="WEM",
network_code=facility_code,
network_region="WEM",
)
capacity_registered = clean_capacity(row["Maximum Capacity (MW)"])
capacity_unit = clean_capacity(row["Maximum Capacity (MW)"])
registered_date = row["Registered From"]
facility.status_id = "operating"
facility.capacity_registered = capacity_registered
facility.unit_id = 1
facility.unit_number = 1
facility.unit_capacity = capacity_unit
if registered_date:
registered_date_dt = datetime.strptime(registered_date,
"%Y-%m-%d %H:%M:%S")
facility.registered = registered_date_dt
facility.station = station
s.add(facility)
records_added += 1
try:
s.commit()
except IntegrityError as e:
logger.error(e)
pass
except Exception as e:
logger.error("Error: {}".format(e))
finally:
s.close()
return records_added
def process_item(self, item, spider=None):
s = SessionLocal()
records_added = 0
csvreader = csv.DictReader(item["content"].split("\n"))
for row in csvreader:
if "PARTICIPANT_CODE" not in row:
logger.error("Invalid row")
continue
participant = None
participant_code = normalize_duid(row["PARTICIPANT_CODE"])
participant = (s.query(Participant).filter(
Participant.code == participant_code).one_or_none())
if not participant:
participant = Participant(
created_by=spider.name,
approved_by=spider.name,
approved_at=datetime.now(),
code=participant_code,
network_code=participant_code,
network="WEM",
)
s.add(participant)
s.commit()
logger.warning(
"Participant not found created new database entry: {}".
format(participant_code))
station = None
facility = None
facility_code = normalize_duid(row["FACILITY_CODE"])
station_code = parse_wem_facility_code(facility_code)
station_name = station_name_cleaner(row["DISPLAY_NAME"])
station_network_name = normalize_string(row["DISPLAY_NAME"])
station = (s.query(Station).filter(
Station.code == station_code).one_or_none())
if not station:
station = Station(
created_by=spider.name,
approved_by=spider.name,
approved_at=datetime.now(),
code=station_code,
network_code=station_code,
participant=participant,
)
location = Location(state="WA")
s.add(location)
station.location = location
logger.debug("Added WEM station: {}".format(station_code))
lat = row["LATITUDE"]
lng = row["LONGITUDE"]
station.name = station_name
station.network_name = station_network_name
if lat and lng and not station.location.geom:
station.location.geom = "SRID=4326;POINT({} {})".format(
lat, lng)
station.location.geocode_by = "aemo"
station.location.geocode_approved = True
facility = (s.query(Facility).filter(
Facility.code == facility_code).one_or_none())
if not facility:
facility = Facility(
created_by="opennem.wem.live.facilities",
approved_at=datetime.now(),
network_id="WEM",
code=facility_code,
network_code=facility_code,
network_region="WEM",
)
registered_date = row["YEAR_COMMISSIONED"]
if registered_date:
registered_date_dt = None
date_fmt = "%Y"
try:
registered_date_dt = datetime.strptime(
registered_date, date_fmt)
except Exception:
logger.error("Bad date: %s for format %s", registered_date,
date_fmt)
if registered_date_dt:
facility.registered = registered_date_dt
fueltech = lookup_fueltech(fueltype=row["PRIMARY_FUEL"],
techtype=row["FACILITY_TYPE"])
if fueltech and not facility.fueltech:
facility.fueltech_id = fueltech
facility.status_id = "operating"
facility.station = station
s.add(station)
s.add(facility)
s.commit()
records_added += 1
try:
s.commit()
except IntegrityError as e:
logger.error(e)
pass
except Exception as e:
logger.error("Error: {}".format(e))
finally:
s.close()
return records_added
def import_station_set(stations: StationSet,
only_insert_facilities: bool = False) -> None:
session = SessionLocal()
for station in stations:
add_or_update: str = "Updating"
station_model = session.query(Station).filter_by(
code=station.code).one_or_none()
if not station_model:
add_or_update = "Adding"
station_model = Station(code=station.code)
station_model.created_by = "opennem.init"
logger.debug("{} station: {}".format(add_or_update, station.code))
if station.description:
station_model.description = station.description
if station.name:
station_model.name = station.name
station_model.approved = station.approved
if station.approved:
station_model.approved = True
station_model.approved_at = datetime.now()
station_model.approved_by = "opennem.init"
else:
station_model.approved_at = None
station_model.approved_by = None
if station.website_url:
station_model.website_url = station.website_url
if station.network_name:
station_model.network_name = station.network_name
if not station_model.location:
station_model.location = Location()
if station.location:
station_model.location.locality = station.location.locality
station_model.location.state = station.location.state
station_model.location.postcode = station.location.postcode
station_model.location.country = station.location.country
if station.location.lat and station.location.lng:
station_model.location.geom = "SRID=4326;POINT({} {})".format(
station.location.lng, station.location.lat)
session.add(station_model)
session.commit()
for fac in station.facilities:
facility_added = False
facility_model = (session.query(Facility).filter_by(
code=fac.code).filter_by(
network_id=fac.network.code).one_or_none())
if facility_model and only_insert_facilities:
logger.debug(" => skip updating {}".format(
facility_model.code))
continue
if not facility_model:
facility_model = Facility(code=fac.code,
network_id=fac.network.code)
facility_added = True
if facility_model.station_id != station_model.id:
facility_model.station_id = station_model.id
logger.debug(" => Reassigned facility {} to station {}".format(
facility_model.code, station_model.code))
# fueltech
if fac.fueltech:
facility_model.fueltech_id = fac.fueltech.code
if fac.fueltech_id:
facility_model.fueltech_id = fac.fueltech_id
# network
if fac.network:
facility_model.network_id = fac.network.code
if fac.network_id:
facility_model.network_id = fac.network_id
# status
if fac.status:
facility_model.status_id = fac.status.code
if fac.status_id:
facility_model.status_id = fac.status_id
# rest
if fac.dispatch_type:
facility_model.dispatch_type = fac.dispatch_type
if fac.capacity_registered:
facility_model.capacity_registered = fac.capacity_registered
if fac.registered:
facility_model.registered = fac.registered
if fac.network_region:
facility_model.network_region = fac.network_region
facility_model.unit_id = fac.unit_id
facility_model.unit_number = fac.unit_number
facility_model.unit_alias = fac.unit_alias
facility_model.unit_capacity = fac.unit_capacity
if fac.emissions_factor_co2:
facility_model.emissions_factor_co2 = fac.emissions_factor_co2
if fac.approved:
facility_model.approved = fac.approved
if fac.approved:
facility_model.approved_by = "opennem.importer"
else:
facility_model.approved_by = None
if not facility_model.created_by:
facility_model.created_by = "opennem.init"
session.add(facility_model)
station_model.facilities.append(facility_model)
logger.debug(" => {} facility {} to {} {}".format(
"Added" if facility_added else "Updated",
fac.code,
facility_model.network_id,
facility_model.network_region,
))
session.add(station_model)
session.commit()
def import_nem_interconnects() -> None:
session = SessionLocal()
# Load the MMS CSV file that contains interconnector info
csv_data = load_data(
"mms/PUBLIC_DVD_INTERCONNECTOR_202006010000.CSV",
from_project=True,
)
# gotta be a string otherwise decode
if not isinstance(csv_data, str):
csv_data = decode_bytes(csv_data)
# parse the AEMO CSV into schemas
aemo_table_set = None
try:
aemo_table_set = parse_aemo_csv(csv_data)
except AEMOParserException as e:
logger.error(e)
return None
records: List[MarketConfigInterconnector] = aemo_table_set.get_table(
"MARKET_CONFIG_INTERCONNECTOR").get_records()
for interconnector in records:
if not isinstance(interconnector, MarketConfigInterconnector):
raise Exception("Not what we're looking for ")
# skip SNOWY
# @TODO do these need to be remapped for historical
if interconnector.regionfrom in [
"SNOWY1"
] or interconnector.regionto in ["SNOWY1"]:
continue
logger.debug(interconnector)
interconnector_station = (session.query(Station).filter_by(
code=interconnector.interconnectorid).filter_by(
network_code=interconnector.interconnectorid).one_or_none())
if not interconnector_station:
interconnector_station = Station(
code=interconnector.interconnectorid,
network_code=interconnector.interconnectorid,
)
interconnector_station.approved = False
interconnector_station.created_by = "opennem.importer.interconnectors"
if not interconnector_station.location:
interconnector_station.location = Location(
state=state_from_network_region(interconnector.regionfrom))
interconnector_station.name = interconnector.description
# for network_region in [interconnector.regionfrom, interconnector.regionto]:
# Fac1
int_facility = (session.query(Facility).filter_by(
code=interconnector.interconnectorid).filter_by(
dispatch_type=DispatchType.GENERATOR).filter_by(
network_id="NEM").filter_by(network_region=interconnector.
regionfrom).one_or_none())
if not int_facility:
int_facility = Facility( # type: ignore
code=interconnector.interconnectorid,
dispatch_type=DispatchType.GENERATOR,
network_id="NEM",
network_region=interconnector.regionfrom,
)
int_facility.status_id = "operating"
int_facility.approved = False
int_facility.created_by = "opennem.importer.interconnectors"
int_facility.fueltech_id = None
int_facility.interconnector = True
int_facility.interconnector_region_to = interconnector.regionto
interconnector_station.facilities.append(int_facility)
session.add(interconnector_station)
logger.debug("Created interconnector station: {}".format(
interconnector_station.code))
session.commit()
return None