def insert_refdata(config): ci_db_helper = PGDbHelper(config.pgsql_conn_str()) logger.info("Insert special coordinate systems %s" % config.dbname) for spref in SPREFS: ci_db_helper.execute(spref.sql_insert_statement) #insert custom functions for calculating du/dx, dv/dx logger.info("Inserting special plp/sql procedures %s" % config.dbname) from namo_app.db.models import init_mapper init_mapper(sqa_conn_str=config.sqa_connection_string()) logger.info("Inserting ref data on %s" % config.dbname) from namo_app.db.ref_data import PROVIDERS, VARIABLES, FORMATS with SqaAccess(conn_str=config.sqa_connection_string()) as sqa: logger.info("Inserting variables") sqa.insert(VARIABLES) logger.info("Inserting formats") sqa.insert(FORMATS) logger.info("Inserting providers") sqa.insert(PROVIDERS)
def upsert_datagranule(sqa, config, provider_name, variable_name, format_name, level, extent, srid, start_time, end_time, grib_file_name, overwrite_existing_datagranule=False, name_suffix=None): dataformat = sqa.one(DataFormat, filterr={"name": format_name}) provider = sqa.one(Provider, filterr={"name": provider_name}) variable = sqa.one(Variable, filterr={"name": variable_name}) existing_dg = sqa.one(DataGranule, filterr={ "provider_id": provider.id, "variable_id": variable.id, "dataformat_id": dataformat.id, "start_time": start_time, "end_time": end_time, "level": level, "extent": extent}) if existing_dg: datagranule = existing_dg if overwrite_existing_datagranule: raster_format = sqa.one(DataFormat, filterr={"name": "RASTER"}) vector_format = sqa.one(DataFormat, filterr={"name": "VECTOR"}) if datagranule.dataformat_id == raster_format.id: tiles = sqa.all(RasterTile, filterr={"datagranule_id": datagranule.id}) for tile in tiles: sqa.delete(tile) logger.info("Deleted existing tile %d" % tile.id) if datagranule.dataformat_id == vector_format.id: #should probably delete table using ogr pgdb_helper = PGDbHelper(conn_str=config.pgsql_conn_str()) pgdb_helper.execute("drop table if exists %s" % datagranule.table_name) logger.info("returning existing datagranule %s" % datagranule.name) else: granule_name = "%s_%s_%s_%d" % (provider_name, variable_name, start_time.strftime("%Y%m%d %H:%M"), level) table_name = "%s_%s_%s_%d" % (provider_name, variable_name, start_time.strftime("%Y%m%d%H%M"), level) if name_suffix: granule_name = "%s_%s" % (granule_name, name_suffix) table_name = "%s_%s" % (table_name, name_suffix) datagranule = DataGranule(provider_id=provider.id, variable_id=variable.id, start_time=start_time, end_time=end_time, level=level, extent=extent, name=granule_name, srid=srid, table_name=table_name, file_name=grib_file_name, dataformat_id=dataformat.id) if variable: datagranule.variable = variable sqa.insert(datagranule) return datagranule
def create_new_geodb(config): admin_db_helper = PGDbHelper(conn_str=config.pgsql_postgres_conn_str()) if check_db_exists(admin_db_helper, config.dbname): logger.info("Deleting existing db %s" % config.dbname) admin_db_helper.execute("DROP DATABASE %s" % config.dbname) if not check_db_exists(admin_db_helper, config.dbname): logger.info("Creating new db %s" % config.dbname) admin_db_helper.execute("CREATE DATABASE %s" % config.dbname) #connect to new db ci_db_helper = PGDbHelper(config.pgsql_conn_str()) logger.info("Enabling geodatabase on %s" % config.dbname) ci_db_helper.execute("CREATE EXTENSION POSTGIS;")
def upsert_datagranule(sqa, config, provider_name, variable_name, format_name, level, extent, srid, start_time, end_time, grib_file_name, overwrite_existing_datagranule=False, name_suffix=None): dataformat = sqa.one(DataFormat, filterr={"name": format_name}) provider = sqa.one(Provider, filterr={"name": provider_name}) variable = sqa.one(Variable, filterr={"name": variable_name}) existing_dg = sqa.one(DataGranule, filterr={ "provider_id": provider.id, "variable_id": variable.id, "dataformat_id": dataformat.id, "start_time": start_time, "end_time": end_time, "level": level, "extent": extent }) if existing_dg: datagranule = existing_dg if overwrite_existing_datagranule: raster_format = sqa.one(DataFormat, filterr={"name": "RASTER"}) vector_format = sqa.one(DataFormat, filterr={"name": "VECTOR"}) if datagranule.dataformat_id == raster_format.id: tiles = sqa.all(RasterTile, filterr={"datagranule_id": datagranule.id}) for tile in tiles: sqa.delete(tile) logger.info("Deleted existing tile %d" % tile.id) if datagranule.dataformat_id == vector_format.id: #should probably delete table using ogr pgdb_helper = PGDbHelper(conn_str=config.pgsql_conn_str()) pgdb_helper.execute("drop table if exists %s" % datagranule.table_name) logger.info("returning existing datagranule %s" % datagranule.name) else: granule_name = "%s_%s_%s_%d" % (provider_name, variable_name, start_time.strftime("%Y%m%d %H:%M"), level) table_name = "%s_%s_%s_%d" % (provider_name, variable_name, start_time.strftime("%Y%m%d%H%M"), level) if name_suffix: granule_name = "%s_%s" % (granule_name, name_suffix) table_name = "%s_%s" % (table_name, name_suffix) datagranule = DataGranule(provider_id=provider.id, variable_id=variable.id, start_time=start_time, end_time=end_time, level=level, extent=extent, name=granule_name, srid=srid, table_name=table_name, file_name=grib_file_name, dataformat_id=dataformat.id) if variable: datagranule.variable = variable sqa.insert(datagranule) return datagranule