package usda.weru.soil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.NumberFormat; import java.text.SimpleDateFormat; import java.util.Date; import javax.measure.Measure; import javax.measure.quantity.Length; import javax.measure.unit.NonSI; import javax.measure.unit.SI; import javax.measure.unit.Unit; import javax.swing.ProgressMonitor; import usda.weru.util.*; /** * * @author maxerdwien */ public class NASIS { //Logs for errors and warnings. WepsMessageLog c_log = new WepsMessageLog(); ProgressMonitor c_progress; String errIncFile = ""; String state = ""; String county = ""; String ssaname = ""; String ssaid = ""; String musym = ""; String compname = ""; String comppct = ""; String taxorder = ""; String localphase = ""; double losstolerance = 0.0; double slope = 0.0; double albedodry = 0.0; // double fragmentcover = 0.0; int numlay = 0; int adjlay = 0; double hzdept[]; double hzdepb[]; double hzthk[]; String texture[]; boolean stratified[]; boolean rv[]; double claytotal[]; double sandtotal[]; double silttotal[]; double sandvco[]; double sandco[]; double sandmed[]; double sandfine[]; double sandvf[]; double dbthirdbar[]; // double dbovendry[]; double wtenthbar[]; double wthirdbar[]; double w15thbar[]; double ksat[]; double cec7[]; double ecec[]; double om[]; double caco3[]; double ph1to1h2o[]; double ph01mcacl2[]; double fragvol[]; double lep[]; //Not exactly sure where these variable should be placed in the list 1143.2 double bedrockDepth; double impermiableDepth; /* *The following soil layers and any below with the following textures are ignored by the interface. "by" - Boulders "cem" - Cemented "ce" - Coprogeneous earth "dur" - Duripan "frag" - Fragmental material "gyp" - Gypsiferous material "hpm" - Highly decomposed plant material "ind" - Indurated "marl" - Marl "mat" - Material "mpm" - Moderately decomposed plant material "muck" - Muck "mpt" - Mucky peat "or" - Orstein "opwd" - Oxide protected weathered bedrock "peat" - Peat "pc" - Petrocalcic "pf" - Petroferric "pgp" - Petrogypsic "pum" - Pumiceous "spm" - Slightly decomposed plant material "st" - Stones "udom" - Undecomposed organic matter "u" - Unknown texture "uwb" - Unweathered bedrock "var" - Variable "w" - Water "wb" - weathered bedrock */ private String textureChk[] = {"by", "cem", "ce", "dur", "frag", "gyp", "hpm", "ind", "marl", "mat", "mpm", "muck", "mpt", "or", "opwd", "peat", "pc", "pf", "pgp", "pum", "spm", "st", "udom", "u", "uwb", "var", "w", "wb", "cem-", "pf-"}; /** * */ public NASIS() { } /** * * @param inpnumlay */ public NASIS(int inpnumlay) { numlay = inpnumlay; if (numlay == 0) { return; } hzdept = new double[numlay]; hzdepb = new double[numlay]; hzthk = new double[numlay]; texture = new String[numlay]; stratified = new boolean[numlay]; rv = new boolean[numlay]; claytotal = new double[numlay]; sandtotal = new double[numlay]; silttotal = new double[numlay]; sandvco = new double[numlay]; sandco = new double[numlay]; sandmed = new double[numlay]; sandfine = new double[numlay]; sandvf = new double[numlay]; dbthirdbar = new double[numlay]; // dbovendry = new double[numlay]; wtenthbar = new double[numlay]; wthirdbar = new double[numlay]; w15thbar = new double[numlay]; ksat = new double[numlay]; cec7 = new double[numlay]; ecec = new double[numlay]; om = new double[numlay]; caco3 = new double[numlay]; ph1to1h2o = new double[numlay]; ph01mcacl2 = new double[numlay]; fragvol = new double[numlay]; lep = new double[numlay]; } /** * * @param con */ public NASIS(Connection con) { this(10); // arbitrary max number of layers for NASIS SQL data c_con = con; } /** * * @param con * @param progress */ public NASIS(Connection con, ProgressMonitor progress) { this(con); // arbitrary max number of layers for NASIS SQL data c_progress = progress; } /** * */ protected Connection c_con; /****************************************************************************/ private static final String STRATIFIED_LAYER_KEY = "SR"; /** * */ public void averageStratifiedLayers() { StratifiedTextures st = StratifiedTextures.getInstance(); Layers: for (int i = 0; i < numlay; i++) { String layerTexture = texture[i]; if (stratified[i] && rv[i] && layerTexture.trim().toUpperCase().startsWith(STRATIFIED_LAYER_KEY)) { //This layer is stratified String[] textures = layerTexture.split(" ", -1); boolean first = true; double sand = 0; double clay = 0; double vsf = 0; int count = 0; for (String subTexture : textures) { subTexture = subTexture.trim(); if (first) { //skip the sr marker first = false; continue; } if (st.isTextureValid(subTexture)) { count++; sand += st.getSand(subTexture); sand /= count; clay += st.getClay(subTexture); clay /= count; vsf += st.getVeryFineSand(subTexture); vsf /= count; } else { //can't fix up this layer continue Layers; } } if (sand + clay > 1) { //TODO: log error continue Layers; } double silt = 1 - sand - clay; sandtotal[i] = sand * 100; silttotal[i] = silt * 100; claytotal[i] = clay * 100; sandvf[i] = vsf * 100; logMessage(WepsMessage.MessageSeverity.INFORMATION, "Averaged stratified layer values on layer " + String.valueOf(i + 1) + "."); } } } private boolean chkTexture(String texture) { String tmp = texture.toLowerCase(); for (String textureChk1 : textureChk) { if (tmp.indexOf(textureChk1) >= 0) { int tdx = tmp.indexOf(textureChk1); // check if token if (tdx > 0) { if (!Character.isWhitespace(tmp.charAt(tdx - 1))) { continue; } if (tmp.charAt(tdx - 1) != '-') { continue; } } if (tdx + textureChk1.length() < tmp.length()) { if (!Character.isWhitespace(tmp.charAt(tdx + textureChk1.length()))) { continue; } } logMessage(WepsMessage.MessageSeverity.WARNING, "Layer dropped for texture " + texture + "."); return true; } } return false; } /** * Returns the message log of the object. * @return */ public WepsMessageLog getLog() { return c_log; } /****************************************************************************/ /* adjust number of layers down to eliminate rock or highly organic soil */ /** * */ public void fixUpData() { for (adjlay = 1; adjlay < numlay; adjlay++) { // stop if layer is identified as rock, peat, etc. if (chkTexture(texture[adjlay])) { break; } // stop if both sand and clay are missing or zero if ((Double.isNaN(claytotal[adjlay]) || claytotal[adjlay] == 0.0) && (Double.isNaN(sandtotal[adjlay]) || sandtotal[adjlay] == 0.0)) { break; } // stop if layer does not have wet bulk density if (Double.isNaN(dbthirdbar[adjlay]) || dbthirdbar[adjlay] == 0.0) { break; } } } private void logMessage(WepsMessage.MessageSeverity severity, String message) { logMessage(new WepsMessage(severity, message)); } private void logMessage(WepsMessage message) { c_log.logMessage(message); } private void logSubstitutionMessage(WepsMessage.MessageSeverity severity, String dataElement, String header, Object newValue) { logSubstitutionMessage(severity, dataElement, header, newValue, -1); } private void logSubstitutionMessage(WepsMessage.MessageSeverity severity, String dataElement, String header, Object newValue, int layer) { String layerTag = ""; if (layer > -1) { layerTag = " from layer " + (layer + 1); } WepsMessage tempMessage = new WepsMessage(severity, dataElement + " (" + header + ") is missing" + layerTag + ". Null value was substituted with '" + newValue.toString() + "'."); logMessage(tempMessage); } private void logEstimatedMessage(WepsMessage.MessageSeverity severity, String dataElement, String header, Object newValue) { logEstimatedMessage(severity, dataElement, header, newValue, -1); } private void logEstimatedMessage(WepsMessage.MessageSeverity severity, String dataElement, String header, Object newValue, int layer) { String layerTag = ""; if (layer > -1) { layerTag = " from layer " + (layer + 1); } WepsMessage tempMessage = new WepsMessage(severity, dataElement + " (" + header + ") is missing" + layerTag + ". Value was estimated to '" + newValue.toString() + "'."); logMessage(tempMessage); } private enum SoilElementTestAction { IGNORE, ERROR, ESTIMATE } private String testStringSoilElement(String value, String name, String header, SoilElementTestAction action, String actionValue) { WepsMessage.MessageSeverity warning = WepsMessage.MessageSeverity.WARNING; WepsMessage.MessageSeverity error = WepsMessage.MessageSeverity.ERROR; //1. Test is not null if (value == null || value.length() == 0) { switch (action) { case IGNORE: logSubstitutionMessage(warning, name, header, actionValue); return actionValue; case ESTIMATE: logEstimatedMessage(warning, name, header, actionValue); return actionValue; case ERROR: logMessage(error, name + " (" + header + ") is missing."); return actionValue; } } return value; } private double testNumericSoilElement(double value, String units, double minimum, double maximum, String name, String header, SoilElementTestAction action, double actionValue) { return testNumericSoilElement(value, units, minimum, maximum, name, header, action, actionValue, -1); } private double testNumericSoilElement(double value, String units, double minimum, double maximum, String name, String header, SoilElementTestAction action, double actionValue, int layer) { WepsMessage.MessageSeverity warning = WepsMessage.MessageSeverity.WARNING; WepsMessage.MessageSeverity error = WepsMessage.MessageSeverity.ERROR; String layerTag = ""; if (layer > -1) { layerTag = " from layer " + (layer + 1); } //1. Test is not null if (Double.isNaN(value)) { switch (action) { case IGNORE: logSubstitutionMessage(warning, name, header, actionValue, layer); return actionValue; case ESTIMATE: logEstimatedMessage(warning, name, header, actionValue, layer); return actionValue; case ERROR: logMessage(error, name + " (" + header + ") is missing" + layerTag + "."); return actionValue; } } //2. Test in range if needed boolean outOfRange = false; if (!Double.isNaN(minimum) && value < minimum) { outOfRange = true; } if (!Double.isNaN(maximum) && value > maximum) { outOfRange = true; } if (outOfRange) { switch (action) { case IGNORE: logSubstitutionMessage(warning, name, header, Double.toString(actionValue) + " " + units, layer); return actionValue; case ESTIMATE: logEstimatedMessage(warning, name, header, Double.toString(actionValue) + " " + units, layer); return actionValue; case ERROR: logMessage(error, name + " (" + header + ")" + layerTag + " is outside the acceptable range (" + Double.toString(minimum) + " to " + Double.toString(maximum) + ")."); return actionValue; } } return value; } /** * */ public void testData() { SoilElementTestAction ignore = SoilElementTestAction.IGNORE; SoilElementTestAction ignore2 = SoilElementTestAction.IGNORE; SoilElementTestAction estimate = SoilElementTestAction.ESTIMATE; SoilElementTestAction error = SoilElementTestAction.ERROR; if (!estimateNullValues) { //We don't want to estimate estimate = error; ignore2 = error; //Test values that must be present in surgo state = testStringSoilElement(state, "State", "state", SoilElementTestAction.IGNORE, "unknown"); } //Errors if (numlay <= 0) { logMessage(WepsMessage.MessageSeverity.ERROR, "No layers found in soil record."); } //Soil Components compname = testStringSoilElement(compname, "Component Name", "compname", ignore, "unknown"); comppct = testStringSoilElement(comppct, "Component Percent", "comppct_r", ignore, "unknown"); taxorder = testStringSoilElement(taxorder, "Taxonomic Order", "taxorder", ignore, "unknown"); localphase = testStringSoilElement(localphase, "Local Phase", "localphase", ignore, "unknown"); losstolerance = testNumericSoilElement(losstolerance, "t/ac/yr", 1, 5, "Soil Loss Tolerance", "losstolerance", ignore, -1); slope = testNumericSoilElement(slope, "fraction", 0, 999, "Slope Gradient", "slope", ignore, -1); albedodry = testNumericSoilElement(albedodry, "fraction", 0, 1, "Dry Soil Albedo", "albedodry", estimate, 0.6 / Math.exp(0.4 * om[0])); //End Soil Components //Map Unit musym = testStringSoilElement(musym, "Map Symbol", "musym", ignore2, "no map symbol"); //End Map Unit //MuAggatt bedrockDepth = testNumericSoilElement(bedrockDepth, "cm", 0, 9999, "Bedrock Depth", "bedrockDepth", ignore, 9999); //End MuAggatt //CoRestrictions impermiableDepth = testNumericSoilElement(impermiableDepth, "", 0, 9999, "Impermiable/Restrictive Depth", "resdept_r_min", ignore, 9999); //End CoRestrictions //Legend ssaname = testStringSoilElement(ssaname, "Area Name/Area State", "areaname", ignore2, "no area name, no area state"); ssaid = testStringSoilElement(ssaid, "Area Symbol", "areasymbol", ignore2, "no area symbol"); //End Legend //Test Soil Layers, only tests layers that were not dropped for texture reasons. if (adjlay <= numlay) { for (int layer = 0; layer < adjlay; layer++) { hzdept[layer] = testNumericSoilElement(hzdept[layer], "cm", 0, 999, "Layer Depth Top", "hzdept", error, Double.NaN, layer); hzdepb[layer] = testNumericSoilElement(hzdepb[layer], "cm", 0, 999, "Layer Depth Bottom", "hzdepb", error, Double.NaN, layer); hzthk[layer] = hzdepb[layer] - hzdept[layer]; hzthk[layer] = testNumericSoilElement(hzthk[layer], "cm", 0.01, 1000, "Layer Thickness", "hzthk", estimate, hzdept[layer] - hzdepb[layer], layer); claytotal[layer] = testNumericSoilElement(claytotal[layer], "weight %", 0, 100, "Total Clay Content", "claytotal", error, Double.NaN, layer); sandtotal[layer] = testNumericSoilElement(sandtotal[layer], "weight %", 0, 100, "Total Sand Content", "sandtotal", error, Double.NaN, layer); double calculatedSiltTotal = 100 - (claytotal[layer] + sandtotal[layer]); silttotal[layer] = testNumericSoilElement(silttotal[layer], "weight %", calculatedSiltTotal * 0.999, calculatedSiltTotal * 1.001, "Total Silt Content", "silttotal", SoilElementTestAction.ESTIMATE, calculatedSiltTotal, layer); sandvf[layer] = testNumericSoilElement(sandvf[layer], "weight %", 0, 100, "Very Fine Sand", "sandvf", error, Double.NaN, layer); dbthirdbar[layer] = testNumericSoilElement(dbthirdbar[layer], "g/cm3", .02, 2.60, "1/3 Bar Bulk Density", "dbthirdbar", error, Double.NaN, layer); //the value will be estimated by ifc wthirdbar[layer] = testNumericSoilElement(wthirdbar[layer], "volume %", 0, 80, "1/3 bar water ", "wthirdbar", ignore, Double.NaN, layer); om[layer] = testNumericSoilElement(om[layer], "weight %", 0, 100, "Organic Matter Content", "om", error, Double.NaN, layer); caco3[layer] = testNumericSoilElement(caco3[layer], "weight %", 0, 110, "CaCO3 Equivalent", "caco3", error, Double.NaN, layer); //cec7[layer] = testNumericSoilElement(cec7[layer], "weight %", 0, 110, //"CEC7 or ECEC", "cec7", error, Double.NaN, layer); if (Double.isNaN(cec7[layer]) && Double.isNaN(ecec[layer])) { logMessage(WepsMessage.errorMessage("CEC7 and ECEC values are missing from layer " + (layer + 1) + ".")); } //total sand and total clay can not be greater than 100% combined. if (claytotal[layer] + sandtotal[layer] > 100) { logMessage(WepsMessage.errorMessage("Total Clay and Sand content for layer " + (layer + 1) + " exceeds 100%.")); } // fractionRock = (double[]) nasinp.fragvol.clone(); // rock % // veryCoarseSandFraction = (double[]) nasinp.sandvco.clone(); // coarseSandFraction // coarseSandFraction = (double[]) nasinp.sandco.clone(); // coarseSandFraction // mediumSandFraction = (double[]) nasinp.sandmed.clone(); // mediumSandFraction // fineSandFraction = (double[]) nasinp.sandfine.clone(); // fineSandFraction // veryFineSandFraction = (double[]) nasinp.sandvf.clone(); if (!estimateNullValues) { //Test values per layer if (Double.isNaN(ph1to1h2o[layer]) && Double.isNaN(ph01mcacl2[layer])) { logMessage(WepsMessage.errorMessage("pH 1:1 H2O and 0.01 MCaCl2 values are missing from layer " + (layer + 1) + ".")); } ksat[layer] = testNumericSoilElement(ksat[layer], "um/s", 0, 705, "Ksat", "ksat", error, Double.NaN, layer); w15thbar[layer] = testNumericSoilElement(w15thbar[layer], "volume %", 0, 70, "15 bar water (wp)", "wfifteenbar", ignore, Double.NaN, layer); } //Special case for LEP if (Double.isNaN(lep[layer])) { if (estimateNullValues) { //Estimate a value double bsd = SoilUtil.estimateSettledBulkDensity(claytotal[layer], sandtotal[layer], om[layer]); if (wthirdbar[layer] > bsd) { bsd = wthirdbar[layer]; String msgText = "Settled bulk density adjusted: Set to wet bulk density."; c_log.logMessage(WepsMessage.warningMessage(msgText)); } else { String msgText = "Settled bulk density estimated: " + Util.formatDouble(bsd, 4); c_log.logMessage(WepsMessage.warningMessage(msgText)); } double lepc = SoilUtil.estimateLinearExtensibility(bsd, wthirdbar[layer]); lep[layer] = testNumericSoilElement(lep[layer], "%", 0, 30, "Linear Extensibility", "lep", estimate, lepc, layer); } else { if (om[layer] > omFractionThreshold) { lep[layer] = testNumericSoilElement(lep[layer], "%", 0, 30, "Linear Extensibility", "lep", estimate, 0.0d, layer); } else { lep[layer] = testNumericSoilElement(lep[layer], "%", 0, 30, "Linear Extensibility", "lep", error, Double.NaN, layer); } } } else { lep[layer] = testNumericSoilElement(lep[layer], "%", 0, 30, "Linear Extensibility", "lep", error, Double.NaN, layer); } } } //Append to the error variable for (WepsMessage message : c_log.getMessages()) { errIncFile += "# " + message.toString() + "\n"; } } /** * * @param rs */ protected void copyComponent(ResultSet rs) { compname = getValueFromRS(rs, "compname", null); comppct = getValueFromRS(rs, "comppct_r", null); taxorder = getValueFromRS(rs, "taxorder", null); localphase = getValueFromRS(rs, "localphase", ""); String temp = getValueFromRS(rs, "tfact", "0"); if (temp.trim().length() == 0) { temp = "0"; } losstolerance = Integer.parseInt(temp); // losstolerance = getValueFromRS(rs, "tfact"); slope = getValueFromRS(rs, "slope"); albedodry = getValueFromRS(rs, "albedodry"); } /** * * @param rs */ protected void copyMapUnit(ResultSet rs) { musym = getValueFromRS(rs, "musym", null); } /** * * @param rs */ protected void copyMuAggatt(ResultSet rs) { bedrockDepth = getValueFromRS(rs, "brockdepmin"); } /** * * @param rs */ protected void copyCoRestrictions(ResultSet rs) { impermiableDepth = getValueFromRS(rs, "resdeptmin"); } /********************************************************************** wjr * @param rs */ protected void copyLegend(ResultSet rs) { // Date cordate = getDateValueFromRS(rs, "cordate"); // // Date archiveddate = getDateValueFromRS(rs, "ssurgoarchived"); // String tabularVersion = getStringValueFromRS(rs, "tabularversion"); // Date tabularEst = getDateValueFromRS(rs, "tabularverest"); // Date nasisExp = getDateValueFromRS(rs, "tabnasisexportdate"); Date tabularDate = getDateValueFromRS(rs, "tabularverest"); Date nasisDate = getDateValueFromRS(rs, "tabnasisexportdate"); String tabularVersion = getStringValueFromRS(rs, "tabularversion"); DateFormat format = new SimpleDateFormat("yyyy-MM-dd"); // if (cordate != null) { // logMessage(WepsMessage.MessageSeverity.INFORMATION, "Correlation Date: " + format.format(cordate)); // } if (tabularDate != null) { logMessage(WepsMessage.MessageSeverity.INFORMATION, "Tabular Establish Date: " + format.format(tabularDate)); } if (nasisDate != null) { logMessage(WepsMessage.MessageSeverity.INFORMATION, "Nasis Archived Date: " + format.format(nasisDate)); } if (tabularVersion != null && tabularVersion.trim().length() > 0) { logMessage(WepsMessage.MessageSeverity.INFORMATION, "Tabular Version: " + tabularVersion); } String[] parts = ssaname.split(",", -1); if (parts.length == 0) { state = null; county = ""; } else if (parts.length == 1) { state = parts[0].trim(); county = ""; } else { county = parts[0].trim(); state = parts[1].trim(); } } /********************************************************************** wjr * @param rs * @param fieldName * @return */ protected String getStringValueFromRS(ResultSet rs, String fieldName) { try { return rs.getString(fieldName); } catch (SQLException e) { return null; } } /** * * @param rs * @param fieldName * @return */ protected int getIntergerValueFromRS(ResultSet rs, String fieldName) { try { return rs.getInt(fieldName); } catch (SQLException e) { return Integer.MIN_VALUE; } } /** * * @param rs * @param fieldName * @return */ protected Date getDateValueFromRS(ResultSet rs, String fieldName) { try { return rs.getDate(fieldName); } catch (SQLException e) { return null; } } /** * * @param rs * @param fieldName * @return */ protected double getValueFromRS(ResultSet rs, String fieldName) { return getValueFromRS(rs, fieldName, Double.NaN); } /** * * @param rs * @param fieldName * @param defaultValue * @return */ protected double getValueFromRS(ResultSet rs, String fieldName, double defaultValue) { try { String rtnStr = rs.getString(fieldName + "_r"); if (rtnStr == null) { throw new SQLException(); } try { return Double.parseDouble(rtnStr); } catch (NumberFormatException g) { throw new SQLException(); } } catch (SQLException e) { try { String rtn_l = rs.getString(fieldName + "_l"); String rtn_h = rs.getString(fieldName + "_h"); if (rtn_l == null || rtn_h == null) { throw new SQLException(); } try { return (Double.parseDouble(rtn_l) + Double.parseDouble(rtn_h)) / 2; } catch (NumberFormatException h) { throw new SQLException(); } } catch (SQLException f) { return defaultValue; } } catch (NullPointerException npe) { return defaultValue; } } /** * * @param resultSet * @param fieldName * @param defaultValue * @return */ protected String getValueFromRS(ResultSet resultSet, String fieldName, String defaultValue) { try { String temp = resultSet.getString(fieldName); return temp != null ? temp : defaultValue; } catch (Exception e) { // todo: it looks like this catch clause is always activated return defaultValue; } } /********************************************************************** wjr * @param rs * @param idx */ protected void copyHorizon(ResultSet rs, int idx) { this.hzdept[idx] = getValueFromRS(rs, "hzdept"); this.hzdepb[idx] = getValueFromRS(rs, "hzdepb"); this.claytotal[idx] = getValueFromRS(rs, "claytotal"); this.sandtotal[idx] = getValueFromRS(rs, "sandtotal"); this.silttotal[idx] = getValueFromRS(rs, "silttotal"); sandvco[idx] = getValueFromRS(rs, "sandvc", 0); sandco[idx] = getValueFromRS(rs, "sandco", 0); this.sandmed[idx] = getValueFromRS(rs, "sandmed", 0); this.sandfine[idx] = getValueFromRS(rs, "sandfine", 0); this.sandvf[idx] = getValueFromRS(rs, "sandvf"); this.dbthirdbar[idx] = getValueFromRS(rs, "dbthirdbar"); // this.dbovendry[idx] = getValueFromRS(rs, "dbovendry"); // this.wtenthbar[idx] = getValueFromRS(rs, "wtenthbar"); this.wtenthbar[idx] = -9.9; this.wthirdbar[idx] = getValueFromRS(rs, "wthirdbar"); this.w15thbar[idx] = getValueFromRS(rs, "wfifteenbar"); this.ksat[idx] = getValueFromRS(rs, "ksat"); this.cec7[idx] = getValueFromRS(rs, "cec7"); this.ecec[idx] = getValueFromRS(rs, "ecec"); // I think my error checking will work - it compiles :-) // However, my test case error is not because both fields are null, // but that one is null for one layer and the other is null for another and // the NASIS(SSURGO) to IFC code can't handle that correctly (yet) - LEW //if ((this.cec7[idx] < 0.0) && (this.ecec[idx] < 0.0)) //System.err.println("NASIS_copyHorizon: cec7 or ecec fields not populated "); //if ((this.cec7[idx] < 0.0) && (this.ecec[idx] < 0.0)) throw new FieldNotPopulated("cec7 and ecec"); this.om[idx] = getValueFromRS(rs, "om"); this.caco3[idx] = getValueFromRS(rs, "caco3"); this.ph1to1h2o[idx] = getValueFromRS(rs, "ph1to1h2o"); this.ph01mcacl2[idx] = getValueFromRS(rs, "ph01mcacl2"); this.lep[idx] = getValueFromRS(rs, "lep"); } /********************************************************************** wjr * @param rs * @param idx */ protected void copyFrags(ResultSet rs, int idx) { this.fragvol[idx] = 0.0; try { while (rs.next()) { double tempFragVol = getValueFromRS(rs, "fragvol"); this.fragvol[idx] += (Double.isNaN(tempFragVol)) ? 0.0 : tempFragVol; } } catch (SQLException e) { } } /********************************************************************** wjr * @param rs * @param idx */ protected void copyFragCover(ResultSet rs, int idx) { // this.fragmentcover = 0.0; try { while (rs.next()) { double tempFragVol = getValueFromRS(rs, "sfragcov"); this.fragvol[idx] += (Double.isNaN(tempFragVol)) ? 0.0 : tempFragVol; } } catch (SQLException e) { } } /********************************************************************** wjr * @param rs * @param idx */ protected void copyTexture(ResultSet rs, int idx) { this.texture[idx] = ""; try { rs.next(); String temp = rs.getString("texture"); temp = (temp == null) ? "" : temp; temp = temp.replace("-", "_"); this.texture[idx] = temp; String stratifiedString = rs.getString("stratextsflag"); stratified[idx] = stratifiedString != null ? (stratifiedString.trim().equalsIgnoreCase("yes")) : false; String rvString = rs.getString("rvindicator"); rv[idx] = rvString != null ? (rvString.trim().equalsIgnoreCase("yes")) : false; } catch (SQLException e) { } } /********************************************************************** wjr * @param cokey * @return */ public boolean loadSQL(String cokey) { return loadSQL(c_con, cokey); } /** * * @param con * @param cokey * @return */ public boolean loadSQL(Connection con, String cokey) { /** * This pings data from the Soil Data Access Related Tables. * * If data falls out of the table, it should be able to be accessed by * following the URL pointed to by the file used to define the database, * and finding query help, which should have links to the documentation * of the table columns. * */ try { boolean updateProgress = c_progress != null; if (updateProgress) { c_progress.setMinimum(0); c_progress.setMaximum(10); } String mukey = null; String lkey = null; String chkey = null; if (updateProgress) { c_progress.setProgress(1); } Statement stmt = con.createStatement(); // String query = new String("SELECT * FROM component WHERE cokey = '"+cokey+"'"); /** * Documenting the official descriptions of all the components we are pulling. * * compname - Name assigned to a component based on its range of properties. * comppct_r - The percentage of the component of the mapunit. RV. * taxorder - The highest level in Soil Taxonomy. * slope_r - The difference in elevation between two points, * expressed as a percentage of the distance * between those points. (SSM). RV. * albedodry_r - The estimated ratio of the incident short-wave (solar) * radiation that is reflected by the air dry, * less than 2 mm fraction of the soil surface. * mukey - A non-connotative string of characters used to uniquely * identify a record in the Mapunit table * localphase - Phase criterion to be used at a local level, in * conjunction with "component name" to help * identify a soil component. * tfact - Soil loss tolerance factor. The maximum amount of * erosion at which the quality of a soil as a * medium for plant growth can be maintained. * * Sincerely, * Jonathan Hornbaker. */ String query = "SELECT compname,comppct_r,taxorder,slope_r,albedodry_r,mukey,localphase," + "tfact FROM component WHERE cokey = '" + cokey + "'"; ResultSet rs = stmt.executeQuery(query); boolean tmp = rs.next(); if (!tmp) { if (updateProgress) { c_progress.setProgress(10); } return false; } copyComponent(rs); //can test taxorder now to see if organic if (ConfigData.getDefault().getSoilTestOrganic()) { if ("histosols".equalsIgnoreCase(taxorder)) { throw new OrganicSoilException(); } } mukey = rs.getString("mukey"); rs.close(); if (updateProgress) { c_progress.setProgress(2); } /** * Documenting the official descriptions of all the components we are pulling. * * musym - The symbol used to uniquely identify the soil mapunit in the soil survey * lkey - A non-connotative string of characters used to uniquely * identify a record in the Legend table * * Sincerely, * Jonathan Hornbaker. */ query = "SELECT musym,lkey FROM mapunit WHERE mukey = '" + mukey + "'"; rs = stmt.executeQuery(query); tmp = rs.next(); copyMapUnit(rs); lkey = rs.getString("lkey"); rs.close(); //1143.2 if (updateProgress) { c_progress.setProgress(3); } /** * Documenting the official descriptions of all the components we are pulling. * * brockdepmin - The distance from the soil surface to the top of a * paralithic or lithic bedrock layer, expressed * as a shallowest depth of components whose * composition in the map unit is equal to or exceeds 15% * * Sincerely, * Jonathan Hornbaker. */ //Bedrock Depth query = "SELECT brockdepmin AS brockdepmin_r FROM muaggatt WHERE mukey = '" + mukey + "'"; rs = stmt.executeQuery(query); tmp = rs.next(); copyMuAggatt(rs); rs.close(); //1143.2 if (updateProgress) { c_progress.setProgress(4); } /** * Documenting the official descriptions of all the components we are pulling. * * resdept_r - The distance from the soil surface to the upper boundary * of the restrictive layer. RV. * * Sincerely, * Jonathan Hornbaker. */ //Restritive Depth query = "SELECT Min(resdept_r) AS resdeptmin_r FROM corestrictions WHERE cokey='" + cokey + "' GROUP BY cokey"; rs = stmt.executeQuery(query); tmp = rs.next(); copyCoRestrictions(rs); rs.close(); if (updateProgress) { c_progress.setProgress(5); } /** * Documenting the official descriptions of all the components we are pulling. * * areaname - The name given to the specified geographic area. * areasymbol - A symbol that uniquely identifies a single occurrence * of a particular type of area (e.g. Lancaster * Co., Nebraska is NE109) * [cordate - The date the final correlation document for a * soil survey is signed, expressed as month, year (e.g. 07/1999).] * [ssurgoarchived - The date on which the SSURGO product for a particular * soil survey is actually archived, expressed * as month, day, year -- xx/xx/xxxx.] * tabularversion - A sequential integer number used to denote the * serial version of the tabular data for a soil survey area. * * * Parameters surrounded by [] appear to no longer be filled in, and * are essentially useless. * * Sincerely, * Jonathan Hornbaker. */ try { query = "SELECT areaname,areasymbol FROM legend WHERE lkey = '" + lkey + "'"; rs = stmt.executeQuery(query); } catch (SQLException sqle) { query = "SELECT areaname,areasymbol,cordate,ssurgoarchived,tabularversion FROM legend WHERE lkey = '" + lkey + "'"; rs = stmt.executeQuery(query); } tmp = rs.next(); ssaname = getValueFromRS(rs, "areaname", null); ssaid = getValueFromRS(rs, "areasymbol", null); /** * Documenting the official descriptions of all the components we are pulling. * * tabularversion - A sequential integer number used to denote the * serial version of the tabular data for * a soil survey area. * tabularverest - The date and time that a particular version * of tabular data for the soil survey * area was established * tabnasisexportdate - The date and time that soil survey area * tabular data was exported from NASIS. * * This table is a new access, to make up for the lack of properly * filled in parameters in the legend table. * * Sincerely, * Jonathan Hornbaker. */ try { query = "SELECT tabularversion,tabularverest,tabnasisexportdate FROM satabularver WHERE areasymbol = '" + ssaid + "'"; rs = stmt.executeQuery(query); }catch (SQLException sqle) { System.out.println("Tabular Version Failure: " + sqle); } tmp = rs.next(); copyLegend(rs); rs.close(); if (updateProgress) { c_progress.setProgress(6); } Statement stmtFrags = con.createStatement(); Statement stmtTexture = con.createStatement(); // query = new String("SELECT * FROM chorizon WHERE cokey = '"+cokey+"'"); /** * Documenting the official descriptions of all the components we are pulling. * * hzdept - The distance from the top of the soil to the upper * boundary of the soil horizon * hzdepb - The distance from the top of the soil to the base * of the soil horizon. * claytotal - Mineral particles less than 0.002mm in equivalent * diameter as a weight percentage of the less * than 2.0mm fraction. * sandtotal - Mineral particles 0.05mm to 2.0mm in equivalent * diameter as a weight percentage of the less * than 2 mm fraction. * silttotal - Mineral particles 0.002 to 0.05mm in equivalent * diameter as a weight percentage of the less * than 2.0mm fraction * sandvc - Mineral particles 1.0mm to 2.0mm in equivalent * diameter as a weight percentage of the less * than 2 mm fraction. * sandco - Mineral particles 0.5mm to 1.0mm in equivalent * diameter as a weight percentage of the less * than 2 mm fraction. * sandmed - Mineral particles 0.25mm to 0.5mm in equivalent * diameter as a weight percentage of the less * than 2 mm fraction. * sandfine - Mineral particles 0.10 to 0.25mm in equivalent * diameter as a weight percentage of the less * than 2 mm fraction * sandvf - Mineral particles 0.05 to 0.10mm in equivalent * diameter as a weight percentage of the less * than 2 mm fraction * dbthirdbar - The oven dry weight of the less than 2 mm soil * material per unit volume of soil at a water * tension of 1/3 bar. * wtenthbar - The volumetric content of soil water retained at a * tension of 1/10 bar (10 kPa), expressed as a * percentage of the whole soil. * wthirdbar - The volumetric content of soil water retained at a * tension of 1/3 bar (33 kPa), expressed as a * percentage of the whole soil. * wfifteenbar - The volumetric content of soil water retained at a * tension of 15 bars (1500 kPa), expressed as * a percentage of the whole soil. * cec7 - The amount of readily exchangeable cations that can * be electrically adsorbed to negative charges * in the soil, soil constituent, or other * material, at pH 7.0, as estimated by the * ammonium acetate method. * ecec - The sum of NH4OAc extractable bases plus KCl * extractable aluminum. * om - The amount by weight of decomposed plant and animal * residue expressed as a weight percentage of * the less than 2 mm soil material * caco3 - The quantity of Carbonate (CO3) in the soil expressed * as CaCO3 and as a weight percentage of the * less than 2 mm size fraction. * ph1to1h2o - The negative logarithm to the base 10, of the * hydrogen ion activity in the soil using the * 1:1 soil-water ratio method. A numerical * expression of the relative acidity or * alkalinity of a soil sample. (SSM) * ph01mcacl2 - The negative logarithm to base of 10 or the hydrogen * ion activity in the soil, using the 0.01M CaCl2 * method, in a 1:2 soil:solution ratio. A * numerical expression of the relative acidity * or alkalinity of a soil sample. (SSM) * lep - The linear expression of the volume difference of * natural soil fabric at 1/3 or 1/10 bar water * content and oven dryness. The volume change * is reported as percent change for the whole soil. * ksat - The amount of water that would move vertically * through a unit area of saturated soil in unit * time under unit hydraulic gradient. * chkey - A non-connotative string of characters used to * uniquely identify a record in the Horizon table. * desgnmaster - One of four kinds of symbols, that when concatenated, * are used to distinguish different kinds of * layers in soils. Master horizons and layers * are the base symbols to which other characters * are added to complete the designations. * Capital letters, virgules (/), and ampersands (&) * are used. (SSM) * hzname - The concatenated string of four kinds of symbols * (five data elements) used to distinguish * different kinds of layers in the soil. (SSM) * * Sincerely, * Jonathan Hornbaker. */ String[] hrzFlds = {"hzdept", "hzdepb", "claytotal", "sandtotal", "silttotal", "sandvc", "sandco", "sandmed", "sandfine", "sandvf", "dbthirdbar", "wtenthbar", "wthirdbar", "wfifteenbar", "cec7", "ecec", "om", "caco3", "ph1to1h2o", "ph01mcacl2", "lep", "ksat"}; StringBuilder sb = new StringBuilder("SELECT "); for (String hrzFld : hrzFlds) { sb.append(hrzFld + "_r,");//RV sb.append(hrzFld + "_l,");//Low sb.append(hrzFld + "_h,");//High } //hzdept_r, (hzdept_l + hzdept_h) / 2 sb.append("chkey, desgnmaster, hzname FROM chorizon WHERE cokey = '" + cokey + "' ORDER BY hzdept_r ASC"); // Order by depth to make sure layers are in the correct order. String chorizonQuery = sb.toString(); rs = stmt.executeQuery(chorizonQuery); int layerNum = 0; int i = 0; boolean foundAMineralLayer = false; //up to this amount is allowed to be ignored double maxOrganicDepth = ConfigData.getDefault().getSoilMaxOrganicDepth().doubleValue(SI.MILLIMETER); //as we loop over the layers, we add up how much organic layer has been ignored double usedOrganicDepth = 0; while (rs.next()) { chkey = rs.getString("chkey"); /** * Documenting the official descriptions of all the components we are pulling. * * texture - Name for the concatenation of TEXTURE_MODIFIER * and TEXTURE_CLASS * stratextsflag - A Boolean flag that when set (Y) indicates * that the textures that comprise a * particular texture group, are stratified. * rvindicator - A yes/no field that indicates if a value * or row (set of values) is representative * for the component * * Sincerely, * Jonathan Hornbaker. */ String chtexturegrpQuery = "SELECT texture, stratextsflag, rvindicator FROM chtexturegrp WHERE chkey = '" + chkey + "'"; try (ResultSet rsTexture = stmtTexture.executeQuery(chtexturegrpQuery)) { //do we ignore an organic surface? if (ConfigData.getDefault().isSkipOrganicSoilSurfaceLayers() && !foundAMineralLayer && usedOrganicDepth <= maxOrganicDepth) { String hzname = getValueFromRS(rs, "hzname", null); String desgnmaster = getValueFromRS(rs, "desgnmaster", null); double om = getValueFromRS(rs, "om"); //convert from percent to fraction om = om / 100d; String text = getValueFromRS(rsTexture, "texture", null); boolean organic = isLayerOrganic(hzname, desgnmaster, text, om); if (organic) { //data mart is in cm not mm double thicknessMiliMeter = getValueFromRS(rs, "hzthk", getValueFromRS(rs, "hzdepb", Double.NaN)) * 10; usedOrganicDepth += thicknessMiliMeter; if (usedOrganicDepth <= maxOrganicDepth) { //still allowed to ignore logMessage(WepsMessage.MessageSeverity.INFORMATION, "Organic layer ignored."); continue; } else { Measure max = Measure.valueOf(maxOrganicDepth, SI.MILLIMETER); Measure used = Measure.valueOf(usedOrganicDepth, SI.MILLIMETER); NumberFormat format = DecimalFormat.getNumberInstance(); boolean isSIUnits = Util.SIUnits.equals(ConfigData.getDefault().getData(ConfigData.Units)); Unit units = isSIUnits ? SI.MILLIMETER : NonSI.INCH; if (i == 0) { throw new OrganicSoilException( "The organic surface layer depth exceeded the maximum ignorable depth.\n" + format.format(used.doubleValue(units)) + units + " > " + format.format(max.doubleValue(units)) + units); } else { throw new OrganicSoilException( "The soil layer below the ignored organic layer is still organic."); } } } else { //not organic, so we're good to go foundAMineralLayer = true; } } copyHorizon(rs, layerNum); /** * Documenting the official descriptions of all the components we are pulling. * * fragvol - The volume percentage of the horizon occupied * by the 2 mm or larger fraction (20 mm or * larger for wood fragments), on a whole * soil base. * * Sincerely, * Jonathan Hornbaker. */ String chfragsQuery = "SELECT fragvol_r,fragvol_h,fragvol_l FROM chfrags WHERE chkey = '" + chkey + "'"; ResultSet rsFrags = stmtFrags.executeQuery(chfragsQuery); copyFrags(rsFrags, layerNum); rsFrags.close(); //already executed the query for the organic tests copyTexture(rsTexture, layerNum); layerNum++; } finally { i++; } } rs.close(); this.numlay = layerNum; //If the config option is set, average the stratified layers. if (ConfigData.getDefault().isAverageStratifiedSoilLayers()) { averageStratifiedLayers(); } if (updateProgress) { c_progress.setProgress(7); } fixUpData(); if (updateProgress) { c_progress.setProgress(8); } testData(); if (updateProgress) { c_progress.setProgress(10); } } catch (SQLException e) { e.printStackTrace(); // for (; e != null; e = e.getNextException()) { // //System.err.println("\n" + e); // e.printStackTrace(); // } } return true; } private static final String[] ORGANIC_TEXTURES = {"mpm", "mpt", "muck", "peat", "spm", "udom", "pdom", "hpm"}; private static boolean isLayerOrganic(String hzname, String desgnmaster, String texture, double organicMatter) { //horizon name if (hzname != null && hzname.startsWith("O")) { return true; } if (desgnmaster != null && desgnmaster.startsWith("O")) { return true; } for (String organicTexture : ORGANIC_TEXTURES) { if (organicTexture.equals(texture)) { return true; } } if (organicMatter > omFractionThreshold) { return true; } //aka, a mineral layer return false; } private static boolean estimateNullValues = true; /** * * @param flg */ public static void setEstimateNullValues(boolean flg) { estimateNullValues = flg; } /** * * @return */ public static boolean getEstimateNullValues() { return estimateNullValues; } private static double omFractionThreshold = .15; /** * * @param val */ public static void setOmFractionThreshold(double val) { omFractionThreshold = val; } /** * * @return */ public static double getOmFractionThreshold() { return omFractionThreshold; } }