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 process_item(self, item, spider=None):
s = self.session()
records_updated = 0
records_created = 0
for record in item:
created = False
duid = normalize_duid(record["DUID"])
station_code = facility_map_station(
duid, normalize_duid(record["STATIONID"]))
station = (s.query(Station).filter(
Station.network_code == station_code).one_or_none())
facility = (s.query(Facility).filter(
Facility.network_code == duid).one_or_none())
if not station:
station = Station(
code=station_code,
network_code=station_code,
network_id="NEM",
created_by="au.nem.mms.statdualloc",
)
if not facility:
facility = Facility(
code=duid,
network_code=duid,
network_id="NEM",
status_id="retired",
created_by="au.nem.mms.statdualloc",
)
records_created += 1
created = True
else:
facility.updated_by = "au.nem.mms.statdualloc"
records_updated += 1
facility.station = station
try:
s.add(facility)
s.commit()
except Exception as e:
logger.error(e)
logger.debug("{} facility record with id {}".format(
"Created" if created else "Updated", duid))
logger.info("Created {} facility records and updated {}".format(
records_created, records_updated))
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 patches():
sqls = [
# "update facility set capacity_registered = 2.0, unit_capacity = 2.0 where code = 'GOSNELLS'",
# "update facility set capacity_registered = 1.1, unit_capacity = 1.1 where code = 'ATLAS'",
# code GULLRWF2_74 -> Biala
"update facility set active=false where network_code ='GULLRWF2'",
"update facility set station_id = (select id from station where name = 'Wivenhoe Small Hydro') where code ='WIVENSH'",
"update station set name = 'Wivenhoe Mini' where name = 'Wivenhoe Small'",
# "update facility set fueltech_id = 'pumps' where network_code in ('PUMP2', 'PUMP1')",
# "update facility set active=false where code='PIONEER'",
# "update facility set station_id = null where name='Crookwell' and code is null",
# "update facility set station_id = null where name='Pioneer Sugar Mill' and code is null",
]
with engine.connect() as c:
for query in sqls:
rows = c.execute(query)
pprint(rows)
s = session()
duid = None
unit = parse_unit_duid(1, duid)
unit_code = get_unit_code(unit, duid, "Singleton Solar Farm")
singleton = Station(
name="Singleton",
locality="singleton",
network_name="Singleton Solar Farm",
network_id="NEM",
created_by="opennem.patches",
)
s.add(singleton)
singleston_facility = Facility(
code="0NSISF_1",
status_id="operating",
network_region="NSW1",
network_name="Singleton Solar Farm",
fueltech_id="solar_utility",
unit_id=unit.id,
unit_number=unit.number,
unit_capacity=0.4,
capacity_registered=0.4,
created_by="opennem.patches",
)
singleston_facility.station = singleton
s.add(singleston_facility)
s.commit()
def create_revision(model):
# s = session()
# make_transient(model)
clone = model.asdict(exclude_pk=True)
# clone.pop("id")
# model.fromdict(clone)
# pylint: disable=no-member
clone_model = Station().fromdict(clone)
return clone_model
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 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=None):
s = self.session()
records_updated = 0
records_created = 0
for record in item:
created = False
duid = normalize_duid(record["STATIONID"])
name = station_name_cleaner(record["STATIONNAME"])
network_name = normalize_string(record["STATIONNAME"])
address1 = normalize_string(record["ADDRESS1"])
address2 = normalize_string(record["ADDRESS2"])
city = normalize_string(record["CITY"])
state = normalize_string(record["STATE"]).capitalize()
postcode = normalize_string(record["POSTCODE"])
station = (s.query(Station).filter(
Station.network_code == duid).one_or_none())
if not station:
station = Station(
code=duid,
network_code=duid,
created_by="au.nem.mms.stations",
)
records_created += 1
created = True
else:
station.updated_by = "au.nem.mms.stations"
records_updated += 1
station.name = name
station.network_id = "NEM"
station.network_name = network_name
station.address1 = address1
station.address2 = address2
station.locality = city
station.state = state
station.postcode = postcode
try:
s.add(station)
s.commit()
except Exception as e:
logger.error(e)
logger.debug("{} station record with id {}".format(
"Created" if created else "Updated", duid))
logger.info("Created {} records and updated {}".format(
records_created, records_updated))
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 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 load_opennem_facilities():
station_fixture = load_data("facility_registry.json", from_fixture=True)
stations = [{"station_code": k, **v} for k, v in station_fixture.items()]
engine = db_connect()
session = sessionmaker(bind=engine)
s = session()
for station_data in stations:
station = None
facilities = [{
"code": k,
**v
} for k, v in station_data["duid_data"].items()]
# update facilities
for facility_data in facilities:
facility_duid = facility_data["code"]
station_name = station_name_cleaner(station_data["display_name"])
# station_code = normalize_duid(station_data["station_code"])
station_code = facility_map_station(
facility_duid, normalize_duid(station_data["station_code"]))
station_state = map_compat_facility_state(
station_data["status"]["state"])
station_network = "WEM" if station_data["location"][
"state"] == "WA" else "NEM"
station = s.query(Station).filter(
Station.network_code == station_code).one_or_none()
if not station:
station = Station(
network_id=station_network,
code=station_code,
network_code=station_code,
name=station_name,
network_name=station_data["display_name"],
created_by="opennem.load_facilities",
)
logger.info("Created station: {} {} ".format(
station_name, station_code))
s.add(station)
s.commit()
facility_status = station_state
# Network region
facility_network_region = map_compat_network_region(
station_data["region_id"])
# Fueltech
facility_fueltech = None
if "fuel_tech" in facility_data and facility_data["fuel_tech"]:
facility_fueltech = map_compat_fueltech(
facility_data["fuel_tech"])
# Capacity
facility_capacity = None
if "registered_capacity" in facility_data and facility_data[
"registered_capacity"]:
facility_capacity = clean_capacity(
facility_data["registered_capacity"])
facility = None
try:
facility = (s.query(Facility).filter(
Facility.network_code == facility_duid).one_or_none())
except MultipleResultsFound:
logger.error("Multiple facilities found for duid {}".format(
facility_duid))
# facility = (
# s.query(Facility)
# .filter(Facility.network_code == facility_duid)
# .first()
# )
continue
if not facility:
facility = Facility(
code=facility_duid,
network_code=facility_duid,
network_region=facility_network_region,
created_by="opennem.load_facilities",
)
logger.info("Created facility: {} {} to station {} ".format(
facility_duid, facility_fueltech, station_code))
if not facility.unit_id:
facility.unit_id = 1
facility.unit_number = 1
if facility_capacity and not facility.unit_capacity:
facility.unit_capacity = facility_capacity
if facility_capacity and not facility.capacity_registered:
facility.capacity_registered = facility_capacity
if facility_fueltech and not facility.fueltech_id:
facility.fueltech_id = facility_fueltech
if not facility.status_id:
facility.status_id = facility_status
if not facility.station:
facility.station = station
s.add(facility)
s.commit()
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 process_generators(self, generators):
s = self.session()
stations_updated = 0
stations_added = 0
generators_updated = 0
generators_added = 0
for station_key, facilities in generators.items():
facility = None
facility_station = None
created_station = False
created_facility = False
station_name = station_key[0]
duid_unique = has_unique_duid(facilities)
facility_count = len(facilities)
# Step 1. Find the station
# First by duid if it's unique
duid = get_unique_duid(facilities)
# This is the most suitable unit record to use for the station
# see helper above
facility_station_record = get_station_record_from_facilities(
facilities)
facility_network_region = get_unique_reqion(facilities)
if duid and duid_unique and facility_count == 1:
facility_lookup = None
try:
facility_lookup = (s.query(Facility).filter(
Facility.network_code == duid).one_or_none())
except MultipleResultsFound:
logger.warning(
"REL: Multiple stations found for {} {} with duid {}".
format(station_name, facility_network_region, duid))
if facility_lookup and facility_lookup.station:
facility_station = facility_lookup.station
if (duid and (duid_unique and facility_count > 1)
or not duid_unique):
facility_lookup = (s.query(Facility).filter(
Facility.network_code == duid).first())
if facility_lookup and facility_lookup.station:
facility_station = facility_lookup.station
if not facility_station and facility_station_join_by_name(
station_name):
try:
facility_station = (s.query(Station).filter(
Station.name == station_name).one_or_none())
except MultipleResultsFound:
logger.warning(
"REL: Multiple stations found for {} {}".format(
station_name, facility_network_region))
logger.debug(
"REL: Looked up {} by name and region {} and found {}".
format(
station_name,
facility_network_region,
facility.station if facility else "nothing",
))
# Create one as it doesm't exist
if not facility_station:
facility_station = Station(
name=station_name,
network_name=name_normalizer(
facility_station_record["station_name"]),
network_id="NEM",
created_by="pipeline.aemo.registration_exemption",
)
s.add(facility_station)
created_station = True
else:
facility_station.updated_by = (
"pipeline.aemo.registration_exemption")
logger.info("REL: {} station with name {} and code {}".format(
"Created" if created_station else "Updated",
facility_station.name,
facility_station.code,
))
# Step 2. Add the facilities/units to the station
# Now that we have a station or created one ..
# Step go through the facility records we got ..
for facility_record in facilities:
network_name = name_normalizer(facility_record["station_name"])
participant_name = name_normalizer(
facility_record["participant"])
facility_region = normalize_aemo_region(
facility_record["region"])
duid = normalize_duid(facility_record["duid"])
reg_cap = clean_capacity(facility_record["reg_cap"])
unit = parse_unit_duid(facility_record["unit_no"], duid)
unit_size = clean_capacity(facility_record["unit_size"])
unit_code = get_unit_code(
unit, duid, facility_station_record["station_name"])
facility_status = "operating"
facility_dispatch_type = parse_dispatch_type(
facility_record["dispatch_type"])
fueltech = lookup_fueltech(
facility_record["fuel_source_primary"],
facility_record["fuel_source_descriptor"],
facility_record["tech_primary"],
facility_record["tech_primary_descriptor"],
facility_record["dispatch_type"],
)
# Skip loads that are not batteries or pumps for now
# @NOTE @TODO better to centralize this as it needs to be consistent
if (facility_dispatch_type == DispatchType.LOAD
and fueltech not in [
"battery_charging",
"pumps",
]):
continue
# check if we have it by ocode first
facility = (s.query(Facility).filter(
Facility.code == unit_code).one_or_none())
# If the duid is unique then we have no issues on which to join/create
if duid and duid_unique and not facility:
try:
facility = (s.query(Facility).filter(
Facility.network_code == duid).one_or_none())
except MultipleResultsFound:
logger.warning(
"REL: Multiple facilities found for {} {}".format(
station_name, duid))
if duid and not duid_unique and not facility:
facility = (s.query(Facility).filter(
Facility.network_code == duid).filter(
Facility.unit_number == None).filter(
Facility.status_id == "operating").first())
# If the duid is not unique then we need to figure things out ..
if duid and not duid_unique and not facility:
facility_lookup = (
s.query(Facility).filter(Facility.network_code == duid)
# Not having a code means we haven't written to this record yet so we'll use it
.filter(Facility.code == None).all())
facility_db_count = len(facility_lookup)
logging.debug(
"Non unique duid: {} with {} in database and {} in facility duid is {}"
.format(
station_name,
facility_db_count,
facility_count,
duid,
))
if len(facility_lookup) > 0:
facility = facility_lookup.pop()
if not facility:
facility = Facility(
code=unit_code,
network_code=duid,
created_by="pipeline.aemo.registration_exemption",
)
created_facility = True
else:
facility.updated_by = (
"pipeline.aemo.registration_exemption")
# Sanity checking
if len(unit_code) < 3:
raise Exception(
"Unit code {} is invalid. For station {} with duid {}".
format(unit_code, station_name, duid))
#
if not facility.code:
facility.code = unit_code
facility.fueltech_id = fueltech
facility.network_code = duid
facility.network_region = facility_region
facility.network_name = network_name
facility.capacity_registered = reg_cap
facility.dispatch_type = facility_dispatch_type
facility.unit_id = unit.id
facility.unit_number = unit.number
facility.unit_alias = unit.alias
facility.unit_capacity = unit_size
# Assume all REL's are operating if we don't have a status
facility.status_id = "operating"
facility.station = facility_station
# Log that we have a new fueltech
if fueltech and fueltech != facility.fueltech_id:
logger.warning(
"Fueltech mismatch for {} {}: prev {} new {}".format(
facility.name_clean,
facility.code,
facility.fueltech_id,
fueltech,
))
if not created_facility:
facility.updated_by = (
"pipeline.aemo.registration_exemption")
s.add(facility)
s.commit()
logger.info("REL: {} facility with duid {} and id {}".format(
"Created" if created_facility else "Updated",
facility.code,
facility.network_code,
))
generators_updated += 1
logger.info(
"NEM REL Pipeline: Added {} stations, updated {} stations. Added {}, updated {} generators of {} total"
.format(
stations_added,
stations_updated,
generators_added,
generators_updated,
len(generators),
))
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 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 = self.session()
records_updated = 0
records_created = 0
for record in item:
created = False
participant_code = normalize_duid(
record["facilities"][0]["PARTICIPANTID"])
# Step 1. Find participant by code or create
participant = (s.query(ParticipantModel).filter(
ParticipantModel.code == participant_code).one_or_none())
if not participant:
participant = ParticipantModel(
code=participant_code,
network_code=participant_code,
created_by="au.nem.mms.dudetail_summary",
)
logger.debug("Created participant {}".format(participant_code))
else:
participant.updated_by = "au.nem.mms.dudetail_summary"
# Step 3. now create the facilities and associate
for facility_record in record["facilities"]:
duid = normalize_duid(facility_record["DUID"])
station_code = facility_map_station(
duid, normalize_duid(record["id"]))
network_region = normalize_aemo_region(
facility_record["REGIONID"])
date_start = facility_record["date_start"]
date_end = facility_record["date_end"]
facility_state = "retired"
# Step 2. Find station or create
station = (s.query(Station).filter(
Station.network_code == station_code).one_or_none())
if not station:
station = Station(
code=station_code,
network_code=station_code,
network_id="NEM",
created_by="au.nem.mms.dudetail_summary",
)
logger.debug("Created station {}".format(station_code))
else:
station.updated_by = "au.nem.mms.dudetail_summary"
station.participant = participant
if date_end == None:
facility_state = "operating"
if not "DISPATCHTYPE" in facility_record:
logger.error(
"MMS dudetailsummary: Invalid record: {}".format(
facility_record))
continue
dispatch_type = parse_dispatch_type(
facility_record["DISPATCHTYPE"])
facility = (s.query(Facility).filter(
Facility.network_code == duid).one_or_none())
if not facility:
facility = Facility(
code=duid,
network_code=duid,
dispatch_type=dispatch_type,
created_by="au.nem.mms.dudetail_summary",
)
records_created += 1
created = True
else:
facility.updated_by = "au.nem.mms.dudetail_summary"
records_updated += 1
facility.network_region = network_region
facility.deregistered = date_end
facility.registered = date_start
facility.status_id = facility_state
if not facility.dispatch_type:
facility.dispatch_type = dispatch_type
# Associations
facility_station_id = facility_map_station(duid, station.id)
facility.station_id = station.id
try:
s.add(facility)
s.commit()
except Exception as e:
logger.error(e)
logger.debug(
"MMS DudetailSummary:{} facility record with id {}".format(
"Created" if created else "Updated", duid))
logger.info(
"MMS DudetailSummary: Created {} facility records and updated {}".
format(records_created, records_updated))
def process_facilities(self, records):
s = self.session()
# Store a list of all existing duids
all_duids = list(
set([
i[0] for i in s.query(Facility.network_code).filter(
Facility.network_code != None).all()
]))
for _, facility_records in records.items():
facility_index = 1
facility_station = None
created_station = False
station_network_name = record_get_station_name(facility_records)
station_name = station_name_cleaner(station_network_name)
duid_unique = has_unique_duid(facility_records)
facility_count = len(facility_records)
# Step 1. Find the station
# First by duid if it's unique
duid = get_unique_duid(facility_records)
# all GI records should have a region
station_network_region = get_unique_reqion(facility_records)
# This is the most suitable unit record to use for the station
# see helper above
facility_station_record = get_station_record_from_facilities(
facility_records)
if duid and duid_unique and facility_count == 1:
facility_lookup = None
try:
facility_lookup = (s.query(Facility).filter(
Facility.network_code == duid).filter(
Facility.network_region ==
station_network_region).one_or_none())
except MultipleResultsFound:
logger.error(
"Found multiple duid for station with code {}".format(
duid))
continue
if facility_lookup and facility_lookup.station:
facility_station = facility_lookup.station
if (duid and (duid_unique and facility_count > 1)
or not duid_unique):
facility_lookup = (s.query(Facility).filter(
Facility.network_code == duid).filter(
Facility.network_region ==
station_network_region).first())
if facility_lookup and facility_lookup.station:
facility_station = facility_lookup.station
if not facility_station and facility_station_join_by_name(
station_name):
try:
facility_station = (s.query(Station).filter(
Station.name == station_name).one_or_none())
except MultipleResultsFound:
logger.warning(
"Multiple results found for station name : {}".format(
station_name))
facility_station = None
# If we have a station name, and no duid, and it's ok to join by name
# then find the station (make sure to region lock)
if (station_name and not duid and not facility_station
and facility_station_join_by_name(station_name)):
facility = (s.query(Facility).join(Facility.station).filter(
Facility.network_region == station_network_region).filter(
Station.name == station_name).first())
if facility:
facility_station = facility.station
# Create one as it doesn't exist
if not facility_station:
facility_station = Station(
name=station_name,
network_name=name_normalizer(
facility_station_record["station_name"]),
network_id="NEM",
created_by="pipeline.aemo.general_information",
)
s.add(facility_station)
s.commit()
created_station = True
else:
facility_station.updated_by = (
"pipeline.aemo.general_information")
for facility_record in facility_records:
if facility_record["FuelType"] in ["Natural Gas Pipeline"]:
continue
# skip these statuses too
if facility_record["UnitStatus"] in FACILITY_INVALID_STATUS:
continue
facility = None
created_facility = False
facility_network_name = name_normalizer(
facility_record["station_name"])
facility_name = station_name_cleaner(
facility_record["station_name"])
duid = normalize_duid(facility_record["duid"])
reg_cap = clean_capacity(facility_record["NameCapacity"])
units_num = facility_record["Units"] or 1
unit_id = facility_index + (units_num - 1)
unit = parse_unit_duid(unit_id, duid)
unit_size = clean_capacity(facility_record["unit_capacity"])
unit_code = get_unit_code(unit, duid,
facility_record["station_name"])
facility_comissioned = facility_record["SurveyEffective"]
facility_comissioned_dt = None
if type(facility_comissioned) is datetime:
facility_comissioned_dt = facility_comissioned
try:
if type(facility_comissioned) is str:
facility_comissioned_dt = datetime.strptime(
facility_comissioned, "%d/%m/%y")
except ValueError:
logger.error(
"Error parsing date: {}".format(facility_comissioned))
facility_status = map_aemo_facility_status(
facility_record["UnitStatus"])
facility_network_region = normalize_aemo_region(
facility_record["Region"])
facility_fueltech = (lookup_fueltech(
facility_record["FuelType"],
techtype=facility_record["TechType"],
) if ("FuelType" in facility_record
and facility_record["FuelType"]) else None)
if not facility_fueltech:
logger.error("Error looking up fueltech: {} {} ".format(
facility_record["FuelType"],
facility_record["TechType"],
))
# check if we have it by ocode first
facility = (s.query(Facility).filter(
Facility.code == unit_code).one_or_none())
if not facility and duid:
try:
facility = (
s.query(Facility).filter(
Facility.network_code == duid).filter(
Facility.network_region ==
facility_network_region)
# .filter(Facility.nameplate_capacity != None)
.one_or_none())
except MultipleResultsFound:
logger.warn(
"Multiple results found for duid : {}".format(
duid))
if facility:
if facility.station and not facility_station:
facility_station = facility.station
logger.info(
"GI: Found facility by DUID: code {} station {}".
format(
facility.code,
facility.station.name
if facility.station else None,
))
# Done trying to find existing
if not facility:
facility = Facility(
code=unit_code,
network_code=duid,
created_by="pipeline.aemo.general_information",
)
facility.station = facility_station
created_facility = True
if duid and not facility.network_code:
facility.network_code = duid
facility.updated_by = "pipeline.aemo.general_information"
if not facility.network_region:
facility.network_region = facility_network_region
facility.updated_by = "pipeline.aemo.general_information"
if not facility.network_name:
facility.network_name = facility_network_name
facility.updated_by = "pipeline.aemo.general_information"
if not facility.fueltech_id and facility_fueltech:
facility.fueltech_id = facility_fueltech
facility.updated_by = "pipeline.aemo.general_information"
if not facility.capacity_registered or (
facility.status and facility.status != "operating"):
facility.capacity_registered = reg_cap
facility.updated_by = "pipeline.aemo.general_information"
# @TODO work this out
# facility.dispatch_type = facility_dispatch_type
if not facility.unit_id:
facility.unit_id = unit.id
facility.unit_number = unit.number
facility.unit_size = unit_size
facility.unit_alias = unit.alias
if not facility.unit_capacity or (
facility.status and facility.status != "operating"):
facility.unit_capacity = unit_size
facility.updated_by = "pipeline.aemo.general_information"
# if not facility.status_id:
facility.status_id = facility_status
# facility.updated_by = "pipeline.aemo.general_information"
if not facility.registered and facility_comissioned_dt:
facility.registered = facility_comissioned_dt
facility.updated_by = "pipeline.aemo.general_information"
facility.station = facility_station
if facility.fueltech_id is None:
logger.warning("Could not find fueltech for: {} {}".format(
facility.code, facility.network_code))
# facility.status_id = facility_status
if facility_station and not facility.station:
facility.station = facility_station
if facility.status_id is None:
raise Exception(
"GI: Failed to map status ({}) on row: {}".format(
facility.status_id, facility_record))
s.add(facility)
s.commit()
facility_index += units_num
if created_station:
logger.info("GI: {} station with name {} ".format(
"Created" if created_station else "Updated",
station_name,
# facility_station.id,
))
if created_facility:
logger.info(
"GI: {} facility with duid {} to station {}".format(
"Created" if created_facility else "Updated",
duid,
station_name,
))
try:
s.commit()
except Exception as e:
logger.error(e)
raise e
finally:
s.close()
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
