@@ -1,4 +1,4 @@ |
- /* |
+/* |
*/ |
package usda.weru.soil; |
|
@@ -11,62 +11,87 @@ |
//public class SoilUI extends SoilUI_n implements ActionListener ,ItemListener |
//,HelpDelegator |
//,ItemListener |
+public class Soil { |
|
-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) |
- { |
+ |
+ /** |
+ * ************************************************************************** |
+ */ |
+ public static void main(String[] args) { |
int startingRow = 0; |
- try |
- { |
- if (args.length > 0) |
+ String connectString = ""; |
+ |
+ try { |
+ if (args.length > 0) { |
database = args[0]; |
- |
- if (args.length > 1) |
+ } |
+ |
+ if (args.length > 1) { |
startingRow = Integer.parseInt(args[1]); |
- |
+ if (args.length > 2) { |
+ connectString = args[2]; |
+ } |
+ } |
+ |
System.out.println("Starting row=" + startingRow); |
- |
- c_con = DriverManager.getConnection(conn + database, "postgres", ""); |
- |
+ 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 = "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 ='10374004' " + |
- "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); |
+ 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 ='10374004' " + |
+ "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 ='10374004' " + |
+ "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.relative(startingRow); |
- |
- int i=1; |
+ |
+ int i = 1; |
String cokey = ""; |
System.out.println("any records?"); |
- while (rs.next()) { |
- try |
- { |
+ while (rs.next()) { |
+ try { |
NASIS nasis = new NASIS(c_con); |
cokey = rs.getString(9); |
String filename = convertCoKey(rs.getString(9)); |
@@ -77,7 +102,7 @@ |
i++; |
//if (i>100) break; |
boolean valid = nasis.loadSQL(cokey); |
- if(!valid) { |
+ if (!valid) { |
System.err.println("error reading soil record"); |
return; |
} |
@@ -87,47 +112,41 @@ |
|
// 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()) |
+ 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) |
- { |
+ } |
+ |
+ 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) + "');"); |
+ 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) |
- { |
+ } 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) |
- { |
+ } 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(":","_"); |
+ private static String convertCoKey(String coKeyIn) { |
+ return coKeyIn.replace(":", "_"); |
} |
} |