def move_to_db(ddf, table_name, ftype="fitacf", coords="mlt",
db_name=None,
config_filename="../../mysql_dbconfig_files/config.ini",
section="midlat"):
"""Writes POES Aur Bnd fitted coeffs to MySQL DB"""
from mysql.connector import MySQLConnection
from sqlalchemy import create_engine
import sys
sys.path.append("../../")
from mysql_dbutils.db_config import db_config
# construct a db name
if db_name is None:
db_name = "master_" + coords + "_" +ftype
# read db config info
config = db_config(config_filename=config_filename, section=section)
config_info = config.read_db_config()
# make a connection to master db
try:
engine = create_engine("mysql://" + config_info["user"] + ":" +\
config_info["password"] + "@" +\
config_info["host"] + "/" + db_name)
conn = engine.connect()
except Exception, e:
logging.error(e, exc_info=True)
def add_aur_bnd(sd_input_table,
sd_output_table,
poes_table,
coords="mlt",
ftype="fitacf",
nbatches=20,
db_name=None,
config_filename="../mysql_dbconfig_files/config.ini",
section="midlat"):
""" Calculates Equatorward Bnd of Aur MLAT & rel_MLAT and writes
them to new columns in sd_input_table
"""
from mysql.connector import MySQLConnection
from sqlalchemy import create_engine
import sys
sys.path.append("../")
from mysql_dbutils.db_config import db_config
if db_name is None:
db_name = "master_" + coords + "_" + ftype
# read db config info
config = db_config(config_filename=config_filename, section=section)
config_info = config.read_db_config()
# make a connection to ten-min median iscat db
try:
conn = MySQLConnection(database=db_name, **config_info)
cur = conn.cursor(buffered=True)
except Exception, e:
logging.error(e, exc_info=True)
def _create_dbconn(self,
config_filename="../mysql_dbconfig_files/config.ini",
section="midlat",
db_name=None):
""" creates a db connection
Parameters
----------
config_filename: str
name and path of the configuration file
section: str, default to "midlat"
section of database configuration
db_name : str, default to None
Name of the MySQL db to which iscat data has been written
"""
from mysql.connector import MySQLConnection
import sys
sys.path.append("../")
from mysql_dbutils.db_config import db_config
import logging
# read the db config info
config = db_config(config_filename=config_filename, section=section)
config_info = config.read_db_config()
# make db connection
if db_name is None:
db_name = self.rad + "_iscat_" + self.ftype
try:
conn = MySQLConnection(database=db_name, **config_info)
except Exception, e:
logging.error(e, exc_info=True)
def master_summary_by_radar_season(
input_table,
output_table,
coords="mlt",
db_name=None,
radar_pair=["cvw", "cve"],
config_filename="../mysql_dbconfig_files/config.ini",
section="midlat"):
""" stores the summay statistics of the data in master table into
a different table in the same database.
Time and rad informatin are all lost at this point.
NOTE: this function is only used to selecet for pairs of radars from the
six U.S. radars.
Parameters
----------
input_table : str
Name of a master table in master db
output_table : str
Name of a master_summary table in master db
coords : str
Coordinates in which the binning process took place.
Default to "mlt, can be "geo" as well.
radar_pair = list
a pair of three-character radar names
config_filename: str
name and path of the configuration file
section: str, default to "midlat"
section of database configuration
db_name : str, default to None
Name of the master db
Returns
-------
Nothing
"""
import datetime as dt
import numpy as np
from mysql.connector import MySQLConnection
import sys
sys.path.append("../")
from mysql_dbutils.db_config import db_config
import logging
# construct a db name
if db_name is None:
db_name = "master_" + coords + "_" + ftype
# read db config info
config = db_config(config_filename=config_filename, section=section)
config_info = config.read_db_config()
# make a connection to master db
try:
conn = MySQLConnection(database=db_name, **config_info)
cur = conn.cursor(buffered=True)
except Exception, e:
logging.error(e, exc_info=True)
def combine_ten_min_median(
rads,
ftype="fitacf",
coords="mlt",
config_filename="../mysql_dbconfig_files/config.ini",
section="midlat",
db_name=None):
""" combines ten-minute median filtered gridded data from radars
specified by rads argument into a single table.
Parameters
----------
rads : list
A list of three-letter radar codes
ftype : str
SuperDARN file type
coords : str
The Coordinate system. Default to "mlt. Can be "geo" as well.
config_filename: str
name and path of the configuration file
section: str, default to "midlat"
section of database configuration
db_name : str, default to None
Name of the MySQL db where ten-min median data is stored.
Returns
-------
Nothing
"""
import numpy as np
import datetime as dt
from mysql.connector import MySQLConnection
import sys
sys.path.append("../")
from mysql_dbutils.db_config import db_config
import logging
# consruct a db name and a table name
if db_name is None:
db_name = "ten_min_median_" + coords + "_" + ftype
output_table = "all_radars_" + ftype
# read the db config info
config = db_config(config_filename=config_filename, section=section)
config_info = config.read_db_config()
# make a connection to ten-min median db
try:
conn = MySQLConnection(database=db_name, **config_info)
cur = conn.cursor(buffered=True)
except Exception, e:
logging.error(e, exc_info=True)
def gmi_based_filter(rad,
output_table,
stm=None,
etm=None,
ftype="fitacf",
coords="mlt",
isKp_based=True,
kp_lim=[0.0, 2.3],
isSymH_based=False,
symh_min=-50,
config_filename="../mysql_dbconfig_files/config.ini",
section="midlat",
ten_min_median_dbname=None,
gmi_dbname=None,
gmi_db_location="../../data/sqlite3/"):
""" Selects the data based on geomagnetic indicies.
Parameters
----------
rads : str
Three-letter name of a radar whose data will be filtered.
output_table : str
Name of the table where filtered iscat data will be stored.
stm : datetime.datetime
The start time.
Default to None, in which case takes the earliest in db.
etm : datetime.datetime
The end time.
Default to None, in which case takes the latest time in db.
NOTE: if stm is None then etm should also be None, and vice versa.
ftype : str
SuperDARN LOS data file type
coords : str
Coordinate systems. valid inputs are "geo" or "mlt".
isKp_based : bool
If set to True, data will be filtered based on Kp.
kp_lim : list
The range of Kp. The range bondaries are inclusive.
isSymH_based : bool
If set to True, data will be filtered based on SymH.
symh_min : float
The lower limit for SYMH.
config_filename: str
name and path of the configuration file
section: str, default to "midlat"
section of database configuration
ten_min_median_dbname : str
Name of the db where ten min median iscat data is stored.
Default to None.
gmi_dbname : str
Name of the sqlite3 db where geomagnetic indices are stored.
Default to None.
gmi_db_location : str
The path to gmi_dbname sqlite db.
Default to None.
Returns
-------
Nothing
Note
----
SymH base filter has not been implemented yet
"""
import datetime as dt
import sqlite3
import os
import sys
sys.path.append("../../")
from mysql.connector import MySQLConnection
from mysql_dbutils.db_config import db_config
import logging
# construct db names and table names
if ten_min_median_dbname is None:
ten_min_median_dbname = "ten_min_median_" + coords + "_" + ftype
if gmi_dbname is None:
gmi_dbname = "gmi_imf.sqlite"
input_table = rad + "_" + ftype
# make db connection to geomag indicies data
conn_gmi = sqlite3.connect(database=gmi_db_location + gmi_dbname,
detect_types=sqlite3.PARSE_DECLTYPES)
cur_gmi = conn_gmi.cursor()
# make db connection for ten-min median filtered iscat data
# read the db config info
config = db_config(config_filename=config_filename, section=section)
config_info = config.read_db_config()
# make a connection
try:
conn_iscat = MySQLConnection(database=ten_min_median_dbname,
**config_info)
cur_iscat = conn_iscat.cursor(buffered=True)
except Exception, e:
logging.error(e, exc_info=True)
# create db name
if input_dbname is None:
input_dbname = "ten_min_median_" + coords + "_" + ftype
if output_dbname is None:
output_dbname = "master_" + coords + "_" + ftype
# create a db (if not exist) that combines all the data
try:
# create a db
db_tools.create_db(output_dbname)
except Exception, e:
logging.error(e, exc_info=True)
# read db config info
config = db_config(config_filename=config_filename, section=section)
config_info = config.read_db_config()
# make a connection to ten-min median iscat db
try:
conn_ten = MySQLConnection(database=input_dbname, **config_info)
cur_ten = conn_ten.cursor(buffered=True)
except Exception, e:
logging.error(e, exc_info=True)
# make a connection to master db
try:
conn = MySQLConnection(database=output_dbname, **config_info)
cur = conn.cursor(buffered=True)
except Exception, e:
logging.error(e, exc_info=True)
def read_from_db(rad,
stm,
etm,
ftype="fitacf",
config_filename="../mysql_dbconfig_files/config.ini",
section="midlat",
dbName=None,
plotrti=False):
""" reads the boxcar-filtered data from db instead of files.
NOTE: plotrti option does not work. working on it...
Parameters
----------
rad : str
Three-letter radar code
stm : datetime.datatime
Start time for reading data
etm : datetime.datatime
End time for reading data
ftype : str
file type
config_filename : str
name of the configuration file
section: str
section of database configuration
dbName : str, default to None
db name
plotrti : bool
NOTE: plotrti option does not work. working on it...
Returns
-------
dict
A list of dicts of the data. Each dict element stores data for a certain beam.
Data in each dict is stored in lists and separated in to tbands.
"""
import json
import datetime as dt
from mysql.connector import MySQLConnection
import sys
sys.path.append("../")
from mysql_dbutils.db_config import db_config
import logging
# construct a db name
if dbName is None:
dbName = rad + "_boxcar_" + ftype
# read the db config info
config = db_config(config_filename=config_filename, section=section)
config_info = config.read_db_config()
# make db connection
conn = MySQLConnection(database=dbName, **config_info)
cur = conn.cursor()
# get all the table names
command = "SELECT table_name FROM information_schema.tables "
command = command + "where TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='{db}'".\
format(db=dbName)
try:
cur.execute(command)
tbl_names = cur.fetchall()
tbl_names = [x[0] for x in tbl_names]
except Exception, e:
logging.error(e, exc_info=True)
def bin_to_grid(rad,
bmnum,
stm=None,
etm=None,
ftype="fitacf",
coords="mlt",
hemi="north",
config_filename="../mysql_dbconfig_files/config.ini",
section="midlat",
db_name=None):
""" bins the data into mlat-mlt-azm.
Parameters
----------
rad : str
Three-letter radar code
bmnum : int
Radar beam
stm : datetime.datetime
The start time.
Default to None, in which case takes the earliest in db.
etm : datetime.datetime
The end time.
Default to None, in which case takes the latest time in db.
NOTE: if stm is None then etm should also be None, and vice versa.
ftype : str
SuperDARN file type
coords : str
Coordinates in which the binning process takes place.
Default to "mlt. Can be "geo" as well.
hemi : str
Hemisphere. e.g., "north", "south"
NOTE: hemi not have been implemented yet.
config_filename: str
name and path of the configuration file
section: str, default to "midlat"
section of database configuration
db_name : str, default to None
Name of the MySQL db to which iscat data has been written
Note
----
0 xxx_gazmc directs towards magnetic (or geo) north. 180 gazmc directs towards south.
gazmc (gridded azimuthal center) spans from 5 - 355 degrees.
"""
import numpy as np
import datetime as dt
from mysql.connector import MySQLConnection
import sys
sys.path.append("../")
from mysql_dbutils.db_config import db_config
import logging
# create grid points
if hemi == "north":
grds = grids(lat_min=20, lat_max=90, dlat=1, half_dlat_offset=False)
# read the db config info
config = db_config(config_filename=config_filename, section=section)
config_info = config.read_db_config()
# make db connection
if db_name is None:
db_name = rad + "_iscat_" + ftype
try:
conn = MySQLConnection(database=db_name, **config_info)
except Exception, e:
logging.error(e, exc_info=True)
# create tmpdirs to store dmap files temporarily
for rad in rad_list:
tmpdir = "../data/" + rad + "_tmp"
os.system("mkdir -p " + tmpdir)
# create dbs (if not exist) for radars
for rad in rad_list:
db_name = rad + "_boxcar_" + ftype
try:
# create a db
db_tools.create_db(db_name)
except Exception, e:
logging.error(e, exc_info=True)
# read the db config info
config = db_config.db_config(config_filename="../mysql_dbconfig_files/config.ini",
section="midlat")
config_info = config.read_db_config()
# make db connections and save them into a dict
conn_dict = {}
for rad in rad_list:
db_name = rad + "_boxcar_" + ftype
try:
conn_tmp = MySQLConnection(database=db_name, **config_info)
conn_dict[rad] = conn_tmp
except Exception, e:
logging.error(e, exc_info=True)
# create dates, does not include the edate
all_dates = [sdate + dt.timedelta(days=i) for i in range((edate-sdate).days)]
def cos_fit(input_table,
output_table,
db_name=None,
config_filename="../mysql_dbconfig_files/config.ini",
section="midlat",
ftype="fitacf",
coords="mlt",
azbin_nvel_min=10,
naz_min=3,
az_span_min=30,
sqrt_weighting=True):
""" Does cosine fitting to the LOS data in each MLAT/MLT grid,
and stores the results in a different table named "master_cosfit_xxx".
This table has only the qualified latc-lonc grid points.
Parameters
----------
input_table : str
A table name in db_name db
output_table : str
A table name in db_name db
config_filename: str
name and path of the configuration file
section: str, default to "midlat"
section of database configuration
db_name : str, default to None
Name of the master db
ftype : str
SuperDARN file type
coords : str
Coordinates in which the binning process took place.
Default to "mlt, can be "geo" as well.
azbin_nvel_min : int
The minimum number of measurements an azimuthal bin should
have to be qualified for cosfitting.
naz_min : int
The minimum number of azimuthal bins within a grid cell.
cosine fitting is done if a grid cell has at least
naz_min number of qualified azimuthal bins
az_span_min : int
The minimum azimuhtal span a grid cell should have to
be qualified for cosfitting.
sqrt_weighting : bool
if set to True, the fitting is weighted by the number of points within
each azimuthal bin. if set to False, all azimuthal bins are
considered equal regardless of the nubmer of points
each of them contains.
Returns
-------
Nothing
"""
import numpy as np
import datetime as dt
from mysql.connector import MySQLConnection
import sys
sys.path.append("../")
from mysql_dbutils.db_config import db_config
import logging
# construct a db name
if db_name is None:
db_name = "master_" + coords + "_" + ftype
# read db config info
config = db_config(config_filename=config_filename, section=section)
config_info = config.read_db_config()
# make a connection to master db
try:
conn = MySQLConnection(database=db_name, **config_info)
cur = conn.cursor(buffered=True)
except Exception, e:
logging.error(e, exc_info=True)
def move_to_db(self,
config_filename="../mysql_dbconfig_files/config.ini",
section="midlat",
db_name=None):
""" writes the data into a MySQL db given by conn argument.
Parameters
----------
config_filename: str
name and path of the configuration file
section: str, default to mysql
section of database configuration
db_name : str, default to None
Name of the MySQL db to which iscat data will be written
Returns
-------
Nothing
"""
from mysql.connector import MySQLConnection
import sys
sys.path.append("../")
from mysql_dbutils.db_config import db_config
import logging
# read the db config info
config = db_config(config_filename=config_filename, section=section)
config_info = config.read_db_config()
# make db connection
if db_name is None:
db_name = self.rad + "_iscat_" + self.ftype
conn = MySQLConnection(database=db_name, **config_info)
cur = conn.cursor()
# loop through each radar beam
for bmnum in self.events.keys():
data_dict = self.events[bmnum]
# create a table
table_name = self.rad + "_bm" + str(bmnum)
command = "CREATE TABLE IF NOT EXISTS {tb} (\
datetime DATETIME,\
vel TEXT DEFAULT NULL,\
slist TEXT DEFAULT NULL,\
rsep TINYINT(4) DEFAULT NULL,\
frang SMALLINT(4) DEFAULT NULL,\
bmazm FLOAT(7,2) DEFAULT NULL,\
PRIMARY KEY (datetime))".format(tb=table_name)
try:
cur.execute(command)
except Exception, e:
logging.error(e, exc_info=True)
# loop through each scan time, usually 2 minutes,
# and write the data into table_name in db
for i in xrange(len(data_dict['datetime'])):
command = "INSERT IGNORE INTO {tb} (datetime, vel, slist, rsep, " +\
"frang, bmazm) VALUES (%s, %s, %s, %s, %s, %s)"
command = command.format(tb=table_name)
try:
cur.execute(command,
(data_dict["datetime"][i], data_dict["vel"][i],
data_dict["slist"][i], data_dict["rsep"][i],
data_dict["frang"][i], data_dict["bmazm"][i]))
except Exception, e:
logging.error(e, exc_info=True)
def fetch_data(input_table,
lat_range=[52, 59],
nvel_min=250,
season="winter",
config_filename="../mysql_dbconfig_files/config.ini",
section="midlat",
db_name=None,
ftype="fitacf",
coords="mlt",
sqrt_weighting=True,
limit_to_night=True):
""" fetch fitted data from the master db into a dict
Parameters
----------
input_table : str
A table name in db_name db
lat_ragne : list
The range of latitudes of interest
nvel_min : int
minimum requirement for the number of velocity measurements
in a lat-lon grid cell
season : str
season of interest
config_filename: str
name and path of the configuration file
section: str, default to "midlat"
section of database configuration
db_name : str, default to None
Name of the master db
ftype : str
SuperDARN file type
coords : str
Coordinates in which the binning process took place.
Default to "mlt, can be "geo" as well.
sqrt_weighting : bool
if set to True, the fitted vectors that are produced through weighting the
number of points within each azimuthal bin will be retrieved.
if set to False, the fitted vectors that are produced by equality weighting
the number of points within each azimuthal bin will be retrieved.
Return
------
data_dict : dict
"""
import sqlite3
import datetime as dt
import numpy as np
from mysql.connector import MySQLConnection
import sys
sys.path.append("../")
from mysql_dbutils.db_config import db_config
import logging
# construct a db name
if db_name is None:
db_name = "master_" + coords + "_" + ftype
# read db config info
config = db_config(config_filename=config_filename, section=section)
config_info = config.read_db_config()
# make a connection to master db
try:
conn = MySQLConnection(database=db_name, **config_info)
cur = conn.cursor(buffered=True)
except Exception, e:
logging.error(e, exc_info=True)