def latlon2xy(self, lat, lon, srid_to):
db = PGDbHelper(self.config.pgsql_conn_str())
sql = """
SELECT ST_AsGeoJSON(ST_Transform(ST_GeomFromText('POINT(%f %f)', %d), %d))
""" % (lon, lat, SRID_WGS84, srid_to)
results = db.query(sql)
str_json = results[0][0]
obj = json.loads(str_json)
return tuple(obj["coordinates"])
import gzip
import os
from datetime import datetime, timedelta
from ci.models.gdal_raster import GDALRaster
from ci.util.nc_file_helper import nc_get_1d_vars_as_list
from ci.ingest import config, base_ingestor, proj_helper
from ci.config import get_instance
from ci.db.pgdbhelper import PGDbHelper
from ci.util.common import TimeMe
block_sizes = []
for i in range(1, 50):
block_sizes.append((i * 20, i * 20))
conf = get_instance()
pgdb_helper = PGDbHelper(conn_str=conf.pgsql_conn_str(), echo=conf.logsql)
def cb(x):
if x < 35:
return 0
else:
return 1
def process_mrms_file(mrms_file):
provider_name = "MRMS"
variable_name = "REFL"
ext_parts = os.path.splitext(mrms_file)
from datetime import timedelta, datetime
from ci.db.pgdbhelper import PGDbHelper
from ci.config import get_instance
from ci.util.proj_helper import ProjHelper
from ci.models.spatial_reference import SRID_RAP
__author__ = 'ujjwal'
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,
def create_gist_index(self, table_name, index_name, column_name="rast"):
pgdb_helper = PGDbHelper(conn_str=self.config.pgsql_conn_str())
pgdb_helper.create_gist_index(table_name, index_name, column_name)
for gtopo_file in files:
granule_name = "GTOPO30Elev"
srid = 4326
band_num = 1
block_size = (50, 50)
dtime = datetime(year=1979, month=1, day=1, hour=0, minute=0, second=0)
level = 0
ras = GDALRaster(gtopo_file, srid)
ras.nodata_value = -9999
bbox = proj_helper.get_bbox(srid)
base_ingestor.ingest(ras=ras,
provider_name=provider_name,
variable_name=variable_name,
granule_name=granule_name,
table_name=granule_name,
srid=srid,
level=level,
block_size=block_size,
dynamic=False,
start_time=dtime,
end_time=datetime.max,
subset_bbox=bbox,
overwrite=True)
#create slope and aspect rasters
pgdb_helper = PGDbHelper(conn_str=config.pgsql_conn_str())
pgdb_helper.insert_slope_and_aspect_rasters(granule_name,
overwrite=True)
return response
return '', 204
if __name__ == '__main__':
config = get_instance()
#config_file = os.path.join("/home/ujjwal/DPR_SM/python/dpr_sm/ingest/lis_config.yml")
#config = get_instance(config_file=config_file)
engine = create_engine(config.sqa_connection_string())
mapper = Mapper(engine=engine)
mapper.map_tables()
pgdb_access = PGDbHelper(config.pgsql_conn_str(), echo=config.logsql)
pgdb_access.ensure_gist_index('rastertile', 'rastertile_rast_gist_idx',
'rast')
pgdb_access.ensure_datagranule_id_index("rastertile",
"rastertile_datagranule_id_idx",
"datagranule_id")
#check and create tile cache dir if necessary
tiles_dir = os.path.join(os.path.dirname(__file__), "tiles")
if not os.path.exists(tiles_dir):
os.mkdir(tiles_dir)
app.run(port=5001)
import os
from sqlalchemy import create_engine
from ci.db.pgdbhelper import PGDbHelper
from ci.db.sqa.mapper import Mapper
from ci.config import get_instance
__author__ = 'ujjwal'
config = get_instance()
pgdb_helper = PGDbHelper(conn_str=config.pgsql_conn_str(), echo=True)
engine = create_engine(config.sqa_connection_string())
mapper = Mapper(engine=engine)
mapper.map_tables()
missing_data = -999
def generate_output_file(fname):
if os.path.exists(fname):
os.remove(fname)
with open(fname, 'w') as of:
sql = """
select distinct(var.name)
from forecast_roi_stats rs
left join variable var on var.id=rs.variable_id
order by var.name asc
"""
rows = pgdb_helper.query(sql)
var_names = []
from sqlalchemy import create_engine
from ci.db.sqa.access import SqaAccess
from ci.db.pgdbhelper import PGDbHelper
from ci.db.sqa.mapper import Mapper
from ci.db.sqa.models import DataGranule, Variable, Provider
from ci.ingest import config, logger
__author__ = 'ujjwal'
engine = create_engine(config.sqa_connection_string())
pgdb_helper = PGDbHelper(conn_str=config.pgsql_conn_str())
mapper = Mapper(engine=engine)
mapper.map_tables()
def get_granules(var_name, start_date, end_date):
sql = """
select datagranule.id, datagranule.starttime, datagranule.endtime from datagranule
join provider on provider.id = datagranule.provider_id
join variable on variable.id = datagranule.variable_id
where provider.name like 'RAP' and variable.name like '%s'
and (('%s', '%s') overlaps (datagranule.starttime, datagranule.endtime))
order by datagranule.starttime asc
""" % (var_name, start_date, end_date)
rows = pgdb_helper.query(sql)
return rows
if __name__ == '__main__':
from multiprocessing import Pool
from ci.db.pgdbhelper import PGDbHelper
from ci.config import get_instance
from ci.util.common import TimeMe
__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,
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)
block_size=block_size,
dynamic=False,
start_time=t_start,
end_time=t_end,
subset_bbox=bbox,
overwrite=True)
logger.info("Inserted %s" % granule_name)
start_time = datetime(year=2014, month=7, day=23, hour=13, minute=0, second=0)
end_time = datetime(year=2014, month=7, day=23, hour=18, minute=0, second=0)
dtime = start_time
tstep = timedelta(hours=1)
pgdb_helper = PGDbHelper(conn_str=config.pgsql_conn_str(), echo=True)
while dtime < end_time:
sql = """
select id, track_id, starttime, endtime, center_lat, center_lon, type from ci_events
where starttime >= %s and starttime < %s order by starttime
"""
values = (dtime, dtime + tstep)
rows = pgdb_helper.query(sql, values)
if len(rows):
lats = []
lons = []
for row in rows:
lats.append(row[4])
lons.append(row[5])
import ogr
from datetime import timedelta
from multiprocessing import Pool
from ci.db.pgdbhelper import PGDbHelper
from ci.db.adminpgdbhelper import AdminPGDbHelper
from ci.models.spatial_reference import SRID_RAP
from ci.models.spatial_reference import SRID_ALBERS
from ci.config import get_instance
from ci.util.proj_helper import ProjHelper
from ci.util.common import TimeMe
__author__ = 'ujjwal'
#get accessor to the old db
config = get_instance()
pgdb_helper = PGDbHelper(conn_str=config.pgsql_conn_str(), echo=config.logsql)
#pgdb_helper = PGDbHelper(conn_str=config.pgsql_conn_str(), echo=config.logsql)
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,
from ci.models.spatial_reference import SRID_RAP
from ci.db.pgdbhelper import PGDbHelper
from ci.config import get_instance
__author__ = 'ujjwal'
config = get_instance()
logger = config.logger
pgdb_helper = PGDbHelper(conn_str=config.pgsql_conn_str(), echo=config.logsql)
sql = """
DROP TABLE IF EXISTS rastertile_reproj;
CREATE TABLE rastertile_reproj
(
id serial NOT NULL,
rast raster NOT NULL,
datagranule_id integer NOT NULL,
CONSTRAINT rastertile_reproj_pkey PRIMARY KEY (id),
CONSTRAINT rastertile_reproj_datagranule_id_fkey FOREIGN KEY (datagranule_id)
REFERENCES datagranule (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
"""
pgdb_helper.submit(sql)
sql = """
SELECT datagranule.id, datagranule.srid FROM datagranule
JOIN variable on variable.id=datagranule.variable_id
from ci.config import get_instance
from ci.db.pgdbhelper import PGDbHelper
from ci.db.sqa.access import SqaAccess
from ci.db.sqa.mapper import Mapper
from ci.db.sqa.models import DataGranule, Variable, Provider, RasterTile
from sqlalchemy import create_engine
config = get_instance()
logger = config.logger
engine = create_engine(config.sqa_connection_string())
mapper = Mapper(engine=engine)
mapper.map_tables()
pgdb_helper = PGDbHelper(conn_str=config.pgsql_conn_str())
with SqaAccess(engine=engine) as sqa:
refl_reclass_vars = sqa.find(Variable, filterr={'name': 'REFL_reclass'})
refl_var = sqa.find(Variable, filterr={'name': 'REFL'})[0]
if len(refl_reclass_vars) == 0:
refl_reclass_var = Variable(name='REFL_reclass', unit=None, description="Reclassified MRMS REFL")
sqa.insertOne(refl_reclass_var)
else:
refl_reclass_var = refl_reclass_vars[0]
mrms_provider = sqa.find(Provider, filterr={'name': 'MRMS'})[0]
mrms_granules = sqa.find(DataGranule, filterr={'provider_id': mrms_provider.id, 'variable_id': refl_var.id})
for g in mrms_granules: