DBQueries.java [src/java/wqm/utils] Revision: 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 wqm.utils;
import csip.annotations.Resource;
import java.util.Iterator;
/**
*
* @author robert
* @author Rumpal Sidhu
*/
@Resource(from = DBResources.class)
public class DBQueries {
public static String WQM01Query(String geom) {
String qry = "SELECT shape_area, wqs_rating "
+ "FROM wqm_huc12.wqsr_area WHERE geom.STIntersects(geometry::STGeomFromText('"
+ geom + "', 0))=1 AND geom.STIsValid()=1 " + "AND geometry::STGeomFromText('"
+ geom + "', 0).STIsValid()=1;";
return qry;
}
public static String WQM02Query01(Iterator iter) {
String qry = "SELECT ssurgo.component.mukey, ssurgo.component.cokey, ssurgo.component.compname, "
+ "ssurgo.component.comppct_r, ssurgo.component.hydgrp, ssurgo.component.slope_r, "
+ "ssurgo.component.taxorder, ssurgo.chorizon.chkey, ssurgo.chorizon.om_r, "
+ "ssurgo.chorizon.hzthk_r, ssurgo.chorizon.hzdept_r, ssurgo.chorizon.hzdepb_r, "
+ "ssurgo.chorizon.kwfact, ssurgo.chorizon.kffact, ssurgo.chfrags.fragvol_r, "
+ "ssurgo.chfrags.chfragskey FROM ssurgo.component LEFT OUTER JOIN ssurgo.chorizon "
+ "ON ssurgo.chorizon.cokey=ssurgo.component.cokey LEFT OUTER JOIN ssurgo.chfrags "
+ "ON ssurgo.chfrags.chkey=ssurgo.chorizon.chkey WHERE ssurgo.component.mukey in ('";
int mapCount = 0;
while (iter.hasNext()) {
if (mapCount > 0) {
qry += ", '" + iter.next().toString() + "' ";
} else {
qry += iter.next().toString() + "' ";
}
mapCount++;
}
qry += ") AND ssurgo.component.comppct_r IS NOT NULL AND ssurgo.component.hydgrp IS NOT NULL "
+ "ORDER BY ssurgo.component.mukey, ssurgo.component.cokey, ssurgo.chorizon.hzdept_r DESC, "
+ "ssurgo.chorizon.kffact;";
return qry;
}
public static String WQM02Query02(String WKTPolygon) {
String qry = "SELECT areasymbol, musym, mukey, muname, "
+ "geography::STGeomFromText(intersectPoly.STAsText(), 4326).MakeValid().STArea()/4046.86 "
+ "AS sizeIntersectionAcres FROM (SELECT m.areasymbol, m.musym, m.mukey, "
+ "ssurgo.mapunit.muname, m.the_geom.STIntersection(geometry::STGeomFromText(" + WKTPolygon
+ ", 0)).MakeValid() AS intersectPoly FROM ssurgo.soilmu_a AS m WITH (index(geom_sidx)) "
+ "INNER JOIN ssurgo.mapunit ON m.mukey=ssurgo.mapunit.mukey WHERE "
+ "m.the_geom.STIntersects(geometry::STGeomFromText(" + WKTPolygon + ", 0))=1 "
+ "AND m.the_geom.STIsValid()=1 AND (geometry::STGeomFromText(" + WKTPolygon
+ ", 0).STIsValid())=1) AS a;";
return qry;
}
public static String WQM02Query03(String WKTPolygon, String polygonText) {
String qry = "SELECT m.mukey, ssurgo.mapunit.muname, st_area(st_transform(st_intersection(ST_PolygonFromText("
+ WKTPolygon + ", 4326), m.the_geom::geography::geometry), 3541))/43560 AS sizeIntersectionAcres "
+ "FROM ssurgo.soilmu_a AS m INNER JOIN ssurgo.mapunit ON m.mukey=ssurgo.mapunit.mukey "
+ "WHERE ST_Intersects(" + polygonText + ", m.the_geom) AND st_isvalid(m.the_geom) "
+ "AND st_isvalid(" + polygonText + ") ORDER BY m.mukey;";
return qry;
}
public static String WQM04Query(String pest_id) {
String qry = "SELECT PC_CODE, AI_NAME, PH, SOIL_HL_RV, KOC_RV, SOL_RV, HUMAN_TOX_PPB, "
+ "HUMAN_TOX_TYPE, FISH_TOX_PPB, FISH_TOX_TYPE FROM wqm.wqm_pesticides "
+ "WHERE PC_CODE IN (" + pest_id + ");";
return qry;
}
public static String WQM04Query01(String pestRows) {
String qry = "SELECT PC_CODE, AI_NAME, PH, SOIL_HL_RV, KOC_RV, SOL_RV, HUMAN_TOX_PPB, "
+ "HUMAN_TOX_TYPE, FISH_TOX_PPB, FISH_TOX_TYPE FROM wqm.wqm_pesticides "
+ "WHERE Id IN (" + pestRows + ");";
return qry;
}
public static String WQM10Query01(String ai_plp, String aoa_pslp) {
String qry = "SELECT wqm_ilp FROM wqm.wqm_soil_pest_interaction_leaching "
+ "WHERE wqm_plp='" + ai_plp + "' AND wqm_slp='" + aoa_pslp + "';";
return qry;
}
public static String WQM10Query02(String ai_psrp, String aoa_ssrp) {
String qry = "SELECT wqm_isrp FROM wqm.wqm_soil_pest_interaction_solution_runoff "
+ "WHERE wqm_psrp='" + ai_psrp + "' AND wqm_ssrp='" + aoa_ssrp + "';";
return qry;
}
public static String WQM10Query03(String ai_parp, String aoa_sarp) {
String qry = "SELECT wqm_iarp FROM wqm.wqm_soil_pest_interaction_adsorbed_runoff "
+ "WHERE wqm_parp='" + ai_parp + "' AND wqm_sarp='" + aoa_sarp + "';";
return qry;
}
public static String WQM11Query01(String op_pest_ilp_temp, String eat_rating_human_temp) {
String qry = "SELECT wqm_phr FROM wqm.wqm_pesticide_hazard_potential "
+ "WHERE wqm_ilr=" + op_pest_ilp_temp + " AND wqm_eat=" + eat_rating_human_temp;
return qry;
}
public static String WQM11Query02(String op_pest_isrp_temp, String eat_rating_human_temp) {
String qry = "SELECT wqm_phr FROM wqm.wqm_pesticide_hazard_potential "
+ "WHERE wqm_ilr=" + op_pest_isrp_temp + " AND wqm_eat=" + eat_rating_human_temp;
return qry;
}
public static String WQM11Query03(String op_pest_iarp_temp,
String eat_rating_human_temp) {
String qry = "SELECT wqm_phr FROM wqm.wqm_pesticide_hazard_potential "
+ "WHERE wqm_ilr=" + op_pest_iarp_temp + " AND wqm_eat=" + eat_rating_human_temp;
return qry;
}
public static String WQM11Query04(String op_pest_iarp_temp,
String eat_rating_stvfish_temp) {
String qry = "SELECT wqm_phr FROM wqm.wqm_pesticide_hazard_potential "
+ "WHERE wqm_ilr=" + op_pest_iarp_temp + " AND wqm_eat=" + eat_rating_stvfish_temp;
return qry;
}
public static String WQM11Query05(String op_pest_ilp_temp,
String eat_rating_matcfish_temp) {
String qry = "SELECT wqm_phr FROM wqm.wqm_pesticide_hazard_potential "
+ "WHERE wqm_ilr=" + op_pest_ilp_temp + " AND wqm_eat=" + eat_rating_matcfish_temp;
return qry;
}
public static String WQM11Query06(String op_pest_isrp_temp,
String eat_rating_matcfish_temp) {
String qry = "SELECT wqm_phr FROM wqm.wqm_pesticide_hazard_potential "
+ "WHERE wqm_ilr=" + op_pest_isrp_temp + " AND wqm_eat=" + eat_rating_matcfish_temp;
return qry;
}
public static String WQM12Query(String tPoints) {
String qry = "SELECT TOP 1 m.co_fips, r2_path, r2_name, r_factor, g.geometry "
+ " FROM r2gis.map_climates AS m, r2gis.cli_geom AS g WITH (Index(cli_geom_idx)) "
+ " WHERE g.geometry.STIntersects(geometry::STPolyFromText('POLYGON(("
+ tPoints + "))', 0))=1 AND ((g.co_fips = m.co_fips AND g.geometry.STIsValid()=1 "
+ " AND m.ei_rang is null) OR (g.co_fips = m.co_fips AND g.ei_rang = m.ei_rang "
+ " AND m.ei_rang IS NOT NULL AND g.geometry.STIsValid()=1)) AND g.geometry.STIsValid()=1 "
+ " AND geometry::STPolyFromText('POLYGON((" + tPoints + "))', 0).STIsValid()=1;";
return qry;
}
public static String WQM12Query02(String WKT) {
String qry = "SELECT TOP 1 m.co_fips, r2_path, r2_name, r_factor, g.geometry "
+ " FROM r2gis.map_climates AS m, r2gis.cli_geom AS g WITH (Index(cli_geom_idx)) "
+ " WHERE g.geometry.STIntersects(geometry::STGeomFromText('"
+ WKT + "', 0))=1 AND ((g.co_fips = m.co_fips AND g.geometry.STIsValid()=1 "
+ " AND m.ei_rang is null) OR (g.co_fips = m.co_fips AND g.ei_rang = m.ei_rang "
+ " AND m.ei_rang IS NOT NULL AND g.geometry.STIsValid()=1)) AND g.geometry.STIsValid()=1 "
+ " AND geometry::STGeomFromText('" + WKT + "', 0).STIsValid()=1 "
+ " ORDER BY r_factor;";
return qry;
}
public static String WQM12Query03(double[] centroid) {
String qry = "SELECT top 1 m.co_fips, r2_path, r2_name, r_factor, "
+ " CASE WHEN geography::STGeomFromText(geometry.STAsText(), 4326).MakeValid().EnvelopeAngle() > 90 "
+ " THEN geography::STGeomFromText(geometry.STAsText(), 4326).MakeValid().ReorientObject().STDistance( geography::STGeomFromText('POINT(" + centroid[1] + " " + centroid[0] + ")',4326)) / 1000.0 "
+ " ELSE geography::STGeomFromText(geometry.STAsText(), 4326).MakeValid().STDistance( geography::STGeomFromText('POINT(" + centroid[1] + " " + centroid[0] + ")', 4326))/ 1000.0 "
+ " END AS cliDistance "
+ " FROM r2gis.cli_geom as m"
+ " INNER JOIN r2gis.map_climates on m.co_fips=r2gis.map_climates.co_fips"
+ " WHERE "
+ " ID in( "
+ " SELECT top 15 ID "
+ " FROM r2gis.cli_geom "
+ " ORDER BY geometry.STDistance(geometry::STGeomFromText('POINT(" + centroid[1] + " " + centroid[0] + ")', 0)) asc"
+ " ) "
+ " AND ( (r2gis.map_climates.ei_rang is NULL) OR (r2gis.map_climates.ei_rang = m.ei_rang))"
+ " ORDER BY cliDistance asc, r_factor;";
return qry;
}
public static String WQM13Query01(String concern, String hazardRating, int rfactor, String treatmentLevel) {
String query = "SELECT threshold_treatment_score "
+ "FROM wqm.d_threshold_scores "
+ "WHERE wqm_concern = '" + concern
+ "' AND hazard_loss_rating = '" + hazardRating + "' "
+ (rfactor != -1 ? "AND rfact_range_min < " + rfactor + " AND rfact_range_max >= " + rfactor : "")
+ "AND treatment_level='" + treatmentLevel + "';";
return query;
}
public static String WQM14Query01(int techId, String concern, String action, String discrimType, String discrim) {
String query = "SELECT nut_tech_score "
+ "FROM wqm.d_nutrient_technique_scores "
+ "WHERE nutrient_technique_id = " + techId
+ " AND wqm_concern = '" + concern + "' "
+ (action != null ? " AND mode_of_action = '" + action + "' " : "")
+ "AND tech_discrim_type "
+ (discrimType != null ? "= '" + discrimType + "' " : "IS NULL ")
+ "AND tech_discrim "
+ (discrim != null ? "= '" + discrim + "';" : "IS NULL;");
return query;
}
public static String WQM15Query01(int practiceId, String concern, String actionMode, String discrimType, int discrimValue) {
String query = "SELECT nut_pract_score "
+ "FROM wqm.d_sediment_nutrient_practice_scores "
+ "WHERE practice_id = " + practiceId
+ " AND wqm_concern = '" + concern
+ "' AND mode_of_action = '" + actionMode
+ (discrimType == null ? "' AND pract_discrim_type IS NULL;" : "' AND CAST(min_pract_discrim AS int) <= "
+ discrimValue + " AND CAST(max_pract_discrim AS int) > " + discrimValue + ";");
return query;
}
public static String WQM16Query01(int cropId, String cropYieldUnits) {
String query = "SELECT crop.pct_dmat, crop.pct_nitrogen, crop.pct_phosphorus, type.name AS crop_type "
+ "FROM wqm.d_crop AS crop "
+ "INNER JOIN wqm.d_crop_type AS type ON type.id = crop.type_id "
+ "WHERE crop.id = '" + cropId + "'"
+ (cropYieldUnits != null ? " AND LOWER(crop.units) = '" + cropYieldUnits.toLowerCase() + "'" : "")
+ ";";
return query;
}
public static String WQM16Query02(String nutrient, String appMgtKind, String appMgtFactor,
String soilTestResult, Double removeRatio, Integer app_day_diff) {
String query = "SELECT app_mgt_score "
+ "FROM wqm.d_nutrient_application_mgt_scores "
+ "WHERE "
+ (appMgtKind != null ? "LOWER(app_mgt_kind) = '" + appMgtKind.toLowerCase() + "'" : "")
+ (nutrient != null ? " AND LOWER(nutrient) = '" + nutrient.toLowerCase() + "'" : "")
+ (appMgtFactor != null ? " AND LOWER(app_mgt_factor) = '" + appMgtFactor.toLowerCase() + "'" : "")
+ (soilTestResult != null ? " AND LOWER(soil_test_result) = '" + soilTestResult.toLowerCase() + "'" : "")
+ (removeRatio != null ? " AND remove_ratio_1 <= " + removeRatio + " AND remove_ratio_2 > " + removeRatio : "")
+ (app_day_diff != null ? " AND days_fr_plant_1 <= " + app_day_diff + " AND days_fr_plant_2 > " + app_day_diff : "")
+ ";";
return query;
}
public static String WQM17Query01(String ipmLevel, String concern) {
String query = "SELECT ipm_mitigation_score "
+ "FROM wqm.d_ipm_scores "
+ "WHERE ipm_level = '" + ipmLevel
+ "' AND wqm_concern = '" + concern + "';";
return query;
}
public static String WQM18Query01(int plan_ipm_technique, String concern) {
String query = "SELECT ipm_technique_score "
+ "FROM wqm.d_ipm_technique_scores "
+ "WHERE ipm_technique_id = " + plan_ipm_technique
+ " AND wqm_concern = '" + concern + "';";
return query;
}
public static String WQM19Query1(int ipm, String variant, String concern) {
String query = "SELECT ipm_practice_score "
+ "FROM wqm.d_ipm_practice_scores "
+ "WHERE wqm_concern = '" + concern
+ "' AND practice_id = " + ipm + (variant != null ? " AND practice_variant = '" + variant + "';" : ";");
return query;
}
public static String WQM22Query() {
String qry = "SELECT * FROM wqm.wqm_nutrient_technique_scores;";
return qry;
}
public static String WQM23Query() {
String qry = "SELECT * FROM wqm.wqm_sediment_nutrient_practice_scores";
return qry;
}
public static String WQM24Query() {
String qry = "SELECT * FROM wqm.wqm_ipm_scores";
return qry;
}
public static String WQM25Query() {
String qry = "SELECT * FROM wqm.wqm_ipm_technique_scores";
return qry;
}
public static String WQM26Query() {
String qry = "SELECT * FROM wqm.wqm_ipm_practice_scores";
return qry;
}
public static String WQM28Query(String stateCode) {
return "SELECT f.id, f.name, f.display_name, c.cost, p.placement, fo.nitrogen_pct, fo.phosphorus_pct, fo.potassium_pct, u.units "
+ "FROM fertilizer.d_cost AS c "
+ "JOIN fertilizer.d_cost_units AS u ON c.unit_id = u.id "
+ "JOIN fertilizer.d_fertilizer AS f ON c.fertilizer_id = f.id "
+ "JOIN fertilizer.d_formulation AS fo ON f.formulation_id = fo.id "
+ "JOIN fertilizer.d_placement AS p ON f.default_placement_id = p.id "
+ "WHERE region_id = (SELECT region_id FROM fertilizer.d_state_region WHERE fips = " + stateCode + ");";
}
public static String WQM29Query() {
return "SELECT * FROM fertilizer.d_placement";
}
}