GEOG 6150 Spatial Database Design
Development of a Database to Track Hop Terroir
Introduction
Terroir, the impact of geography and culture to the cultivation and production of a crop and it’s by product, is a well established and acknowledged concept in viticulture. The impact of geography and terroir in is a relatively new concept in the craft brewing industry, but one that is taking hold. This could be a result of the plurality of ingredients in beer, however recently hop cultivation has become greatly influenced by terroir. A 2017 study of Amarillo hops grown in Idaho and Washington indicated significant differences in taste and aroma among the same strain of hop (Holle et. al 2017) Understanding characteristic geographical and agricultural practices that contribute to these variances is tremendously important to the future of hop cultivation and the craft brewing industry. The database constructed to track the manifestation of terroir as related to the cultivation of crops will be a tremendous tool.
Methods
Conceptual Data Design
There are ten data layers in this database, see Figure 1, which geographically is restricted to Washington, Oregon and Idaho. The land currently under cultivation for hops and the physical and cultural elements that make up the terroir are the primary components of the database. Hop farm is the central data polygon layer and is derived from the cropscape layers of the Department of Agriculture. Slope and elevation are derived polygon layers from DEMs, with the value of slope or elevation. Temperature and rainfall are averaged rasters, which are then converted to polygons. The hop farm is related to each of these four layers by its’ location. Depending on the size of the hop farm, it is possible for there to be multiple locations, elevation values and slope values associated to it.
Soil data is a polygon layer provided by NRCS and contains several characteristics, soil type, soil pH, soil summary and a soil ID. There is a unique identifier with each soil type, in this database it’s the map symbol ID from the original dataset. Soil data has a many to many relationship to the hop farm, related by the farm ID. Similar to physical characteristics of they farm, multiple soil types may be represented in the farm depending on the size.
The State data is a polygon layer. The layer has a unique ID, the state name. Hop farms are located in states, states can contain many hop farms. Breweries dataset contains fields for the name and address, and are located in states. States can have many breweries. Statistical data about hop cultivation is not spatial, it is provided in tabular form by NASS. The NASS summarizes the data on a state level, therefore this data is related to the State layer by they state name. NASS data includes the state, hop type, and acreage cultivated.
The Blocks layer is provided by the US Census and is a polygon layer. The blocks layer is related to the farm layer by location, and it is a many to many relationship. The Blocks layer also has a unique identifier from the original dataset GEOID10, and the the tract classification.
Figure 1 Conceptual Design
Logical Model
The structure of the logical model can be seen in Figure 2. The central component of the database will be the HopFarm layer. The FarmID is used to link the hop farm to certain layers that express physical features of the farm. Slope, Soil, Elevation, Temperature and Rainfall are all linked to the HopFarm layer via this key. FarmID will be a random sequence, generated for the database, with the state abbreviation as the first two characters. State and Block layers are also linked to the HopFarm by their primary keys. As parcel data is not reliably available in a spatial format, the HopFarm is linked to the Block it falls in by the GEOID, which provides information on landuse classification. GEOID is a unique ID created by the US Census. The HopFarm layer will also have the state name as an attribute and use this as a foreign key to link to the State layer. Additionally the Brewery layer and NASS_Stat table will use the state name as a foreign key to the State layer. As the NASS_Stat data is only provided at the state level, this will be important to linking the data to the HopFarm layer. As there are multiple hops grown in each state, many the same variety, the State and HopType attribute will be a compound primary key for NASS_Stat table.
Figure 2 Logical Design
Physical Database
Translating the model into a file geodatabase required creating a spatial feature for each data layer and a table to contain the NASS data. Hop farm, soil, temperature, rainfall, slope, state, breweries and census blocks were all new feature classes. The data from the source was modified to include only the necessary fields and imported to database.Relationship classes were created from the logical model. For the soil, temperature, rainfall, slope and elevation layers, separate tables were created through a spatial join to link the multiple data layers to the hop farm data layer. A one to many relationship class joined each feature class to the table. NASS stats, breweries and hop farm layers all have a many to one relationship to the state layer, so the relationship was simply created linked the data sets.After the frame work was created, data from several public sources was loaded into the database including landuse data from the USDA to identify hop farms, soil data from NRCS, slope and elevation data derived from DEMs of Oregon, Washington and Idaho, as well as average temperature and rainfall fall for the regions.
Results and Discussion
Determining the structure for this database was driven primarily by the need to localize geophysical information. Locality is one of the key concepts of terroir, and attaching information at the level of the hop farm will allow users to begin to understand the terroir of the region they are interested in. As terroir is also dictated by farming practices, as much as geophysical characteristics, being able to access NASS and USDA data related to these practices enables the user to begin to form a more complete picture of the influences in hop flavors and aromatics.
The relationship classes allow the user to navigate the database efficiently. The most important information, physical relationships to hop farms, are easily queried in the GIS so that selecting a hop farm also brings up the related attributed from these fields. Identifying what hops are grown where is accomplished through the link in the NASS table and the state layer. This is important for understanding what is currently grown in what state as a means of establishing contrasts between qualitative attributes of the hops and the associate. The database also links this information to the breweries which will hopefully be more impactful as the data in this dataset becomes more complete. Comparison of beers brewed with hops from each region is the intention behind this relationship, however that data does not exist yet.
Conclusions
The database outlined here is the baseline for developing hop terroir. There is much more that could be done to improve the output and the database. Most of the improvements involve seriously time consuming data collection. First improvement would be to know what hops are grown on what field. This would require a large scale effort to contact hop farmers and get detailed information about their current and past crops. It would also likely require some field visits to hop farms or collaboration with farmers to generate more accurate boundaries of plots. Communicating with farmers would also provide more information related to actual farming practices in certain regions.
The next phase would be to collect a more exhaustive list of breweries. The breweries logged with the Brewers Association do not represent the total operations in each state. Contacting the brewers in each region to determine the hops being used, for what beer and how the flavor profile emerges in the beer would be the crowning achievement of this database. Similar to wine, understanding the components that create hop flavor and aromatic profiles is only part of the concept of terroir. How the plant is used in the final product is also tremendously important to understanding the full scope.
For more information, read the full report here.
MSGIS Program Skills:
Spatial Data and Algorithms
GIS Workflow
Data Models and Structures
Database Design
Structured Query Language
Communication Skills