MsSQLGIS.java [src/java/adb] Revision: default Date:
package adb;
import csip.SessionLogger;
import csip.utils.Binaries;
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 static adb.DBResources.EROSION_SQLSVR_1_3;
import csip.annotations.Resource;
import java.util.logging.Level;
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
/**
*
* @author Shaun Case
* @author Wes Lloyd
*/
@Resource(from = DBResources.class)
public class MsSQLGIS implements GIS_DB_1_0 {
private Connection connection;
private SessionLogger LOG;
MsSQLGIS(Class<?> c, SessionLogger log) throws csip.api.server.ServiceException, SQLException {
connection = Binaries.getResourceJDBC(c, EROSION_SQLSVR_1_3, log);
LOG = log;
//connection = getResourceJDBC("csip.erosion.sqlsvr");
if (!connection.getMetaData().getDatabaseProductName().contains("Microsoft SQL")) {
throw new csip.api.server.ServiceException("Invalid connection type passed to MsSQLGIS. Class requires a connection to a MsSQL server.");
}
}
MsSQLGIS(Connection conn) throws csip.api.server.ServiceException, SQLException {
if (null != conn) {
if (conn.getMetaData().getDatabaseProductName().contains("Microsoft SQL")) {
connection = conn;
} else {
throw new csip.api.server.ServiceException("Invalid connection type passed to MsSQLGIS. Class requires a connection to a MsSQL server.");
}
} else {
throw new csip.api.server.ServiceException("No connection object passed to MsSQLGIS. Class requires a connection to a MsSQL server.");
}
}
private void Log(Level level, String msg, Throwable t) {
if ((null != LOG) && (LOG.isLoggable(level))) {
if (LOG.isLoggable(level)) {
LOG.log(level, msg, t);
}
}
}
private void Log(Level level, String msg) {
if ((null != LOG) && (LOG.isLoggable(level))) {
LOG.log(level, msg);
}
}
private void Log(Level level, String msg, Object... params) {
if ((null != LOG) && (LOG.isLoggable(level))) {
LOG.log(level, msg, params);
}
}
@Override
public Connection getConnection() {
return connection;
}
private GIS_DB_1_0.FileQryResult findFile(String select) throws SQLException {
GIS_DB_1_0.FileQryResult res = null;
try (Statement s = connection.createStatement();) {
ResultSet r = s.executeQuery(select);
int cols = r.getMetaData().getColumnCount();
if (cols != 3) {
Log(Level.SEVERE, "invalid columns in soil query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
Log(Level.SEVERE, "no result from soil query!");
} else {
res = new GIS_DB_1_0.FileQryResult();
res.id = r.getString(1);
res.file_path = r.getString(2);
res.file_name = r.getString(3);
}
}
return res;
}
private String findCmzName(String select, int colcnt) throws SQLException {
String ret_val = "0";
try (Statement s = connection.createStatement()) {
ResultSet r = s.executeQuery(select);
int cols = r.getMetaData().getColumnCount();
if (cols != colcnt) {
Log(Level.SEVERE, "invalid number of columns returned in find dbl query:" + select);
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
Log(Level.SEVERE, "no result from find dbl query!");
} else {
ret_val = r.getString(1);
}
return ret_val;
}
}
private Collection<GIS_DB_1_0.Soil> findPolygons(String select) throws SQLException {
LinkedList<GIS_DB_1_0.Soil> soilLst = new LinkedList<>();
try (Statement s = connection.createStatement();) {
ResultSet r = s.executeQuery(select);
int cols = r.getMetaData().getColumnCount();
if (cols != 13) {
Log(Level.SEVERE, "invalid columns in soil query");
throw new IllegalArgumentException("invalid Columns");
}
while (r.next()) {
GIS_DB_1_0.Soil soil = new GIS_DB_1_0.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;
soilLst.add(soil);
}
}
return soilLst;
}
private Collection<GIS_DB_1_0.Soil> findPolygonsWithSand(String select) throws SQLException {
LinkedList<GIS_DB_1_0.Soil> soilLst = new LinkedList<>();
try (Statement s = connection.createStatement();) {
ResultSet r = s.executeQuery(select);
SQLWarning warning = s.getWarnings();
if (warning != null) {
String sqlWarning = warning.getMessage();
if ((sqlWarning != null) && (sqlWarning.length() > 0)) {
throw new SQLException(sqlWarning);
}
}
int cols = r.getMetaData().getColumnCount();
if (cols != 14) {
Log(Level.SEVERE, "invalid columns in soil query");
throw new IllegalArgumentException("invalid Columns");
}
while (r.next()) {
GIS_DB_1_0.Soil soil = new GIS_DB_1_0.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);
soilLst.add(soil);
}
}
return soilLst;
}
private Collection<GIS_DB_1_0.Soil> findPolygonsWithSandWeps(String select) throws SQLException {
LinkedList<GIS_DB_1_0.Soil> soilLst = new LinkedList<>();
try (Statement s = connection.createStatement();) {
ResultSet r = s.executeQuery(select);
SQLWarning warning = s.getWarnings();
if (warning != null) {
String sqlWarning = warning.getMessage();
if ((sqlWarning != null) && (sqlWarning.length() > 0)) {
throw new SQLException(sqlWarning);
}
}
int cols = r.getMetaData().getColumnCount();
if (cols != 16) {
Log(Level.SEVERE, "invalid columns in soil query");
throw new IllegalArgumentException("invalid Columns");
}
while (r.next()) {
GIS_DB_1_0.Soil soil = new GIS_DB_1_0.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.r2Path = r.getString(6);
soil.r2Name = r.getString(7);
soil.muName = r.getString(8);
soil.compName = r.getString(9);
soil.kffact = r.getString(10);
soil.tfact = r.getDouble(11);
soil.slopeR = r.getDouble(12);
soil.coKey = r.getString(13);
soil.sandtotalR = Double.parseDouble(r.getString(14)); // issues here with precision from ssurgo are avoided by converting the string value to double
soil.percentAoi = r.getDouble(15);
soil.sizeAoi = r.getDouble(16);
soil.lengthR = calculateSlopeR(soil.slopeR, soil.coFips);
soilLst.add(soil);
}
}
return soilLst;
}
private Collection<GIS_DB_1_0.Soil> findPolygonsWeps(String select) throws SQLException {
LinkedList<GIS_DB_1_0.Soil> soilLst = new LinkedList<>();
try (Statement s = connection.createStatement();) {
ResultSet r = s.executeQuery(select);
int cols = r.getMetaData().getColumnCount();
if (cols != 13) {
Log(Level.SEVERE, "invalid columns in soil query");
throw new IllegalArgumentException("invalid Columns");
}
while (r.next()) {
GIS_DB_1_0.Soil soil = new GIS_DB_1_0.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);
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 (double[] PalouseSlopeLength1 : PalouseSlopeLength) {
if (PalouseSlopeLength1[1] != -1) {
if ((slope_r >= PalouseSlopeLength1[0]) && (slope_r < PalouseSlopeLength1[1])) {
lengthR = PalouseSlopeLength1[2];
break;
} else if (slope_r >= PalouseSlopeLength1[0]) {
lengthR = PalouseSlopeLength1[2];
break;
}
}
}
} else // Uses Lightle and Weesies 10/1/1996 slope length lookup table (see documentation).
// Not for Palouse region
{
for (double[] LightleWeesiesSlopeLength1 : LightleWeesiesSlopeLength) {
if (LightleWeesiesSlopeLength1[1] != -1) {
if ((slope_r >= LightleWeesiesSlopeLength1[0]) && (slope_r < LightleWeesiesSlopeLength1[1])) {
lengthR = LightleWeesiesSlopeLength1[2];
break;
}
} else if (slope_r >= LightleWeesiesSlopeLength1[0]) {
lengthR = LightleWeesiesSlopeLength1[2];
break;
}
}
}
return lengthR;
}
private boolean isValidCoord(String select) throws SQLException {
boolean ret_val = false;
try (Statement s = connection.createStatement();) {
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);");
}
@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_1_0.FileQryResult fqr = findSoils(lat, lon);
return fqr != null;
}
@Override
public boolean IsValidWepsSoil(double lat, double lon) throws SQLException {
GIS_DB_1_0.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_1_0.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
//
// TODO: fix me
//
// added string matching to fix a problem which requires us to regenerate r2 soils
public GIS_DB_1_0.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_1_0.Soil> findSoilsForPolygon(String _polygon, double poly_longitude) throws SQLException {
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('" + getOpenGISPolygon(_polygon) + "', 0).MakeValid()).MakeValid().STAsText(), 4326).MakeValid().STArea()) / (geography::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 4326 ).MakeValid().STArea() )) * 100.0 as percentAoi, "
+ "geography::STGeomFromText(geometry.STIntersection( geometry::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 0).MakeValid()).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('" + getOpenGISPolygon(_polygon) + "',0).MakeValid()) = 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;";
Log(Level.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_1_0.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))')";
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...
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('" + getOpenGISPolygon(_polygon) + "', 0).MakeValid()).MakeValid().STAsText(), 4326).MakeValid().STArea()) / (geography::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 4326 ).MakeValid().STArea() )) * 100.0 as percentAoi, "
+ "geography::STGeomFromText(the_geom.STIntersection( geometry::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 0).MakeValid()).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('" + getOpenGISPolygon(_polygon) + "',0).MakeValid()) = 1 "
+ "AND g.mukey = m.mukey "
+ "AND the_geom.STIsValid() = 1 "
+ "and m.r2_name like '%' + m.compname + '%';";
return findPolygonsWithSand(select);
}
public Collection<GIS_DB_1_0.Soil> findSoilsForPolygonWithSandWeps(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))')";
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...
String select = "SELECT m.co_fips, m.musym, m.mukey, weps_path, weps_name, 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('" + getOpenGISPolygon(_polygon) + "', 0).MakeValid()).MakeValid().STAsText(), 4326).MakeValid().STArea()) / (geography::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 4326 ).MakeValid().STArea() )) * 100.0 as percentAoi, "
+ "geography::STGeomFromText(the_geom.STIntersection( geometry::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 0).MakeValid()).MakeValid().STAsText(), 4326).MakeValid().STArea() / 4046.86 as sizeAoi "
+ "FROM ssurgo.soilmu_a AS g with (Index(geom_sidx)), r2gis.map_soils as m, r2gis.map_soils_weps as n "
+ "WHERE g.the_geom.STIntersects(geometry::STGeomFromText('" + getOpenGISPolygon(_polygon) + "',0).MakeValid()) = 1 "
+ "AND the_geom.STIsValid() = 1 AND g.mukey=m.mukey AND m.cokey=n.cokey "
+ "and m.r2_name like '%' + m.compname + '%';";
return findPolygonsWithSandWeps(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_1_0.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))')";
if ((_polygon == null) || (_polygon.length() < 1)) {
//throw new SQLException("no polygon provided for WEPS soil query!");
_polygon = polygon;
poly_longitude = -90.111;
}
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('" + getOpenGISPolygon(_polygon) + "', 0).MakeValid()).MakeValid().STAsText(), 4326).MakeValid().STArea()) / (geography::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 4326 ).MakeValid().STArea() )) * 100.0 as percentAoi, "
+ "geography::STGeomFromText(the_geom.STIntersection( geometry::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 0).MakeValid()).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('" + getOpenGISPolygon(_polygon) + "',0).MakeValid()) = 1 "
+ "AND g.mukey = m.mukey "
+ "AND the_geom.STIsValid() = 1;";
return findPolygonsWeps(select);
}
@Override
public GIS_DB_1_0.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_1_0.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_1_0.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_1_0.County findCounty(double lat, double lon) throws SQLException {
GIS_DB_1_0.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();) {
ResultSet r = s.executeQuery(select);
int cols = r.getMetaData().getColumnCount();
if (cols != 3) {
Log(Level.SEVERE, "invalid columns in cligen station query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
Log(Level.SEVERE, "no result from cligen station query!");
} else {
cnty = new GIS_DB_1_0.County();
cnty.st_abbr = r.getString(1);
cnty.county_code = r.getString(2);
cnty.name = r.getString(3);
}
} catch (SQLException ex) {
LOG.log(Level.SEVERE, null, ex);
}
return cnty;
}
@Override
public GIS_DB_1_0.StationResult findCligenStation(double lat, double lon) throws SQLException {
GIS_DB_1_0.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();) {
ResultSet r = s.executeQuery(select);
int cols = r.getMetaData().getColumnCount();
if (cols != 7) {
Log(Level.SEVERE, "invalid columns in cligen station query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
Log(Level.SEVERE, "no result from cligen station query!");
} else {
cligenst = new GIS_DB_1_0.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));
}
} catch (SQLException ex) {
LOG.log(Level.SEVERE, null, ex);
}
return cligenst;
}
@Override
public GIS_DB_1_0.StationResult findWindgenStation(double lat, double lon) throws SQLException {
GIS_DB_1_0.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();) {
ResultSet r = s.executeQuery(select);
int cols = r.getMetaData().getColumnCount();
if (cols != 3) {
Log(Level.SEVERE, "invalid columns in windgen station query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
Log(Level.SEVERE, "no result from windgen station query!");
} else {
windgenst = new GIS_DB_1_0.StationResult();
windgenst.state = r.getString(1);
windgenst.stationId = Integer.toString(r.getInt(2));
windgenst.distance = r.getDouble(3);
}
} catch (SQLException ex) {
LOG.log(Level.SEVERE, null, ex);
}
return windgenst;
}
@Override
public GIS_DB_1_0.StationResult cliGeomIntersect(double lat, double lon) throws SQLException {
GIS_DB_1_0.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();) {
ResultSet r = s.executeQuery(select);
int cols = r.getMetaData().getColumnCount();
if (cols != 7) {
Log(Level.SEVERE, "invalid columns in cliGeomIntersect query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
Log(Level.SEVERE, "no result from cliGeomIntersect query!");
} else {
clist = new GIS_DB_1_0.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);
}
} catch (SQLException ex) {
LOG.log(Level.SEVERE, null, ex);
}
return clist;
}
@Override
public GIS_DB_1_0.StationResult windGeomIntersect(double lat, double lon) throws SQLException {
GIS_DB_1_0.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();) {
ResultSet r = s.executeQuery(select);
int cols = r.getMetaData().getColumnCount();
if (cols != 6) {
//s.close();
Log(Level.SEVERE, "invalid columns in windGeomIntersect query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
//s.close();
Log(Level.SEVERE, "no result from windGeomIntersect query!");
} else {
windgenst = new GIS_DB_1_0.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);
}
} catch (SQLException ex) {
LOG.log(Level.SEVERE, null, ex);
}
return windgenst;
}
@Override
// Uses a field lat long, to determine the county, then returns the county's centroid lat/long
public GIS_DB_1_0.County countyCentroid(double lat, double lon) throws SQLException {
GIS_DB_1_0.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();) {
ResultSet r = s.executeQuery(select);
int cols = r.getMetaData().getColumnCount();
if (cols != 5) {
Log(Level.SEVERE, "invalid columns in countyCentroid query");
throw new IllegalArgumentException("invalid Columns");
}
if (!r.next()) {
Log(Level.SEVERE, "no result from countyCentroid query!");
} else {
county = new GIS_DB_1_0.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");
}
} catch (SQLException ex) {
LOG.log(Level.SEVERE, null, ex);
}
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_1_0.FileQryResult> findManagements(double lat, double lon) throws SQLException {
List<GIS_DB_1_0.FileQryResult> l = new ArrayList<>();
return l;
}
@Override
@Deprecated
public List<GIS_DB_1_0.FileQryResult> findManagementsFilter(double lat, double lon, String filter1, String filter2) throws SQLException {
List<GIS_DB_1_0.FileQryResult> l = new ArrayList<>();
return l;
}
@Override
public void close() throws SQLException {
if (null != connection) {
connection.close();
}
}
}