In [184]:
import sqlite3
import os.path
import csv
import json
import numpy as np
db_name = 'sample.db'
In [185]:
if os.path.isfile(db_name):
os.remove(db_name)
conn = sqlite3.connect(db_name)
c = conn.cursor()
# create ooi array codes
c.execute('''CREATE TABLE ooi_array
(
array_code character varying NOT NULL,
array_description character varying,
CONSTRAINT pkooi_array PRIMARY KEY (array_code)
)''')
# create ooi node codes
c.execute('''CREATE TABLE ooi_nodes
(
node_code character varying NOT NULL,
node_description character varying,
CONSTRAINT pkooi_nodes PRIMARY KEY (node_code)
)''')
# create ooi instrument classes
c.execute('''CREATE TABLE ooi_instrument_class
(
instrument_class_code character varying NOT NULL,
instrument_class_description character varying,
CONSTRAINT pkooi_instrument_class PRIMARY KEY (instrument_class_code)
)''')
# create ooi subsite
c.execute('''CREATE TABLE ooi_subsite
(
ooi_subsite_code character varying NOT NULL,
ooi_subsite_id character varying,
ooi_subsite_description character varying
)''')
# create ooi platforms
c.execute('''CREATE TABLE ooi_platforms
(
array_code text NOT NULL,
site_prefix text NOT NULL,
site_suffix text NOT NULL,
node_type_code text NOT NULL,
port_number text NOT NULL,
latitude real,
longitude real,
node_site_sequence text NOT NULL
)''')
Out[185]:
In [186]:
arrays = [
{"id":"CE","name":"Endurance Array"},
{"id":"CI","name":"Cyber Infrastructure"},
{"id":"CP","name":"Pioneer Array"},
{"id":"GA","name":"Global Argentine"},
{"id":"GI","name":"Global Irminger Sea"},
{"id":"GP","name":"Station PAPA"},
{"id":"GS","name":"55 South"},
{"id":"OO","name":"OOI"},
{"id":"RS","name":"Regional"},
{"id":"SS","name":"Shore Side Facilities"}]
for a in arrays:
query = "INSERT INTO ooi_array(array_code,array_description) VALUES ('"+a["id"]+"','"+a["name"]+"')"
c.execute(query)
conn.commit()
In [187]:
note_types = ["SF(Shallow Profiler Science Float)",
"ID(IF box HD camera)",
"MF(Multi-Function Node)",
"PC(Platform Interface Controller)",
"SB(Surface Buoy)",
"WF(Wire-Following Profiler)",
"MJ(MP Jbox)",
"SP(Surface-Piercing Profiler)",
"RI(Mooring Riser)",
"LJ(LP Jbox)",
"AV(AUV)",
"DP(Deep Profiler)",
"GL(Gliders)",
"MP(Mid-Water Platform)",
]
for node in note_types:
n = []
n = node.split("(")
n[1] = n[1][0:-1]
query = "INSERT INTO ooi_nodes(node_code,node_description) VALUES ('"+n[0]+"','"+n[1]+"')"
c.execute(query)
conn.commit()
In [188]:
in_class = ["TMPSF","VADCP","PREST", "PPSDN", "THSPH", "CTDBP", "METBK", "OBSSP", "VEL3D", "PARAD", "MASSP", "SPKIR", "CAMHD", "DOSTA", "RASFL", "ZPLSC", "FLORD", "HPIES", "HYDLF", "OSMOI", "ADCPS", "ZPLSG", "DOFST", "PHSEN", "NUTNR", "HYDBB", "CTDPF", "TRHPH", "VELPT", "OBSBB", "FLOBN", "OPTAA", "WAVSS", "FDCHP", "BOTPT", "PCO2A", "PCO2W", "CTDAV", "ADCPT", "FLORT", "CTDGV", "PRESF", "CTDMO", "CAMDS", "ADCPA", "OBSBK"]
for in_c in in_class:
query = "INSERT INTO ooi_instrument_class(instrument_class_code) VALUES ('"+in_c+"')"
c.execute(query)
conn.commit()
In [189]:
site_nodes = ["CP01CNSP", "GI01SUMO", "CP04OSPM", "CE01ISSM", "GS03FLMB", "CP05MOAS", "CP03ISSP", "CP01CNSM", "CE05MOAS", "GA03FLMA", "RS03AXBS", "RS03AXVM", "RS01SLBS", "CE07SHSM", "RS03ECAL", "CE06ISSP", "CE04OSSM", "GA05MOAS", "CP02PMUO", "GP05MOAS", "GP03FLMB", "GS01SUMO", "RS01SUM1", "RS01SBVM", "RS01SUM2", "CE02SHSP", "RS03ASHS", "GP03FLMA", "CE02SHBP", "GS03FLMA", "RS03CCAL", "RS03INT2", "CE07SHSP", "GA02HYPM", "RS03INT1", "CE02SHSM", "GI03FLMA", "CE09OSPM", "CE09OSSM", "CE04OSBP", "CP02PMCI", "GP02HYPM", "CP02PMUI", "CE04OSHY", "GI05MOAS", "CE01ISSP", "GI02HYPM", "CP03ISSM", "GA01SUMO", "GA03FLMB", "CP04OSSM", "CE06ISSM", "GI03FLMB", "GS02HYPM", "CP02PMCO", "GS05MOAS"]
codelist = []
for node in site_nodes:
array = node[0:2]
site_id = node[2:4]
site_code = node[4:]
if site_id+site_code not in codelist:
codelist.append(site_id+site_code)
#print node,array,site_id,site_code
query = "INSERT INTO ooi_subsite(ooi_subsite_code,ooi_subsite_id) VALUES ('"+site_code+"','"+site_id+"')"
c.execute(query)
conn.commit()
In [190]:
with open('platform_ports.csv', 'rb') as csvfile:
csvreader = csv.reader(csvfile, delimiter=';', quotechar='|')
for row in csvreader:
#print (row)
datarow = ""
valarray = []
for i,col in enumerate(row):
if i == 5 or i == 6:
try:
val = float(row[i])
except:
val = '-999'
else:
val = "'"+col+"'"
valarray.append(str(val))
datarow = ",".join(valarray)
query = "INSERT INTO ooi_platforms VALUES ("+datarow+")"
c.execute(query)
conn.commit()
In [191]:
#NOT IMPLEMENTED
In [192]:
c.execute("SELECT * FROM ooi_platforms")
rows = c.fetchall()
data_dict = {}
print "rows",len(rows)
st_count = 0
for row in rows:
array = row[0]
site_id = row[1] #site prefix
site = row[2] #site suffix
node = row[3] #note type
port = row[4]
lat = row[5]
lon = row[6]
node_seq = row[-1] #node seq?
if array not in data_dict:
data_dict[array] = {}
if site not in data_dict[array]:
data_dict[array][site] = {"ports":[],"lat":lat,"lon":lon,"site_id":site_id,"node_seq":[]}
st_count +=1
if port not in data_dict[array][site]["ports"]:
data_dict[array][site]["ports"].append(port)
if node_seq not in data_dict[array][site]["node_seq"]:
data_dict[array][site]["node_seq"].append(node_seq)
with open("stations.json", "w") as outfile:
json.dump(data_dict, outfile, indent=4)
print "platforms",st_count
In [193]:
conn.close()
In [193]:
In [193]: