SqlGIS.java [src/java/c] Revision: 34471c588da17ba0ac02e5f0853ba5daf2074dcb Date: Mon Apr 10 21:52:18 MDT 2017
package c;
import csip.ServiceException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
public class SqlGIS implements GIS_DB {
static final Logger logger = Logger.getLogger(SqlGIS.class.getName());
Connection connection;
public SqlGIS(Connection c) throws Exception {
connection = c;
}
@Override
public void close() throws SQLException {
connection.close();
}
private GIS_DB.FileQryResult findFile(String select) throws SQLException {
GIS_DB.FileQryResult res = null;
try (Statement s = connection.createStatement();) {
logger.info("query sql=" + select);
try (ResultSet r = s.executeQuery(select)) {
int cols = r.getMetaData().getColumnCount();
if (cols != 3) {
logger.severe("invalid columns in soil query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
logger.severe("no result from soil query!");
} else {
res = new GIS_DB.FileQryResult();
res.id = r.getString(1);
res.file_path = r.getString(2);
res.file_name = r.getString(3);
logger.info("id=" + res.id);
logger.info("r2_path=" + res.file_path);
logger.info("r2_name=" + res.file_name);
}
}
}
return res;
}
private String findCmzName(String select, int colcnt) throws SQLException {
String ret_val = "0";
try (Statement s = connection.createStatement()) {
logger.info("query sql=" + select);
try (ResultSet r = s.executeQuery(select)) {
int cols = r.getMetaData().getColumnCount();
if (cols != colcnt) {
logger.severe("invalid number of columns returned in find dbl query:" + select);
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
logger.severe("no result from find dbl query!");
} else {
ret_val = r.getString(1);
logger.info("value=" + ret_val);
}
return ret_val;
}
}
}
private Collection<GIS_DB.Soil> findPolygons(String select) throws SQLException {
LinkedList<GIS_DB.Soil> soilLst = new LinkedList<GIS_DB.Soil>();
try (Statement s = connection.createStatement();) {
logger.info("query sql=" + select);
try (ResultSet r = s.executeQuery(select)) {
int cols = r.getMetaData().getColumnCount();
if (cols != 13) {
logger.severe("invalid columns in soil query");
throw new IllegalArgumentException("invalid Columns");
}
while (r.next()) {
GIS_DB.Soil soil = new GIS_DB.Soil();
soil.coFips = r.getString(1);
soil.muSym = r.getString(2);
soil.muKey = r.getString(3);
soil.r2Path = r.getString(4);
soil.r2Name = r.getString(5);
soil.muName = r.getString(6);
soil.compName = r.getString(7);
soil.kffact = r.getString(8);
soil.tfact = r.getDouble(9);
soil.slopeR = r.getDouble(10);
soil.coKey = r.getString(11);
soil.percentAoi = r.getDouble(12);
soil.sizeAoi = r.getDouble(13);
soil.lengthR = calculateSlopeR(soil.slopeR, soil.coFips);
String sInfo = "ADDING SOIL TO LIST cokey=" + soil.coKey;
sInfo += " ,mukey=" + soil.muKey;
sInfo += " ,r2_path=" + soil.r2Path;
sInfo += " ,r2_name=" + soil.r2Name;
logger.info(sInfo);
soilLst.add(soil);
}
}
}
return soilLst;
}
private Collection<GIS_DB.Soil> findPolygonsWithSand(String select) throws SQLException {
LinkedList<GIS_DB.Soil> soilLst = new LinkedList<GIS_DB.Soil>();
try (Statement s = connection.createStatement();) {
logger.info("query sql=" + select);
try (ResultSet r = s.executeQuery(select)) {
SQLWarning warning = s.getWarnings();
if (warning != null) {
String sqlWarning = warning.getMessage();
logger.info("**************************************************SQL WARNINGS! executing polygon with sand query");
logger.info("**************************************************message=" + sqlWarning);
logger.info("**************************************************sql-state=" + warning.getSQLState());
logger.info("**************************************************error-code=" + warning.getErrorCode());
if ((sqlWarning != null) && (sqlWarning.length() > 0)) {
throw new SQLException(sqlWarning);
}
} else {
logger.info("**************************************************NO SQL WARNINGS! executing polygon with sand query");
}
int cols = r.getMetaData().getColumnCount();
if (cols != 14) {
logger.severe("invalid columns in soil query");
throw new IllegalArgumentException("invalid Columns");
}
while (r.next()) {
GIS_DB.Soil soil = new GIS_DB.Soil();
soil.coFips = r.getString(1);
soil.muSym = r.getString(2);
soil.muKey = r.getString(3);
soil.r2Path = r.getString(4);
soil.r2Name = r.getString(5);
soil.muName = r.getString(6);
soil.compName = r.getString(7);
soil.kffact = r.getString(8);
soil.tfact = r.getDouble(9);
soil.slopeR = r.getDouble(10);
soil.coKey = r.getString(11);
soil.sandtotalR = Double.parseDouble(r.getString(12)); // issues here with precision from ssurgo are avoided by converting the string value to double
soil.percentAoi = r.getDouble(13);
soil.sizeAoi = r.getDouble(14);
soil.lengthR = calculateSlopeR(soil.slopeR, soil.coFips);
String sInfo = "ADDING SOIL TO LIST cokey=" + soil.coKey;
sInfo += " ,mukey=" + soil.muKey;
sInfo += " ,r2_path=" + soil.r2Path;
sInfo += " ,r2_name=" + soil.r2Name;
logger.info(sInfo);
soilLst.add(soil);
}
}
}
return soilLst;
}
private Collection<GIS_DB.Soil> findPolygonsWeps(String select) throws SQLException {
LinkedList<GIS_DB.Soil> soilLst = new LinkedList<GIS_DB.Soil>();
try (Statement s = connection.createStatement();) {
logger.info("query sql=" + select);
try (ResultSet r = s.executeQuery(select)) {
int cols = r.getMetaData().getColumnCount();
if (cols != 13) {
logger.severe("invalid columns in soil query");
throw new IllegalArgumentException("invalid Columns");
}
while (r.next()) {
GIS_DB.Soil soil = new GIS_DB.Soil();
soil.coFips = r.getString(1);
soil.muSym = r.getString(2);
soil.muKey = r.getString(3);
soil.wepsPath = r.getString(4);
soil.wepsName = r.getString(5);
soil.muName = r.getString(6);
soil.compName = r.getString(7);
soil.kffact = r.getString(8);
soil.tfact = r.getDouble(9);
soil.slopeR = r.getDouble(10);
soil.coKey = r.getString(11);
//soil.sandtotalR = r.getDouble(12);
soil.percentAoi = r.getDouble(12);
soil.sizeAoi = r.getDouble(13);
soil.lengthR = calculateSlopeR(soil.slopeR, soil.coFips);
String sInfo = "ADDING SOIL TO LIST cokey=" + soil.coKey;
sInfo += " ,mukey=" + soil.muKey;
sInfo += " ,weps_path=" + soil.wepsPath;
sInfo += " ,weps_name=" + soil.wepsName;
logger.info(sInfo);
soilLst.add(soil);
}
}
}
return soilLst;
}
private double calculateSlopeR(double slope_r, String areaSymbol) {
double lengthR = 0.0;
// Determines if the soil survey area is in the Palouse region
if (PalouseAreas.contains(areaSymbol)) // Use Palouse region slope length lookup table.
{
for (int i = 0; i < PalouseSlopeLength.length; i++) {
if (PalouseSlopeLength[i][1] != -1) {
if ((slope_r >= PalouseSlopeLength[i][0]) && (slope_r < PalouseSlopeLength[i][1])) {
lengthR = PalouseSlopeLength[i][2];
break;
} else if (slope_r >= PalouseSlopeLength[i][0]) {
lengthR = PalouseSlopeLength[i][2];
break;
}
}
}
} else // Uses Lightle and Weesies 10/1/1996 slope length lookup table (see documentation).
// Not for Palouse region
{
for (int i = 0; i < LightleWeesiesSlopeLength.length; i++) {
// logger.info("LightleWeesiesSlopeLength[" + i + "][0]=" + LightleWeesiesSlopeLength[i][0]);
// logger.info("LightleWeesiesSlopeLength[" + i + "][1]=" + LightleWeesiesSlopeLength[i][1]);
// logger.info("LightleWeesiesSlopeLength[" + i + "][2]=" + LightleWeesiesSlopeLength[i][2]);
// logger.info("slope_r=" + slope_r);
// logger.info("expr val=" + ((slope_r >= LightleWeesiesSlopeLength[i][0]) && (slope_r < LightleWeesiesSlopeLength[i][1])));
if (LightleWeesiesSlopeLength[i][1] != -1) {
if ((slope_r >= LightleWeesiesSlopeLength[i][0]) && (slope_r < LightleWeesiesSlopeLength[i][1])) {
lengthR = LightleWeesiesSlopeLength[i][2];
// logger.info("grabbing value now from double check");
break;
}
} else if (slope_r >= LightleWeesiesSlopeLength[i][0]) {
lengthR = LightleWeesiesSlopeLength[i][2];
// logger.info("grabbing value now from SINGLE check");
break;
}
}
}
return lengthR;
}
private boolean isValidCoord(String select) throws SQLException {
boolean ret_val = false;
try (Statement s = connection.createStatement();) {
logger.info("query sql=" + select);
try (ResultSet r = s.executeQuery(select)) {
int cols = r.getMetaData().getColumnCount();
if (cols != 1) {
throw new IllegalArgumentException("invalid Columns");
}
if (r.next()) {
String sValidCoord = r.getString(1).trim();
ret_val = (sValidCoord.contains("1"));
}
}
}
return ret_val;
}
@Override
public boolean IsValidCliCoord(double lat, double lon) throws SQLException {
return isValidCoord("select count(1) where exists (select 1 where exists (select top 1 cli_key "
+ "from r2gis.cli_geom with (Index(cli_geom_idx)) "
+ "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(geometry) = 1));");
}
@Override
public boolean IsValidManCoord(double lat, double lon) throws SQLException {
return isValidCoord("select count(1) where exists (select top 1 co_cmz "
+ "from r2gis.cmz_geom "
+ "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(geometry) = 1);");
}
@Override
public boolean IsValidSoiCoord(double lat, double lon) throws SQLException {
return isValidCoord("select count(1) where exists (select top 1 areasymbol "
+ "from ssurgo.soilmu_a with (Index(geom_sidx)) "
+ "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(the_geom) = 1);");
}
public boolean IsValidCMZCoord(double lat, double lon) throws SQLException {
return isValidCoord("select count(1) where exists (select top 1 co_cmz "
+ "from r2gis.cmz_geom "
+ "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(geometry) = 1);");
}
@Override
public boolean IsValidGisCoord(double lat, double lon) throws SQLException {
return isValidCoord("select count(1) where exists (select top 1 co_cmz "
+ "from r2gis.cmz_geom "
+ "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(geometry) = 1) and exists "
+ "(select 1 where exists (select top 1 cli_key "
+ "from r2gis.cli_geom with (Index(cli_geom_idx)) "
+ "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(geometry) = 1)) "
+ "and exists "
+ "(select 1 where exists (select top 1 areasymbol "
+ "from ssurgo.soilmu_a with (Index(geom_sidx)) "
+ "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(the_geom) = 1));");
}
@Override
public boolean IsValidRusle2Soil(double lat, double lon) throws SQLException {
GIS_DB.FileQryResult fqr = findSoils(lat, lon);
return fqr != null;
}
@Override
public boolean IsValidWepsSoil(double lat, double lon) throws SQLException {
GIS_DB.FileQryResult fqr = findSoilsWeps(lat, lon);
return fqr != null;
}
@Override
// Determine if a point is within the wind station interpolation boundary for WEPS
public boolean IsInInterpolateBoundary(double lat, double lon) throws SQLException {
return isValidCoord("SELECT geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 4326).STWithin(the_geom) FROM r2gis.weps_int_boundary;");
}
@Override
public GIS_DB.FileQryResult findSoils(double lat, double lon) throws SQLException {
return findFile("SELECT top 1 m.musym co_fips, r2_path, r2_name FROM ssurgo.soilmu_a AS g with (Index(geom_sidx)), r2gis.map_soils AS m "
+ "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(g.the_geom) = 1 "
+ "AND g.mukey = m.mukey;");
}
@Override
public GIS_DB.FileQryResult findSoilsByCokey(String cokey, double lon) throws SQLException {
return findFile("SELECT distinct m.musym co_fips, cast(r2_path as varchar(1200)) r2_path, cast(r2_name as varchar(1200)) r2_name FROM ssurgo.soilmu_a AS g, r2gis.map_soils AS m "
+ "WHERE m.cokey='" + cokey + "' "
+ "AND g.mukey = m.mukey and m.r2_name like '%' + m.compname + '%';");
}
@Override
public Collection<GIS_DB.Soil> findSoilsForPolygon(String _polygon, double poly_longitude) throws SQLException {
logger.info("The input polygon is=" + _polygon);
logger.info("The input polygon len len =" + _polygon.length());
if ((_polygon == null) || (_polygon.length() < 1)) {
throw new SQLException("Empty polygon specified to findSoilsForPolygon, SQL Error.");
}
String select = "SELECT m.co_fips, m.musym, m.mukey, r2_path, r2_name, m.muname, m.compname, m.kffact, m.tfact, m.slope_r, m.cokey, "
+ "((geography::STGeomFromText(geometry.STIntersection( geometry::STGeomFromText('" + _polygon + "', 0)).MakeValid().STAsText(), 4326).MakeValid().STArea()) / (geography::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 4326 ).MakeValid().STArea() )) * 100.0 as percentAoi, "
+ "geography::STGeomFromText(geometry.STIntersection( geometry::STGeomFromText('" + _polygon + "', 0)).MakeValid().STAsText(), 4326).MakeValid().STArea() / 4046.86 as sizeAoi "
+ "FROM soil_geom AS g with (Index(geom_sidx)), map_soils AS m "
+ "WHERE g.geometry.STIntersects(geometry::STGeomFromText('" + _polygon + "',0)) = 1 "
+ "AND g.mukey = m.mukey "
+ "AND geometry.STIsValid() = 1;";
return findPolygons(select);
}
private String getOpenGISPolygon(String _polygon) throws SQLException {
String ret_val = "";
try (Statement s = connection.createStatement();) {
String select = "SELECT CASE WHEN geography::STGeomFromText('" + _polygon + "', 4326).MakeValid().EnvelopeAngle() > 90 THEN geography::STGeomFromText('" + _polygon + "', 4326).MakeValid().ReorientObject().STAsText() ELSE geography::STGeomFromText('" + _polygon + "', 4326).MakeValid().STAsText() END as openGISRotation;";
logger.info("query sql=" + select);
ResultSet r = s.executeQuery(select);
if (r.next()) {
ret_val = r.getString("openGISRotation");
}
}
return ret_val;
}
// To calculate area in acres, we use the st_transform function to transform the intersection polygon into
// a NAD83 Kansas projection which has units in feet (not degrees), which produces square feet.
// We then divide by 43,560 sq ft to get acres.
@Override
public Collection<GIS_DB.Soil> findSoilsForPolygonWithSand(String _polygon, double poly_longitude) throws SQLException {
String polygon = "ST_PolygonFromText('POLYGON((-90.111 38.19,-90.110 38.19, -90.110 38.18,-90.111 38.18, -90.111 38.19))')";
logger.info("The input polygon is=" + _polygon);
logger.info("The input polygon len len =" + _polygon.length());
if ((_polygon == null) || (_polygon.length() < 1)) {
_polygon = polygon;
poly_longitude = -90.111;
}
// added a fix "and m.r2_name like '%' || m.compname || '%'" to the end of this query because
// soils have duplicate records presently, and need to be regenerated...
//TODO: Check this query in MsSQL...Fix the ST_ functions to match those found in MsSQL.
String select = "SELECT m.co_fips, m.musym, m.mukey, r2_path, r2_name, m.muname, m.compname, m.kffact, m.tfact, m.slope_r, m.cokey, m.sandtotal_r, "
+ "((geography::STGeomFromText(the_geom.STIntersection( geometry::STGeomFromText('" + _polygon + "', 0)).MakeValid().STAsText(), 4326).MakeValid().STArea()) / (geography::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 4326 ).MakeValid().STArea() )) * 100.0 as percentAoi, "
+ "geography::STGeomFromText(the_geom.STIntersection( geometry::STGeomFromText('" + _polygon + "', 0)).MakeValid().STAsText(), 4326).MakeValid().STArea() / 4046.86 as sizeAoi "
+ "FROM ssurgo.soilmu_a AS g with (Index(geom_sidx)), r2gis.map_soils AS m "
+ "WHERE g.the_geom.STIntersects(geometry::STGeomFromText('" + _polygon + "',0)) = 1 "
+ "AND g.mukey = m.mukey "
+ "AND the_geom.STIsValid() = 1 "
+ "and m.r2_name like '%' + m.compname + '%';";
return findPolygonsWithSand(select);
}
@Override
// To calculate area in acres, we use the st_transform function to transform the intersection polygon into
// a NAD83 Kansas projection which has units in feet (not degrees), which produces square feet.
// We then divide by 43,560 sq ft to get acres.
public Collection<GIS_DB.Soil> findSoilsForPolygonWeps(String _polygon, double poly_longitude) throws SQLException {
// String polygon = "ST_PolygonFromText('POLYGON((-90.111 38.19,-90.110 38.19, -90.110 38.18,-90.111 38.18, -90.111 38.19))')";
logger.info("find weps soils - The input polygon is=" + _polygon);
logger.info("The input polygon len len =" + _polygon.length());
if ((_polygon == null) || (_polygon.length() < 1)) {
throw new SQLException("no polygon provided for WEPS soil query!");
// _polygon = polygon;
// poly_longitude = -90.111;
}
//TODO: Check this query in MsSQL...Fix the ST_ functions to match those found in MsSQL.
String select = "SELECT m.co_fips, m.musym, m.mukey, weps_path, weps_name, m.muname, m.compname, m.kffact, m.tfact, m.slope_r, m.cokey, "
+ "((geography::STGeomFromText(the_geom.STIntersection( geometry::STGeomFromText('" + _polygon + "', 0)).MakeValid().STAsText(), 4326).MakeValid().STArea()) / (geography::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 4326 ).MakeValid().STArea() )) * 100.0 as percentAoi, "
+ "geography::STGeomFromText(the_geom.STIntersection( geometry::STGeomFromText('" + _polygon + "', 0)).MakeValid().STAsText(), 4326).MakeValid().STArea() / 4046.86 as sizeAoi "
+ "FROM ssurgo.soilmu_a AS g with (Index(geom_sidx)), r2gis.map_soils_weps AS m "
+ "WHERE g.the_geom.STIntersects(geometry::STGeomFromText('" + _polygon + "',0)) = 1 "
+ "AND g.mukey = m.mukey "
+ "AND the_geom.STIsValid() = 1;";
return findPolygonsWeps(select);
}
@Override
public GIS_DB.FileQryResult findSoilsWeps(double lat, double lon) throws SQLException {
return findFile("SELECT TOP 1 m.musym co_fips, weps_path, weps_name FROM ssurgo.soilmu_a AS g with (Index(geom_sidx)), r2gis.map_soils_weps AS m "
+ "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 0).STWithin( g.the_geom ) = 1 "
+ "AND g.mukey = m.mukey;");
}
@Override
public GIS_DB.FileQryResult findSoilsWepsByCokey(String cokey, double lon) throws SQLException {
return findFile("SELECT TOP 1 m.musym co_fips, weps_path, weps_name FROM ssurgo.soilmu_a AS g, r2gis.map_soils_weps AS m "
+ "WHERE m.cokey='" + cokey + "' "
+ "AND g.mukey = m.mukey;");
}
@Override
//
// 09-03-2013 WJL
// Added the "DESC" to sort values in order to favor REQ for IDAHO and RMOD for COLORADO
//
// Basically these climate files (rmod, req, etc.), where they exist, should always be returned in favor of the just "R" ones...
public GIS_DB.FileQryResult findClimate(double lat, double lon) throws SQLException {
return findFile("SELECT top 1 m.co_fips, r2_path, r2_name FROM r2gis.cli_geom as g WITH (Index(cli_geom_idx)), r2gis.map_climates AS m "
+ "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 0).STWithin( g.geometry ) = 1 "
+ "AND g.co_fips = m.co_fips "
+ "AND ((g.ei_rang=m.ei_rang) or (g.ei_rang = ''))"
+ "ORDER BY cast(m.r2_name as varchar(1200)) desc;");
}
@Override
public GIS_DB.County findCounty(double lat, double lon) throws SQLException, ServiceException {
GIS_DB.County cnty = null;
String select = "SELECT m.st_abbr, m.co_numb, m.co_name FROM r2gis.cnty_geom as g, r2gis.cnty_data AS m "
+ "where geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 0).STWithin( g.geometry ) = 1 "
+ "AND g.co_fips = m.co_fips;";
try (Statement s = connection.createStatement();) {
logger.info("query sql=" + select);
try (ResultSet r = s.executeQuery(select)) {
int cols = r.getMetaData().getColumnCount();
if (cols != 3) {
logger.severe("invalid columns in cligen station query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
logger.severe("no result from cligen station query!");
throw new ServiceException("no result from cligen station query!");
} else {
cnty = new GIS_DB.County();
cnty.st_abbr = r.getString(1);
cnty.county_code = r.getString(2);
cnty.name = r.getString(3);
//s.close();
logger.info("state abbr=" + cnty.st_abbr + " county code=" + cnty.county_code + " county name=" + cnty.name);
}
}
} catch (SQLException | ServiceException ex) {
logger.log(Level.SEVERE, null, ex);
throw ex;
}
return cnty;
}
@Override
public GIS_DB.StationResult findCligenStation(double lat, double lon) throws SQLException {
GIS_DB.StationResult cligenst = null;
String select = "SELECT top 1 s.st_numb, s.station_id, point.STX, point.STY, s.elevation, point.STDistance(geometry::STPointFromText('POINT(" + lon + " " + lat + ")',4326)) as distance, s.name "
+ "FROM r2gis.cligen_stations as s "
+ "order by distance;";
try (Statement s = connection.createStatement();) {
logger.info("query sql=" + select);
try (ResultSet r = s.executeQuery(select)) {
int cols = r.getMetaData().getColumnCount();
if (cols != 7) {
logger.severe("invalid columns in cligen station query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
logger.severe("no result from cligen station query!");
} else {
cligenst = new GIS_DB.StationResult();
cligenst.state = Integer.toString(r.getInt(1));
cligenst.stationId = Integer.toString(r.getInt(2));
cligenst.stationX = r.getString(3);
cligenst.stationY = r.getString(4);
cligenst.elevation = r.getString(5);
cligenst.distance = r.getDouble(6);
cligenst.name = (r.getString(7) != null ? r.getString(7) : "NAME_UNAVAILABLE-LOOKUP-ID#" + r.getString(2));
logger.info("state number=" + cligenst.state + " state-result-set=" + r.getString(1) + " station id=" + cligenst.stationId + " stationid-resultset=" + r.getString(2) + " station_X=" + cligenst.stationX + " stationY=" + cligenst.stationY + " name=" + cligenst.name);
logger.info("distance in deg=" + cligenst.distance);
}
}
}
return cligenst;
}
@Override
public County findWEPSCounty(double lat, double lon) throws SQLException, ServiceException {
throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
}
@Override
public GIS_DB.StationResult findWindgenStation(double lat, double lon) throws SQLException {
GIS_DB.StationResult windgenst = null;
String select = "SELECT top 1 st_numb, station_id, geometry.STDistance(geometry::STPointFromText('POINT(" + lon + " " + lat + ")',4326)) as distance "
+ "FROM r2gis.windgen_stations "
+ "order by distance; ";
try (Statement s = connection.createStatement();) {
logger.info("query sql=" + select);
try (ResultSet r = s.executeQuery(select)) {
int cols = r.getMetaData().getColumnCount();
if (cols != 3) {
logger.severe("invalid columns in windgen station query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
logger.severe("no result from windgen station query!");
} else {
windgenst = new GIS_DB.StationResult();
windgenst.state = r.getString(1);
windgenst.stationId = Integer.toString(r.getInt(2));
windgenst.distance = r.getDouble(3);
//s.close();
logger.info("state=" + windgenst.state);
logger.info("station id=" + windgenst.stationId + " rs.getstr=" + r.getString(2));
logger.info("distance in deg=" + windgenst.distance);
}
}
}
return windgenst;
}
@Override
public GIS_DB.StationResult cliGeomIntersect(double lat, double lon) throws SQLException {
GIS_DB.StationResult clist = null;
String select = "select st, name, st_fips, c1, lat_dd, long_dd, elev_m "
+ "from r2gis.weps_cli_geom "
+ "where geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 4326).STWithin( r2gis.weps_cli_geom.geometry ) = 1;";
try (Statement s = connection.createStatement()) {
logger.info("query sql=" + select);
try (ResultSet r = s.executeQuery(select)) {
int cols = r.getMetaData().getColumnCount();
if (cols != 7) {
logger.severe("invalid columns in cliGeomIntersect query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
logger.severe("no result from cliGeomIntersect query!");
} else {
clist = new GIS_DB.StationResult();
// Result set is getting a float for clist.stationId and clist.state
clist.state = Integer.toString(r.getInt(3));
clist.name = r.getString(2);
clist.stationId = Integer.toString(r.getInt(4));
clist.stationY = r.getString(5);
clist.stationX = r.getString(6);
clist.elevation = r.getString(7);
logger.info("state=" + clist.state + " resultset str-value=" + r.getString(3));
logger.info("cli station id=" + clist.stationId + " resultset str-value=" + r.getString(4));
logger.info("cli station name=" + clist.name);
logger.info("cli station elev=" + clist.elevation);
}
}
}
return clist;
}
@Override
public GIS_DB.StationResult windGeomIntersect(double lat, double lon) throws SQLException {
GIS_DB.StationResult windgenst = null;
String select = "select lat_dd, long_dd, wban, cntry, st, name "
+ "from r2gis.weps_wind_geom "
+ "where geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 4326).STWithin( r2gis.weps_wind_geom.geometry ) = 1;";
try (Statement s = connection.createStatement();) {
logger.info("query sql=" + select);
try (ResultSet r = s.executeQuery(select)) {
int cols = r.getMetaData().getColumnCount();
if (cols != 6) {
//s.close();
logger.severe("invalid columns in windGeomIntersect query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
//s.close();
logger.severe("no result from windGeomIntersect query!");
} else {
windgenst = new GIS_DB.StationResult();
windgenst.stationX = r.getString(2);
windgenst.stationY = r.getString(1);
windgenst.stationId = Integer.toString(r.getInt(3));
windgenst.country = r.getString(4);
windgenst.state = r.getString(5);
windgenst.name = r.getString(6);
logger.info("state=" + windgenst.state);
logger.info("station id=" + windgenst.stationId + " rs.getStr=" + r.getString(3));
logger.info("station name=" + windgenst.name);
}
}
}
return windgenst;
}
@Override
// Uses a field lat long, to determine the county, then returns the county's centroid lat/long
public GIS_DB.County countyCentroid(double lat, double lon) throws SQLException {
GIS_DB.County county = null;
String select = "select state, county, name, geometry.STCentroid().STX centroid_long, geometry.STCentroid().STY centroid_lat "
+ "from r2gis.weps_county_geom "
+ "where geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 4326).STWithin( r2gis.weps_county_geom.geometry ) = 1;";
try (Statement s = connection.createStatement();) {
logger.info("query sql=" + select);
try (ResultSet r = s.executeQuery(select)) {
int cols = r.getMetaData().getColumnCount();
if (cols != 5) {
logger.severe("invalid columns in countyCentroid query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
logger.severe("no result from countyCentroid query!");
} else {
county = new GIS_DB.County();
county.st_abbr = r.getString("state");
county.county_code = r.getString("county");
county.name = r.getString("name");
county.county_centroid_X = r.getString("centroid_long");
county.county_centroid_Y = r.getString("centroid_lat");
logger.info("county_fips_code=" + county.st_abbr + county.county_code);
logger.info("name=" + county.name);
logger.info("centroidX=" + county.county_centroid_X);
logger.info("centroidY=" + county.county_centroid_Y);
}
}
}
return county;
}
@Override
public String findCmz(double lat, double lon) throws SQLException {
return findCmzName("select distinct TOP 1 co_cmz from r2gis.cmz_geom "
+ "where geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 0).STWithin( r2gis.cmz_geom.geometry ) = 1;", 1);
}
@Override
@Deprecated
public List<GIS_DB.FileQryResult> findManagements(double lat, double lon) throws SQLException {
List<GIS_DB.FileQryResult> l = new ArrayList<GIS_DB.FileQryResult>();
return l;
}
@Override
@Deprecated
public List<GIS_DB.FileQryResult> findManagementsFilter(double lat, double lon, String filter1, String filter2) throws SQLException {
List<GIS_DB.FileQryResult> l = new ArrayList<GIS_DB.FileQryResult>();
return l;
}
}