| @@ -17,6 +17,7 @@ |
| import org.codehaus.jettison.json.JSONArray; |
| import org.codehaus.jettison.json.JSONException; |
| import org.codehaus.jettison.json.JSONObject; |
| + |
| /** |
| * |
| * @author Sandeep |
| @@ -28,17 +29,12 @@ |
| |
| public class V1_0 extends ModelDataService { |
| |
| - private Connection conn; |
| - private Statement statement; |
| - |
| private String error_msg; |
| private ArrayList<AoA> list; |
| |
| @Override |
| public void preProcess() { |
| this.error_msg = ""; |
| - this.conn = null; |
| - this.statement = null; |
| String points; |
| String finalpoints; |
| JSONArray aoaArr; |
| @@ -83,34 +79,33 @@ |
| String rFactor; |
| ArrayList<String> rfactorList = new ArrayList<>(); |
| |
| - String confString = Config.getString("rse-db","r2gis"); |
| + String confString = Config.getString("rse-db", "r2gis"); |
| if (this.error_msg.isEmpty()) { |
| - try { |
| - this.conn = wqm.utils.WQMTools.getConnection(confString, LOG); |
| - statement = this.conn.createStatement(); |
| + try ( |
| + Connection conn = wqm.utils.WQMTools.getConnection(confString, LOG); |
| + Statement statement = conn.createStatement();) { |
| |
| for (AoA list1 : this.list) { |
| String tPoints = list1.getCoordinates(); |
| String gisQuery = ""; |
| - switch (confString) |
| - { |
| + switch (confString) { |
| // ms-sql server |
| case "rse-sql": |
| - gisQuery = "SELECT TOP 1 m.co_fips, r2_path, r2_name, r_factor, g.geometry " + |
| - "FROM r2gis.map_climates AS m, r2gis.cli_geom AS g " + |
| - "WITH (Index(cli_geom_idx)) " + |
| - "WHERE " + |
| - "g.geometry.STIntersects(geometry::STPolyFromText('POLYGON((" + tPoints + "))',0)) = 1 " + |
| - "AND ((g.co_fips = m.co_fips and g.geometry.STIsValid()=1 and m.ei_rang is null) " + |
| - "or (g.co_fips = m.co_fips and g.ei_rang = m.ei_rang and m.ei_rang is not null and g.geometry.STIsValid()=1)) " + |
| - "and g.geometry.STIsValid()=1 and geometry::STPolyFromText('POLYGON((" + tPoints + "))',0).STIsValid()=1;"; |
| + gisQuery = "SELECT TOP 1 m.co_fips, r2_path, r2_name, r_factor, g.geometry " |
| + + "FROM r2gis.map_climates AS m, r2gis.cli_geom AS g " |
| + + "WITH (Index(cli_geom_idx)) " |
| + + "WHERE " |
| + + "g.geometry.STIntersects(geometry::STPolyFromText('POLYGON((" + tPoints + "))',0)) = 1 " |
| + + "AND ((g.co_fips = m.co_fips and g.geometry.STIsValid()=1 and m.ei_rang is null) " |
| + + "or (g.co_fips = m.co_fips and g.ei_rang = m.ei_rang and m.ei_rang is not null and g.geometry.STIsValid()=1)) " |
| + + "and g.geometry.STIsValid()=1 and geometry::STPolyFromText('POLYGON((" + tPoints + "))',0).STIsValid()=1;"; |
| break; |
| // postgresql - postgis |
| case "r2gis": |
| // gisQuery = "SELECT m.co_fips, r2_path, r2_name, m.ei_rang, m.r_factor, st_area(st_intersection(ST_PolygonFromText('POLYGON((" + tPoints + "))'), geometry)) / st_area(ST_PolygonFromText('POLYGON((" + tPoints + "))'))*100 as percentAoi,st_area(st_intersection(ST_PolygonFromText('POLYGON((" + tPoints + "))'), geometry))/43560 as sizeAoi FROM cli_geom AS g, map_climates AS m WHERE ST_Intersects(ST_PolygonFromText('POLYGON((" + tPoints + "))'), g.geometry) AND ((g.co_fips = m.co_fips and st_isvalid(geometry)='t' and m.ei_rang is null) or (g.co_fips = m.co_fips and g.ei_rang = m.ei_rang and m.ei_rang is not null and st_isvalid(geometry)='t')) and st_isvalid(geometry)='t' and st_isvalid(ST_PolygonFromText('POLYGON((" + tPoints + "))'))='t';"; |
| // why do the complex percentAoi and sizeAoi calculations for intersecting with climate shapes here? |
| // this is not returned by the rfactor service ! |
| - gisQuery = "SELECT m.co_fips, r2_path, r2_name, m.ei_rang, m.r_factor FROM cli_geom AS g, map_climates AS m WHERE ST_Intersects(ST_PolygonFromText('POLYGON((" + tPoints + "))'), g.geometry) AND ((g.co_fips = m.co_fips and st_isvalid(geometry)='t' and m.ei_rang is null) or (g.co_fips = m.co_fips and g.ei_rang = m.ei_rang and m.ei_rang is not null and st_isvalid(geometry)='t')) and st_isvalid(geometry)='t' and st_isvalid(ST_PolygonFromText('POLYGON((" + tPoints + "))'))='t';"; |
| + gisQuery = "SELECT m.co_fips, r2_path, r2_name, m.ei_rang, m.r_factor FROM cli_geom AS g, map_climates AS m WHERE ST_Intersects(ST_PolygonFromText('POLYGON((" + tPoints + "))'), g.geometry) AND ((g.co_fips = m.co_fips and st_isvalid(geometry)='t' and m.ei_rang is null) or (g.co_fips = m.co_fips and g.ei_rang = m.ei_rang and m.ei_rang is not null and st_isvalid(geometry)='t')) and st_isvalid(geometry)='t' and st_isvalid(ST_PolygonFromText('POLYGON((" + tPoints + "))'))='t';"; |
| break; |
| } |
| LOG.info("RFACTOR SQL=" + gisQuery); |
| @@ -136,14 +131,7 @@ |
| } |
| } |
| |
| - if (this.statement != null) { |
| - this.statement.close(); |
| - this.statement = null; |
| - } |
| - if (this.conn != null) { |
| - this.conn.close(); |
| - this.conn = null; |
| - } |
| + |
| } catch (Exception ex) { |
| this.error_msg = "Cannot process that request: " + ex.getMessage(); |
| LOG.log(Level.SEVERE, this.error_msg); |
| @@ -219,7 +207,6 @@ |
| // |
| // return ret_val; |
| // } |
| - |
| public class AoA { |
| |
| private final String aoa_id; |