mssql_sdm.java [src/soils/db] Revision: default Date:
/*
* 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.
*/
package soils.db;
import csip.SessionLogger;
import csip.annotations.Resource;
import csip.api.server.ServiceException;
import csip.utils.Binaries;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.logging.Level;
import soils.Component;
import soils.Horizon;
import soils.MapUnit;
import static soils.db.DBResources.SDM;
import static soils.db.DBResources.SDM_REST;
import soils.db.tables.TableCocropyld;
import soils.db.tables.TableCoecoclass;
import soils.db.tables.TableComonth;
import soils.db.tables.TableComponent;
import soils.db.tables.TableComponentCalculations;
import soils.db.tables.TableFragments;
import soils.db.tables.TableHorizon;
import soils.db.tables.TableMapUnit;
import soils.db.tables.TableMuaggatt;
import soils.db.tables.TableMucropyld;
import soils.db.tables.TableTexture;
import soils.db.tables.TableTextureGroup;
import soils.exceptions.SDMException;
import soils.utils.EvalResult;
/**
*
* @author <a href="mailto:shaun.case@colostate.edu">Shaun Case</a>
*/
@Resource(from = soils.db.DBResources.class)
public class mssql_sdm extends MSSQL_SOILS {
public mssql_sdm(Class<?> c, SessionLogger log) throws ServiceException, SQLException {
DB_GEOM_SRID = "4326";
logPrefix = "mssql_sdm";
LOG = log;
connection = Binaries.getResourceJDBC(c, SDM, log);
if (!connection.getMetaData().getDatabaseProductName().contains("Microsoft SQL")) {
throw new ServiceException("Invalid connection type passed to mssql_ssurgo. Class requires a connection to a MsSQL server.");
} else if (!isValidDb()) {
throw new ServiceException("Invalid connection database type passed to soils_data mssql_sdm implementation. Class requires a connection to an instance of the SDM database.");
}
}
public mssql_sdm(Class<?> c, SessionLogger log, boolean rest) throws ServiceException, SQLException {
DB_GEOM_SRID = "4326";
logPrefix = "mssql_sdm";
LOG = log;
if (rest) {
connection = Binaries.getResourceJDBC(c, SDM_REST, log);
} else {
connection = Binaries.getResourceJDBC(c, SDM, log);
}
if (!connection.getMetaData().getDatabaseProductName().contains("Microsoft SQL")) {
throw new ServiceException("Invalid connection type passed to mssql_sdm. Class requires a connection to a MsSQL server.");
} else if (!isValidDb()) {
throw new ServiceException("Invalid connection database type passed to soils_data mssql_sdm implementation. Class requires a connection to an instance of the SDM database.");
}
}
public mssql_sdm(Connection conn, SessionLogger Log) throws ServiceException, SQLException {
DB_GEOM_SRID = "4326";
logPrefix = "mssql_sdm";
LOG = Log;
if (null != conn) {
if (conn.getMetaData().getDatabaseProductName().contains("Microsoft SQL")) {
connection = conn;
if (!isValidDb()) {
throw new ServiceException("Invalid connection database type passed to soils_data mssql_sdm implementation. Class requires a connection to an instance of the SDM database.");
}
} else {
throw new ServiceException("Invalid connection type passed to mssql_sdm. Class requires a connection to a MsSQL server.");
}
} else {
throw new ServiceException("No connection object passed to mssql_sdm. Class requires a connection to a MsSQL server.");
}
}
@Override
protected String buildBasicNoFilterMukeyByCokeyQuery(String cokey) {
return "SELECT "
+ getMapUnitFieldList() + ", "
+ "legend.areasymbol, legend.areaname, mapunit.muacres,"
+ " sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
+ " FROM component "
+ " INNER JOIN mapunit on component.mukey=mapunit.mukey "
+ " INNER JOIN legend on mapunit.lkey=legend.lkey "
+ " INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol "
+ " INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol "
+ " INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol "
+ " WHERE component.cokey=" + Integer.parseInt(cokey)
+ " AND legend.areatypename like 'Non-MLRA%' ;";
}
@Override
protected String buildBasicMukeyByCokeyQuery(String cokey) {
return "SELECT mapunit.mukey, mapunit.musym, mapunit.muname, legend.areasymbol, legend.areaname, mapunit.muacres,"
+ " sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
+ " FROM component "
+ " INNER JOIN mapunit on component.mukey=mapunit.mukey "
+ " INNER JOIN legend on mapunit.lkey=legend.lkey "
+ " INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol "
+ " INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol "
+ " INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol "
+ " WHERE component.cokey=" + Integer.parseInt(cokey)
+ " AND legend.areatypename like 'Non-MLRA%' ;";
}
@Override
protected String buildBasicMukeyQuery(String mukey) {
return "SELECT mapunit.mukey, mapunit.musym, mapunit.muname, legend.areasymbol, legend.areaname, mapunit.muacres,"
+ " sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
+ " FROM mapunit "
+ " INNER JOIN legend on mapunit.lkey=legend.lkey "
+ " INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol "
+ " INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol "
+ " INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol "
+ " WHERE mapunit.mukey=" + Integer.parseInt(mukey)
+ " AND legend.areatypename like 'Non-MLRA%' "
+ " ORDER BY mukey;";
}
@Override
protected String buildBasicMukeyQueryByArea(String areaSymbol) {
return "SELECT mapunit.mukey, mapunit.musym, mapunit.muname, legend.areasymbol, legend.areaname, mapunit.muacres, mupolygon.mupolygongeo.STAsText() as mupolygon, "
+ " sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
+ " FROM legend "
+ " INNER JOIN mapunit on legend.lkey=mapunit.lkey "
+ " INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol "
+ " INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol "
+ " INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol "
+ " INNER JOIN mupolygon on mapunit.mukey=mupolygon.mukey "
+ " WHERE legend.areasymbol='" + areaSymbol + "' "
+ " AND legend.areatypename like 'Non-MLRA%' "
+ " ORDER BY mapunit.mukey;";
}
@Override
protected String buildBasicMukeyQueryByAreaWithShape(String areaSymbol) {
return null;
}
@Override
protected String buildCHFQuery(String mukey, String filter) {
return "SELECT component.majcompflag, component.mukey, component.cokey, component.compname, "
+ " component.comppct_r, component.hydgrp, component.slope_r, "
+ " component.taxorder, chorizon.chkey, chorizon.om_r, "
+ " chorizon.hzthk_r, chorizon.hzdept_r, chorizon.hzdepb_r, "
+ " chorizon.kwfact, chorizon.kffact, chorizon.sar_r, chorizon.ec_r, "
+ " component.wei, component.slopelenusle_r, component.tfact, component.comppct_r, component.hydricrating, "
+ " chorizon.cokey, chorizon.kffact, chorizon.kwfact, "
+ " chorizon.sandtotal_r, chorizon.om_r, chorizon.hzdepb_r, chorizon.hzthk_r, "
+ " chorizon.ph1to1h2o_r, chorizon.ph1to1h2o_l, chorizon.ph1to1h2o_h, "
+ " chorizon.silttotal_r, chorizon.claytotal_r, "
+ " component.taxorder, "
+ " chfrags.fragvol_r, chfrags.chfragskey, chfrags.fraghard, "
+ " chfrags.fragkind, chfrags.fraground, chfrags.fragshp, chfrags.fragsize_h, "
+ " chfrags.fragsize_l, chfrags.fragsize_r, chfrags.fragvol_h, chfrags.fragvol_l "
+ " FROM component "
+ " INNER JOIN chorizon on component.cokey=chorizon.cokey "
+ " LEFT OUTER JOIN chfrags on chorizon.chkey=chfrags.chkey "
+ " WHERE component.mukey=" + mukey + " "
+ " AND component.slope_r is not null"
+ " AND UPPER(component.majcompflag)='YES' "
+ ((filter.equalsIgnoreCase("WATER")) ? " AND (component.taxorder!='' AND component.taxorder is not null) ORDER BY component.cokey, chorizon.hzdept_r "
: " AND (component.taxorder!='' AND component.taxorder is not null) "
+ " AND (chorizon.sandtotal_r is not null OR ( (UPPER(taxorder)='HISTOSOLS') OR ( (UPPER(taxorder)<>'HISTOSOLS') AND hzdepb_r>10 AND om_r>15 ) ))"
+ " ORDER BY component.cokey, chorizon.hzdept_r") + ";";
}
@Override
protected String buildWeppWepsCHFQuery(String cokey, String filter) {
return "SELECT mapunit.musym, mapunit.muname, legend.areasymbol, legend.areaname, "
+ " component.majcompflag, "
+ " component.mukey, "
+ " component.cokey, "
+ " component.compname, "
+ " component.comppct_r, "
+ " component.hydgrp, "
+ " component.slope_r, component.slope_l, component.slope_h, "
+ " component.taxorder, component.taxsubgrp, "
+ " component.wei, "
+ " component.slopelenusle_r, component.slopelenusle_l, component.slopelenusle_h, "
+ " component.tfact, "
+ " component." + TableComponent.ALBEDODRY_R + ", component." + TableComponent.ALBEDODRY_L + ", component." + TableComponent.ALBEDODRY_H + ", "
+ " chorizon.chkey, "
+ " chorizon.om_r, chorizon.om_l, chorizon.om_h, "
+ " chorizon.hzthk_r, chorizon.hzdept_r, chorizon.hzdepb_r, "
+ " chorizon.kwfact, "
+ " chorizon.kffact, "
+ " chorizon.cokey, "
+ " chorizon.sandtotal_r, chorizon.sandtotal_l, chorizon.sandtotal_h, "
+ " chorizon.sandvc_r, chorizon.sandvc_l, chorizon.sandvc_h, "
+ " chorizon.sandco_r, chorizon.sandco_l, chorizon.sandco_h, "
+ " chorizon.sandmed_r, chorizon.sandmed_l, chorizon.sandmed_h, "
+ " chorizon.sandfine_r, chorizon.sandfine_l, chorizon.sandfine_h, "
+ " chorizon.sandvf_r, chorizon.sandvf_l, chorizon.sandvf_h, "
+ " chorizon.caco3_r, chorizon.caco3_l, chorizon.caco3_h, "
+ " chorizon.cec7_l, chorizon.cec7_r, chorizon.cec7_h, "
+ " chorizon.wthirdbar_l, chorizon.wthirdbar_r, chorizon.wthirdbar_h, "
+ " chorizon.wfifteenbar_l, chorizon.wfifteenbar_r, chorizon.wfifteenbar_h, "
+ " chorizon.dbthirdbar_l, chorizon.dbthirdbar_r, chorizon.dbthirdbar_h, "
+ " chorizon.silttotal_r, chorizon.silttotal_l, chorizon.silttotal_h, "
+ " chorizon.claytotal_r, chorizon.claytotal_l, chorizon.claytotal_h, "
+ " chorizon.ph1to1h2o_l, chorizon.ph1to1h2o_r, chorizon.ph1to1h2o_h, "
+ " chorizon." + TableHorizon.ECEC_R + ", chorizon." + TableHorizon.ECEC_L + ", chorizon." + TableHorizon.ECEC_H + ","
+ " chorizon." + TableHorizon.LEP_R + ", chorizon." + TableHorizon.LEP_L + ", chorizon." + TableHorizon.LEP_H + ", "
+ " chorizon." + TableHorizon.KSAT_R + ", chorizon." + TableHorizon.KSAT_R + ", chorizon." + TableHorizon.KSAT_H + " "
+ " chfrags.fragvol_r, chfrags.chfragskey, chfrags.fraghard, "
+ " chfrags.fragkind, chfrags.fraground, chfrags.fragshp, chfrags.fragsize_h, "
+ " chfrags.fragsize_l, chfrags.fragsize_r, chfrags.fragvol_h, chfrags.fragvol_l "
+ " FROM component "
+ " INNER JOIN mapunit ON component.mukey=mapunit.mukey "
+ " INNER JOIN legend ON legend.lkey=mapunit.lkey "
+ " INNER JOIN chorizon on component.cokey=chorizon.cokey "
+ " LEFT OUTER JOIN chfrags on chorizon.chkey=chfrags.chkey "
+ " WHERE component.cokey=" + cokey + " "
+ " AND legend.areatypename like 'Non-MLRA%' "
+ " AND component.slope_r is not null "
+ " AND UPPER(component.majcompflag)='YES' "
+ " AND (component.taxorder!='' AND component.taxorder is not null) "
+ ((filter.equalsIgnoreCase("WATER")) ? " AND (component.taxorder!='' AND component.taxorder is not null) ORDER BY chorizon.hzdept_r, chorizon.chkey "
: ((filter.equalsIgnoreCase("WIND")) ? " AND (component.taxorder!='' AND component.taxorder is not null) "
+ " AND (chorizon.sandtotal_r is not null OR ( (UPPER(taxorder)='HISTOSOLS') OR ( (UPPER(taxorder)<>'HISTOSOLS') AND hzdepb_r>10 AND om_r>15 ) )) "
: "")
+ " ORDER BY chorizon.hzdept_r, chorizon.chkey ") + ";";
}
@Override
protected String buildCHQuery(String cokey, String filter) {
return "SELECT mapunit.musym, mapunit.muname, legend.areasymbol, legend.areaname, "
+ " component.majcompflag, "
+ " component.mukey, "
+ " component.cokey, "
+ " component.compname, "
+ " component.comppct_r, "
+ " component.hydgrp, "
+ " component.slope_r, component.slope_l, component.slope_h, "
+ " component.taxorder, component.taxsubgrp, "
+ " component.wei, "
+ " component.slopelenusle_r, component.slopelenusle_l, component.slopelenusle_h, component.hydricrating, "
+ " component.tfact, "
+ " component." + TableComponent.ALBEDODRY_R + ", component." + TableComponent.ALBEDODRY_L + ", component." + TableComponent.ALBEDODRY_H + ", "
+ " chorizon.chkey, "
+ " chorizon.om_r, chorizon.om_l, chorizon.om_h, "
+ " chorizon.hzthk_r, chorizon.hzdept_r, chorizon.hzdepb_r, "
+ " chorizon.kwfact, "
+ " chorizon.kffact, "
+ " chorizon.cokey, "
+ " chorizon.sandtotal_r, chorizon.sandtotal_l, chorizon.sandtotal_h, "
+ " chorizon.sandvc_r, chorizon.sandvc_l, chorizon.sandvc_h, "
+ " chorizon.sandco_r, chorizon.sandco_l, chorizon.sandco_h, "
+ " chorizon.sandmed_r, chorizon.sandmed_l, chorizon.sandmed_h, "
+ " chorizon.sandfine_r, chorizon.sandfine_l, chorizon.sandfine_h, "
+ " chorizon.sandvf_r, chorizon.sandvf_l, chorizon.sandvf_h, "
+ " chorizon.caco3_r, chorizon.caco3_l, chorizon.caco3_h, "
+ " chorizon.cec7_l, chorizon.cec7_r, chorizon.cec7_h, "
+ " chorizon.wthirdbar_l, chorizon.wthirdbar_r, chorizon.wthirdbar_h, "
+ " chorizon.wfifteenbar_l, chorizon.wfifteenbar_r, chorizon.wfifteenbar_h, "
+ " chorizon.dbthirdbar_l, chorizon.dbthirdbar_r, chorizon.dbthirdbar_h, "
+ " chorizon.silttotal_r, chorizon.silttotal_l, chorizon.silttotal_h, "
+ " chorizon.claytotal_r, chorizon.claytotal_l, chorizon.claytotal_h, "
+ " chorizon.ph1to1h2o_l, chorizon.ph1to1h2o_r, chorizon.ph1to1h2o_h, "
+ " chorizon." + TableHorizon.ECEC_R + ", chorizon." + TableHorizon.ECEC_L + ", chorizon." + TableHorizon.ECEC_H + ","
+ " chorizon." + TableHorizon.LEP_R + ", chorizon." + TableHorizon.LEP_L + ", chorizon." + TableHorizon.LEP_H + ", "
+ " chorizon." + TableHorizon.KSAT_R + ", chorizon." + TableHorizon.KSAT_R + ", chorizon." + TableHorizon.KSAT_H + ", "
+ " chorizon." + TableHorizon.EC_R + ", chorizon." + TableHorizon.SAR_R + ", "
+ " chfrags.fragvol_r, chfrags.chfragskey, chfrags.fraghard, "
+ " chfrags.fragkind, chfrags.fraground, chfrags.fragshp, chfrags.fragsize_h, "
+ " chfrags.fragsize_l, chfrags.fragsize_r, chfrags.fragvol_h, chfrags.fragvol_l "
+ " FROM component "
+ " INNER JOIN mapunit ON component.mukey=mapunit.mukey "
+ " INNER JOIN legend ON legend.lkey=mapunit.lkey "
+ " INNER JOIN chorizon on component.cokey=chorizon.cokey "
+ " LEFT OUTER JOIN chfrags on chorizon.chkey=chfrags.chkey "
+ " WHERE component.cokey=" + cokey + " "
+ " AND legend.areatypename like 'Non-MLRA%' "
+ " AND component.slope_r is not null "
+ " AND UPPER(component.majcompflag)='YES' "
+ " AND (component.taxorder!='' AND component.taxorder is not null) "
+ ((filter.equalsIgnoreCase("WATER")) ? " AND (component.taxorder!='' AND component.taxorder is not null) ORDER BY chorizon.hzdept_r "
: ((filter.equalsIgnoreCase("WIND")) ? " AND (component.taxorder!='' AND component.taxorder is not null) "
+ " AND (chorizon.sandtotal_r is not null OR ( (UPPER(taxorder)='HISTOSOLS') OR ( (UPPER(taxorder)<>'HISTOSOLS') AND hzdepb_r>10 AND om_r>15 ) )) "
: "")
+ " ORDER BY chorizon.hzdept_r ") + ";";
}
@Override
protected String buildCSMQuery(MapUnit mapUnit) {
String ret_val = "";
String lastQueryWhere = "";
String lastQueryWhere2 = "";
if (mapUnit.components().size() > 0) {
for (String cokey : mapUnit.components().keySet()) {
if (lastQueryWhere.length() > 1) {
lastQueryWhere += " OR component.cokey='" + cokey + "'";
lastQueryWhere2 += " OR WT1.cokey='" + cokey + "'";
} else {
lastQueryWhere += " component.cokey='" + cokey + "'";
lastQueryWhere2 += "WT1.cokey='" + cokey + "'";
}
}
ret_val = "With WT1 As (Select component.cokey, component.compname, component.comppct_r, MIN(cosoilmoist.soimoistdept_r) As wtbl_top_min, MAX(cosoilmoist.soimoistdepb_r) As wtbl_bot_max From component Inner Join comonth On component.cokey=comonth.cokey Inner Join cosoilmoist On comonth.comonthkey=cosoilmoist.comonthkey "
+ "Where " + lastQueryWhere + "and cosoilmoist.soimoiststat='Wet' Group By component.cokey, component.compname, component.comppct_r), WT2 As (Select WT1.cokey, WT1.compname, WT1.comppct_r, WT1.wtbl_top_min, WT1.wtbl_bot_max, MAX(cosoilmoist.soimoistdept_r) As nonwet_top_max From WT1 Left Outer Join comonth On WT1.cokey=comonth.cokey Left Outer Join cosoilmoist On comonth.comonthkey=cosoilmoist.comonthkey "
+ "Where " + lastQueryWhere2 + " and (cosoilmoist.soimoiststat NOT IN ('Wet') OR cosoilmoist.soimoiststat IS NULL) Group By WT1.cokey, WT1.compname, WT1.comppct_r, WT1.wtbl_top_min, WT1.wtbl_bot_max) Select WT2.cokey, WT2.compname, WT2.comppct_r, WT2.wtbl_top_min, WT2.wtbl_bot_max, WT2.nonwet_top_max, case when (wtbl_bot_max < 165 or nonwet_top_max >= wtbl_bot_max) then 'Perched' else 'Apparent' end as wtkind from WT2";
}
return ret_val;
}
@Override
protected String buildCoCropYldQuery(HashMap<String, MapUnit> mapUnits, ArrayList<String> cropNames, ArrayList<String> yldUnits) {
String query = "";
String cokeyList;
String cropNameList = "";
String unitList = "";
if ((null != mapUnits) && (mapUnits.size() > 0)) {
cokeyList = "(";
int count = 0;
for (MapUnit mapunit : mapUnits.values()) {
LinkedHashMap<String, Component> components = mapunit.components();
for (String cokey : components.keySet()) {
if (count > 0) {
cokeyList += ", ";
}
cokeyList += cokey;
count++;
}
}
cokeyList += ") ";
if (null != cropNames) {
count = 0;
for (String cropName : cropNames) {
if (count > 0) {
cropNameList += ", ";
}
String cleanCropName = EvalResult.cleanStringForSQL(cropName);
if (!cleanCropName.isEmpty()) {
cropNameList += "'" + cleanCropName + "'";
count++;
}
}
}
if (null != yldUnits) {
count = 0;
for (String yldUnit : yldUnits) {
if (count > 0) {
unitList += ", ";
}
String cleanYldUnit = EvalResult.cleanStringForSQL(yldUnit);
if (!cleanYldUnit.isEmpty()) {
unitList += "'" + cleanYldUnit + "'";
count++;
}
}
}
cokeyList += ((cropNameList.isEmpty()) ? "" : (" AND " + "cocropyld." + TableCocropyld.CROPNAME + " in (" + cropNameList + ") "));
cokeyList += ((unitList.isEmpty()) ? "" : (" AND " + "cocropyld." + TableCocropyld.YLDUNITS + " in (" + unitList + ") "));
cokeyList += " ORDER BY mukey, cocropyld.cokey;";
query = "SELECT mapunit.mukey, "
+ "cocropyld." + TableCocropyld.CROPNAME + ", "
+ "cocropyld." + TableCocropyld.YLDUNITS + ", "
+ "cocropyld." + TableCocropyld.NONIRRYIELD_L + ", "
+ "cocropyld." + TableCocropyld.NONIRRYIELD_R + ", "
+ "cocropyld." + TableCocropyld.NONIRRYIELD_H + ", "
+ "cocropyld." + TableCocropyld.IRRYIELD_L + ", "
+ "cocropyld." + TableCocropyld.IRRYIELD_R + ", "
+ "cocropyld." + TableCocropyld.IRRYIELD_H + ", "
+ "cocropyld." + TableCocropyld.CROPPRODINDEX + ", "
+ "cocropyld." + TableCocropyld.VASOIPRDGRP + ", "
+ "cocropyld." + TableCocropyld.COKEY + ", "
+ "cocropyld." + TableCocropyld.COCROPYLDKEY + " "
+ " FROM cocropyld "
+ " INNER JOIN component on cocropyld.cokey=component.cokey "
+ " INNER JOIN mapunit on component.mukey=mapunit.mukey "
+ " WHERE cocropyld.cokey in " + cokeyList;
}
return query;
}
@Override
protected String buildCoCropYldQuery(LinkedHashMap<String, Component> component) {
String query = "";
String cokeyList;
if ((null != component) && (component.size() > 0)) {
cokeyList = "(";
int count = 0;
for (String cokey : component.keySet()) {
if (count > 0) {
cokeyList += ", ";
}
cokeyList += cokey;
count++;
}
cokeyList += ") ORDER BY mapunit.mukey, cocropyld.cokey;";
query = "SELECT mapunit.mukey, "
+ "cocropyld." + TableCocropyld.CROPNAME + ", "
+ "cocropyld." + TableCocropyld.YLDUNITS + ", "
+ "cocropyld." + TableCocropyld.NONIRRYIELD_L + ", "
+ "cocropyld." + TableCocropyld.NONIRRYIELD_R + ", "
+ "cocropyld." + TableCocropyld.NONIRRYIELD_H + ", "
+ "cocropyld." + TableCocropyld.IRRYIELD_L + ", "
+ "cocropyld." + TableCocropyld.IRRYIELD_R + ", "
+ "cocropyld." + TableCocropyld.IRRYIELD_H + ", "
+ "cocropyld." + TableCocropyld.CROPPRODINDEX + ", "
+ "cocropyld." + TableCocropyld.VASOIPRDGRP + ", "
+ "cocropyld." + TableCocropyld.COKEY + ", "
+ "cocropyld." + TableCocropyld.COCROPYLDKEY + " "
+ " FROM cocropyld "
+ " INNER JOIN component on cocropyld.cokey=component.cokey "
+ " INNER JOIN mapunit on component.mukey=mapunit.mukey "
+ " WHERE cocropyld.cokey in " + cokeyList;
}
return query;
}
@Override
protected String buildCoEcoClassQuery(HashMap<String, MapUnit> mapUnits) {
String query = "";
String cokeyList;
if ((null != mapUnits) && (mapUnits.size() > 0)) {
cokeyList = "(";
int count = 0;
for (MapUnit mapunit : mapUnits.values()) {
LinkedHashMap<String, Component> components = mapunit.components();
for (String cokey : components.keySet()) {
if (count > 0) {
cokeyList += ", ";
}
cokeyList += cokey;
count++;
}
}
cokeyList += ") ORDER BY mapunit.mukey, component.cokey;";
query = "SELECT mapunit.mukey, "
+ TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.ECOCLASSTYPENAME + ", "
+ TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.ECOCLASSREF + ", "
+ TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.ECOCLASSID + ", "
+ TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.ECOCLASSNAME + ", "
+ TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.COKEY + ", "
+ TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.COECOCLASSKEY + ", "
+ TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.SOURCESDWPRIMARYKEY + ", "
+ TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.SOURCESDWTABLEPHYSICALNAME + " "
+ " FROM coecoclass "
+ " INNER JOIN component on coecoclass.cokey=component.cokey "
+ " INNER JOIN mapunit on component.mukey=mapunit.mukey "
+ " WHERE coecoclass.cokey in " + cokeyList;
}
return query;
}
@Override
protected String buildCoEcoClassQuery(LinkedHashMap<String, Component> component) {
String query = "";
String cokeyList;
if ((null != component) && (component.size() > 0)) {
cokeyList = "(";
int count = 0;
for (String cokey : component.keySet()) {
if (count > 0) {
cokeyList += ", ";
}
cokeyList += cokey;
count++;
}
cokeyList += ") ORDER BY mukey, cokey;";
query = "SELECT mapunit.mukey, "
+ TableCoecoclass.ECOCLASSTYPENAME + ", "
+ TableCoecoclass.ECOCLASSREF + ", "
+ TableCoecoclass.ECOCLASSID + ", "
+ TableCoecoclass.ECOCLASSNAME + ", "
+ TableCoecoclass.COKEY + ", "
+ TableCoecoclass.COECOCLASSKEY + ", "
+ TableCoecoclass.SOURCESDWPRIMARYKEY + ", "
+ TableCoecoclass.SOURCESDWTABLEPHYSICALNAME + " "
+ " FROM coecoclass "
+ " INNER JOIN component on coecoclass.cokey=component.cokey "
+ " INNER JOIN mapunit on component.mukey=mapunit.mukey "
+ " WHERE coecoclass.cokey in " + cokeyList;
}
return query;
}
@Override
protected String buildCoMonthQuery(HashMap<String, MapUnit> mapUnits) {
String query = "";
String cokeyList;
if ((null != mapUnits) && (mapUnits.size() > 0)) {
cokeyList = "(";
int count = 0;
for (MapUnit mapunit : mapUnits.values()) {
LinkedHashMap<String, Component> components = mapunit.components();
for (String cokey : components.keySet()) {
if (count > 0) {
cokeyList += ", ";
}
cokeyList += cokey;
count++;
}
}
cokeyList += ") ORDER BY " + TableComponent.TABLE_NAME + "." + TableComponent.MUKEY + ", " + TableComponent.TABLE_NAME + "." + TableComponent.COKEY + ", "
+ TableComonth.TABLE_NAME + "." + TableComonth.MONTH_SEQ + " ;";
query = "SELECT " + TableComponent.TABLE_NAME + "." + TableComponent.MUKEY + ","
+ TableComonth.TABLE_NAME + "." + TableComonth.COMONTH_KEY + ", "
+ TableComonth.TABLE_NAME + "." + TableComonth.MONTH + ", "
+ TableComonth.TABLE_NAME + "." + TableComonth.FLOOD_FREQ_CL + ", "
+ TableComonth.TABLE_NAME + "." + TableComonth.POND_FREQ_CL + ", "
+ TableComonth.TABLE_NAME + "." + TableComonth.MONTH_SEQ + ", "
+ TableComonth.TABLE_NAME + "." + TableComonth.COKEY + " "
+ " FROM " + TableComonth.TABLE_NAME + " "
+ " INNER JOIN "
+ TableComponent.TABLE_NAME + " ON "
+ TableComonth.TABLE_NAME + "." + TableComonth.COKEY + "="
+ TableComponent.TABLE_NAME + "." + TableComponent.COKEY + " "
+ " WHERE " + TableComonth.TABLE_NAME + "." + TableComonth.COKEY + " IN " + cokeyList;
}
return query;
}
@Override
protected String buildFindMapUnitsByPolyKeyQuery(String mupolygonkey, boolean returnIntersectionShape) {
return "SELECT mupolygon.areasymbol, mapunit.musym, mapunit.mukey, mapunit.muacres, mapunit.muname, muaggatt.brockdepmin, mupolygon.muareaacres as area, "
+ ((returnIntersectionShape) ? " mupolygon.mupolygongeo.STAsText() AS intersectionShape, " : " ")
+ " sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
+ " FROM dbo.mupolygon "
+ " INNER JOIN dbo.mapunit ON mupolygon.mukey=dbo.mapunit.mukey "
+ " INNER JOIN dbo.legend ON mapunit.lkey=dbo.legend.lkey "
+ " INNER JOIN dbo.muaggatt ON mapunit.mukey=dbo.muaggatt.mukey "
+ " INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol "
+ " INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol "
+ " INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol "
+ " WHERE mupolygon.mupolygonkey=" + mupolygonkey + ";";
}
@Override
protected String buildFindMapUnitsQuery(String WKTShape, boolean returnIntersectionShape) {
return "SELECT areasymbol, areaname, musym, mukey, muname, muacres, brockdepmin, geography::STGeomFromText(intersectPoly.STAsText(), " + DEFAULT_GEOG_SRID + ").MakeValid().STArea() / 4046.86 AS area "
+ (returnIntersectionShape ? ", geography::STGeomFromText(intersectPoly.STAsText(), " + DEFAULT_GEOG_SRID + ").MakeValid().STAsText() AS intersectionShape " : " ")
+ " , saversion, saverest, spatialversion, spatialverest, tabularversion, tabularverest "
+ " FROM "
+ " (SELECT m.areasymbol, legend.areaname, m.musym, m.mukey, mapunit.muacres, mapunit.muname, muaggatt.brockdepmin, "
+ " m.mupolygongeo.STIntersection(geometry::STGeomFromText ('" + WKTShape + "', " + DB_GEOM_SRID + ")).MakeValid() as intersectPoly, "
+ " sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
+ " FROM dbo.mupolygon as m "
+ " WITH (index(SI_mupolygon_24876)) "
+ " INNER JOIN dbo.mapunit ON m.mukey=dbo.mapunit.mukey "
+ " INNER JOIN dbo.legend ON mapunit.lkey=dbo.legend.lkey "
+ " INNER JOIN dbo.muaggatt ON m.mukey=dbo.muaggatt.mukey "
+ " INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol "
+ " INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol "
+ " INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol "
+ " WHERE m.mupolygongeo.STIntersects(geometry::STGeomFromText ('" + WKTShape + "', " + DB_GEOM_SRID + ")) = 1 "
+ " AND m.mupolygongeo.STIsValid()=1 "
+ " AND (geometry:: STGeomFromText ('" + WKTShape + "', " + DB_GEOM_SRID + ").STIsValid())=1 "
+ " AND legend.areatypename like 'Non-MLRA%' ) as a"
+ " ORDER BY mukey; ";
}
@Override
protected String buildFragmentsQuery(String chkey) {
return "SELECT " + TableFragments.CHFRAGSKEY + ", " + TableFragments.FRAGVOL_R + ", " + TableFragments.FRAGVOL_L + ", " + TableFragments.FRAGVOL_H
+ ", " + TableFragments.FRAGSIZE_R + ", " + TableFragments.FRAGSIZE_L + ", " + TableFragments.FRAGSIZE_H
+ ", " + TableFragments.FRAGHARD + ", " + TableFragments.FRAGSHP + ", " + TableFragments.FRAGROUND + ", " + TableFragments.FRAGKIND
+ " FROM chfrags WHERE chkey=" + chkey + " Order By " + TableFragments.FRAGVOL_R + " ; ";
}
@Override
protected String buildGenericMapUnitIntersectQuery() {
throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
}
@Override
protected String buildIsConusQuery(String lat, String lon) {
return "SELECT top 1 areasymbol, "
+ " CASE WHEN SUBSTRING(areasymbol, 1, 2) in ('AL','AZ','AR','CA','CO','CT','DC','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY')THEN 1 ELSE 0 END as CONUS "
+ " from dbo.mupolygon "
+ " WITH (index(SI_mupolygon_24876)) "
+ " WHERE mupolygongeo.STIntersects(geometry::STGeomFromText('POINT("
+ lon + " " + lat + ")', 4326)) = 1;";
}
@Override
protected String buildMinResdept_rQuery(String cokey) {
return "SELECT min(resdept_r) as " + TableComponentCalculations.RESDEPTMIN_R + " FROM corestrictions WHERE cokey=" + cokey + "; ";
}
@Override
protected String buildMuCropYldQuery(HashMap<String, MapUnit> mapUnits, ArrayList<String> cropNames, ArrayList<String> yldUnits) {
String query = "";
String cropNameList = "";
String unitList = "";
int count = 0;
if ((null != mapUnits) && (mapUnits.size() > 0)) {
query = "SELECT " + TableMucropyld.CROPNAME + ", "
+ TableMucropyld.YLDUNITS + ", "
+ TableMucropyld.NONIRRYIELD_L + ", "
+ TableMucropyld.NONIRRYIELD_R + ", "
+ TableMucropyld.NONIRRYIELD_H + ", "
+ TableMucropyld.IRRYIELD_L + ", "
+ TableMucropyld.IRRYIELD_R + ", "
+ TableMucropyld.IRRYIELD_H + ", "
+ TableMucropyld.MUKEY + ", "
+ TableMucropyld.MUCRPYLDKEY
+ " FROM mucropyld WHERE mukey in ( ";
for (String mukey : mapUnits.keySet()) {
if (count > 0) {
query += ", ";
}
query += mukey;
count++;
}
query += ") ";
if (null != cropNames) {
count = 0;
for (String cropName : cropNames) {
if (count > 0) {
cropNameList += ", ";
}
String cleanCropName = EvalResult.cleanStringForSQL(cropName);
if (!cleanCropName.isEmpty()) {
cropNameList += "'" + cleanCropName + "'";
count++;
}
}
}
if (null != yldUnits) {
count = 0;
for (String yldUnit : yldUnits) {
if (count > 0) {
unitList += ", ";
}
String cleanYldUnit = EvalResult.cleanStringForSQL(yldUnit);
if (!cleanYldUnit.isEmpty()) {
unitList += "'" + cleanYldUnit + "'";
count++;
}
}
}
query += ((cropNameList.isEmpty()) ? "" : (" AND " + TableMucropyld.CROPNAME + " in (" + cropNameList + ") "));
query += ((unitList.isEmpty()) ? "" : (" AND " + TableMucropyld.YLDUNITS + " in (" + unitList + ") "));
query += " ORDER BY mukey;";
}
return query;
}
@Override
protected String buildPlainComponentQuery(String mukey) {
return "SELECT " + getComponentFieldList() + " FROM " + TableComponent.TABLE_NAME
+ " WHERE " + TableComponent.TABLE_NAME + "." + TableComponent.MUKEY + "=" + mukey + " ;";
}
/**
* Retrieves all component, horizon and fragments information for each mapunit
* specified in mapUnits.
*
* @param mapUnits
* @return
* @throws ServiceException
*/
@Override
protected String buildPlainNoFilterCHFQuery(HashMap<String, MapUnit> mapUnits) throws ServiceException {
return buildPlainNoFilterCHFQuery(mapUnits, false);
}
@Override
protected String buildBasicNoFilterCHFQuery(HashMap<String, MapUnit> mapUnits) throws ServiceException {
return buildPlainNoFilterCHFQuery(mapUnits, true);
}
@Override
protected String buildPlainNoFilterCHFQuery(HashMap<String, MapUnit> mapUnits, boolean includeNonMajComp) throws ServiceException {
String mukeyList = "";
if ((null != mapUnits) && (mapUnits.size() > 0)) {
for (MapUnit mapunit : mapUnits.values()) {
if (mukeyList.length() > 1) {
mukeyList += ", ";
}
mukeyList += mapunit.mukey();
}
} else {
throw new ServiceException("No Mapunits specified to the buildPlainNoFilterCHFQuery() Function");
}
return "SELECT "
+ getComponentFieldList() + ", "
+ getHorizonFieldList() + ", "
+ getFragFieldList()
+ " FROM " + TableComponent.TABLE_NAME
+ " INNER JOIN " + TableHorizon.TABLE_NAME + " ON "
+ TableComponent.TABLE_NAME + "." + TableComponent.COKEY
+ "="
+ TableHorizon.TABLE_NAME + "." + TableHorizon.COKEY
+ " LEFT OUTER JOIN "
+ TableFragments.TABLE_NAME + " ON "
+ TableHorizon.TABLE_NAME + "." + TableHorizon.CHKEY_NAME
+ "="
+ TableFragments.TABLE_NAME + "." + TableFragments.CHKEY
+ " WHERE "
+ TableComponent.TABLE_NAME + "." + TableComponent.MUKEY + " IN (" + mukeyList + ") "
+ (!includeNonMajComp
? " AND UPPER(" + TableComponent.TABLE_NAME + "." + TableComponent.MAJ_COMP_FLAG + ")='YES' "
: "")
+ " ORDER BY "
+ TableComponent.TABLE_NAME + "." + TableComponent.MUKEY
+ ", "
+ TableComponent.TABLE_NAME + "." + TableComponent.COKEY
+ ", "
+ TableHorizon.TABLE_NAME + "." + TableHorizon.HZDEPT_R_NAME
+ ", "
+ TableHorizon.TABLE_NAME + "." + TableHorizon.CHKEY_NAME
+ ", "
+ TableFragments.TABLE_NAME + "." + TableFragments.CHFRAGSKEY
+ ";";
}
@Override
protected String buildPlainCHFQuery(String mukey) {
return "SELECT component.majcompflag, component.mukey, component.cokey, component.compname, "
+ " component.comppct_r, component.hydgrp, component.slope_r, "
+ " component." + TableComponent.RS_PROD_H + ", "
+ " component." + TableComponent.RS_PROD_L + ", "
+ " component." + TableComponent.RS_PROD_R + ", "
+ " component." + TableComponent.OTHERPH_NAME + ", "
+ " component." + TableComponent.LOCALPHASE_NAME + ", "
+ " component." + TableComponent.MAJ_COMP_FLAG + ", "
+ " component.taxorder, chorizon.chkey, chorizon.om_r, "
+ " chorizon.hzthk_r, chorizon.hzdept_r, chorizon.hzdepb_r, "
+ " chorizon.kwfact, chorizon.kffact, "
+ " component.wei, component.slopelenusle_r, component.tfact, "
+ " chorizon.cokey, chorizon.kffact, chorizon.kwfact, "
+ " chorizon.sandtotal_r, chorizon.om_r, chorizon.hzdepb_r, chorizon.hzthk_r, "
+ " chorizon.ph1to1h2o_r, chorizon.ph1to1h2o_l, chorizon.ph1to1h2o_h, "
+ " chorizon.silttotal_r, chorizon.claytotal_r, "
+ " chfrags.fragvol_r, chfrags.chfragskey, chfrags.fraghard, "
+ " chfrags.fragkind, chfrags.fraground, chfrags.fragshp, chfrags.fragsize_h, "
+ " chfrags.fragsize_l, chfrags.fragsize_r, chfrags.fragvol_h, chfrags.fragvol_l "
+ " FROM component "
+ " INNER JOIN chorizon on component.cokey=chorizon.cokey "
+ " LEFT OUTER JOIN chfrags on chorizon.chkey=chfrags.chkey "
+ " WHERE component.mukey=" + mukey + " "
+ " AND component.slope_r is not null"
+ " AND UPPER(component.majcompflag)='YES' ;";
}
@Override
protected String buildStateAreaSymbolQuery(String stateAbbrev) {
return "SELECT areasymbol FROM legend "
+ " WHERE areatypename like 'Non-MLRA%' "
+ " AND areasymbol like '" + stateAbbrev + "%' "
+ " ORDER BY areasymbol;";
}
@Override
protected String buildStateCountyByLocationQuery(String countyWKT) {
String query = null;
if (null != countyWKT) {
query = "SELECT distinct(areasymbol) as areasymbol FROM ( "
+ " SELECT legend.areasymbol "
+ " FROM sapolygon "
+ " INNER JOIN dbo.legend ON sapolygon.lkey=dbo.legend.lkey "
+ " WHERE sapolygongeo.STIntersects( geometry::STGeomFromText( '" + countyWKT + "', 4326).MakeValid())=1 "
+ " AND sapolygongeo.STIsValid()=1 "
+ " AND legend.areatypename like 'Non-MLRA%' "
+ " ) as a order by areasymbol;";
}
return query;
}
@Override
protected String buildTextureDataQuery(HashMap<String, MapUnit> mapUnits) {
String query = "";
String keyList;
if ((null != mapUnits) && (mapUnits.size() > 0)) {
keyList = "(";
int count = 0;
for (MapUnit mapunit : mapUnits.values()) {
LinkedHashMap<String, Component> components = mapunit.components();
for (Component component : components.values()) {
LinkedHashMap<String, Horizon> horizons = component.horizons();
for (Horizon horizon : horizons.values()) {
if (count > 0) {
keyList += ", ";
}
keyList += horizon.chkey();
count++;
}
}
}
if (count > 0) {
keyList += ") ORDER BY mapunit.mukey, component.cokey, chorizon.chkey, chtexturegrp.chtgkey, chtexture.chtkey;";
query = "SELECT mapunit.mukey, component.cokey, chorizon.chkey "
+ ", chtexturegrp." + TableTextureGroup.CHTGKEY
+ ", chtexturegrp." + TableTextureGroup.DESCRIPTION
+ ", chtexturegrp." + TableTextureGroup.TEXTURE
+ ", chtexture." + TableTexture.CHTKEY
+ ", chtexture." + TableTexture.TEXTURE_CL
+ ", chtexture." + TableTexture.LIEUTEX
+ ", CASE WHEN UPPER(chtexturegrp." + TableTextureGroup.STRATEXTSFLAG + ")='YES' THEN '1' ELSE '0' END AS " + TableTextureGroup.STRATEXTSFLAG
+ ", CASE WHEN UPPER(chtexturegrp." + TableTextureGroup.RVINDICATOR + ")='YES' THEN '1' ELSE '0' END AS " + TableTextureGroup.RVINDICATOR
+ " FROM chtexturegrp "
+ " INNER JOIN chtexture on chtexturegrp." + TableTextureGroup.CHTGKEY + "=chtexture." + TableTexture.CHTGKEY
+ " INNER JOIN chorizon on chtexturegrp.chkey=chorizon.chkey "
+ " INNER JOIN component on chorizon.cokey=component.cokey "
+ " INNER JOIN mapunit on component.mukey=mapunit.mukey "
+ " WHERE chtexturegrp." + TableTextureGroup.CHKEY + " IN " + keyList
+ " ;";
}
}
return query;
}
@Override
protected String buildTextureGroupsQuery(String chkey) {
return "SELECT " + TableTextureGroup.CHTGKEY + ", " + TableTextureGroup.TEXTURE
+ ", CASE WHEN UPPER(" + TableTextureGroup.STRATEXTSFLAG + ")='YES' THEN '1' ELSE '0' END AS " + TableTextureGroup.STRATEXTSFLAG
+ ", CASE WHEN UPPER(" + TableTextureGroup.RVINDICATOR + ")='YES' THEN '1' ELSE '0' END AS " + TableTextureGroup.RVINDICATOR
+ " FROM chtexturegrp WHERE chkey=" + chkey + "; ";
}
////////////////////////////////
//
// Private member functions
//
////////////////////////////////
@Override
protected final boolean isValidDb() {
boolean ret_val = false;
String query = "select top 1 mukey from dbo.mupolygon;";
try (Statement statement = connection.createStatement();) {
ResultSet results = statement.executeQuery(query);
if (results.next()) {
ret_val = true;
}
} catch (SQLException ex) {
//Not a valid SDM database...
Log(Level.SEVERE, "Database provided to soils_data.mssql_sdm is not a valid SDM instance. Cannot continue: " + ex.getMessage(), ex);
}
return ret_val;
}
@Override
public boolean filterComponentHorizonFragData(ResultSet dbResults, FilterResults filterResults, MapUnit mapUnit, String filter, boolean computeLightleWeesieSlope) throws SQLException, ServiceException {
return super.filterComponentHorizonFragData(dbResults, filterResults, mapUnit, filter, computeLightleWeesieSlope); //To change body of generated methods, choose Tools | Templates.
}
@Override
public boolean filterRusle2ComponentHorizonData(ResultSet dbResults, FilterResults filterResults, MapUnit mapUnit, Component tComponent) throws SQLException, ServiceException {
return super.filterRusle2ComponentHorizonData(dbResults, filterResults, mapUnit, tComponent); //To change body of generated methods, choose Tools | Templates.
}
@Override
public boolean filterWEPSComponentHorizonFragData(ResultSet dbResults, FilterResults filterResults, MapUnit mapUnit, Component tComponent, boolean computeLightleWeesieSlope) throws SQLException, ServiceException {
return super.filterWEPSComponentHorizonFragData(dbResults, filterResults, mapUnit, tComponent, computeLightleWeesieSlope); //To change body of generated methods, choose Tools | Templates.
}
@Override
public boolean findComponentsForMapUnit(MapUnit mapUnit) throws ServiceException {
throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
}
@Override
public MapUnit findComponentsForMukey(String mukey) throws ServiceException {
throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
}
@Override
protected String buildValidateCokeyQuery(int cokey) {
return "SELECT cokey from component WHERE cokey=" + cokey + ";";
}
@Override
protected String buildFindAllByShapeQuery(String WKT, boolean returnIntersectionShape) throws SDMException {
String query = "";
TableMapUnit mapUnit = new TableMapUnit();
TableMuaggatt muaggatt = new TableMuaggatt();
TableComponent component = new TableComponent();
TableHorizon horizon = new TableHorizon();
TableFragments fragments = new TableFragments();
TableTexture texture = new TableTexture();
TableTextureGroup textureGroup = new TableTextureGroup();
ArrayList<String> tableColumns = mapUnit.getColumnList();
ArrayList<String> asColumnNames = new ArrayList<>();
query = "SELECT ";
for (String key : muaggatt.getColumnList()) {
if (!tableColumns.contains(key)) {
tableColumns.add(key);
}
}
for (String key : component.getColumnList()) {
if (!tableColumns.contains(key)) {
tableColumns.add(key);
}
}
for (String key : horizon.getColumnList()) {
if (!tableColumns.contains(key)) {
tableColumns.add(key);
}
}
for (String key : fragments.getColumnList()) {
if (!tableColumns.contains(key)) {
tableColumns.add(key);
}
}
for (String key : texture.getColumnList()) {
if (!tableColumns.contains(key)) {
tableColumns.add(key);
}
}
for (String key : textureGroup.getColumnList()) {
if (!tableColumns.contains(key)) {
tableColumns.add(key);
}
}
for (String key : tableColumns) {
query += "a." + key + ", ";
}
query += " a.saversion, a.saverest, a.spatialversion, a.spatialverest, a.tabularversion, a.tabularverest "
+ ((returnIntersectionShape) ? ", a.intersectPoly" : "");
query += " FROM "
+ "( SELECT ";
tableColumns.clear();
for (String key : mapUnit.getColumnList()) {
if (key.equalsIgnoreCase("areaname")) {
tableColumns.add("legend." + key);
} else {
if (key.equalsIgnoreCase("areasymbol")) {
tableColumns.add("legend." + key);
} else {
tableColumns.add(mapUnit.getTableName() + "." + key);
}
}
asColumnNames.add(key);
}
for (String key : muaggatt.getColumnList()) {
if (!asColumnNames.contains(key)) {
tableColumns.add(muaggatt.getTableName() + "." + key);
asColumnNames.add(key);
}
}
for (String key : component.getColumnList()) {
if (!asColumnNames.contains(key)) {
tableColumns.add(component.getTableName() + "." + key);
asColumnNames.add(key);
}
}
for (String key : horizon.getColumnList()) {
if (!asColumnNames.contains(key)) {
tableColumns.add(horizon.getTableName() + "." + key);
asColumnNames.add(key);
}
}
for (String key : fragments.getColumnList()) {
if (!asColumnNames.contains(key)) {
tableColumns.add(fragments.getTableName() + "." + key);
asColumnNames.add(key);
}
}
for (String key : texture.getColumnList()) {
if (!asColumnNames.contains(key)) {
tableColumns.add(texture.getTableName() + "." + key);
asColumnNames.add(key);
}
}
for (String key : textureGroup.getColumnList()) {
if (!asColumnNames.contains(key)) {
tableColumns.add(textureGroup.getTableName() + "." + key);
asColumnNames.add(key);
}
}
if (tableColumns.size() != asColumnNames.size()) {
throw new SDMException("Cannot build list of column names. As list size does not match column list size.");
}
for (int i = 0; i < tableColumns.size(); i++) {
String cName = tableColumns.get(i);
String asName = asColumnNames.get(i);
query += cName + " as " + asName;
query += ", ";
}
query += " sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
+ ((returnIntersectionShape) ? ", m.mupolygongeo.STIntersection(geometry::STGeomFromText (' " + WKT + "', " + DB_GEOM_SRID + ")).MakeValid() as intersectPoly " : "")
+ " FROM dbo.mupolygon as m WITH (index(SI_mupolygon_24876)) "
+ " INNER JOIN dbo.mapunit ON m.mukey=dbo.mapunit.mukey "
+ " INNER JOIN dbo.legend ON mapunit.lkey=dbo.legend.lkey "
+ " INNER JOIN dbo.muaggatt ON m.mukey=dbo.muaggatt.mukey "
+ " INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol "
+ " INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol "
+ " INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol "
+ " INNER JOIN dbo.component ON m.mukey=dbo.component.mukey "
+ " INNER JOIN dbo.chorizon on dbo.component.cokey=dbo.chorizon.cokey "
+ " LEFT OUTER JOIN dbo.chfrags on dbo.chorizon.chkey=dbo.chfrags.chkey "
+ " INNER JOIN dbo.chtexturegrp on dbo.chorizon.chkey=dbo.chtexturegrp.chkey "
+ " INNER JOIN dbo.chtexture on dbo.chtexturegrp.chtgkey=dbo.chtexture.chtgkey "
+ " WHERE m.mupolygongeo.STIntersects(geometry::STGeomFromText ('" + WKT + "', " + DB_GEOM_SRID + ")) = 1 "
+ " AND m.mupolygongeo.STIsValid()=1 "
+ " AND (geometry:: STGeomFromText ('" + WKT + "', " + DB_GEOM_SRID + ").STIsValid())=1 "
+ " AND legend.areatypename like 'Non-MLRA%' "
+ " AND UPPER(component.majcompflag)='YES' ";
query += " ) as a "
+ " ORDER BY mukey, cokey, hzdept_r, chkey, chtgkey;";
return query;
}
}