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_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=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 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), ))