def create_db_views(self):
SQLFile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions","add_spatial_views_for_gis2threejs.sql")
myconnection = utils.dbconnection()
myconnection.connect2db()
curs = myconnection.conn.cursor()
curs.execute("PRAGMA foreign_keys = ON") #Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately.
sqliteline = r"""create view strat_obs_p_for_qgsi2threejs as select distinct "a"."rowid" as "rowid", "a"."obsid" as "obsid", "a"."geometry" as "geometry" from "obs_points" as "a" JOIN "stratigraphy" as "b" using ("obsid") where (typeof("a"."h_toc") in ('integer', 'real') or typeof("a"."h_gs") in ('integer', 'real'))"""
curs.execute(sqliteline)
sqliteline = r"""insert into views_geometry_columns (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only) values ('strat_obs_p_for_qgsi2threejs', 'geometry', 'rowid', 'obs_points', 'geometry',1);"""
curs.execute(sqliteline)
for key in self.strat_layers_dict:
f = open(SQLFile, 'r')
linecounter = 1
for line in f:
if linecounter > 1: # first line is encoding info....
sqliteline = line.replace('CHANGETOVIEWNAME',key).replace('CHANGETOPLOTTYPESDICTVALUE',self.strat_layers_dict[key])
#print(sqliteline)#debug
curs.execute(sqliteline)
linecounter += 1
curs.execute("PRAGMA foreign_keys = OFF")
myconnection.closedb()
def zip_db(self):
force_another_db = False
dbpath=None
if self.db:
use_current_db = utils.Askuser("YesNo",'Vill du göra backup av %s?'%self.db,'Which database?')
if use_current_db.result == 1:
dbpath = self.db
force_another_db = False
elif use_current_db.result == 0:
force_another_db = True
elif use_current_db.result == '':
return
if not self.db or force_another_db:
dbpath = QFileDialog.getOpenFileName(None, 'Ange db som du vill skapa backup utav','',"Spatialite (*.sqlite)")[0]
if dbpath:
QApplication.setOverrideCursor(Qt.WaitCursor)
connection = utils.dbconnection(dbpath)
connection.connect2db()
connection.conn.cursor().execute("begin immediate")
file_path = os.path.realpath(dbpath)
dir_path = os.path.dirname(file_path)
current_dir = dir_path.split(os.sep)[-1]
bkupname = dbpath + datetime.datetime.now().strftime('%Y%m%dT%H%M') + '.zip'
zf = zipfile.ZipFile(bkupname, mode='w')
zf.write(dbpath,os.path.basename(dbpath), compress_type=compression) #compression will depend on if zlib is found or not
zf.close()
connection.conn.rollback()
connection.closedb()
self.iface.messageBar().pushMessage("Information","Database backup was written to " + bkupname, 1,duration=15)
QApplication.restoreOverrideCursor()
def LoadColumnsFromTable(self, table=''):
""" This method returns a list with all the columns in the table"""
if len(table)>0 and len(self.ms.settingsdict['database'])>0: # Should not be needed since the function never should be called without existing table...
myconnection = utils.dbconnection(self.ms.settingsdict['database'])
if myconnection.connect2db() == True:
curs = myconnection.conn.cursor()
sql = r"""SELECT * FROM '"""
sql += str(table)
sql += """'"""
rs = curs.execute(sql) #Send the SQL statement to get the columns in the table
columns = {}
columns = [tuple[0] for tuple in curs.description]
rs.close()
myconnection.closedb()# then close the database
else:
columns = {}
return columns # This method returns a list with all the columns in the table
def zip_db(self):
force_another_db = False
dbpath = None
if self.db:
use_current_db = utils.Askuser(
"YesNo", 'Vill du göra backup av %s?' % self.db,
'Which database?')
if use_current_db.result == 1:
dbpath = self.db
force_another_db = False
elif use_current_db.result == 0:
force_another_db = True
elif use_current_db.result == '':
return
if not self.db or force_another_db:
dbpath = QFileDialog.getOpenFileName(
None, 'Ange db som du vill skapa backup utav', '',
"Spatialite (*.sqlite)")[0]
if dbpath:
QApplication.setOverrideCursor(Qt.WaitCursor)
connection = utils.dbconnection(dbpath)
connection.connect2db()
connection.conn.cursor().execute("begin immediate")
file_path = os.path.realpath(dbpath)
dir_path = os.path.dirname(file_path)
current_dir = dir_path.split(os.sep)[-1]
bkupname = dbpath + datetime.datetime.now().strftime(
'%Y%m%dT%H%M') + '.zip'
zf = zipfile.ZipFile(bkupname, mode='w')
zf.write(dbpath,
os.path.basename(dbpath),
compress_type=compression
) #compression will depend on if zlib is found or not
zf.close()
connection.conn.rollback()
connection.closedb()
self.iface.messageBar().pushMessage(
"Information",
"Database backup was written to " + bkupname,
1,
duration=15)
QApplication.restoreOverrideCursor()
def loadTablesFromDB(self,db): # This method populates all table-comboboxes with the tables inside the database
# Execute a query in SQLite to return all available tables (sql syntax excludes some of the predefined tables)
# start with cleaning comboboxes before filling with new entries
myconnection = utils.dbconnection(db)#self.ms.settingsdict['database'])
if myconnection.connect2db() == True:
cursor = myconnection.conn.cursor()
rs=cursor.execute(r"""SELECT tbl_name FROM sqlite_master WHERE (type='table' or type='view') and not (name in""" + midvatten_defs.SQLiteInternalTables() + r""") ORDER BY tbl_name""") #SQL statement to get the relevant tables in the spatialite database
self.ListOfTables.addItem('')
self.ListOfTables_2.addItem('')
self.ListOfTables_3.addItem('')
self.ListOfTables_WQUAL.addItem('')
for row in cursor:
self.ListOfTables.addItem(row[0])
self.ListOfTables_2.addItem(row[0])
self.ListOfTables_3.addItem(row[0])
self.ListOfTables_WQUAL.addItem(row[0])
rs.close()
myconnection.closedb()# then close the database
def LoadDistinctPiperParams(self,db):
self.ClearPiperParams()
myconnection = utils.dbconnection(db)#self.ms.settingsdict['database'])
if myconnection.connect2db() == True:
cursor = myconnection.conn.cursor()
rs=cursor.execute(r"""SELECT DISTINCT parameter FROM w_qual_lab ORDER BY parameter""") #SQL statement to get all unique parameter names
self.paramCl.addItem('')
self.paramHCO3.addItem('')
self.paramSO4.addItem('')
self.paramNa.addItem('')
self.paramK.addItem('')
self.paramCa.addItem('')
self.paramMg.addItem('')
for row in cursor:
self.paramCl.addItem(row[0])
self.paramHCO3.addItem(row[0])
self.paramSO4.addItem(row[0])
self.paramNa.addItem(row[0])
self.paramK.addItem(row[0])
self.paramCa.addItem(row[0])
self.paramMg.addItem(row[0])
rs.close()
myconnection.closedb()# then close the database
def showtheplot(self, layer): # PlotTS method that, at the moment, performs all the real work
provider = layer.dataProvider() #Something with OGR
kolumnindex = provider.fieldNameIndex('obsid') # To find the column named 'obsid'
if kolumnindex == -1:
kolumnindex = provider.fieldNameIndex('OBSID') # backwards compatibility
if(layer):
nF = layer.selectedFeatureCount()
if (nF > 0):
myconnection = utils.dbconnection()
if myconnection.connect2db() == True:
# skapa en cursor
curs = myconnection.conn.cursor()
# Load all selected observation points
ob = layer.selectedFeatures()
# Create a plot window with one single subplot
fig = plt.figure() # causes conflict with plugins "statist" and "chartmaker"
ax = fig.add_subplot(111)
if len(self.y3col):
nY = 3
elif len(self.y2col):
nY = 2
else:
nY = 1
p=[None]*nF*nY # List for plot objects
plabel=[None]*nF*nY # List for label strings
i=0
j=0
for k in ob: # Loop through all selected objects, a plot is added for each one of the observation points (i.e. selected objects)
attributes = ob[i]
obsid = attributes[kolumnindex] # Copy value in column obsid in the attribute list
# Load all observations (full time series) for the object [i] (i.e. selected observation point no i)
sql =r"""SELECT """
sql += unicode(self.xcol) #MacOSX fix1
sql += r""" as 'x'"""
if len(self.y1col):
sql += r""", """
sql += unicode(self.y1col) #MacOSX fix1
sql += r""" as 'y1'"""
if len(self.y2col):
sql += r""", """
sql += unicode(self.y2col) #MacOSX fix1
sql += r""" as 'y2'"""
if len(self.y3col):
sql += r""", """
sql += unicode(self.y3col) #MacOSX fix1
sql += r""" as 'y3'"""
sql += """ FROM """
sql += unicode(self.table) #MacOSX fix1
sql += r""" WHERE obsid = '"""
sql += obsid
sql += """' ORDER BY """
sql += unicode(self.xcol) #MacOSX fix1
rs = curs.execute(sql) #Send SQL-syntax to cursor
recs = rs.fetchall() # All data are stored in recs
"""Transform data to a numpy.recarray"""
if len(self.y1col):
My_format = [('x', float), ('y1', float)]
if len(self.y2col):
My_format.append(('y2', float))
if len(self.y3col):
My_format.append(('y3', float))
table = np.array(recs, dtype=My_format) #NDARRAY
table2=table.view(np.recarray) # RECARRAY Makes the two columns inte callable objects, i.e. write table2.values
if self.markers==2: # If the checkbox is checked - markers will be plotted, just a line #MacOSX fix1
p[j], = ax.plot(table2.x, table2.y1, marker = 'o', linestyle = '-', label=obsid) # PLOT!!
else:
p[j], = ax.plot(table2.x, table2.y1, marker = 'None', linestyle = '-', label=obsid) # PLOT!!
plabel[j] = obsid + unicode(self.y1col) #+ str(j)# Label for the plot #MacOSX fix1
if len(self.y2col):
j = j + 1
if self.markers==2:# If the checkbox is checked - markers will be plotted, just a line #MacOSX fix1
p[j], = ax.plot(table2.x, table2.y2, marker = 'o', linestyle = '-', label=obsid) # PLOT!!
else:
p[j], = ax.plot(table2.x, table2.y2, marker = 'None', linestyle = '-', label=obsid) # PLOT!!
plabel[j] = obsid + unicode(self.y2col) #+ str(j)# Label for the plot #MacOSX fix1
if len(self.y3col):
j = j + 1
if self.markers==2: # If the checkbox is checked - markers will be plotted, just a line #MacOSX fix1
p[j], = ax.plot(table2.x, table2.y3, marker = 'o', linestyle = '-', label=obsid) # PLOT!!
else:
p[j], = ax.plot(table2.x, table2.y3, marker = 'None', linestyle = '-', label=obsid) # PLOT!!
plabel[j] = obsid + unicode(self.y3col) #+ str(j)# Label for the plot #MacOSX fix1
j = j + 1
i = i+1
""" Close SQLite-connections """
rs.close() # First close the table
myconnection.closedb()# then close the database
""" Finish plot """
ax.grid(True)
ax.yaxis.set_major_formatter(tick.ScalarFormatter(useOffset=False, useMathText=False))
ax.set_xlabel(self.xcol) #MacOSX fix1
ylabel = unicode(self.y1col) + ", \n" + unicode(self.y2col) + ", \n" + unicode(self.y3col) #MacOSX fix1
ax.set_ylabel(ylabel)
ax.set_title(self.settingsdict['xytable']) #MacOSX fix1
leg = fig.legend(p, plabel, loc=0)
leg.draggable(state=True)
frame = leg.get_frame() # the matplotlib.patches.Rectangle instance surrounding the legend
frame.set_facecolor('0.80') # set the frame face color to light gray
frame.set_fill(False) # set the frame face color transparent
for t in leg.get_texts():
t.set_fontsize(10) # the legend text fontsize
for label in ax.xaxis.get_ticklabels():
label.set_fontsize(10)
for label in ax.yaxis.get_ticklabels():
label.set_fontsize(10)
plt.show() # causes conflict with plugins "statist" and "chartmaker"
else:
utils.pop_up_info("Please select at least one point with xy data")
else:
utils.pop_up_info("Please select a layer containing observations with xy data")
