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();
        }
    }
}