Soil.java [tools/WepsSoilsIfcCreator/src/usda/weru/soil] Revision: default Date:
/*
*/
package usda.weru.soil;
import java.io.File;
import java.lang.ClassLoader.*;
import java.sql.*;
//main() is in this function
//based on classes from WEPS GUI Java code: extends usda.weru.soil.gui.SoilUI_n implements PropertyChangeListener //implements ActionListener ,ItemListener
//public class SoilUI extends SoilUI_n implements ActionListener ,ItemListener
//,HelpDelegator
//,ItemListener
public class Soil {
//protected static String conn = "jdbc:postgresql://129.82.225.74:5432/";
//protected static String conn = "jdbc:postgresql://localhost:5432/";
protected static String conn = "jdbc:postgresql://csip.engr.colostate.edu:5435/";
protected static String database = "ssurgo";
protected static String username = "postgres";
protected static String password = "";
protected static Connection c_con;
protected static String componentKey = "1008427:1454414";
/**
* **************************************************************************
*/
public static void main(String[] args) {
int startingRow = 0;
String connectString = "";
try {
if (args.length > 0) {
database = args[0];
}
if (args.length > 1) {
startingRow = Integer.parseInt(args[1]);
if (args.length > 2) {
connectString = args[2];
}
}
System.out.println("Starting row=" + startingRow);
if (connectString.isEmpty()) {
c_con = DriverManager.getConnection(conn + database, "postgres", "");
} else {
c_con = DriverManager.getConnection(connectString);
}
// OLD soils query before adding new phacil fields
// String query = "SELECT soilmu_a.areasymbol as co_fips, soilmu_a.musym, component.mukey, cokey " +
// "FROM ssurgo.component join ssurgo.soilmu_a on component.mukey = soilmu_a.mukey " +
//// "WHERE to_number(component.mukey, '999999999') >= 382040 and to_number(component.mukey, '999999999') <= 382050 " +
// "GROUP BY soilmu_a.areasymbol, soilmu_a.musym, component.mukey, component.cokey;";
String query = "";
if (c_con.getMetaData().getDatabaseProductName().contains("Microsoft")) {
query = "SELECT DISTINCT legend.areasymbol as co_fips, mapunit.musym, component.mukey, muname, compname, chorizon.kffact, component.tfact, component.slope_r, component.cokey "
+ "FROM ssurgo.component LEFT OUTER JOIN ssurgo.mapunit ON (component.mukey = mapunit.mukey) "
+ "LEFT OUTER JOIN ssurgo.legend ON (legend.lkey = mapunit.lkey) "
+ "LEFT OUTER JOIN ssurgo.chorizon ON (component.cokey = chorizon.cokey) "
+ "LEFT OUTER JOIN ssurgo.chtexturegrp ON (chtexturegrp.chkey = chorizon.chkey) "
+ "LEFT OUTER JOIN ssurgo.chtexture ON (chtexturegrp.chtgkey = chtexture.chtgkey) "
+ "WHERE ((component.taxorder = 'Histosols') OR "
+ "( (NOT ( taxsubgrp like '%Histic%')) OR (taxsubgrp is null))) "
+ "AND component.comppct_r > 15 "
+ // for testing specific cokeys
//" and component.cokey ='10988756' " +
"AND hzdept_r IN (select hzdept_r from ssurgo.minhzdept_t where minhzdept_t.cokey=component.cokey and minhzdept_t.areasymbol=legend.areasymbol and minhzdept_t.musym=mapunit.musym and minhzdept_t.mukey=mapunit.mukey) "
+ "order by co_fips, musym, component.mukey;";
} else {
query = "SELECT distinct legend.areasymbol as co_fips, mapunit.musym, component.mukey, muname, compname, chorizon.kffact, component.tfact, component.slope_r, component.cokey "
+ "FROM ssurgo.component LEFT OUTER JOIN ssurgo.mapunit ON (component.mukey = mapunit.mukey) "
+ "LEFT OUTER JOIN ssurgo.legend ON (legend.lkey = mapunit.lkey) "
+ "LEFT OUTER JOIN ssurgo.chorizon ON (component.cokey = chorizon.cokey) "
+ "LEFT OUTER JOIN ssurgo.chtexturegrp ON (chtexturegrp.chkey = chorizon.chkey) "
+ "LEFT OUTER JOIN ssurgo.chtexture ON (chtexturegrp.chtgkey = chtexture.chtgkey) "
+ "where ((component.taxorder = 'Histosols') or "
+ "((position('Histic' in taxsubgrp) <= 0) or (taxsubgrp is null))) "
+ "and component.comppct_r > 15 "
+ // for testing specific cokeys
//" and component.cokey ='10988756' " +
"and hzdept_r in (select hzdept_r from ssurgo.minhzdept_t where minhzdept_t.cokey=component.cokey and minhzdept_t.areasymbol=legend.areasymbol and minhzdept_t.musym=mapunit.musym and minhzdept_t.mukey=mapunit.mukey) "
+ "order by co_fips, musym, component.mukey;";
}
Statement stmt = c_con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(query);
rs.absolute(startingRow);
int i = 1;
String cokey = "";
System.out.println("any records?");
while (rs.next()) {
try {
NASIS nasis = new NASIS(c_con);
cokey = rs.getString(9);
String filename = convertCoKey(rs.getString(9));
String stateAbbrev = rs.getString(1).substring(0, 2);
System.out.println("stateAbbrev=" + stateAbbrev);
System.out.println("Processing soil " + i + " with cokey=" + cokey);
i++;
//if (i>100) break;
boolean valid = nasis.loadSQL(cokey);
if (!valid) {
System.err.println("error reading soil record");
return;
}
IFC ifc = new IFC(nasis);
ifc.sendErrorsToInternalVariable();
ifc.getLog().mergeLog(nasis.getLog());
// comment this out for testing to avoid writing soils ifcs and generating insert statements, supports counting errors in the data:
File thedir = new File("soils/" + stateAbbrev);
if (!thedir.exists()) {
thedir.mkdirs();
}
if (ifc.writeNewIfc("soils/" + stateAbbrev + "/" + filename)) {
System.out.println("INSERT into MAP_SOILS_WEPS values ('" + rs.getString(1) + "','" + rs.getString(2) + "','" + rs.getString(3)
+ "','soils/" + stateAbbrev + "','" + filename + "', '" + rs.getString(4)
+ "','" + rs.getString(5) + "','" + rs.getString(6) + "','" + rs.getString(7)
+ "','" + rs.getString(8) + "','" + rs.getString(9) + "');");
}
} catch (OrganicSoilException ose) {
System.err.println(ose.getMessage());
System.err.println("Organic Soil Exception caught.\nIgnoring error and skipping .ifc file generation for soil component=" + cokey);
System.err.println("Will generate pointer to default organic soil .ifc file instead.");
System.out.println("INSERT into MAP_SOILS_WEPS values ('" + rs.getString(1) + "','" + rs.getString(2) + "','" + rs.getString(3)
+ "','soils/NRCS%20Generic%20Soils/" + "','Organic%20Soil', '" + rs.getString(4)
+ "','" + rs.getString(5) + "','" + rs.getString(6) + "','" + rs.getString(7)
+ "','" + rs.getString(8) + "','" + rs.getString(9) + "');");
ose.printStackTrace(System.err);
} catch (InvalidSoilException ise) {
System.err.println(ise.getMessage());
System.err.println("Invalid Soil Exception caught.\nIgnoring error and skipping soil component=" + cokey);
ise.printStackTrace(System.err);
}
}
System.out.println("end processing.");
} catch (SQLException se) {
System.err.println("Error connecting to ssurgo db.");
System.err.println(se.toString());
}
}
// Converts (soil) component key to a format to use for the output (ifc) file
// Removes the "_"
private static String convertCoKey(String coKeyIn) {
return coKeyIn.replace(":", "_");
}
}