extract multiple textures / horizon from NASIS data

[dylanbeaudette]

See get_hz_data_from_NASIS_db(). Specifically:

SELECT peiid, phiid, upedonid as pedon_id,
  hzname, dspcomplayerid as genhz, hzdept, hzdepb,
  claytotest AS clay, CASE WHEN silttotest IS NULL THEN 100 - (claytotest + sandtotest) ELSE silttotest END AS silt, 
  sandtotest AS sand, fragvoltot, texture, texcl, lieutex, phfield, effclass, phs.labsampnum, rupresblkdry, stickiness, plasticity
  
  FROM 

  pedon_View_1 p 
  INNER JOIN phorizon_View_1 ph ON ph.peiidref = p.peiid 
  LEFT OUTER JOIN phsample_View_1 phs ON phs.phiidref = ph.phiid
  LEFT OUTER JOIN 
  (
  SELECT phiidref, MIN(texcl) AS texcl, MIN(lieutex) as lieutex
  FROM phtexture_View_1 
  GROUP BY phiidref
  ) AS pht ON pht.phiidref = ph.phiid
  
  ORDER BY p.upedonid, ph.hzdept ASC;

MIN(texcl) is making the selection based on the minimum of coded values. This may or may not make sense.

Ideas:

• select the coarsest / finest texture
• selection is arbitrary, and noted in another column
• selection is done in R and multiple are noted in another column

[dylanbeaudette]

From the metadata:

Looks like MIN(texcl) will always select the coarsest texture.

[brownag]

Related to #6

[dylanbeaudette]

Reminder that aggregation by rank → subset ("finer" / "coarser" ) depends on the intended use of the categories: mean particle diameter, AWC, etc..

Simple idea: another argument to fetchNASIS for a "multiple texture selection criteria".

Thinking some more, this is a similar problem to the evolution of "mixing multiple horizon colors"--initially this seemed reasonable. Now, I am not so sure and leaning more towards selection of a single color.

Leaving a reminder in aqp issue 141.

[brownag]

I think the current selection based on MIN(texcl) is well justified based on historic precedent in and outside of NASIS. The texture class domains are designed such that this works as it should. Granted there are other interpretations of the rank of texture classes, but I think that is outside scope of soilDB.

I think that we could provide new methods e.g. get_phtexture_from_NASIS() and get_chtexturegrp_from_NASIS(). These could have additional options to return: 1:1, many:1 results, concatenated list of all textures, RV alone with concatenated non-RV textures, etc.

[brownag]

Consider providing analogous methods for structure/structure group.