@@ -24,6 +24,7 @@ |
import org.codehaus.jettison.json.JSONObject; |
import rhem.utils.DBQueries; |
import rhem.utils.DBResources; |
+import static rhem.utils.DBResources.MSSQL_RHEM; |
|
/** |
* @version 1.0 |
@@ -65,62 +66,52 @@ |
|
@Override |
public void doProcess() throws ServiceException { |
- try (Connection connection = getResourceJDBC(DBResources.MSSQL_RHEM);) { |
- this.getMapunitValues(connection); |
- this.getComponentValues(connection); |
- this.getSurfaceTextureClass(connection); |
+ try (Connection sdmConnection = getResourceJDBC(DBResources.MSSQL_SDM); |
+ Connection esdConnection = getResourceJDBC(DBResources.MSSQL_ESD); |
+ Connection rhemConnection = getResourceJDBC(DBResources.MSSQL_RHEM);) { |
+ this.getMapunitValues(sdmConnection); |
+ this.getComponentValues(sdmConnection, esdConnection); |
+ this.getSurfaceTextureClass(sdmConnection, rhemConnection); |
} catch (ServiceException | SQLException se) { |
LOG.log(Level.SEVERE, "RHEM-03: SQLException.", se); |
throw new ServiceException("SQL problem.", se); |
} |
} |
|
- private void getMapunitValues(Connection connection) throws ServiceException { |
- try (Statement statement = connection.createStatement();) { |
+ private void getMapunitValues(Connection sdmConnection) throws ServiceException { |
+ |
+ try (Statement statement = sdmConnection.createStatement();) { |
// Sets the GIS Engine of the GISObjects to the DBMS specified in |
//the creation of the “connection” variable. MsSQL in this case |
- GISObjectFactory.setGISObjectEngine(GISEngineFactory.createGISEngine(connection)); |
+ GISObjectFactory.setGISObjectEngine(GISEngineFactory.createGISEngine(sdmConnection)); |
GISObject geometry = GISObjectFactory.createGISObject(rhemSiteGeometryPoint); |
String mukey = null; |
-// String query = "SELECT mukey " |
-// + "FROM ssurgo.soilmu_a " |
-// + "WITH (index(geom_sidx)) " |
-// + "WHERE the_geom.STContains(geometry::STGeomFromText('" |
-// + geometry.getGeometry() + "', 0))=1 " |
-// + "AND (geometry::STGeomFromText('" |
-// + geometry.getGeometry() + "', 0).STIsValid())=1;"; |
+ |
+ String query = "SELECT mupoly.mukey, mapunit.musym, mapunit.muname FROM dbo.mupolygon AS mupoly " |
+ + "WITH (index(SI_mupolygon_24876)) " |
+ + "JOIN dbo.mapunit AS mapunit ON " |
+ + "mupoly.mukey = mapunit.mukey " |
+ + "WHERE mupoly.mupolygongeo.STIntersects( geometry::STGeomFromText('" |
+ + geometry.getGeometry() + "', 4326)) = 1;"; |
+ String musym = null; |
+ String muname = null; |
try (ResultSet resultSet = statement.executeQuery(DBQueries.RHEM03Query01(geometry))) { |
while (resultSet.next()) { |
mukey = resultSet.getString("mukey"); |
+ musym = resultSet.getString("musym"); |
+ muname = resultSet.getString("muname"); |
} |
} |
if (mukey != null) { |
-// query = "SELECT musym, muname FROM ssurgo.mapunit WHERE mukey ='" + mukey + "';"; |
- String musym = null; |
- String muname = null; |
+ ArrayList<Component> componentList = new ArrayList<>(); |
try (ResultSet resultSet = statement.executeQuery(DBQueries.RHEM03Query02(mukey))) { |
while (resultSet.next()) { |
- musym = resultSet.getString("musym"); |
- muname = resultSet.getString("muname"); |
- } |
- } |
- |
- ArrayList<Component> componentList = new ArrayList<>(); |
-// query = "SELECT cokey, compname, comppct_r, slope_l, slope_r, " |
-// + "slope_h "//slopelenusle_l, slopelenusle_r, slopelenusle_h " |
-// + "FROM ssurgo.component " |
-// + "WHERE mukey ='" + mukey + "' ORDER BY comppct_r DESC;"; |
- try (ResultSet resultSet = statement.executeQuery(DBQueries.RHEM03Query03(mukey))) { |
- while (resultSet.next()) { |
componentList.add(new Component(resultSet.getString("cokey"), |
resultSet.getString("compname"), |
resultSet.getInt("comppct_r"), |
resultSet.getInt("slope_l"), |
resultSet.getInt("slope_r"), |
resultSet.getInt("slope_h"))); |
-// resultSet.getDouble("slopelenusle_l"), |
-// resultSet.getDouble("slopelenusle_r"), |
-// resultSet.getDouble("slopelenusle_h") |
} |
} |
mapunitList.add(new Mapuint(musym, muname, componentList)); |
@@ -131,34 +122,21 @@ |
} |
} |
|
- private void getComponentValues(Connection connection) throws ServiceException { |
- try (Statement statement = connection.createStatement(); |
- Connection esdConnection = getResourceJDBC(DBResources.MSSQL_ESD); |
- Statement esdStmt = esdConnection.createStatement();) { |
+ private void getComponentValues(Connection connection, Connection esdConnection) throws ServiceException { |
+ try (Statement statement = connection.createStatement();) { |
for (Mapuint mapuint : this.mapunitList) { |
for (Component component : mapuint.getComponentList()) { |
ArrayList<EcologicalSite> ecoList = new ArrayList<>(); |
- |
-// String query = "SELECT ecoclassid " |
-// + "FROM ssurgo.coecoclass " |
-// + "WHERE cokey = '" + component.getCokey() |
-// + "' AND ecoclassid LIKE 'R%';"; |
String ecoclassid = null; |
- try (ResultSet resultSet = statement.executeQuery(DBQueries.RHEM03Query04(component.getCokey()))) { |
+ try (ResultSet resultSet = statement.executeQuery(DBQueries.RHEM03Query03(component.getCokey()))) { |
while (resultSet.next()) { |
ecoclassid = resultSet.getString("ecoclassid"); |
} |
} |
+ if (ecoclassid != null) { |
+ try (Statement esdStmt = esdConnection.createStatement(); |
+ ResultSet resultSet = esdStmt.executeQuery(DBQueries.RHEM03Query04(ecoclassid));) { |
|
- if (ecoclassid != null) { |
-// query = "SELECT concat(range_site_primary_name, ' ', " |
-// + "range_site_secondary_name, ' ', range_site_tertiary_name) " |
-// + "AS es_range_name " |
-// + "FROM esd.ecological_sites " |
-// + "WHERE concat(es_type, es_mlra, es_mlru, es_site_number, es_state) = '" |
-// + ecoclassid + "';"; |
- |
- try (ResultSet resultSet = esdStmt.executeQuery(DBQueries.RHEM03Query05(ecoclassid))) { |
while (resultSet.next()) { |
ecoList.add(new EcologicalSite(ecoclassid, |
resultSet.getString("es_range_name"))); |
@@ -174,29 +152,16 @@ |
} |
} |
|
- private void getSurfaceTextureClass(Connection connection) throws ServiceException { |
+ private void getSurfaceTextureClass(Connection connection, Connection rhemConnection) throws ServiceException { |
try (Statement statement = connection.createStatement();) { |
for (Mapuint mapuint : this.mapunitList) { |
for (Component component : mapuint.getComponentList()) { |
ArrayList<SurfaceTexture> surfaceTextureList = new ArrayList<>(); |
- |
-// String query = "SELECT texcl FROM ssurgo.chtexture " |
-// + "WHERE chtgkey IN " |
-// + "(SELECT chtgkey FROM ssurgo.chtexturegrp WHERE chkey IN " |
-// + "(SELECT chkey FROM ssurgo.chorizon WHERE cokey = '" |
-// + component.getCokey() + "' AND hzdept_r = 0));"; |
- try (ResultSet resultSet = statement.executeQuery(DBQueries.RHEM03Query06(component.cokey));) { |
+ try (ResultSet resultSet = statement.executeQuery(DBQueries.RHEM03Query05(component.cokey));) { |
while (resultSet.next()) { |
String texcl = resultSet.getString("texcl"); |
- |
-// String q = "SELECT d2.texture_subclass_id, d3.text_abreviation, d3.text_label " |
-// + "FROM rhem.d_rhem_text_lookup d1 " |
-// + "JOIN rhem.d_rhem_texture_class_subclass d2 ON (d1.text_id = d2.text_subclass_id) " |
-// + "JOIN rhem.d_rhem_text_lookup d3 ON (d2.text_class_id = d3.text_id) " |
-// + "WHERE d1.text_label = '" + texcl + "' AND d2.obsolete = 'false';"; |
- |
- try (Statement stmt = connection.createStatement(); |
- ResultSet rst = stmt.executeQuery(DBQueries.RHEM03Query07(texcl));) { |
+ try (Statement stmt = rhemConnection.createStatement(); |
+ ResultSet rst = stmt.executeQuery(DBQueries.RHEM03Query06(texcl));) { |
while (rst.next()) { |
int textureId = rst.getInt("texture_subclass_id"); |
String textureClass = rst.getString("text_abreviation"); |
@@ -237,12 +202,6 @@ |
componentArr.put(JSONUtils.dataDesc("slopepct_l", component.getSlopePctL(), "Slope Percent – Low Value")); |
componentArr.put(JSONUtils.dataDesc("slopepct_r", component.getSlopePctR(), "Slope Percent – Representative Value")); |
componentArr.put(JSONUtils.dataDesc("slopepct_h", component.getSlopePctH(), "Slope Percent – High Value")); |
-// componentArr.put(JSONUtils.dataDesc("slopelen_l", Math.round(component.getSlopeLenL()), "Slope Length (meters) – Low Value")); |
-// componentArr.put(JSONUtils.dataDesc("slopelen_r", Math.round(component.getSlopeLenR()), "Slope Length (meters) – Representative Value")); |
-// componentArr.put(JSONUtils.dataDesc("slopelen_h", Math.round(component.getSlopeLenH()), "Slope Length (meters) – High Value")); |
-// componentArr.put(JSONUtils.dataDesc("slopelen_ft_l", Math.round(component.getSlopeLenFtL()), "Slope Length (feet) – Low Value")); |
-// componentArr.put(JSONUtils.dataDesc("slopelen_ft_r", Math.round(component.getSlopeLenFtR()), "Slope Length (feet) – Representative Value")); |
-// componentArr.put(JSONUtils.dataDesc("slopelen_ft_h", Math.round(component.getSlopeLenFtH()), "Slope Length (feet) – High Value")); |
|
JSONArray ecoSiteArray = new JSONArray(); |
for (EcologicalSite ecoSite : component.getEcoSiteList()) { |
@@ -263,7 +222,6 @@ |
surfaceTestureArray.put(JSONUtils.dataDesc("surface_texture_class", surfaceTextureArr, "Soil Component Surface Texture Class")); |
} |
componentArr.put(JSONUtils.dataDesc("surface_texture_class_list", surfaceTestureArray, "Soil Component Surface Texture Class List")); |
- |
componentArray.put(JSONUtils.dataDesc("component", componentArr, "Component")); |
} |
mapuintArr.put(JSONUtils.dataDesc("component_list", componentArray, "Component List")); |
@@ -53,40 +53,41 @@ |
} |
|
public static String RHEM03Query01(GISObject geometry) { |
- String query = "SELECT mukey " |
- + "FROM ssurgo.soilmu_a " |
- + "WITH (index(geom_sidx)) " |
- + "WHERE the_geom.STContains(geometry::STGeomFromText('" |
- + geometry.getGeometry() + "', 0))=1 " |
- + "AND (geometry::STGeomFromText('" |
- + geometry.getGeometry() + "', 0).STIsValid())=1;"; |
+// String query = "SELECT mukey " |
+// + "FROM ssurgo.soilmu_a " |
+// + "WITH (index(geom_sidx)) " |
+// + "WHERE the_geom.STContains(geometry::STGeomFromText('" |
+// + geometry.getGeometry() + "', 0))=1 " |
+// + "AND (geometry::STGeomFromText('" |
+// + geometry.getGeometry() + "', 0).STIsValid())=1;"; |
|
+ String query = "SELECT mupoly.mukey, mapunit.musym, mapunit.muname " |
+ + "FROM dbo.mupolygon AS mupoly " |
+ + "WITH (index(SI_mupolygon_24876)) " |
+ + "JOIN dbo.mapunit AS mapunit " |
+ + "ON mupoly.mukey = mapunit.mukey " |
+ + "WHERE mupoly.mupolygongeo.STIntersects( geometry::STGeomFromText('" |
+ + geometry.getGeometry() + "', 4326)) = 1;"; |
return query; |
} |
|
public static String RHEM03Query02(String mukey) { |
- String query = "SELECT musym, muname FROM ssurgo.mapunit WHERE mukey ='" + mukey + "';"; |
- |
- return query; |
- } |
- |
- public static String RHEM03Query03(String mukey) { |
String query = "SELECT cokey, compname, comppct_r, slope_l, slope_r, slope_h " |
- + "FROM ssurgo.component " |
+ + "FROM dbo.component " |
+ "WHERE mukey ='" + mukey + "' ORDER BY comppct_r DESC;"; |
return query; |
} |
|
- public static String RHEM03Query04(String cokey) { |
+ public static String RHEM03Query03(String cokey) { |
String query = "SELECT ecoclassid " |
- + "FROM ssurgo.coecoclass " |
+ + "FROM dbo.coecoclass " |
+ "WHERE cokey = '" + cokey |
+ "' AND ecoclassid LIKE 'R%';"; |
|
return query; |
} |
|
- public static String RHEM03Query05(String ecoclassid) { |
+ public static String RHEM03Query04(String ecoclassid) { |
String query = "SELECT concat(range_site_primary_name, ' ', " |
+ "range_site_secondary_name, ' ', range_site_tertiary_name) " |
+ "AS es_range_name " |
@@ -96,16 +97,16 @@ |
return query; |
} |
|
- public static String RHEM03Query06(String cokey) { |
- String query = "SELECT texcl FROM ssurgo.chtexture " |
+ public static String RHEM03Query05(String cokey) { |
+ String query = "SELECT texcl FROM dbo.chtexture " |
+ "WHERE chtgkey IN " |
- + "(SELECT chtgkey FROM ssurgo.chtexturegrp WHERE chkey IN " |
- + "(SELECT chkey FROM ssurgo.chorizon WHERE cokey = '" |
+ + "(SELECT chtgkey FROM dbo.chtexturegrp WHERE chkey IN " |
+ + "(SELECT chkey FROM dbo.chorizon WHERE cokey = '" |
+ cokey + "' AND hzdept_r = 0));"; |
return query; |
} |
|
- public static String RHEM03Query07(String texcl) { |
+ public static String RHEM03Query06(String texcl) { |
String query = "SELECT d2.texture_subclass_id, d3.text_abreviation, d3.text_label " |
+ "FROM rhem.d_rhem_text_lookup d1 " |
+ "JOIN rhem.d_rhem_texture_class_subclass d2 ON (d1.text_id = d2.text_subclass_id) " |