SSURGO (Soil Survey Geographic Database)

The US Department of Agriculture's Soil Survey Geographic Database (SSURGO) is the standard soil dataset for the United States. The dataset contains not only data about the soil itself, but many other types of data, such as crop production and potential, forest species, slopes and many others.

With such high quality and finely located data comes a price: the dataset is large and difficult to work with. Clockwork Micro has completed several projects related to SSURGO for partners and looks forward to projects with new partners.

SSURGO - Working with a Large Data Set

The first challenge when working with SSURGO is the size. Each state is its own geodatabase. Within each geodatabase are 76 (!!) different tables. These, however, are so large that it is often difficult to export the tables that one is interested in. We recommend PostGIS for working with SSURGO, however loading such large datasets to PostGIS also comes with challenges. Clockwork Micro has expertise in efficiently handling such large datasets, moving them to PostGIS and working with them within PostGIS.

SSURGO - Complicated Joins, Selects

The SSURGO data itself also presents challenges. Here we consider one of these. The main geospatial data set (the mupolygon table) is grouped into small regions called 'map units' and have the identifier 'mukey'. A commonly used SSURGO table is the component table. The component table contains 111 columns with data on the type of vegetation coverage, slope, precipitation, air temperature and others. The challenge, however, is that each mukey can correspond to several components in the component table.

To join exactly one component row value to each mupolygon row value we have to choose which component value to join. Our first criterion is whether a component has been marked with the 'major component flag' (majcompflag). If only one component had this flag, but cases occur when multiple component have the majcompflag value "Yes". So, among the components with majcompflag value "Yes" we choose the one (or one among) value with the highest comppct_r value, which is the percent of the total component value that the row contributes. Here, however, we have noticed that the comppct_r values do not always add to 100, and comppct_r values do not necessarily have a unique maximal value, e.g. two comppct_r values could be 40.

Postgresql has a fantastic command to execute this query: DISTINCT ON. Distinct on groups entries by one values orders them within each group and then chooses the first row from each group. In our case, we group by mukey, and then we order according to majcompflag (Yes and then No) and then by comppct_r.

SELECT DISTINCT ON (mukey) mukey,cokey,majcompflag,comppct_r
FROM component
ORDER BY mukey,majcompflag DESC,comppct_r DESC

If you've tried to code this without Distinct On you will appreciate this fantastic command. For example, it takes care of the case that there is no row marked majcompflag.

SSURGO - Creating Map Tiles and a Web Map

Of course one also wants to SEE the data. Once the data is in PostGIS, one can join a unique component value to each mupolygon value using the above command. We created such a join and included a sample of the many fields available and exported these using ogr2gor to create a shapefile. The shapefile for the state of Washington for this query would be several GB, and so we've exported just the beautiful Skagit Valley (i.e. Skagit County).

Using our no code tool Map Maker, we create vector tiles, style them, add them to a map and add a data display (i.e. a popup), all with no code. The popup in the example is the default style for a popup in Map Maker. Custom styles are easy to make with no code.

Voilà:

Creating tiles and a web map for a state or larger requires a bit more work simply because the data is large and in general needs to be partitioned into smaller pieces.

Clockwork Micro has a team of experts working with the USDA SSURGO data and looks forward to working on projects with new partners.