def waterContent(loc_id, tube_num, sn): '''WATER CONTENT OF A SOIL SAMPLE Function to compute the water content of a sample Inputs: loc_id - Location ID in Erosion database ('locations' table) tube_num - tube number ('tubes' table) sn - sample number to computer WC ('wcs' table) Outputs: wc - decimal fraction water content of sample (float) ''' cmd = """SELECT mpsw, mps, mp FROM watercontent WHERE loc_id = %d AND tube_num = %d AND sn = %s""" % (loc_id, tube_num, sn) cnn, cur = connectToDB(cmd) Mpsw, Mps, Mp = cur.fetchone() Mw = Mpsw - Mps Ms = Mps - Mp wc = Mw/Ms cur.close() cnn.close() return wc
def specificGravity(loc_id, tube_num): '''SPECIFIC GRAVITY OF A SOIL SAMPLE Function to compute the specific gravity of a soil sample as measured in a pyncometer. Inputs: loc_id - Location ID in Erosion database ('locations' table) tube_num - tube number ('tubes' table) Outputs: SG - specific gravity of sample ''' pyncCF = getCalibFactors(302) cmd = """SELECT mpync, mtot, mp, mps, temperature FROM specificgravity WHERE loc_id = %d AND tube_num = %d""" % (loc_id, tube_num) cnn, cur = connectToDB(cmd) Mpync, Mtot, Mp, Mps, temp = cur.fetchone() M1 = Mtot M2 = pyncCF[0] + pyncCF[1]*temp + pyncCF[2]*temp**2 Ms = Mps - Mp sg = Ms / (Ms - M1 + M2) cur.close() cnn.close() return sg
def getErosionData(loc_id, tube_num, sn): t,d = getPotData(loc_id, tube_num, sn, 'erosion') cmd = """ SELECT EI.sf, EI.tau, EI.dur, C.descr, EI.note, EI.wc_sn, EI.ext_sn FROM erosion_info EI INNER JOIN codes C on C.code = EI.erosion_type WHERE EI.loc_id = %d AND EI.tube_num = %d AND EI.sn = %d """ % (loc_id, tube_num, sn) cnn, cur = connectToDB(cmd) test = cur.fetchone() info = {'sf' : test[0], 'tau' : test[1], 'dur' : test[2], 'type' : test[3], 'note' : test[4], 'wc_sn' : test[5], 'ext_sn' : test[6]} cur.close() cnn.close() return t, d, info
def getExtrusionData(loc_id, tube_num, sn): t, d = getPotData(loc_id, tube_num, sn, 'extrusion') cmd = """ SELECT sf, dur, in1, in2, msw, note, wc_sn FROM extrusion_info WHERE loc_id = %d AND tube_num = %d AND sn = %d """ % (loc_id, tube_num, sn) cnn, cur = connectToDB(cmd) test = cur.fetchone() info = {'sf' : test[0], 'dur' : test[1], 'index' : (test[2], test[3]), 'mss' : test[4], 'note' : test[5], 'wc_sn' : test[6]} cur.close() cnn.close() return t, d, info
def getRheoData(loc_id, tube_num, sn): import numpy as np cmd = """ SELECT gam, tau, gmd FROM yieldstress WHERE loc_id = %d AND tube_num = %d AND sn = %d""" % (loc_id, tube_num, sn) cnn, cur = connectToDB(cmd) gamma = np.array([]) tau = np.array([]) gammadot = np.array([]) for c in cur: gamma = np.hstack([gamma, c[0]]) tau = np.hstack([tau, c[1]]) gammadot = np.hstack([gammadot, c[2]]) rheoData = {'gamma' : gamma, 'tau' : tau, 'gammadot' : gammadot } cur.close() cnn.close() return rheoData
def getRheoInfo(loc_id, tube_num, sn): import numpy as np cmd = """ SELECT * FROM yieldstress_info WHERE loc_id = %d AND tube_num = %d AND sn = %d""" % (loc_id, tube_num, sn) cnn, cur = connectToDB(cmd) c = cur.fetchone() rheoInfo = {'tauMin' : c[3], 'tauMax' : c[4], 'dur' : c[5], 'mc' : c[6], 'mcsw' : c[7], 'mp' : c[8], 'mps' : c[9], 'lowerN' : np.array([c[10:12], c[12:14]]), 'upperN' : np.array([c[14:16], c[16:18]]), 'axLimU' : np.array([c[18:20], c[20:22]]), 'y' : np.array([c[22:24], c[24:]]) } cur.close() cnn.close() return rheoInfo
def organicMatterConent(loc_id, tube_num): cmd = """SELECT mpsa, mps, mp FROM organicmatter WHERE loc_id = %d AND tube_num = %d""" % (loc_id, tube_num) cnn, cur = connectToDB(cmd) Mpsa, Mps, Mp = cur.fetchone() Ma = Mpsa - Mps Ms = Mps - Ms om = Ma/Ms cur.close() cnn.close() return om
def __test_getAllData(table): #Tables = ['erosion', 'erosion_info', 'extrusion', 'extrusion_info'] cmd = """ SELECT DISTINCT loc_id, tube_num, sn FROM %s""" % table cnn, cur = connectToDB(cmd) loc_id = [] tube_num = [] sn = [] for c in cur: loc_id.append(c[0]) tube_num.append(c[1]) sn.append(c[2]) return loc_id, tube_num, sn
def plasticLimit(loc_id, tube_num): '''PLASTIC LIMIT OF A SOIL SAMPLe ''' cmd = """SELECT sn FROM watercontent WHERE loc_id = %d AND tube_num = %d AND wcs_type = 205""" % (loc_id, tube_num) cnn, cur = connectToDB(cmd) wc = np.array([]) for row in cur: wc = np.hstack([wc, waterContent(loc_id, tube_num, row[0])]) PL = wc.mean() cur.close() cnn.close() return PL
def getPotData(loc_id, tube_num, sn, table): import numpy as np cmd = """ SELECT rn, disp FROM %s WHERE loc_id = %d AND tube_num = %d AND sn = %d """ % (table, loc_id, tube_num, sn) cnn, cur = connectToDB(cmd) t = np.array([]) d = np.array([]) for c in cur: t = np.hstack([t, c[0]]) d = np.hstack([d, c[0]]) cur.close() cnn.close() return t, d
def liquidLimit(loc_id, tube_num): '''LIQUID LIMIT OF A SOIL SAMPLE ''' cmd = """SELECT sn, result, wcs_type FROM watercontent WHERE loc_id = %d AND tube_num = %d AND wcs_type IN (203,204)""" % (loc_id, tube_num) cnn, cur = connectToDB(cmd) wc = np.array([]) x = np.array([]) for row in cur: wc = np.hstack([wc, waterContent(loc_id, tube_num, row[0])]) x = np.hstack([x, row[1]]) LL_type = row[2] cur.close() cnn.close() return wc, x, LL_type
def grainSize(loc_id, tube_num, plot=False): # set up tables for hydrometer analysis (ASTM D 422) Table1 = {'SG' : np.arange(2.45, 3.00, 0.05), 'alpha' : np.arange(1.04, 0.93, -0.01)} Table1['alpha'][0] = 1.05 # minor correction Table2 = {'AHyR' : np.arange(0,61), 'EffD' : np.array([ 16.3, 16.1, 16.0, 15.8, 15.6, 15.5, 15.3, 15.2, 15.0, 14.8, 14.7, 14.5, 14.3, 14.2, 14.0, 13.8, 13.7, 13.5, 13.3, 13.2, 13.0, 12.9, 12.7, 12.5, 12.4, 12.2, 12.0, 11.9, 11.7, 11.5, 11.4, 11.2, 11.1, 10.9, 10.7, 10.6, 10.4, 10.2, 10.1, 9.9, 9.7, 9.6, 9.4, 9.2, 9.1, 8.9, 8.8, 8.6, 8.4, 8.3, 8.1, 7.9, 7.8, 7.6, 7.4, 7.3, 7.1, 7.0, 6.8, 6.6, 6.5])} Table3 = {'SG' : np.arange(2.45,2.90,0.05), 'T' : np.arange(16.0,31.0,1.0), 'K' : np.array([[0.01530, 0.01505, 0.01481, 0.01457, 0.01435, 0.01414, 0.01394, 0.01374, 0.01356], [0.01511, 0.01486, 0.01462, 0.01439, 0.01417, 0.01396, 0.01376, 0.01356, 0.01338], [0.01492, 0.01467, 0.01443, 0.01421, 0.01399, 0.01378, 0.01359, 0.01339, 0.01321], [0.01474, 0.01449, 0.01425, 0.01403, 0.01382, 0.01361, 0.01342, 0.01323, 0.01305], [0.01456, 0.01431, 0.01408, 0.01386, 0.01365, 0.01344, 0.01325, 0.01307, 0.01289], [0.01438, 0.01414, 0.01391, 0.01374, 0.01369, 0.01348, 0.01328, 0.01291, 0.01273], [0.01421, 0.01397, 0.01374, 0.01353, 0.01332, 0.01312, 0.01294, 0.01276, 0.01258], [0.01404, 0.01381, 0.01358, 0.01337, 0.01317, 0.01297, 0.01279, 0.01261, 0.01243], [0.01388, 0.01365, 0.01342, 0.01321, 0.01301, 0.01282, 0.01264, 0.01246, 0.01229], [0.01372, 0.01349, 0.01327, 0.01306, 0.01286, 0.01267, 0.01249, 0.01232, 0.01215], [0.01357, 0.01334, 0.01312, 0.01291, 0.01272, 0.01253, 0.01235, 0.01218, 0.01201], [0.01342, 0.01319, 0.01297, 0.01277, 0.01258, 0.01329, 0.01221, 0.01204, 0.01188], [0.01327, 0.01304, 0.01283, 0.01264, 0.01255, 0.01244, 0.01208, 0.01191, 0.01175], [0.01312, 0.01290, 0.01269, 0.01249, 0.01230, 0.01212, 0.01195, 0.01178, 0.01162], [0.01298, 0.01276, 0.01256, 0.01236, 0.01217, 0.01199, 0.01182, 0.01165, 0.01149]])} table1Interp = spi.interp1d(Table1['SG'], Table1['alpha']) table2Interp = spi.interp1d(Table2['AHyR'], Table2['EffD']) table3Interp = spi.interp2d(Table3['SG'], Table3['T'], Table3['K']) # initialize output arrays Dsve = np.array([]) Dhyd = np.array([]) PFsve = np.array([]) PFhyd = np.array([]) Mret = np.array([]) # get the sieve data cmd = """SELECT dsve, mtot-msve FROM sieve WHERE loc_id = %d AND tube_num = %d""" % (loc_id, tube_num) cnn, cur = connectToDB(cmd) for row in cur: Dsve = np.hstack([Dsve, row[0]]) Mret = np.hstack([Mret, row[1]]) # read hydrometer data cmd = """SELECT time, hydr, temperature FROM hydrometer WHERE loc_id = %d AND tube_num = %d""" % (loc_id, tube_num) cur.execute(cmd) time = np.array([]) hydr = np.array([]) temp = np.array([]) for row in cur: time = np.hstack([time, row[0]]) hydr = np.hstack([hydr, row[1]]) temp = np.hstack([temp, row[2]]) # get info about that hydrometer test if len(time) == 0: cumFracRet = Mret.cumsum()/Mret.sum() PFsieve = (1 - cumFracRet[:-1]) * 100 else: cmd = """SELECT msw, wcs_sn FROM luhydrometer WHERE loc_id = %d AND tube_num = %d""" % (loc_id, tube_num) cur.execute(cmd) Msw, wcs_sn = cur.fetchone() # hygroscopic water content of hydrometer soil wcHygro = waterContent(loc_id, tube_num, wcs_sn) Ms = Msw / (1 + wcHygro) Mcoarse = Mret.sum() Mfine = Ms - Mcoarse n = len(Mret) cumFracRet = Mret.cumsum()/Ms PFsieve = (1 - cumFracRet[:-1]) * 100 hydrCF = getCalibFactors(301) hydrCorrection = hydrCF[0] + hydrCF[1] * temp + hydrCF[2] * temp**2 hydrFinal = hydr - hydrCorrection SG = specificGravity(loc_id, tube_num) alpha = table1Interp(SG) PFhydro = hydrFinal * alpha / Ms * 100 L = np.zeros(len(hydr)) K = np.zeros(len(hydr)) for n in range(len(hydr)): L[n] = table2Interp(hydr[n]) K[n] = table3Interp(SG, temp[n]) Dhyd = K * np.sqrt(L/time) D = np.hstack([Dsve[:-1], Dhyd]) PF = np.hstack([PFsieve, PFhydro]) label = None if plot: cmd = """SELECT L.county, T.top, T.bottom FROM tubes T INNER JOIN locations L ON T.loc_id = L.id WHERE T.loc_id = %d AND T.num = %d""" % (loc_id, tube_num) cur.execute(cmd) tubeInfo = cur.fetchone() Label = '%s %0.1f ft to %0.1f ft' % tubeInfo print(Label) fig = pl.figure() ax = fig.add_subplot(111) ax.plot(D, PF, 'ko', ms=6, label=Label) ax.set_ylabel('Percent Finer') ax.set_xlabel('Particle Size, mm') ax.legend(loc='lower right') ax.set_xscale('log') ax.set_ylim([0,100]) fig.savefig('%s%d.pdf' % (tubeInfo[0], tube_num), dpi=300, bbox_inches='tight') pl.close(fig) cur.close() cnn.close() return D, PF, Label