Identification of Dated Surfaces via Soil Series

East Side Alluvial Formations
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.

Soil Series Associated Formation Approximate Age (1000 yrs ago)
Redding Laguna 1600 - 2000 kya
Corning Laguna 1600 - 2000 kya
Keyes Laguna 1600 - 2000 kya
Whitney Turlock Lake 500 - 700 kya
Montpellier Turlock Lake 500 - 700 kya
Rocklin Turlock Lake 500 - 700 kya
Snelling Riverbank 100 - 300 kya
San Joaquin Riverbank 100 - 300 kya
Exiter Riverbank 100 - 300 kya
Madera Riverbank 100 - 300 kya
Hanford Modesto 10 - 40 kya
Grangeville Holocene < 10 kya

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_acformation
295868 Riverbank
253424 Modesto
151149 Turlock Lake
121085 Laguna
96981 Holocene

(5 rows)

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