コード例 #1
0
def insert_curw_obs_runs():

    hash_mapping = [['old_hash_id', 'new_hash_id']]

    try:
        # pool = get_Pool(host=CURW_OBS_HOST, port=CURW_OBS_PORT, user=CURW_OBS_USERNAME, password=CURW_OBS_PASSWORD,
        #         db=CURW_OBS_DATABASE)

        pool = get_Pool(host=HOST,
                        port=PORT,
                        user=USERNAME,
                        password=PASSWORD,
                        db=DATABASE)

        curw_old_obs_entries = read_csv('all_curw_obs.csv')

        for old_index in range(len(curw_old_obs_entries)):

            meta_data = {}

            old_hash_id = curw_old_obs_entries[old_index][0]
            run_name = curw_old_obs_entries[old_index][1]
            station_name = curw_old_obs_entries[old_index][4]
            latitude = curw_old_obs_entries[old_index][5]
            longitude = curw_old_obs_entries[old_index][6]
            description = curw_old_obs_entries[old_index][7]
            variable = curw_old_obs_entries[old_index][8]
            unit = curw_old_obs_entries[old_index][9]
            unit_type = curw_old_obs_entries[old_index][10]

            meta_data['run_name'] = run_name

            meta_data['variable'] = variable
            meta_data['unit'] = unit
            meta_data['unit_type'] = unit_type

            meta_data['latitude'] = latitude
            meta_data['longitude'] = longitude

            if variable == "WaterLevel":
                station_type = StationEnum.CUrW_WaterLevelGauge
            else:
                station_type = StationEnum.CUrW_WeatherStation

            meta_data['station_type'] = StationEnum.getTypeString(station_type)

            unit_id = get_unit_id(pool=pool,
                                  unit=unit,
                                  unit_type=UnitType.getType(unit_type))

            if unit_id is None:
                add_unit(pool=pool,
                         unit=unit,
                         unit_type=UnitType.getType(unit_type))
                unit_id = get_unit_id(pool=pool,
                                      unit=unit,
                                      unit_type=UnitType.getType(unit_type))

            variable_id = get_variable_id(pool=pool, variable=variable)

            if variable_id is None:
                add_variable(pool=pool, variable=variable)
                variable_id = get_variable_id(pool=pool, variable=variable)

            station_id = get_station_id(pool=pool,
                                        latitude=latitude,
                                        longitude=longitude,
                                        station_type=station_type)

            if station_id is None:
                add_station(pool=pool,
                            name=station_name,
                            latitude=latitude,
                            longitude=longitude,
                            station_type=station_type,
                            description=description)
                station_id = get_station_id(pool=pool,
                                            latitude=latitude,
                                            longitude=longitude,
                                            station_type=station_type)

            TS = Timeseries(pool=pool)

            tms_id = TS.get_timeseries_id_if_exists(meta_data=meta_data)

            meta_data['station_id'] = station_id
            meta_data['variable_id'] = variable_id
            meta_data['unit_id'] = unit_id

            if tms_id is None:
                tms_id = TS.generate_timeseries_id(meta_data=meta_data)
                meta_data['tms_id'] = tms_id
                TS.insert_run(run_meta=meta_data)

            hash_mapping.append([old_hash_id, tms_id])

        create_csv(file_name='curw_to_curw_obs_hash_id_mapping.csv',
                   data=hash_mapping)

    except Exception:
        traceback.print_exc()
        print(
            "Exception occurred while inserting run entries to curw_obs run table and making hash mapping"
        )
    finally:
        destroy_Pool(pool=pool)
コード例 #2
0
def generate_curw_obs_hash_id(pool,
                              variable,
                              unit,
                              unit_type,
                              latitude,
                              longitude,
                              station_type=None,
                              station_name=None,
                              description=None,
                              append_description=False,
                              start_date=None):
    """
    Generate corresponding curw_obs hash id for a given curw observational station
    :param pool: databse connection pool
    :param variable: str: e.g. "Precipitation"
    :param unit: str: e.g. "mm"
    :param unit_type: str: e.g. "Accumulative"
    :param latitude: float: e.g. 6.865576
    :param longitude: float: e.g. 79.958181
    :param station_type: str: enum:  'CUrW_WeatherStation' | 'CUrW_WaterLevelGauge' | 'CUrW_CrossSection' | 'Irrigation_Department'
    :param station_name: str: "Urumewella"
    :param description: str: "A&T Communication Box, Texas Standard Rain Gauge"
    :param append_description: bool:
    :param start_date: str: e.g."2019-07-01 00:00:00" ; the timestamp of the very first entry of the timeseries

    :return: new curw_obs hash id
    """

    try:

        lat = '%.6f' % float(latitude)
        lon = '%.6f' % float(longitude)
        meta_data = {
            'unit': unit,
            'unit_type': unit_type,
            'latitude': lat,
            'longitude': lon
        }

        if variable == "Waterlevel":
            variable = "WaterLevel"

        meta_data['variable'] = variable

        if station_type == IRRIGATION_DEPARTMENT:
            station_type = StationEnum.Irrigation_Department
        elif variable == CURW_WEATHER_STATION:
            station_type = StationEnum.CUrW_WeatherStation
        elif variable == CURW_WATER_LEVEL_STATION:
            station_type = StationEnum.CUrW_WaterLevelGauge
        elif variable == CURW_CROSS_SECTION:
            station_type = StationEnum.CUrW_CrossSection
        else:
            print(
                "STATION_TYPE should be either Irrigation_Department or CUrW_WeatherStation or"
                "CUrW_WaterLevelGauge or CUrW_CrossSection")
            exit(1)

        meta_data['station_type'] = StationEnum.getTypeString(station_type)

        unit_id = get_unit_id(pool=pool,
                              unit=unit,
                              unit_type=UnitType.getType(unit_type))

        if unit_id is None:
            add_unit(pool=pool,
                     unit=unit,
                     unit_type=UnitType.getType(unit_type))
            unit_id = get_unit_id(pool=pool,
                                  unit=unit,
                                  unit_type=UnitType.getType(unit_type))

        variable_id = get_variable_id(pool=pool, variable=variable)

        if variable_id is None:
            add_variable(pool=pool, variable=variable)
            variable_id = get_variable_id(pool=pool, variable=variable)

        station_id = get_station_id(pool=pool,
                                    latitude=lat,
                                    longitude=lon,
                                    station_type=station_type)

        if station_id is None:
            add_station(pool=pool,
                        name=station_name,
                        latitude=lat,
                        longitude=lon,
                        station_type=station_type)
            station_id = get_station_id(pool=pool,
                                        latitude=lat,
                                        longitude=lon,
                                        station_type=station_type)
            if description:
                update_description(pool=pool,
                                   id_=station_id,
                                   description=description,
                                   append=False)

        elif append_description:
            if description:
                update_description(pool=pool,
                                   id_=station_id,
                                   description=description,
                                   append=True)

        TS = Timeseries(pool=pool)

        tms_id = TS.get_timeseries_id_if_exists(meta_data=meta_data)

        meta_data['station_id'] = station_id
        meta_data['variable_id'] = variable_id
        meta_data['unit_id'] = unit_id

        if tms_id is None:
            tms_id = TS.generate_timeseries_id(meta_data=meta_data)
            meta_data['tms_id'] = tms_id
            TS.insert_run(run_meta=meta_data)
            if start_date:
                TS.update_start_date(id_=tms_id, start_date=start_date)

        return tms_id

    except Exception:
        traceback.print_exc()
        print(
            "Exception occurred while inserting run entries to curw_obs run table and making hash mapping"
        )
コード例 #3
0
def generate_curw_obs_hash_id(pool,
                              variable,
                              unit,
                              unit_type,
                              latitude,
                              longitude,
                              run_name,
                              station_type=None,
                              station_name=None,
                              description=None,
                              append_description=True,
                              update_run_name=True,
                              start_date=None):
    """
    Generate corresponding curw_obs hash id for a given curw observational station
    :param pool: databse connection pool
    :param variable: str: e.g. "Precipitation"
    :param unit: str: e.g. "mm"
    :param unit_type: str: e.g. "Accumulative"
    :param latitude: float: e.g. 6.865576
    :param longitude: float: e.g. 79.958181
    :param run_name: str: e.g "A&T Labs"
    :param station_type: str: enum:  'CUrW_WeatherStation' | 'CUrW_WaterLevelGauge'
    :param station_name: str: "Urumewella"
    :param description: str: "A&T Communication Box, Texas Standard Rain Gauge"
    :param append_description: bool:
    :param update_run_name: bool:
    :param start_date: str: e.g."2019-07-01 00:00:00" ; the timestamp of the very first entry of the timeseries

    :return: new curw_obs hash id
    """
    if run_name not in ('A&T Labs', 'Leecom', 'CUrW IoT'):
        print(
            "This function is dedicated for generating curw_obs hash ids only for 'A&T Labs', 'Leecom', 'CUrW IoT' "
            "weather stations")
        exit(1)

    try:

        meta_data = {
            'run_name': run_name,
            'variable': variable,
            'unit': unit,
            'unit_type': unit_type,
            'latitude': latitude,
            'longitude': longitude
        }

        # run_name = run_name
        # station_name = station_name
        # latitude = latitude
        # longitude = longitude
        # description = description
        # variable = variable
        # unit = unit
        # unit_type = unit_type

        if variable == "Waterlevel":
            variable = "WaterLevel"

        if station_type and station_type in (CURW_WATER_LEVEL_STATION,
                                             CURW_WEATHER_STATION):
            station_type = StationEnum.getType(station_type)
        else:
            if variable == "WaterLevel":
                station_type = StationEnum.CUrW_WaterLevelGauge
            else:
                station_type = StationEnum.CUrW_WeatherStation

        meta_data['station_type'] = StationEnum.getTypeString(station_type)

        unit_id = get_unit_id(pool=pool,
                              unit=unit,
                              unit_type=UnitType.getType(unit_type))

        if unit_id is None:
            add_unit(pool=pool,
                     unit=unit,
                     unit_type=UnitType.getType(unit_type))
            unit_id = get_unit_id(pool=pool,
                                  unit=unit,
                                  unit_type=UnitType.getType(unit_type))

        variable_id = get_variable_id(pool=pool, variable=variable)

        if variable_id is None:
            add_variable(pool=pool, variable=variable)
            variable_id = get_variable_id(pool=pool, variable=variable)

        station_id = get_station_id(pool=pool,
                                    latitude=latitude,
                                    longitude=longitude,
                                    station_type=station_type)

        if station_id is None:
            add_station(pool=pool,
                        name=station_name,
                        latitude=latitude,
                        longitude=longitude,
                        station_type=station_type)
            station_id = get_station_id(pool=pool,
                                        latitude=latitude,
                                        longitude=longitude,
                                        station_type=station_type)
            if description:
                update_description(pool=pool,
                                   id_=station_id,
                                   description=description,
                                   append=False)

        elif append_description:
            if description:
                update_description(pool=pool,
                                   id_=station_id,
                                   description=description,
                                   append=True)

        TS = Timeseries(pool=pool)

        tms_id = TS.get_timeseries_id_if_exists(meta_data=meta_data)

        meta_data['station_id'] = station_id
        meta_data['variable_id'] = variable_id
        meta_data['unit_id'] = unit_id

        if tms_id is None:
            tms_id = TS.generate_timeseries_id(meta_data=meta_data)
            meta_data['tms_id'] = tms_id
            TS.insert_run(run_meta=meta_data)
            if start_date:
                TS.update_start_date(id_=tms_id, start_date=start_date)

        if update_run_name:
            TS.update_run_name(id_=tms_id, run_name=run_name)

        return tms_id

    except Exception:
        traceback.print_exc()
        print(
            "Exception occurred while inserting run entries to curw_obs run table and making hash mapping"
        )