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"])
storm_poly geometry null,
center_lat float not null,
center_lon float not null,
iarea float null,
type int not null,
constraint rap_roi_geom_pkey primary key (id)
);
""")
#grab a single rap granule
sql = """
select dg.id from datagranule dg
join variable on variable.id=dg.variable_id
where variable.name='CAPE0' limit 1
"""
rows = pgdb_helper.query(sql)
rap_granule_id = rows[0][0]
#select roi geoms for each rap grid cell
if mask_name:
sql = """
SELECT
ST_AsEWKT(ST_Transform(ST_Buffer(a.geom, {radius}), {srid})) geom,
ST_AsEWKT(ST_Transform(a.geom, {srid})) center,
ST_Y(ST_Transform(a.geom, 4326)) lat,
ST_X(ST_Transform(a.geom, 4326)) lon
FROM (select st_centroid((st_pixelaspolygons(foo.rast, 1, False)).geom) as geom from
(select st_union(rast) rast from rastertile where datagranule_id={granule_id}
and st_intersects(rast, (select st_transform(geom, st_srid(rast)) from mask where name='{mask_name}' limit 1))) as foo) a
""" . format(radius=radius * 1000, granule_id=rap_granule_id, mask_name=mask_name, srid=SRID_RAP)
else:
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("""
select st_astext(st_transform(st_buffer(st_transform(geom, 102003), %f), 4326)) from mask
where name='%s'
""" % (-1 * shrink_dist_km * 1000, config.mask_name))
mask_wkt = mask_rows[0][0]
def get_non_intersecting(roi_rows):
if len(roi_rows) < 1:
return []
roi_rows_non_intersecting = [roi_rows[0]]
for row1 in roi_rows[1:]:
intersecting = False
for row2 in roi_rows_non_intersecting:
g1 = ogr.CreateGeometryFromWkt(row1[0])
g2 = ogr.CreateGeometryFromWkt(row2[0])
if g2.Intersects(g1):
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])
save_raster(lats, lons, dtime, dtime + tstep)
else:
logger.info("No CI Events for %s" % dtime)
dtime = dtime + tstep
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
WHERE variable.name <> 'MREFL'
"""
dg_rows = pgdb_helper.query(sql)
for dg_row in dg_rows:
dg_id = dg_row[0]
dg_srid = dg_row[1]
sql = """
INSERT INTO rastertile_reproj (rast, datagranule_id) (
SELECT st_tile(st_transform(st_union(rast), %d), 1, 100, 100, TRUE, -999), %d
FROM rastertile WHERE datagranule_id=%d)
""" % (SRID_RAP, dg_id, dg_id)
#
# sql = """
# INSERT INTO rastertile_reproj (rast, datagranule_id) (
# SELECT st_transform(rast, %d), %d
# FROM rastertile WHERE datagranule_id=%d