Logistics: Getting Connected and Executing Queries

 
Building and Saving SQL code snippets
A simple text editor is the best environment for working on (and saving!) your SQL queries. Linux users are encouraged to use either 'Kate' or 'Kwrite'. Windows users should look into Notetab Light or SciTE. Mac users should check out TextWrangler.

 
Connecting to the database
In general, the simplest way to interact with our composite soil survey database is by connecting to the best with an SSH client. Mac/Linux users have this functionality built-in. Windows users will need to use something like Putty, or Xming. Once an ssh connection with the beast has been setup, you can connect to the database with the following:

 
where psql is the postgresql client program, -U soil means connect as the user called "soil", and ssurgo_combined is the name of the actual database.

 
You can quit from the database shell using \q (followed by enter). Typing \? (followed by enter) will give a list of commands available in the psql shell.

 
Query structure and SQL
Numerous resources exisit for learning about SQL. I would recommend looking at the first couple chapters from the PostgreSQL book by Douglas and Douglas (the big purple book on the shelf). For an interactive learning approach SQL Zoo seems like a good start. In general most queries will have the format:

 
A simple query: column selection and filtering
Once connected it is possible to issue queries to the database. The results of a query are normally returned to the screen. For example, asking for the horizon boundaries and horizon names from the componenent identified with the given cokey (467038:646635) might look like this:

 
A more complicated query: column calculation and aggregation
Compute the total water holding capacity for a given component, identified by (467038:646635). Note that several operations are being performed on the data:

1. compute the available water holding capacity (in centemeters) for each horizon [green text]
2. compute the profile depth (in centemeters) for each horizon [green text]
3. aggregate the profile data by summing AHC and depth of each horizon [red text]

-- the query
select cokey, sum( (hzdepb_r - hzdept_r) * awc_r) as component_whc, sum( (hzdepb_r - hzdept_r)  ) as depth 
from chorizon 
where cokey = '467038:646635'
group by cokey ;

-- the results
     cokey     | component_whc | depth 
---------------+---------------+-------
 467038:646635 |         27.91 |   183

 
Note that it is also possible to save the results of a query into a new table. This is the usual strategy when performing any analysis that returns geometry (soil polygons, etc.) as geometry data cannot be visualized in a text display. Tables created in this manner can be exported from the database for map creation or further analysis. Spatial tables can be viewed directly by applications like QGIS or Mapserver. A simple method of accessing PostGIS data is not yet possible with ArcGIS.

Soil Texture Example

 
Premise

Compute a series of weighted-average soil texture fractions (sand, silt, clay), for every component, of every map unit in Yolo County. These values will be further weighted by the spatial distribution of each map unit.

 
Simple Visualization with R

Yolo County Common Soil Textures: Blue symbols mark common soil textures and their relative size denotes abundance. The orange symbol marks the weighted average soil texture for all of Yolo County.

Identification of Dated Surfaces via Soil Series

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.

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

   (5 rows)

6. 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

Seasonally Wet Soils and Shrink-Swell Potential

Example 1 map

Example 2 map

 
Example 1: The location of seasonaly wet soils via two methods: hydricrating and USDA Soil Taxonomy interpretation.

 
Example 1.1 Extract a list of wet components, and sum area based on component (series) name

 musym | mukey  |                             muname                             | wet_area_ac 
-------+--------+----------------------------------------------------------------+-------------
 Sc    | 459268 | Sacramento clay                                                |    35710.90
 Mf    | 459244 | Marvin silty clay loam                                         |    18877.18
 Sg    | 459272 | Sacramento soils, flooded                                      |    12273.51
 Cn    | 459219 | Clear Lake soils, flooded                                      |    11665.84
 Cc    | 459216 | Capay soils, flooded                                           |    11029.83
 Sv    | 459288 | Sycamore complex, drained                                      |     8410.41
 St    | 459286 | Sycamore silty clay loam, drained                              |     6900.77
 Ck    | 459218 | Clear Lake clay                                                |     6737.88
 Sa    | 459266 | Sacramento silty clay loam                                     |     6014.48
 Sp    | 459283 | Sycamore silt loam, drained                                    |     5537.18
 Sw    | 459289 | Sycamore complex, flooded                                      |     5041.96
 Wb    | 459301 | Willows clay                                                   |     4950.34
 Ss    | 459285 | Sycamore silty clay loam                                       |     4859.59
 Pb    | 459254 | Pescadero silty clay, saline-alkali                            |     4700.77
 So    | 459282 | Sycamore silt loam                                             |     3953.42
 Rh    | 459262 | Riverwash                                                      |     3698.88
 Pc    | 459255 | Pescadero soils, flooded                                       |     3589.01
 Tb    | 459292 | Tyndall very fine sandy loam                                   |     3299.03
 Ob    | 459252 | Omni silty clay                                                |     3174.44

[...]

 
Example 2: Classify soils according to shrink-swell capacity of the top 1 meter of soil, weighted by horizon thickness and component percent.

 musym |                              muname                              | muacres | lep  | lep_class 
-------+------------------------------------------------------------------+---------+------+-----------
 Ya    | Yolo silt loam                                                   |   39698 | 2.52 | Low
 Sc    | Sacramento clay                                                  |   34886 | 7.50 | High
 Ca    | Capay silty clay                                                 |   33465 | 7.50 | High
 MrG2  | Millsholm rocky loam, 15 to 75 percent slopes, eroded            |   30118 | 1.50 | Low
 Rg    | Rincon silty clay loam                                           |   24580 | 6.36 | High
 BrA   | Brentwood silty clay loam, 0 to 2 percent slopes                 |   23045 | 7.50 | High
 CtD2  | Corning gravelly loam, 2 to 15 percent slopes, eroded            |   22080 | 5.34 | Moderate
 Mf    | Marvin silty clay loam                                           |   20970 | 6.60 | High
 DaF2  | Dibble clay loam, 30 to 50 percent slopes, eroded                |   18612 | 7.11 | High
 SmE2  | Sehorn-Balcom complex, 15 to 30 percent slopes, eroded           |   17794 | 6.17 | High
 TaA   | Tehama loam, 0 to 2 percent slopes                               |   16622 | 3.75 | Moderate
 BdF2  | Balcom-Dibble complex, 30 to 50 percent slopes, eroded           |   16405 | 5.73 | Moderate
 SmD   | Sehorn-Balcom complex, 2 to 15 percent slopes                    |   16117 | 6.50 | High
 BaF2  | Balcom silty clay loam, 30 to 50 percent slopes, eroded          |   12637 | 4.50 | Moderate
 Sg    | Sacramento soils, flooded                                        |   12258 | 6.27 | High
 Cn    | Clear Lake soils, flooded                                        |   11666 | 6.92 | High
 SmF2  | Sehorn-Balcom complex, 30 to 50 percent slopes, eroded           |   11226 | 6.33 | High
 Cc    | Capay soils, flooded                                             |   11030 | 7.50 | High
 Sv    | Sycamore complex, drained                                        |    9241 | 4.18 | Moderate
 Ms    | Myers clay                                                       |    8938 | 7.50 | High
 PfF2  | Positas gravelly loam, 30 to 50 percent slopes, eroded           |    7920 | 5.34 | Moderate
 St    | Sycamore silty clay loam, drained                                |    7839 | 4.50 | Moderate
 Ck    | Clear Lake clay                                                  |    6946 | 7.50 | High
 Ra    | Reiff very fine sandy loam                                       |    6847 | 1.50 | Low

[...]