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