NRTDb.java [src/java/adb/nrt] Revision: default  Date:
/*
 * $Id$
 *
 * This file is part of the Cloud Services Integration Platform (CSIP),
 * a Model-as-a-Service framework, API, and application suite.
 *
 * 2012-2017, OMSLab, Colorado State University.
 *
 * OMSLab licenses this file to you under the MIT license.
 * See the LICENSE file in the project root for more information.
 */
package adb.nrt;

import adb.GIS_DB;
import static adb.nrt.DBResources.NRT_SQLSVR;
import csip.api.server.ServiceException;
import csip.SessionLogger;
import csip.annotations.Resource;
import csip.utils.Binaries;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author <a href="mailto:shaun.case@colostate.edu">Shaun Case</a>
 */
@Resource(from = adb.nrt.DBResources.class)
public class NRTDb {

    SessionLogger log;
    Class<?> parentClass;

    public NRTDb(Class<?> c, SessionLogger Log) {
        log = Log;
        parentClass = c;
    }

    public String getCountyWKTByName(String stateAbbrev, String countyName) throws ServiceException {
        String ret_val = null;

        if ((null != stateAbbrev) && (null != countyName) && (!stateAbbrev.isEmpty()) && (!countyName.isEmpty())) {
            try (NRT database = new NRT(parentClass);) {
                ret_val = database.findCounty(stateAbbrev, countyName);
            } catch (SQLException ex) {
                throw new ServiceException("Cannot find a county shape (WKT) for that state and county, (" + stateAbbrev + ", " + countyName + "): " + ex.getMessage(), ex);
            }
        }
        return ret_val;
    }

    public String getCountyWKTByCountyCode(String stateAbbrev, String countyCode) throws ServiceException {
        String ret_val = null;

        if ((null != stateAbbrev) && (null != countyCode) && (!stateAbbrev.isEmpty()) && (!countyCode.isEmpty())) {
            try (NRT database = new NRT(parentClass);) {
                ret_val = database.findCountyByCode(stateAbbrev, countyCode);
            } catch (SQLException ex) {
                throw new ServiceException("Cannot find a county shape (WKT) for that state and county code, (" + stateAbbrev + ", " + countyCode + "): " + ex.getMessage(), ex);
            }
        }
        return ret_val;
    }

    public String getCountyWKTByStateCodeAndCountyCode(String state, String county) throws ServiceException {
        String ret_val = null;

        if ((null != state) && (null != county) && (!state.isEmpty()) && (!county.isEmpty())) {
            try (NRT database = new NRT(parentClass);) {
                ret_val = database.findCountyByStateCodeAndCountyCode(state, county);
            } catch (SQLException ex) {
                throw new ServiceException("Cannot find a county shape (WKT) for that state and county code combination, (" + state + ", " + county + "): " + ex.getMessage(), ex);
            }
        }
        return ret_val;
    }

    public String getCountyWKTByStateCountyCode(String stateCounty) throws ServiceException {
        String ret_val = null;

        if ((null != stateCounty) && (!stateCounty.isEmpty())) {
            try (NRT database = new NRT(parentClass);) {
                ret_val = database.findCountyByStateCountyCode(stateCounty);
            } catch (SQLException ex) {
                throw new ServiceException("Cannot find a county shape (WKT) for that state_county code, (" + stateCounty + "): " + ex.getMessage(), ex);
            }
        }
        return ret_val;
    }      

    protected class NRT implements AutoCloseable {

        Connection connection = null;

        public NRT(Class<?> c) throws ServiceException {
            if (null != c) {
                try {
                    connection = Binaries.getResourceJDBC(c, NRT_SQLSVR, log);
                    if (!connection.getMetaData().getDatabaseProductName().contains("Microsoft SQL")) {
                        throw new csip.api.server.ServiceException("Invalid connection type for NRT Database class.  Class requires a connection to a MsSQL server.");
                    }
                } catch (SQLException ex) {
                    throw new ServiceException("Cannot get NRT database MetaData: " + ex.getMessage(), ex);
                }
            }
        }

        @Override
        public void close() throws ServiceException {
            if (null != connection) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    throw new ServiceException("Cannot close the NRT database connection: " + ex.getMessage(), ex);
                }
            }
        }

        public String findCounty(String stateAbbrev, String countyName) throws SQLException, ServiceException {
            String WKT = null;
            try (Statement s = connection.createStatement();) {
                String query = "SELECT state_county_geometry.STAsText() as countyWKT from d_state_county "
                        + " WHERE state_abbr='" + stateAbbrev + "' "
                        + "       AND county_name='" + countyName + "' "
                        + "       AND state_county_type='C';";

                ResultSet results = s.executeQuery(query);
                if (results.next()) {
                        WKT = results.getString("countyWKT");
                } else {
                    throw new ServiceException("No data found for that state and county combination, (" + stateAbbrev + ", " + countyName + ").");
                }
            }

            return WKT;
        }

        public String findCountyByCode(String stateAbbrev, String countyCode) throws SQLException, ServiceException {
            String WKT = null;
            try (Statement s = connection.createStatement();) {
                String query = "SELECT state_county_geometry.STAsText() from d_state_county "
                        + " WHERE state_abbr='" + stateAbbrev + "' "
                        + "       AND county_code='" + countyCode + "' "
                        + "       AND state_county_type='C';";

                ResultSet results = s.executeQuery(query);
                if (results.next()) {

                } else {
                    throw new ServiceException("No data found for that state and county combination, (" + stateAbbrev + ", " + countyCode + ").");
                }
            }

            return WKT;
        }

        public String findCountyByStateCodeAndCountyCode(String stateCode, String countyCode) throws SQLException, ServiceException {
            String WKT = null;
            try (Statement s = connection.createStatement();) {
                String query = "SELECT state_county_geometry.STAsText() from d_state_county "
                        + " WHERE state_code='" + stateCode + "' "
                        + "       AND county_code='" + countyCode + "' "
                        + "       AND state_county_type='C';";

                ResultSet results = s.executeQuery(query);
                if (results.next()) {

                } else {
                    throw new ServiceException("No data found for that state and county code combination, (" + stateCode + ", " + countyCode + ").");
                }
            }

            return WKT;
        }

        public String findCountyByStateCountyCode(String stateCountyCode) throws SQLException, ServiceException {
            String WKT = null;
            try (Statement s = connection.createStatement();) {
                String query = "SELECT state_county_geometry.STAsText() from d_state_county "
                        + " WHERE state_county_code='" + stateCountyCode + "' "
                        + "       AND state_county_type='C';";

                ResultSet results = s.executeQuery(query);
                if (results.next()) {

                } else {
                    throw new ServiceException("No data found for that state_county code, (" + stateCountyCode + ").");
                }
            }

            return WKT;
        }

        /**
         * New and improved version of findCounty. Uses the NRT datasource's
         * d_state_county table as the definitive source
         */
        public GIS_DB.County findCountyByLatLon(double lat, double lon) throws SQLException, ServiceException {
            GIS_DB.County cnty = null;
            try (Statement s = connection.createStatement();) {
                String select = "SELECT state_abbr, county_code, county_display FROM d_state_county "
                        + " WHERE state_county_geometry.STContains( geometry::STGeomFromText('POINT(" + lon + " " + lat + ")', 4326)) = 1 "
                        + " AND state_county_geometry.STSrid is not NULL"
                        + " AND state_county_type='C';";
                try (ResultSet r = s.executeQuery(select)) {
                    if (r.next()) {
                        cnty = new GIS_DB.County();
                        cnty.st_abbr = r.getString("state_abbr");
                        cnty.county_code = r.getString("county_code");
                        cnty.name = r.getString("county_display");
                    } else {
                        throw new ServiceException("No county located in NRT State County query for that point: POINT( " + lon + " " + lat + " )");
                    }
                }
            }
            return cnty;
        }
    }
}