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 chart for database design
Logical Data 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 chart for database design
Physical Database and Proof of Concept
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. Once the data was loaded a number of queries and tests were performed to verify the functionality of the database. See below for several examples of the database capabilities.
Examples of Database functionality:
Query 1:
Select * FROM HopFarm Elevation, Slope, Soil, Temperature and Rainfall WHERE Hopfarm.FarmID = ‘ID1230’
The purpose of this query is to illustrate one of the main functions of this database, which is establishing a understanding of terroir as it relates to Hops. The five features listed above (Elevation, Slope, Soil, Temperature and Rainfall) will likely have an impact on the characteristics of the hops that are grown in these regions.
First step prior to executing the query is to ensure the box next to Automatically select related data is checked, see Figure 3. The five physical features of the landscape related to Hopfarm are linked via related tables and this expedites the process of identifying other characteristics.
Figure 3 Ensuring the Database will select related records
The query is input into the SQL interface, as shown in Figure 4:
Figure 4 SQL interface in ArcGIS Pro
The attributes tab allows the user to interface with the related tables, see Figure 5:
Figure 5 Attribute tab in ArcGIS Pro
From there the user can explore the different characteristics linked to the HopFarm layer via the other related tables, see Figure 6.
Figure 6 Illustration of layer HopFarm related tables in ArcGIS Pro
We can see for this field the following are characteristics of the farm:
Elevation - 736 m
Slope - 0 % slope
Soil - Type is 666912 - "Owyhee-Nyssaton-Greenleaf-Garbutt (s6366)" * Generally a coarse silty loam
Temperature - average min for the months of Jan-March and Oct-Dec of 2017 ranged from -11 C to 1 C. Figure 7 below illustrates the temperature selection. *These are acceptable low temps for causing dormancy in hop vines
Figure 7 Selected temperatures from query in ArcGIS Pro
Rainfall - Average annual rainfall from 1980-2010 was 9 “
Query 2:
SELECT * FROM State, NASS_Stats WHERE State.Name = ‘Washington’ AND NASS_Stats.HopType = ‘Simcoe R, YCR 14’ . Figure 8 shows the query in the ArcGIS Pro interface.
*R is for registered ™
Figure 8 SQL interface in ArcGIS Pro
The pop-up shows the total acreage of Simcoe hops grown in Washington at 3753. Simcoe is strong crop for Washington, in 2017 it was one of the four largest acreage for hops in the state, along with Citra, Centinnial and Cascade. The latter two are relatively established in “brewing culture”, Simcoe and Citra and still new enough that many brewers advertise the beer with the hop in the moniker.
Query 3:
HopSoil.FarmID LIKE 'OR%' And Soil.SoilID = HopSoil.FarmID
The script is take from Arc SQL translator, as shown in Figure 9. The two main tables involved are HopSoil, which is a relationship feature class, and Soil. The intention is to select all the soil types in Oregon that are also present in a Hop farm. It illustrates several very distinct areas and from a visual standpoint helps identify distinct growing ares. It is also interesting to note there are over 1400 rows represented, although several are duplicates, it attests to the ability of hops to grow in a range of soil types.
Figure 9 SQL interface in ArcGIS Pro showing query
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.