forecast_time = datetime(year=2014, month=8, day=7, hour=20, minute=30, second=0)
forecast_times = [forecast_time]
#get accessor to the old db
config = get_instance()
pgdb_helper = PGDbHelper(conn_str=config.pgsql_conn_str(), echo=config.logsql)
proj_helper = ProjHelper(config=config)
logger = config.logger
radius = config.ci_roi_radius
mask_name = config.mask_name
#create table
pgdb_helper.submit("drop table if exists forecast_roi_geoms;")
pgdb_helper.submit(
"""
create table forecast_roi_geoms
(
id serial not null,
roi_name text not null,
rap_granule_id int not null,
starttime timestamp without time zone NOT NULL,
endtime timestamp without time zone NOT NULL,
geom geometry not null,
center geometry not null,
storm_poly geometry null,
center_lat float not null,
center_lon float not null,
iarea float null,
__author__ = 'ujjwal'
#get accessor to the old db
config = get_instance()
logger = config.logger
start_date = config.start_date
end_date = config.end_date
pgdb_helper = PGDbHelper(conn_str=config.pgsql_conn_str(), echo=config.logsql)
RASTERTILE_TABLE = "rastertile_reproj"
cache = {}
#create table for roi stats
pgdb_helper.submit("""
drop table if exists forecast_roi_stats;
""")
pgdb_helper.submit("""
create table forecast_roi_stats
(
id serial not null,
roi_id int not null,
roi_name text not null,
count int not null,
sum float not null,
mean float not null,
stddev float not null,
min float not null,
max float not null,
variable_id int not null
)
def write_to_pg_vector(self,
provider_name,
variable_name,
granule_name,
table_name,
srid,
level,
start_time,
end_time=None,
block_size=(100, 100),
overwrite=False,
threshold=None,
mask_name=None):
with SqaAccess(engine=self.engine) as orm_access:
provider = orm_access.find('provider', {'name': provider_name})[0]
variable = orm_access.find('variable', {'name': variable_name})[0]
extent = self.raster.wkt_extent()
if end_time is None:
end_time = datetime.max
granule = DataGranule(provider=provider,
variable=variable,
starttime=start_time,
endtime=end_time,
extent=extent,
level=level,
name=granule_name,
srid=srid,
table_name=table_name,
file_name=self.raster.dsname)
if overwrite:
check_granule_result = orm_access.find(DataGranule,
filterr={
'provider_id':
provider.id,
'variable_id':
variable.id,
'level':
level,
'starttime':
start_time,
'endtime':
end_time,
'file_name':
self.raster.dsname
})
if len(check_granule_result):
check_granule = check_granule_result[0]
self.config.logger.warn('found existing datagranule %d' %
check_granule.id)
sql = "drop table if exists %s;" % check_granule.table_name
orm_access.session.execute(sql)
#orm_access.delete(DataGranule, id=check_granule.id)
orm_access.session.delete(check_granule)
orm_access.session.commit()
orm_access.insertOne(granule)
pgdb_helper = PGDbHelper(conn_str=self.config.pgsql_conn_str(),
echo=self.config.logsql)
sql = """
create table {table_name}
(
id serial not null,
datagranule_id integer not null,
geom geometry not null,
value double precision,
CONSTRAINT {table_name}_pkey PRIMARY KEY (id)
)
""".format(table_name=table_name)
#orm_access.session.execute(sql)
pgdb_helper.submit(sql)
values = []
for shapes in self.raster.vector_generator(block_size=block_size):
for shape in shapes:
values.append(
(granule.id, shape[0].ExportToWkt(), shape[1]))
if len(values) > 1000:
sql = """
insert into {table_name} (datagranule_id, geom, value) values (%s, st_geomfromtext(%s, 4326), %s)
""".format(table_name=table_name)
#orm_access.session.execute(sql, values)
pgdb_helper.insertMany(sql, values)
values = []
if len(values) > 0:
sql = """
insert into {table_name} (datagranule_id, geom, value) values (%s, st_geomfromtext(%s, 4326), %s)
""".format(table_name=table_name)
pgdb_helper.insertMany(sql, values)
sql = """
create index {table_name}_geom_idx on {table_name} using GIST(geom)
""".format(table_name=table_name)
pgdb_helper.submit(sql)
u_granule = ugrd_granules[i]
v_granule = vgrd_granules[i]
logger.info("Ingesting wind convergence for %s " % u_granule.name)
if u_granule.starttime <> v_granule.starttime:
raise Exception("u and v time mismatch %s, %s" % (u_granule.startime, v_granule.startime))
#create temporary table for calculations
sql = """
drop table if exists tmpraster;
create table tmpraster (
id int,
rast raster
);
"""
pgdb_helper.submit(sql)
#insert du / dx
sql = """
INSERT INTO tmpraster (id, rast) VALUES (
%d, (SELECT ST_MapAlgebraFctNgb(ST_Union(rast), 1, '32BF', 1, 1,
'st_xderivative4ma(float[][],text,text[])'::regprocedure, 'ignore', (@ ST_ScaleX(ST_Union(rast)))::text)
FROM rastertile WHERE datagranule_id = %d))
""" % (u_granule.id, u_granule.id)
pgdb_helper.submit(sql)
#insert dv / dy
sql = """
INSERT INTO tmpraster (id, rast) VALUES (
%d, (SELECT ST_MapAlgebraFctNgb(ST_Union(rast), 1, '32BF', 1, 1,
'st_yderivative4ma(float[][],text,text[])'::regprocedure, 'ignore', (@ ST_ScaleY(ST_Union(rast)))::text)
proj_helper = ProjHelper(config=config)
logger = config.logger
#create table for roi geoms
pgdb_helper.submit("""
drop table if exists roi_geoms_reproj;
drop table if exists roi_geoms;
create table roi_geoms
(
id serial not null,
roi_name text not null,
mrms_granule_id int not null,
starttime timestamp without time zone NOT NULL,
endtime timestamp without time zone NOT NULL,
geom geometry not null,
center geometry not null,
storm_poly geometry null,
center_lat float not null,
center_lon float not null,
iarea float null,
type int not null,
constraint roi_geom_pkey primary key (id)
);
create index roi_geoms_mrms_gran_type_idx on roi_geoms(mrms_granule_id, starttime, endtime, type);
create index roi_geoms_geom_gist_idx on roi_geoms using gist(geom);
""")
mask_wkt = None
shrink_dist_km = 20
if config.mask_name:
mask_rows = pgdb_helper.query("""