Parent material data is stored within the copm and copmgrp tables. The copm table can be linked to the copmgrp table via the 'copmgrpkey' field, and the copmgrp table can be linked to the component table via the 'cokey' field. The following queries illustrate these table relationships, and show one possible strategy for extracting the parent material information associated with the largest component of each map unit.
Several of the example queries are based on this map unit:
Query copmgrp table
SELECT * FROM copmgrp WHERE cokey = '461573:631329' ; areasymbol | pmgroupname | rvindicator | cokey | copmgrpkey ------------+-------------+-------------+---------------+-------------- ca011 | alluvium | Yes | 461573:631329 | 461573:78270
Query copm table
SELECT * FROM copm WHERE copmgrpkey = '461573:78270' ; areasymbol | pmorder | pmmodifer | pmgenmod | pmkind | pmorigin | copmgrpkey | copmkey ------------+---------+-----------+----------+----------+----------+--------------+--------------- ca011 | | | | Alluvium | | 461573:78270 | 461573:101001
Join parent material tables
SELECT cokey, pmgroupname, rvindicator, pmkind
FROM copmgrp
LEFT JOIN copm USING (copmgrpkey)
WHERE cokey = '461573:631329' ;
cokey | pmgroupname | rvindicator | pmkind
---------------+-------------+-------------+----------
461573:631329 | alluvium | Yes | Alluvium
Join component table to parent material tables
SELECT mukey, cokey, comppct_r, taxorder, taxsuborder, taxgrtgroup, taxsubgrp, compname, pm.pmgroupname, pm.rvindicator, pm.pmkind
FROM
component
LEFT JOIN
(
-- get parent material data for each cokey
SELECT cokey, pmorder, pmmodifer, pmgenmod, pmgroupname, rvindicator, pmkind
FROM copmgrp
LEFT JOIN copm USING (copmgrpkey)
) AS pm
USING (cokey)
WHERE mukey = '461573' ;
mukey | cokey | comppct_r | taxorder | taxsuborder | taxgrtgroup | taxsubgrp | compname | pmgroupname | rvindicator | pmkind
--------+---------------+-----------+-----------+-------------+--------------+---------------------+----------+-------------+-------------+----------
461573 | 461573:631329 | 90 | Alfisols | Xeralfs | Palexeralfs | Typic Palexeralfs | Hillgate | alluvium | Yes | Alluvium
461573 | 461573:631330 | 5 | Alfisols | Xeralfs | Haploxeralfs | Typic Haploxeralfs | Arbuckle | alluvium | Yes | Alluvium
461573 | 461573:631331 | 2 | Entisols | Fluvents | Xerofluvents | Mollic Xerofluvents | Arand | alluvium | Yes | Alluvium
461573 | 461573:631332 | 1 | | | | | Unnamed | alluvium | Yes | Alluvium
461573 | 461573:631333 | 2 | Mollisols | Xerolls | Haploxerolls | Pachic Haploxerolls | Westfan | alluvium | Yes | Alluvium
Extract component data and corresponding parent material data for the largest component of each map unit
CREATE TEMP TABLE temp_component_data AS
SELECT DISTINCT ON (mukey) mukey, cokey, comppct_r, taxorder, taxsuborder, taxgrtgroup, taxsubgrp, compname, pm.pmgroupname, pm.rvindicator, pm.pmkind, pm.pmorder, pm.pmmodifer, pm.pmgenmod
FROM
component
LEFT JOIN
(
-- get parent material data for each cokey
SELECT cokey, pmorder, pmmodifer, pmgenmod, pmgroupname, rvindicator, pmkind
FROM copmgrp
LEFT JOIN copm USING (copmgrpkey)
) AS pm
USING (cokey)
WHERE component.areasymbol IN ('ca624', 'ca628')
AND majcompflag = 'Yes'
ORDER BY mukey, comppct_r DESC ;
-- add an index so that joining with geometry is faster
CREATE INDEX temp_component_data_mukey_idx ON temp_component_data (mukey) ;
VACUUM ANALYZE temp_component_data;
Combine with geometry and prepare for export
-- combine with geometry
CREATE TABLE temp_combined_data AS
SELECT ogc_fid, wkb_geometry, temp_component_data.*
FROM mapunit_poly
LEFT JOIN temp_component_data USING (mukey)
WHERE mapunit_poly.areasymbol IN ('ca624', 'ca628') ;
Export to shapefile
# export pgsql2shp -k -f data.shp -h the_host_machine -P the_username -u the_password -g wkb_geometry ssurgo_combined temp_combined_data
Remove temporary tables
-- clean-up DROP TABLE temp_combined_data ;
Links:
Checking Type Locations
Logistics: Getting Connected and Executing Queries
Learning by Example: Common Queries
