Identification of Dated Surfaces via Soil Series

East Side Alluvial Formations

 
Overview
A simple association between dated landforms and soil series name [1] was used to extract soil polygons from a composite soil survey database.

1. Create a soil series - dated landform lookup table:
   -- create a lookup table
   CREATE TABLE dated_landforms (
   soil_series varchar(20),
   formation varchar(20),
   approx_age varchar(30)
   );
   -- populate table
   INSERT INTO dated_landforms VALUES ('Redding','Laguna','1600 - 2000 kya') ;
   INSERT INTO dated_landforms VALUES ('Corning','Laguna','1600 - 2000 kya') ;
   INSERT INTO dated_landforms VALUES ('Keyes','Laguna','1600 - 2000 kya') ;
   INSERT INTO dated_landforms VALUES ('Whitney','Turlock Lake','500 - 700 kya') ;
   INSERT INTO dated_landforms VALUES ('Montpellier','Turlock Lake','500 - 700 kya') ;
   INSERT INTO dated_landforms VALUES ('Rocklin','Turlock Lake','500 - 700 kya') ;
   INSERT INTO dated_landforms VALUES ('Snelling','Riverbank','100 - 300 kya') ;
   INSERT INTO dated_landforms VALUES ('San Joaquin','Riverbank','100 - 300 kya') ;
   INSERT INTO dated_landforms VALUES ('Exiter','Riverbank','100 - 300 kya') ;
   INSERT INTO dated_landforms VALUES ('Madera','Riverbank','100 - 300 kya') ;
   INSERT INTO dated_landforms VALUES ('Hanford','Modesto','10 - 40 kya') ;
   INSERT INTO dated_landforms VALUES ('Grangeville','Holocene','< 10 kya') ;
2. Select map units (mukey) by suitible series concepts, associated with major components. Note that the DISTINCT ON (mukey) ... ORDER BY mukey, comppct_r DESC pattern can be used to select the largest component for each map unit. Pattern matching is used to safely join variants with the soil series names in our look-up table: i.e. 'San Joaquin variant' will match 'San Joaquin'.
   -- keep only the largest formation (by total component percent) within each map unit key
   SELECT DISTINCT ON (mukey) mukey, formation, sum(comppct_r) AS formation_pct
   FROM component
   JOIN
   dated_landforms
   -- use pattern matching to also include variants
   ON compname ~~* lower(soil_series || '%') = 't'
   -- subset to select survey areas on the east side of the valley
   AND component.areasymbol IN ('ca654', 'ca651', 'ca649', 'ca644', 'ca648', 'ca077')
   AND majcompflag = 'Yes'
   -- combine components of the same formation
   GROUP BY mukey, formation
   -- use in conjunction with the DISTINCT statement to keep only the largest
   ORDER BY mukey, formation_pct DESC;
3. Create a new classified table called east_side_all. This query involves 6 survey areas, 11423 polygons, and requires about 11 seconds to complete.
   CREATE TABLE east_side_all AS
   -- select geom column and the feature id
   -- along with our formation names
   SELECT wkb_geometry AS wkb_geometry, ogc_fid, a.*
   FROM mapunit_poly
   JOIN
   (
   SELECT DISTINCT ON (mukey) mukey, formation, sum(comppct_r) AS formation_pct
   FROM component
   JOIN
   dated_landforms
   ON compname ~~* lower(soil_series || '%') = 't'
   AND component.areasymbol IN ('ca654', 'ca651', 'ca649', 'ca644', 'ca648', 'ca077')
   AND majcompflag = 'Yes'
   GROUP BY mukey, formation
   ORDER BY mukey, formation_pct DESC
   ) AS a
   ON mapunit_poly.mukey = a.mukey
   -- limit polygon selection by survey area
   AND mapunit_poly.areasymbol IN ('ca654', 'ca651', 'ca649', 'ca644', 'ca648', 'ca077') ;
4. Index, register geometry, and setup permissions
   
    -- indexing
   CREATE INDEX east_side_all_fid_idx ON east_side_all  (ogc_fid) ;
   CREATE INDEX east_side_all_gis_idx ON east_side_all USING gist (wkb_geometry gist_geometry_ops) ;
   &nbsp;
   -- register geometry
   INSERT INTO geometry_columns VALUES ('','public','east_side_all','wkb_geometry',2,9001,'POLYGON');
   &nbsp;
   -- permissions
   GRANT SELECT ON TABLE east_side_all TO soil  ;
5. Tabulate acreage for each formation. (approximately 1/3 second to complete)
   
    
   SELECT * FROM
           (
           SELECT round(sum(area(wkb_geometry)) * 0.000247) AS area_ac, formation
           FROM east_side_all
           GROUP BY formation
           ) AS a
   ORDER BY a.area_ac DESC;

   area_ac	formation
   295868	Riverbank
   253424	Modesto
   151149	Turlock Lake
   121085	Laguna
   96981	Holocene

   (5 rows)

6. Dump to local file in SHP format
    ogr2ogr east_side_all.shp PG:"dbname=ssurgo_combined user=xxxx password=xxxx host=xxxx" east_side_all

 
References:

1. Smith, D.W. & Verrill, W.L. Witham, C.; Bauder, E.; Belk, D.; Ferren Jr., W. & Ornduff, R. (ed.) Vernal Pool-Soil-Landform Relationships in the Central Valley, California 1998, 15-23