search

ncss-tech / ISSR-800

Intermediate Scale Soil Raster (data) prepared on an 800m grid.
https://casoilresource.lawr.ucdavis.edu/soil-properties/
8 stars 0 forks source link

potential mishandled NULLs #14

Closed NodakJ closed 5 years ago

NodakJ commented 6 years ago

A quick view of results rendering Calcium Carbonate shows an unnatural spike in NE Minnesota in one map unit (s3673). A review of the source data STATSGO confirms that only 1% of the mapunit has a weighted average of roughly 17% calcium carbonate in a depth of 13-152cm. The remainder of the mapunit has no carbonates anywhere in the soil profile. Property grid results are 396.71 kg/m2 higher than some of the most calcareous soils in the Northern Great Plains. carbs_nemn

dylanbeaudette commented 5 years ago

An idea, convert NULL om, caco3, SAR, EC to 0. Relates to #15, #12.

dylanbeaudette commented 5 years ago

Check problem STATSGO musym s3673:

select cokey, round(comppct_r, 2), caco3_kg_sq_m, om_kg_sq_m, ph_025, max_om, sar, ec, ec_025 from statsgo_component_data where mukey = '669348' order by comppct_r DESC;

Note NULL om_r, caco3_r, ec_r, and sar_r converted to 0. The weighted mean of caco3_kg_sq_m is now close to 4 kg / sq. of CaCO3.

  cokey   | round | caco3_kg_sq_m | om_kg_sq_m | ph_025 | max_om | sar | ec | ec_025
----------+-------+---------------+------------+--------+--------+-----+----+--------
 14236676 |  0.38 |             0 |    8.96724 |    4.8 |   0.02 |   0 |  0 |      0
 14236668 |  0.20 |             0 |    1.80624 |    5.5 |   0.02 |   0 |  0 |      0
 14236669 |  0.08 |             0 |    8.96724 |    4.8 |   0.02 |   0 |  0 |      0
 14236665 |  0.04 |             0 |    1.80624 |    5.5 |   0.02 |   0 |  0 |      0
 14236679 |  0.04 |             0 |    5.95935 |    5.3 | 0.0125 |   0 |  0 |      0
 14236666 |  0.03 |             0 |    4.08609 |  5.992 | 0.0075 |   0 |  0 |      0
 14236677 |  0.03 |               |            |        |   0.62 |   0 |  0 |      0
 14236672 |  0.03 |             0 |     4.4928 |      5 |   0.03 |   0 |  0 |      0
 14236678 |  0.03 |               |            |        |      0 |   0 |  0 |      0
 14236675 |  0.03 |             0 |     6.3954 |    5.6 |  0.055 |   0 |  0 |      0
 14236670 |  0.02 |             0 |      5.652 |    5.3 |   0.02 |   0 |  0 |      0
 14236673 |  0.02 |             0 |     4.4928 |      5 |   0.03 |   0 |  0 |      0
 14236674 |  0.01 |             0 |     5.9432 |    5.3 | 0.0125 |   0 |  0 |      0
 14236671 |  0.01 |     396.70776 |    8.94594 |  6.584 |  0.015 |   0 |  0 |      0

NULL stock values are related to missing dbovendry_r values (#19).

  cokey   |  compname   | compkind | comppct_r | om_r | caco3_r | dbovendry_r | dbthirdbar_r | hzname | hzdept_r | hzdepb_r
----------+-------------+----------+-----------+------+---------+-------------+--------------+--------+----------+----------
 14236677 | Seelyeville | Series   |         3 |   62 |         |             |         0.18 | H2     |       25 |      152
 14236677 | Seelyeville | Series   |         3 |   62 |         |             |         0.18 | H1     |        0 |       25
dylanbeaudette commented 5 years ago

The conversion of NULL -> 0 for SAR, CaCO3, and OM seems to have solved this problem. The 2018 value was ~ 396 kg/sq.m. and the 2019 value is ~ 4 kg/sq.m.. This is very close to my calculation from the source data.

image

dylanbeaudette commented 5 years ago

Additional testing and verification.

select cokey, round(comppct_r, 2) AS comppct, caco3_kg_sq_m, om_kg_sq_m, ph_025, max_om, sar, ec, ec_025 from statsgo_component_data where mukey = '669348' order by comppct_r DESC;

  cokey   | comppct | caco3_kg_sq_m | om_kg_sq_m | ph_025 | max_om | sar | ec | ec_025
----------+---------+---------------+------------+--------+--------+-----+----+--------
 14236676 |    0.38 |             0 |    8.96724 |    4.8 |   0.02 |   0 |  0 |      0
 14236668 |    0.20 |             0 |    1.80624 |    5.5 |   0.02 |   0 |  0 |      0
 14236669 |    0.08 |             0 |    8.96724 |    4.8 |   0.02 |   0 |  0 |      0
 14236665 |    0.04 |             0 |    1.80624 |    5.5 |   0.02 |   0 |  0 |      0
 14236679 |    0.04 |             0 |    5.95935 |    5.3 | 0.0125 |   0 |  0 |      0
 14236666 |    0.03 |             0 |    4.08609 |  5.992 | 0.0075 |   0 |  0 |      0
 14236677 |    0.03 |               |            |        |   0.62 |   0 |  0 |      0
 14236672 |    0.03 |             0 |     4.4928 |      5 |   0.03 |   0 |  0 |      0
 14236678 |    0.03 |               |            |        |      0 |   0 |  0 |      0
 14236675 |    0.03 |             0 |     6.3954 |    5.6 |  0.055 |   0 |  0 |      0
 14236670 |    0.02 |             0 |      5.652 |    5.3 |   0.02 |   0 |  0 |      0
 14236673 |    0.02 |             0 |     4.4928 |      5 |   0.03 |   0 |  0 |      0
 14236674 |    0.01 |             0 |     5.9432 |    5.3 | 0.0125 |   0 |  0 |      0
 14236671 |    0.01 |     396.70776 |    8.94594 |  6.584 |  0.015 |   0 |  0 |      0

SELECT * FROM statsgo_grid_mapunit WHERE mukey = '669348'; 
 grid_gid | mu_gid | mukey  |      area_wt       |   gid
----------+--------+--------+--------------------+----------
[...]

 12136044 |  47010 | 669348 |             640000 |  6036962

[...]

SELECT * FROM statsgo_grid_mapunit WHERE grid_gid = 12136044;

 grid_gid | mu_gid | mukey  | area_wt |   gid
----------+--------+--------+---------+---------
 12136044 |  47010 | 669348 |  640000 | 6036962

SELECT gid, cokey, om_kg_sq_m_wt, caco3_kg_sq_m_wt from statsgo_component_weights where gid = 6036962;

   gid   |  cokey   |        om_kg_sq_m_wt        |      caco3_kg_sq_m_wt
---------+----------+-----------------------------+-----------------------------
 6036962 | 14236679 |  25600.00000000000000000000 |  25600.00000000000000000000
 6036962 | 14236678 |                             |
 6036962 | 14236677 |                             |
 6036962 | 14236676 | 243200.00000000000000000000 | 243200.00000000000000000000
 6036962 | 14236675 |  19200.00000000000000000000 |  19200.00000000000000000000
 6036962 | 14236674 |   6400.00000000000000000000 |   6400.00000000000000000000
 6036962 | 14236673 |  12800.00000000000000000000 |  12800.00000000000000000000
 6036962 | 14236672 |  19200.00000000000000000000 |  19200.00000000000000000000
 6036962 | 14236671 |   6400.00000000000000000000 |   6400.00000000000000000000
 6036962 | 14236670 |  12800.00000000000000000000 |  12800.00000000000000000000
 6036962 | 14236669 |  51200.00000000000000000000 |  51200.00000000000000000000
 6036962 | 14236668 | 128000.00000000000000000000 | 128000.00000000000000000000
 6036962 | 14236666 |  19200.00000000000000000000 |  19200.00000000000000000000
 6036962 | 14236665 |  25600.00000000000000000000 |  25600.00000000000000000000

SELECT grid_gid, number_components, caco3_kg_sq_m, om_kg_sq_m from statsgo_gridded_properties where grid_gid = 12136044;

 grid_gid | number_components |  caco3_kg_sq_m   |    om_kg_sq_m
----------+-------------------+------------------+------------------
 12136044 |                14 | 4.45739056179775 | 6.28968325842697

SELECT gid, 
sum(caco3_kg_sq_m * caco3_kg_sq_m_wt) / NULLIF(sum(caco3_kg_sq_m_wt), 0) as caco3_kg_sq_m,
sum(om_kg_sq_m * om_kg_sq_m_wt) / NULLIF(sum(om_kg_sq_m_wt), 0) as om_kg_sq_m
FROM
(
SELECT cokey, round(comppct_r, 2) AS comppct, caco3_kg_sq_m, om_kg_sq_m from statsgo_component_data where mukey = '669348'
) AS a
JOIN 
(
SELECT gid, cokey, om_kg_sq_m_wt, caco3_kg_sq_m_wt from statsgo_component_weights where gid = 6036962
) AS b USING (cokey)
GROUP BY gid
;

   gid   |  caco3_kg_sq_m   |    om_kg_sq_m
---------+------------------+------------------
 6036962 | 4.45739056179775 | 6.28968325842697