Example #1
0
 def execute(self, sql, args=None):
     logger.info(self.mogrify(sql, args))
     try:
         psycopg2.extensions.cursor.execute(self, sql, args)
     except Exception, exc:
         logger.error("%s: %s" % (exc.__class__.__name__, exc))
         raise
Example #2
0
    def __exit__(self, exception_type, exception_value, traceback):
        if exception_value:
            logger.error("Errors were encountered, rolling back session")
            self._session.rollback()
        else:
            self._session.commit()

        self._session.close()

        logger.info("Closed db session")
Example #3
0
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
Example #4
0
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;")
Example #5
0
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;")
Example #6
0
    attrs["__tablename__"] = table_name

    table_class = type(str(table_name), (db.Model, ), attrs)

    #need GET_MANY post processor as flask jsonify doesn't handle list serialization
    manager.create_api(table_class,
                       methods=['GET'],
                       results_per_page=None,
                       postprocessors={
                           'GET_MANY': [post_get_many],
                           'GET_SINGLE': [post_get_single]
                       })

    #manager.create_api(table_class, methods=['GET'], results_per_page=None)
    logger.info("Activted endpoint: api/%s" % table_name)


@app.route("/")
def index():
    return send_from_directory("static", "index.html")


@app.route("/layers/")
@crossdomain(origin="*", methods=["GET", "OPTIONS"])
def layers():
    return flask.json.dumps({"layers": table_names}, cls=GeoJSONEncoder)


@app.route("/api/rastertile/<datagranule_id>")
@crossdomain(origin="*", methods=["GET", "OPTIONS"])
Example #7
0
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)
Example #8
0
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
Example #9
0
def insert_raster(sqa,
                  config,
                  datagranule,
                  ras,
                  srid_in,
                  srid_out,
                  block_size,
                  mask=None):
    raster_format = sqa.one(DataFormat, filterr={"name": "RASTER"})
    vector_format = sqa.one(DataFormat, filterr={"name": "VECTOR"})

    if (srid_in != srid_out) and datagranule.dataformat_id == raster_format.id:
        raise Exception(
            "Cannot reproject raster tiles, ensure srid_in and srid_out are same"
        )

    if datagranule.dataformat_id == raster_format.id:
        tile_count = 0
        mask_wkt = None

        if not mask is None:
            mask_wkt = sqa.AsEWKT(mask.geom, srid_in)

        for tile in ras.tile_generator(block_size=block_size):
            tile_wkt = tile["raster_wkt"]
            extent = tile["extent_wkt"]
            rt = RasterTile(datagranule=datagranule, rast=tile_wkt)
            rt.extent = extent

            if mask_wkt:
                if sqa.Intersects(extent, mask_wkt):
                    sqa.insert(rt)
                    tile_count += 1
            else:
                sqa.insert(rt)
                tile_count += 1

            if tile_count and tile_count % 100 == 0:
                logger.info("Inserted %d tiles" % tile_count)

        logger.info("Inserted %d tiles" % tile_count)
        return tile_count

    elif datagranule.dataformat_id == vector_format.id:
        batch_size = 1000
        ogrds = ogr.Open(config.ogr_connection_string())
        srs_in = get_srs(srid_in)

        if srid_in != srid_out:
            srs_out = get_srs(srid_out)
            transform = osr.CoordinateTransformation(srs_in, srs_out)
            layer = ogrds.CreateLayer(str(datagranule.table_name), srs_out,
                                      ogr.wkbPolygon, ['OVERWRITE=YES'])
        else:
            transform = None
            layer = ogrds.CreateLayer(str(datagranule.table_name), srs_in,
                                      ogr.wkbPolygon, ['OVERWRITE=YES'])

        logger.info("Created layer %s" % str(datagranule.table_name))
        value_field = ogr.FieldDefn(str(datagranule.variable.name),
                                    ogr.OFTReal)
        layer.CreateField(value_field)

        layerDefn = layer.GetLayerDefn()
        layer.StartTransaction()
        count = 0

        #create mask to filter ingest geometries
        mask_geom = None
        if not mask is None:
            mask_wkt = sqa.AsWKT(mask.geom, srid_in)
            mask_geom = ogr.CreateGeometryFromWkt(mask_wkt)

        for items in ras.vector_generator(block_size=block_size):
            for item in items:
                poly, value = item[0], item[1]
                if not mask_geom is None:
                    if not mask_geom.Intersects(poly):
                        continue

                feature = ogr.Feature(layerDefn)
                if transform:
                    try:
                        poly.Transform(transform)
                        feature.SetGeometry(poly)
                    except:
                        logger.error("Reprojection failed")
                else:
                    feature.SetGeometry(poly)

                feature.SetField(str(datagranule.variable.name), value)
                layer.CreateFeature(feature)
                count += 1

                if count % batch_size == 0:
                    logger.info("inserted features - %d" % count)
                    layer.CommitTransaction()

        if count % batch_size != 0:
            logger.info("inserted features - %d" % count)
            layer.CommitTransaction()

        return count

    else:
        raise Exception("Format should be one of raster or vector")
Example #10
0
def insert_raster(sqa, config, datagranule, ras, srid_in, srid_out, block_size, mask=None):
    raster_format = sqa.one(DataFormat, filterr={"name": "RASTER"})
    vector_format = sqa.one(DataFormat, filterr={"name": "VECTOR"})

    if (srid_in != srid_out) and datagranule.dataformat_id == raster_format.id:
        raise Exception("Cannot reproject raster tiles, ensure srid_in and srid_out are same")

    if datagranule.dataformat_id == raster_format.id:
        tile_count = 0
        mask_wkt = None

        if not mask is None:
            mask_wkt = sqa.AsEWKT(mask.geom, srid_in)

        for tile in ras.tile_generator(block_size=block_size):
            tile_wkt = tile["raster_wkt"]
            extent = tile["extent_wkt"]
            rt = RasterTile(datagranule=datagranule, rast=tile_wkt)
            rt.extent = extent

            if mask_wkt:
                if sqa.Intersects(extent, mask_wkt):
                    sqa.insert(rt)
                    tile_count += 1
            else:
                sqa.insert(rt)
                tile_count += 1

            if tile_count and tile_count % 100 == 0:
                logger.info("Inserted %d tiles" % tile_count)

        logger.info("Inserted %d tiles" % tile_count)
        return tile_count

    elif datagranule.dataformat_id == vector_format.id:
        batch_size = 1000
        ogrds = ogr.Open(config.ogr_connection_string())
        srs_in = get_srs(srid_in)

        if srid_in != srid_out:
            srs_out = get_srs(srid_out)
            transform = osr.CoordinateTransformation(srs_in, srs_out)
            layer = ogrds.CreateLayer(str(datagranule.table_name), srs_out, ogr.wkbPolygon, ['OVERWRITE=YES'])
        else:
            transform = None
            layer = ogrds.CreateLayer(str(datagranule.table_name), srs_in, ogr.wkbPolygon, ['OVERWRITE=YES'])

        logger.info("Created layer %s" % str(datagranule.table_name))
        value_field = ogr.FieldDefn(str(datagranule.variable.name), ogr.OFTReal)
        layer.CreateField(value_field)

        layerDefn = layer.GetLayerDefn()
        layer.StartTransaction()
        count = 0

        #create mask to filter ingest geometries
        mask_geom = None
        if not mask is None:
            mask_wkt = sqa.AsWKT(mask.geom, srid_in)
            mask_geom = ogr.CreateGeometryFromWkt(mask_wkt)

        for items in ras.vector_generator(block_size=block_size):
            for item in items:
                poly, value = item[0], item[1]
                if not mask_geom is None:
                    if not mask_geom.Intersects(poly):
                        continue

                feature = ogr.Feature(layerDefn)
                if transform:
                    try:
                        poly.Transform(transform)
                        feature.SetGeometry(poly)
                    except:
                        logger.error("Reprojection failed")
                else:
                    feature.SetGeometry(poly)

                feature.SetField(str(datagranule.variable.name), value)
                layer.CreateFeature(feature)
                count += 1

                if count % batch_size == 0:
                    logger.info("inserted features - %d" % count)
                    layer.CommitTransaction()

        if count % batch_size != 0:
            logger.info("inserted features - %d" % count)
            layer.CommitTransaction()

        return count

    else:
        raise Exception("Format should be one of raster or vector")
Example #11
0
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)
Example #12
0
        if 'type' in col:
            if col["name"] in pkey["constrained_columns"]:
                attrs[col["name"]] = db.Column(col["type"], primary_key=True)
            else:
                attrs[col["name"]] = db.Column(col["type"])

    attrs["__tablename__"] = table_name

    table_class = type(str(table_name), (db.Model,), attrs)

    #need GET_MANY post processor as flask jsonify doesn't handle list serialization
    manager.create_api(table_class, methods=['GET'], results_per_page=None,
                       postprocessors={'GET_MANY': [post_get_many], 'GET_SINGLE': [post_get_single]})

    #manager.create_api(table_class, methods=['GET'], results_per_page=None)
    logger.info("Activted endpoint: api/%s" % table_name)


@app.route("/")
def index():
    return send_from_directory("static", "index.html")


@app.route("/layers/")
@crossdomain(origin="*", methods=["GET", "OPTIONS"])
def layers():
    return flask.json.dumps({"layers": table_names}, cls=GeoJSONEncoder)


@app.route("/api/rastertile/<datagranule_id>")
@crossdomain(origin="*", methods=["GET", "OPTIONS"])