Displaying differences for changeset
 
display as  

src/java/m/rhem/rhem03_compEsd/V1_0.java

@@ -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"));

src/java/rhem/utils/DBQueries.java

@@ -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) "

src/java/rhem/utils/DBResources.java

@@ -21,8 +21,15 @@
     + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;"
     + "org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer"
 })
+@Resource(type = JDBC, file = "${SDM.db}", id = MSSQL_SDM, env = {
+    "removeAbandoned=false", "defaultReadOnly=true", "defaultAutoCommit=false",
+    "jdbcInterceptors=org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
+    + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;"
+    + "org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer"
+})
 
 public interface DBResources {
     static final String MSSQL_RHEM = "mssql-rhem";
     static final String MSSQL_ESD = "mssql-esd";
+    static final String MSSQL_SDM = "sdm";
 }

web/META-INF/csip-conf.json

@@ -2,8 +2,8 @@
     "csip.archive.max.filesize": "1KB",
     "mssql-rhem.db": "jdbc:sqlserver:\/\/129.82.20.241:1433;databaseName=conservation_resources;user=sa;password=csurams#1",
     "mssql-esd.db": "jdbc:sqlserver:\/\/129.82.20.241:1433;databaseName=esd;user=sa;password=csurams#1",
+    "SDM.db":"jdbc:sqlserver://129.82.20.129:1433;databaseName=sdmOnline;user=sa;password=csurams#1",
     "wine.path":"/usr/local/bin/wine",
-    "csip.session.backend":"mongodb",
     "csip.session.ttl": "PT300S",
     "csip.session.ttl.failed": "PT3000S"
 }