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"]) capacity_registered = clean_capacity(record["REGISTEREDCAPACITY"]) capacity_max = clean_capacity(record["MAXCAPACITY"]) dispatch_type = parse_dispatch_type(record["DISPATCHTYPE"]) facility = (s.query(Facility).filter( Facility.network_code == duid).one_or_none()) if not facility: facility = Facility( code=duid, network_code=duid, status_id="retired", dispatch_type=dispatch_type, created_by="au.nem.mms.dudetail", ) records_created += 1 created = True else: facility.updated_by = "au.nem.mms.dudetail" records_updated += 1 facility.capacity_registered = capacity_registered facility.capacity_max = capacity_max try: s.add(facility) s.commit() except Exception as e: logger.error(e) logger.debug("MMS Dudetail: {} facility record with id {}".format( "Created" if created else "Updated", duid)) logger.info( "MMS Dudetail: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 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 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), ))