Getting Parent Material Data out of SSURGO

Submitted by dylan on Fri, 2010-05-28 01:21.

 
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 ;