Mast head image

Lab (KSSL) Physical Properties by State

This script queries the physical properties table for the horizons in the upper 15 cm and excludes "O" horizons.

Text highlighted in yellow in the example script can be changed to a particular area, or you can use wildcards to get an entire State. Examples can be found on the Tips page.

Tips Page

Copy the example script below and paste it into the query page on the Soil Data Access site.

Soil Data Access Query Page

Example Script

SELECT cnn.[pedon_key],cnn.[site_key],p.[pedlabsampnum],[project_key] ,[layer_field_label_1],[hzn_top],[hzn_bot]

, CASE WHEN [hzn_top] < 15 then [hzn_top] ELSE 0 END AS InRangeTop_0_15

,CASE WHEN [hzn_bot] <= 15 THEN [hzn_bot] WHEN [hzn_bot] > 15 and [hzn_top] < 15 THEN 15 ELSE 0 END AS InRangeBot_0_15

,CASE WHEN [corr_name] IS NULL THEN [samp_name] ELSE [corr_name] END AS soil_name

,CASE WHEN [corr_class_type] IS NULL THEN [samp_class_type] ELSE [corr_class_type] END AS class_type

,CASE WHEN [corr_classification_name] IS NULL THEN [samp_classification_name] ELSE [corr_classification_name] END AS [classification_name]

,[siteiid],[usiteid],[site_obsdate],[latitude_decimal_degrees],[longitude_decimal_degrees],country_code ,country_name , state_code , state_name , county_code , county_name , mlra_code , mlra_name , ssa_code , ssa_name ,[note],[pedobjupdate],[siteobjupdate],[prep_code],l.[labsampnum],[total_carbon_ncs],[total_carbon_ncs_method],[caco3_lt_2_mm],[caco3_lt_2_mm_method],[estimated_organic_carbon]

,CASE WHEN [estimated_organic_carbon] IS NOT NULL THEN LEFT (ROUND ([estimated_organic_carbon]* 1.724, 2),4)

WHEN [estimated_organic_carbon] IS NULL AND [caco3_lt_2_mm] IS NOT NULL THEN LEFT (ROUND (([total_carbon_ncs]- ([caco3_lt_2_mm]*0.12))* 1.724, 2),4)

WHEN [estimated_organic_carbon] IS NULL AND [caco3_lt_2_mm] IS NULL THEN LEFT (ROUND ([total_carbon_ncs]* 1.724,2),4) END AS [estimated_organic_matter]

, (SELECT TOP 1 [texture_lab]

FROM [lab_physical_properties] AS lpp WHERE lpp.labsampnum=l.labsampnum AND [texture_lab] IS NOT NULL) AS [lab_texture]

FROM [lab_combine_nasis_ncss] AS cnn

INNER JOIN [lab_pedon] AS p ON p.pedon_key=cnn.pedon_key

INNER JOIN [lab_layer] AS l ON l.pedon_key=p.pedon_key AND ([hzn_top] <= 15 AND [hzn_desgn] NOT LIKE '%O%')

INNER JOIN [lab_chemical_properties] AS lcp ON lcp.labsampnum=l.labsampnum AND [total_carbon_ncs] IS NOT NULL

INNER JOIN (SELECT [area_code] AS country_code,[area_name] AS country_name, area_key FROM lab_area

) AS c ON cnn.[country_key]=c.area_key

INNER JOIN (SELECT [area_code] AS state_code,[area_name] AS state_name, area_key FROM lab_area

WHERE [area_code]= 'WI'

)AS s ON cnn.[state_key]=s.area_key

INNER JOIN (SELECT [area_code] AS county_code,[area_name] AS county_name, area_key FROM lab_area

) AS ct ON cnn.[county_key]=ct.area_key

INNER JOIN (SELECT [area_code] AS mlra_code,[area_name] AS mlra_name, area_key FROM lab_area

) AS m ON cnn.[mlra_key]=m.area_key

INNER JOIN (SELECT [area_code] AS ssa_code,[area_name] AS ssa_name, area_key FROM lab_area

) AS ss ON cnn.[ssa_key]=ss.area_key

Example Table Output

pedon_key site_key pedlabsampnum project_key layer_field_label_1 hzn_top hzn_bot InRangeTop_0_15 InRangeBot_0_15 soil_name class_type classification_name siteiid usiteid site_obsdate latitude_decimal_degrees longitude_decimal_degree country_code country_name state_code state_name county_code county_name mlra_code mlra_name ssa_code ssa_name note pedobjupdate siteobjupdate prep_code labsampnum total_carbon_ncs total_carbon_ncs_method caco3_lt_2_mm caco3_lt_2_mm_method estimated_organic_carbon estimated_organic_matter lab_texture
11412 11412 84P0651 1092 0.0 11.0 0.0 11.0 Seaton series Fine-silty, mixed, superactive, mesic Typic Hapludalfs 120345 S1984WI093012 12/1/1984 12:00:00 AM 44.6222229 -92.4069443 US United States WI Wisconsin WI093 Pierce 105 Northern Mississippi Valley Loess Hills WI093 Pierce County, Wisconsin NASIS updated 5/4/2020 8:16:45 AM 5/24/2018 12:22:55 PM 3/20/2019 4:01:45 PM S 84P03607 4.360 6A2d 7.52 sil

Helping People Help the Land

USDA is an equal opportunity provider, employer, and lender.