| @@ -22,6 +22,7 @@ |
| import org.codehaus.jettison.json.JSONArray; |
| import org.codehaus.jettison.json.JSONException; |
| import org.codehaus.jettison.json.JSONObject; |
| +import rhem.utils.DBQueries; |
| import rhem.utils.DBResources; |
| |
| /** |
| @@ -81,23 +82,23 @@ |
| GISObjectFactory.setGISObjectEngine(GISEngineFactory.createGISEngine(connection)); |
| 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;"; |
| - try (ResultSet resultSet = statement.executeQuery(query)) { |
| +// 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;"; |
| + try (ResultSet resultSet = statement.executeQuery(DBQueries.RHEM03Query01(geometry))) { |
| while (resultSet.next()) { |
| mukey = resultSet.getString("mukey"); |
| } |
| } |
| if (mukey != null) { |
| - query = "SELECT musym, muname FROM ssurgo.mapunit WHERE mukey ='" + mukey + "';"; |
| +// query = "SELECT musym, muname FROM ssurgo.mapunit WHERE mukey ='" + mukey + "';"; |
| String musym = null; |
| String muname = null; |
| - try (ResultSet resultSet = statement.executeQuery(query)) { |
| + try (ResultSet resultSet = statement.executeQuery(DBQueries.RHEM03Query02(mukey))) { |
| while (resultSet.next()) { |
| musym = resultSet.getString("musym"); |
| muname = resultSet.getString("muname"); |
| @@ -105,11 +106,11 @@ |
| } |
| |
| 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(query)) { |
| +// 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"), |
| @@ -138,26 +139,26 @@ |
| 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 query = "SELECT ecoclassid " |
| +// + "FROM ssurgo.coecoclass " |
| +// + "WHERE cokey = '" + component.getCokey() |
| +// + "' AND ecoclassid LIKE 'R%';"; |
| String ecoclassid = null; |
| - try (ResultSet resultSet = statement.executeQuery(query)) { |
| + try (ResultSet resultSet = statement.executeQuery(DBQueries.RHEM03Query04(component.getCokey()))) { |
| while (resultSet.next()) { |
| ecoclassid = resultSet.getString("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 + "';"; |
| +// 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(query)) { |
| + try (ResultSet resultSet = esdStmt.executeQuery(DBQueries.RHEM03Query05(ecoclassid))) { |
| while (resultSet.next()) { |
| ecoList.add(new EcologicalSite(ecoclassid, |
| resultSet.getString("es_range_name"))); |
| @@ -179,23 +180,23 @@ |
| 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(query);) { |
| +// 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));) { |
| 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';"; |
| +// 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(q);) { |
| + ResultSet rst = stmt.executeQuery(DBQueries.RHEM03Query07(texcl));) { |
| while (rst.next()) { |
| int textureId = rst.getInt("texture_subclass_id"); |
| String textureClass = rst.getString("text_abreviation"); |