Getting Parent Material Data out of SSURGO
May 28, 2010 metroadminParent 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
Software
- General Purpose Programming with Scripting Languages
- LaTeX Tips and Tricks
- PostGIS: Spatially enabled Relational Database Sytem
- Importing and Exporting
- Example Spatial SQL Operations on Point, Line and Polygon Geometry Types
- Affine Transformation Operations in PostGIS
- Analysis of SSURGO Data in PostGIS: An Overview
- Making Database Diagrams with PostgreSQL Autodoc
- PROJ: forward and reverse geographic projections
- GDAL and OGR: geodata conversion and re-projection tools
- R: advanced statistical package
- GRASS GIS: raster, vector, and imagery analysis
- Generic Mapping Tools: high quality map production