Identification of Dated Surfaces via Soil Series

-- 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') ;

-- 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;

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') ;

 -- 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) ;
 
-- register geometry
INSERT INTO geometry_columns VALUES ('','public','east_side_all','wkb_geometry',2,9001,'POLYGON');
 
-- permissions
GRANT SELECT ON TABLE east_side_all TO soil  ;

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;