University of Southern California Dissertations and Theses

Building a spatial database for agricultural record keeping and management on a regenerative farm

(USC Thesis Other)

Building a spatial database for agricultural record keeping and management on a regenerative farm

PDF

Download a page range

Download transcript

Copy asset link

Request this asset

Transcript (if available)

Content

Building a Spatial Database for Agricultural Record Keeping and Management on a Regenerative Farm

by

Philip Thomas Hess

A Thesis Presented to the
Faculty of the USC Dornsife School of Letters, Arts and Sciences
University of Southern California
In Partial Fulfillment of the
Requirements for the Degree
Master of Science
(Geographic Information Science and Technology)

August 2022

Copyright © 2022 by Philip Hess

iii

Dedication

To my family, friends, and farmers around the world.

iv

Acknowledgements
I am grateful to my advisor Dr. An Min Wu for her database expertise as well as Dr. Darren
Ruddell and Dr. Jennifer Swift for serving on my thesis committee. I would also like to thank my
peer reviewer Michael Lizarraga for his advice and Dr. Vanessa Osborne for her writing
guidance. Finally, I want to express my appreciation to all the farmers who took time to
participate in this research.

v

Table of Contents
Dedication ...................................................................................................................................... iii
Acknowledgements ........................................................................................................................ iv
List of Tables ............................................................................................................................... viii
List of Figures ................................................................................................................................ ix
List of Abbreviations ..................................................................................................................... xi
Abstract ......................................................................................................................................... xii
Chapter 1 Introduction .................................................................................................................... 1
1.1. Farming Practices................................................................................................................1
1.1.1. Regenerative Agriculture ...........................................................................................1
1.1.2. Industrial Agriculture .................................................................................................4
1.1.3. Farm Record Keeping ................................................................................................5
1.2. Study Motivation ................................................................................................................6
1.2.1. Study Area .................................................................................................................6
1.2.2. Research Goal ............................................................................................................8
Chapter 2 Related Work.................................................................................................................. 9
2.1. Farm Record Keeping and Management ............................................................................9
2.1.1. Evolution of Record-Keeping ....................................................................................9
2.1.2. Farm Management Information Systems (FMIS) ....................................................12
2.2. Geographic Information in Agriculture ............................................................................13
2.2.1. Remote Sensing for Agricultural Record Keeping and Analysis ............................13
2.2.2. GIS Integration with Farms .....................................................................................14
2.2.3. Precision Agriculture ...............................................................................................15
2.3. Spatial Databases ..............................................................................................................16
2.3.1. Agricultural Record Keeping with Databases..........................................................16
vi

2.3.2. Spatial Databases for Farm Management ................................................................17
2.3.3. Open-Source Farm Management .............................................................................19
Chapter 3 Methods ........................................................................................................................ 21
3.1. Research Overview ...........................................................................................................21
3.1.1. Workflow .................................................................................................................21
3.1.2. Software Considerations ..........................................................................................22
3.2. Data Collection .................................................................................................................23
3.2.1. Interviews Methods ..................................................................................................23
3.2.2. Data Preparation.......................................................................................................25
3.3. Database Design................................................................................................................25
3.3.1. Conceptual Design ...................................................................................................25
3.3.2. Logical Design .........................................................................................................28
3.3.3. Data Types ...............................................................................................................29
3.4. Database Input and Access ...............................................................................................31
3.4.1. Data Input.................................................................................................................32
3.4.2. Querying the Database .............................................................................................34
Chapter 4 Results .......................................................................................................................... 36
4.1. Interview Results ..............................................................................................................36
4.1.1. Farming Practices.....................................................................................................37
4.1.2. Current Record Keeping ..........................................................................................38
4.1.3. Farmer’s Data Needs................................................................................................44
4.2. Final Database Design and Implementation .....................................................................45
4.2.1. Final ERD ................................................................................................................45
4.2.2. Farm Data.................................................................................................................49
4.2.3. Physical Implementation and Metadata ...................................................................50
vii

4.3. Data Queries and Visualization ........................................................................................52
4.3.1. Simple Database Queries .........................................................................................52
4.3.2. Relational Queries ....................................................................................................54
4.3.3. Temporal Queries.....................................................................................................55
4.3.4. Spatial Queries .........................................................................................................57
Chapter 5 Conclusion and Discussion .......................................................................................... 62
5.1. Conclusions .......................................................................................................................62
5.1.1. Effectiveness of Database ........................................................................................63
5.1.2. Overall Value ...........................................................................................................63
5.2. Limitations ........................................................................................................................65
5.3. Future Directions ..............................................................................................................67
References ..................................................................................................................................... 70
Appendix A Social Behavioral IRB Protocol ............................................................................... 76
Appendix B IRB Approved Exemption ........................................................................................ 80
Appendix C Interview Script ........................................................................................................ 81
Appendix D Database Creation Script .......................................................................................... 82
Appendix E Table and Attribute Descriptions .............................................................................. 84
Appendix F Database Queries Script ............................................................................................ 90

viii

List of Tables
Table 1. Conceptual framework of agricultural features in a regenerative farm database. .......... 26
Table 2. Regenerative farms and organizations interviewed for this research. ............................ 36

ix

List of Figures
Figure 1. Map of agricultural cropland areas within Ventura County, CA. ................................... 7
Figure 2. An “agenda” of agricultural records kept by a French farmer in the 1970’s. ............... 10
Figure 3. Section of a database schema to hold agricultural records. ........................................... 12
Figure 4. ERD of database for management of an irrigation system ............................................ 18
Figure 5. Entity Relationship Diagram for spatially based farm record keeping ......................... 19
Figure 6. Workflow diagram showing the general steps required to create the
demonstration database (DB) for this thesis. ................................................................................ 22
Figure 7. Bubble diagram of major departments in diverse farms, the entities
they manage, and some of their relationships. ............................................................................. 27
Figure 8. Conceptual ERD of a spatial database for regenerative farm management. ................. 28
Figure 9. Early stage of logical ERD for spatial database of farm records. ................................. 29
Figure 10. Image of an example SQL query for adding data into the CompostTurn table. ......... 32
Figure 11. Image of the CompostTurn table that was uploaded to the database. ......................... 33
Figure 12. SQL used to copy data from a .csv file and populate it into
the CompostTurn table. ................................................................................................................ 33
Figure 13. The spatial attributes of the FarmPlot table created as a shapefile in QGIS. .............. 34
Figure 14. SQL query for joining spatial data to an existing table. .............................................. 34
Figure 15. Images of hand-written planting (a) and harvest (b) records from a
regenerative farm in Ventura County. .......................................................................................... 39
Figure 16. Portions of spreadsheets used to track pasture (a), compost (b),
and garden (c) records on a regenerative farm in Ventura County. ............................................. 41
Figure 17. Physical hardcopies of farm locational references using (a) computer-drawn
garden beds and (b) printed aerial photo imagery. ...................................................................... 42
Figure 18. Examples of farm records stored in (a) Google MyMaps and (b) CropTracker. ........ 43
Figure 19. The final ERD showing the logical schema of a spatial database for a
regenerative farm. ......................................................................................................................... 46
x

Figure 20. SQL used in pgAdmin 4 interface to create a farm database,
add the PostGIS extension, and create database tables. ................................................................ 50
Figure 21. Metadata describing the database tables, attributes, and relationships. ...................... 51
Figure 22. Simple query (Query 1) with a portion of its data output............................................ 52
Figure 23. Simple query (Query 2) with a portion of its data output. .......................................... 53
Figure 24. Simple query (Query 3) with a portion of its data output............................................ 54
Figure 25. Relational query (Query 4) with a portion of its data output. ..................................... 55
Figure 26. Temporal query (Query 5) with its data output. .......................................................... 56
Figure 27. Temporal query (Query 6) with resulting data output. ................................................ 57
Figure 28. Spatial query (Query 7) with a portion of its data output (a) and map view (b). ........ 58
Figure 29. Spatial query (Query 8) with a portion of its geometry viewer output. ...................... 59
Figure 30. Spatial query (Query 9) with a portion of its geometry viewer output. ...................... 60
Figure 31. Spatial query Query 10) with a portion of its geometry viewer output. ...................... 61

xi

List of Abbreviations
Ag Agriculture
CDL Cropland Data Layer
DB Database
FMIS Farm Management Information Systems
GIS Geographic Information Systems
GNSS Global Navigation Satellite Systems
NASS National Agricultural Statistic Service
NDVI Normalized Difference in Vegetation Index
PA Precision Agriculture
RS Remote Sensing
SDB Spatial Database
SQL Structured Query Language
UAV Unmanned Aerial Vehicle
USDA United States Department of Agriculture
USGS United States Geological Survey
xii

Abstract
Identified by polycultures of plants and animals, regenerative farms are made up of complex
interrelated systems and face challenges with data management and record keeping. Despite
regenerative farms having more complex record keeping needs than industrial (monoculture)
farms, they are not well supported by existing farm management software. A spatial database can
be a powerful tool for organizing, accessing, and analyzing farm data. The objectives of this
research are to design and create a functional demonstration of a spatial database for agricultural
record keeping that is tailored to the needs of regenerative farmers. The initial database design
was informed by an extensive literature review of record keeping technologies in agriculture as
well as the author’s professional experience working on regenerative farms. The database’s
logical schema was finalized after conducting interviews with farmers and leaders of the
regenerative movement in Ventura County, CA. Nine interview subjects representing five
regenerative agriculture organizations participated in this study. The farms had varying record
keeping practices, from memory, to spreadsheets, to Farm Management Information Systems
(FMIS). A spatial database was created in PostgreSQL with the PostGIS extension and populated
with archival farm data to demonstrate the database’s usefulness to regenerative farmers. The
data was combined and visualized through SQL queries that leveraged the relational, temporal,
and spatial qualities of the farm data. While this spatial database requires technical proficiency to
set up and maintain, it was found to be more effective at handling a farms’ data than their current
record keeping systems. Spatial databases are well equipped to handle the data needs of a
regenerative farm.

1

Chapter 1 Introduction
Regenerative agriculture can be broadly defined as diverse farms that grow a variety of types of
crops and livestock, limit their chemical inputs, and focus on nutrient cycling and biodiversity as
a source of fertility (Giller 2021). Regenerative agriculture has the goal of providing food for
humans while improving the natural fertility of the land. It also represents a shift away from
industrial agriculture, where farms grow monocultures (i.e., only one crop type) which are
extremely reliant on the agrochemical industry for fertility and pest control (LaCanne 2018).
Given the numerous variables and complexities on a regenerative farm compared to an industrial
monoculture farm, regenerative agriculture has unique and complex data management and
record-keeping needs that can be addressed with a spatial database. The objectives of this
research are to design and create a functional demonstration of a spatial database for agricultural
record keeping that is tailored to the needs of regenerative farmers. This chapter discuses
different farming practices (Section 1.1) which sets up the motivation and outline of the rest of
the thesis (Section 1.2).
1.1. Farming Practices
Humans have been farming for many generations and have developed varied agricultural
practices (Berglund 2014). This section introduces some of these farming practices and compares
regenerative and industrial agriculture. It continues by discussing the intricacies and importance
of farm record keeping for agricultural management.
1.1.1. Regenerative Agriculture
Regenerative agriculture is a holistic farming system that emphasizes biodiversity and
nutrient cycling and is characterized by diverse polycultures of plants and animals. It advocates

2

against chemical inputs, as most chemical inputs (e.g., pesticides, herbicides, and fungicides)
limit biodiversity (White 2020). Regenerative farms aim to reduce all their external inputs and
instead rely on biodiversity to create fertility. While it is no longer the way most food is grown,
regenerative farming with small diverse polycultures is not a new concept. Humans have been
farming this way for thousands of years (Berglund 2014).
As a foundation for life, soil is a relatively good indicator of ecosystem health. Soil is a
complex mixture of minerals, water, bacteria, fungi, protozoa, nematodes, worms, and more
(Saleem 2019). Healthy soil is the basis of plant fertility, as it is where plant roots naturally grow
and where all terrestrial life begins and ends (Saleem 2019). Root growth fuels plant growth and
plant growth produces the food that humans eat. While this is not a novel idea, it is important to
remember the value of soil as much of modern society becomes farther removed from its food
production.
Soil is essentially a food web, where microorganisms are constantly breaking down
(“eating”) dead organic matter (such as a leaf or dead roots). These organisms are often eaten by
larger ones, which connects larger life forms, like worms, with single-celled organisms in the
food web (Rhodes 2017). There are also living plant roots spread throughout the soil, which
release sugars into the surrounding soil to encourage the growth of microbial communities
(Lehman 2015). This diverse community of creatures found in healthy soil converts nutrients
from dead plant material or animal manures into forms that can more readily be used by living
plants. This biodiversity and the complex relationships between all life create healthy soil (Crews
2018).
Rotational grazing is a form of pasture, soil, and livestock management that leverages
biodiversity to build soil and encourage plant and animal health (Savory 1983). In rotational

3

grazing, large ungulates (e.g., cows) move frequently, grazing on plants, trampling seeds into the
soil, and leaving manure wherever they go (Savory 2013). Manure is essentially organic matter
(grass eaten by the cow) macerated and coated with microorganisms from inside the cow’s four
stomachs (Harfoot 1981). This manure adds diverse nutrients and biology to the soil, but only if
there are enough living organisms in the soil to break down the manure.
While the cows rotate, other farm animals can help accelerate the manure’s
decomposition, such as chickens who graze a few days behind cows, eating the larger insects and
larvae growing in fresh manure (Salatin 2018; Hammond 1942; Moula 2018). In the process, the
chickens spread manure around and scratch it into the top layer of the soil, speeding up the
manure's decomposition and incorporating it into the soil food web more efficiently. To fully
take advantage of a rotational grazing system, farmers need to document when pastures were
seeded, when animals have grazed each pasture, and more. Rotational grazing is just one aspect
of regenerative agriculture, but it exemplifies the numerous variables at play and the importance
of the relationships between those variables
In regenerative farming systems diversity is the driving force of fertility. Diversity in the
food web is what connects all life, from large mammals (e.g., humans and cows) to microscopic
soil bacteria. To encourage this diversity, regenerative farms often produce their own compost,
grow multiple species of cover crops between orchard trees, rotate both cows and chickens daily,
and support native habitat areas that do not grow crops (Rhodes 2017). Regenerative agriculture
is a broad term, and every farm is unique. Therefore, the above practices are not meant as an
exhaustive or required list of farm endeavors. Rather, it is meant as an overview of some of the
potential variables present on a regenerative farm. Regenerative farms are complex,

4

interdependent systems which have different organizational requirements than industrial
agriculture.
1.1.2. Industrial Agriculture
While regenerative agriculture encourages diversity in its farming products to help ensure
diversity in the soil, industrial agriculture operates from an opposite framework of specialization
and scale. Industrial agriculture is the product of the Green Revolution which took the tenets and
infrastructure of the Industrial Revolution and applied them toward increasing agricultural
production. The Green Revolution was marked by the advent of genetically modified crops,
chemical fertilizers, pesticides, and both faster and wider distribution networks (Pingali 2012).
This type of industrial farming has been around for fewer than 100 years and has already
transformed global food systems, and by extension, most humans’ diets.
Since the green revolution, much of the agricultural industry has been consolidated into
large monoculture farms (Fitzgerald-Moore 1996). Monocultures require chemical inputs of
fertilizers, herbicides, pesticides, and fungicides to ensure that the one desired crop grows and
nothing else can compete with it (Pimentel 2005). These industrial operations are thousands of
acres and specialized to the point of only producing one type of agricultural good (e.g., annual
crops, livestock, orchard trees, compost—no combinations) (Altieri 2017). Many operations go
further and only grow a few species within a given type of food. A 10,000-acre farm that only
produces corn is not uncommon in the United States, as these practices have historically been
encouraged by the government through subsidies meant to ensure national food security
(Kammer 2012). There are consequences to this farming system beyond its inherent reliance on
the agrochemical industry (Crews 2018).

5

Despite their widespread use, industrial monocropping systems have negative impacts on
environmental health. The environmental downside to these monoculture farms is the same thing
that makes them economically efficient; a lack of diversity (Gibson 2007). While growing only
one crop may streamline production and harvesting, it depletes the soil of nutrients and biology
making crops completely reliant on chemical fertilizers and pesticides to survive. These added
chemicals do not incorporate into the solid food web efficiently, and often runoff into nearby
bodies of water (Pimentel 2005). The lack of diversity in plant species also makes the farm
susceptible to widespread disease and pest damage (Paredes 2020). It is beyond the scope of this
project to analyze all the economic and environmental pros and cons of industrial (large
monoculture) vs. regenerative (small polyculture) agriculture; but one may rest assured that these
are different approaches to land management, each with their own unique challenges.
1.1.3. Farm Record Keeping
Accurate and complete records help businesses understand their operations so they can
make informed decisions (Bailey 2011). Regenerative farms need to record many species of
annual and perennial plant crops at varying stages of growth, track grazing routes of multiple
livestock species, monitor native habitat areas, and more. Industrial agriculture is often interested
in only one crop at a time, which leads to a simpler record keeping system. Records for a
regenerative farm represent many entities occupying the same spaces and moving over time, so
are well suited for a spatial database (Sreekanth 2013). With accurate data about farm operations
(e.g., how many seeds have been planted and how well they have grown), farmers can make
better management choices for both the business and ecological health of the farm (e.g., order the
appropriate amount of seeds needed for a pasture at the right time). More accurate records save
the manager time, labor, and money, all while helping the farm stay ecologically productive

6

(Sreekanth 2013). A regenerative farm is diverse, interconnected, and always changing. Its data
management needs are naturally more complex than that of an industrial farm growing only one
crop per season
1.2. Study Motivation
Much of agricultural record keeping research is focused on Geographic Information
Systems (GIS) used for precision agriculture (PA) on large monoculture farms (Zhang 2016;
Bhakata 2019). While this is a valid use of the technology, GIS and the underlying spatial
databases that support the GIS can also greatly benefit small, diverse farms (Aicardi 2020;
Apolo-Apolo 2020; Nishiguchi 2009). Regenerative agriculture involves many moving parts, all
of which exist on the land; from crop placement and succession planting, to livestock rotation,
grazing patterns, and irrigation plans (Oates 2011). These operations include significant spatial
and temporal data and have many records that need to be updated regularly. Keeping data in a
spatial database can help improve the completeness and accuracy of a farm’s records (Sreekanth
2013). A spatial database can record the spatial relationships between different resources on a
farm as well as how the resources and their management practices change over time (Milicic
2012). This section presents the study area and research goals including an outline of the thesis.
1.2.1. Study Area
The specific study area for this research is agricultural land in Ventura County,
California. Agriculture is the cornerstone of the county’s economy, and an important part of its
heritage (Brownly 2018). Located northwest of Los Angeles, Ventura County is home to a $1.5
billion agricultural industry that supplies about 8% of the jobs and accounts for roughly 27% of
the land in the county (Shirley 2008; McCluskey 1995). Figure 1 shows a map of the cropland
areas in the county in 2021. The figure shows a portion of the Cropland Data Layer, which is a

7

product of the United States Department of Agriculture (USDA)’s National Agricultural
Statistics Service (NASS).

Figure 1. Map of agricultural cropland areas within Ventura County, CA.
Ventura has a mild Mediterranean climate with few frost days, which makes it suitable
for growing many types of crops, but agriculture in the county is limited by low rainfall amounts
(about 15in per year) and high property values (Shirley 2008; McCluskey 1995). Having worked
with farms in Ventura for several years, the author has a professional relationship with the

8

agricultural industry and knows that many of these farms are open to improving their
organization and data management.
1.2.2. Research Goal
This research aims to design, build, and demonstrate a spatial database for record keeping
and management on a regenerative farm. The goal is to create a spatial database that effectively
utilizes the spatial and temporal nature of agricultural data to better support informed decision-
making on a regenerative farm. The data can be accessed, combined, and visualized through
database queries. For example, the orchard team can easily check when the livestock team move
sheep, the compost team can find where the cows were two days prior (to collect manure), or the
picking team can see which orchard rows have a hold on harvesting because of recent
fertilization. Keeping all this data for multiple farm departments in a spatial database allows for
much more detailed and complete analyses by farm managers, who may not be involved in the
day-to-day work of every department. It also helps farm workers understand the complexities of
the farm beyond their individual tasks.
Following this introductory Chapter 1, the next chapter (Chapter 2 Related Work)
explores literature relating to farm record keeping and management systems, geographic
information in agriculture, and spatial databases. Chapter 3 Methods describes the steps that
went into designing this research, how data were collected, and how the database was designed
and implemented. Chapter 4 Results provides the results of the farmer interviews, the final
database design and implementation, and queries to access and visualize the data. Chapter 5
Conclusions and Discussion concludes with the database’s value to farmers, as well as
limitations and future steps in this research.

9

Chapter 2 Related Work
Agriculture is crucial for human life and development. As the source of our food, agriculture is
an integral part of our society, and it accounts for the largest area of land use by humans (Foley
et al. 2005). Agricultural studies focus on improving agricultural management using various
approaches, from national record keeping, to GIS integration and real-time productivity analysis
(Aicardi et al. 2020; Dohlman 2022). This chapter outlines the methods and results of literature
related to farm record keeping and management (Section 2.1), geographic information in
agriculture (Section 2.2), and spatial databases (Section 2.3).
2.1. Farm Record Keeping and Management
Documenting and maintaining agricultural practices and production amounts on a farm is
essential for the success of the farm operation. Good data management is based in creating
efficient methods for storing, organizing, retrieving, and analyzing data. This section summarizes
the evolution of record keeping on farms and introduces modern Farm Management Information
Systems (FMIS).
2.1.1. Evolution of Record-Keeping
Agriculture has been around much longer than computers, and historically farmers have
kept their records without using any form of electronics. Agricultural record keeping starts
simply with memory, as many farmers rely on tradition and experience to understand their crops
(Kok 1985). When more detailed data is required, farmers may write in diaries or daily agendas.
These diaries (as shown in Figure 2) consist of a few words written in a small notebook for each
day telling what was completed that day, what equipment was used, and any technical notes
about the work (Joly 2011). Even today many agricultural operations, especially small farms,

10

rely on their workers' individual knowledge, memory, and hand-written notes to keep track of
relevant information (e.g., planting dates, nutrients applied, number of days grazed) (Norvell
2003; Pers. Com. Steve Sprinkle).

Figure 2. An “agenda” of agricultural records kept by a French farmer in the 1970’s (Image
Source: Joly 2011).
The next step up in record keeping technology is using simple digital spreadsheets to
record information (Claiborne 1991; Grisham 2007). Spreadsheets hold data in cells that are
organized into columns and rows (Van de Aalst 2018). This data can be manually input into a
cell or be calculated based on custom formulas using the data in surrounding cells (Van de Aalst
2018). This is an improvement over mental and paper notes, but the spreadsheets essentially
function as a more legible version of handwritten notes and cannot efficiently handle large
amounts of data (Van de Aalst 2018). Some information is not recorded systematically, and that
knowledge often leaves the farm with the worker. This can be especially problematic given the

11

high employee turnover among farm workers caused by the inherent seasonality and traditionally
low wages of farming (McCluskey 1995). Multiple record keeping systems (e.g., memory,
handwritten notes, and spreadsheets) are often all in place at the same time within the same farm,
and that patchwork of systems leaves information falling through the cracks (Rane 2017). This
jumbled record keeping results in a lot of information and data that rarely stays organized
enough to use seasons later. A well-designed spatial database could change this organizational
standard and help farmers better understand and take advantage of the complexity and
interdependence that fuels farming (Sreekanth 2013).
The improvements in agricultural efficiency brought by increased record keeping have
been studied for over 30 years. Since the early days of computers there have been advocates of
using electronic databases to store more complete agricultural records and improve a farm’s
productivity (Kok 1985). A database is the foundational framework for computer-based data
organization and can be useful for agricultural record keeping. The simple database schema in
Figure 3 shows an early example of a database designed for agricultural records (Kok 1986). The
entities (things being tracked by the farm) represent Parcels, Crops, and Treatments. Treatments
are applied to Parcels and Parcels are seeded with Crops. The full database schema developed by
Kok represents over 20 farm related entities with dozens of attributes and relationships. Kok’s
database schema is effective for what it was built for, and accounts for many parts of a farming
operation, but it does not utilize the spatial relationships between entities. It is also not designed
to handle grazing records or multiple species of livestock animals.

12

Figure 3. Section of a database schema to hold agricultural records. Boxes represent entities,
their contents are attributes, and the connecting lines are relationships. (Image Source: Kok
1986).
Despite the many technologic advances in record keeping, these advances have not all
been fully adopted in agriculture. Grisham’s (2007) study of record keeping technology adoption
was based on interviews with farmers and found that 22% of Louisiana dairy farmers do not use
computers at all in their record keeping. The study found that improved data management and
recordkeeping can benefit small farmers and increase farm efficiency and profitability (Grisham
2007).
2.1.2. Farm Management Information Systems (FMIS)
With the rise in computational power and efficiency of computers, companies have
developed software to integrate a farm’s record keeping with its larger management information
systems. Farm Management Information Systems (FMIS) are computer information systems
specifically developed for agriculture. Tummers et al. (2019) and Guia et al. (2021) review the
state of popular Farm Management Information Systems (FMIS) from 2008 to 2018 and 1998 to
2019, respectively. They find that FMIS are generally specific to certain domains (such as dairy

13

farming or orchard crops). The studies identify the major features of existing FMISs (e.g., crop
management, data processing, accounting) and some of their obstacles, including cost, data size,
and understandability. These FMIS do not have spatial components to their entities and if so, it is
limited to only a few of the entities. Due to a lack of spatial data, features on the farm cannot all
be easily mapped, and their attributes cannot be spatially joined and related. The specificity of
these systems is an issue, as they are only equipped to record certain departments (Tummers et
al. 2019). To keep track of livestock, orchard trees, habitat plantings, compost, and all integral
components of a regenerative farm, would require multiple FMIS.
2.2. Geographic Information in Agriculture
The introduction of Geographic Information Systems (GIS) represents a pivotal phase in
the effectiveness of computers for farm management. GIS are computer- based tools to store,
visualize, analyze, and interpret geographic data. A GIS is equipped to organize and store farm
data because of its analytical and visualization capabilities (Norvell 2003). Farm data is also
particularly suited for GIS because the features represented exist over a large area of land, which
makes them ideal for spatial analysis (Milicic 2012; Palisson 2010). This section describes how
remote sensing (RS) technologies have been applied to agricultural record keeping. It also
introduces GIS integration with farms and the concept of precision agriculture.
2.2.1. Remote Sensing for Agricultural Record Keeping and Analysis
Remote sensing (RS) has been extensively utilized within the agricultural context, from
images to multispectral analyses, lidar, and more. RS involves any kind of measurements from
sensors taken far from the phenomenon being measured (Weiss 2020). The most common type
of RS data is imagery, though there are many other forms of data and many spectral wavelengths
beyond what is visible to the human eye that can be studied through RS (Yao 2019).

14

Remote Sensing information can be used to document and record what happens on a
farm. Detailed organization of RS measurements can bring intriguing new insights to farmers.
Flynn (2006) examines the potential of using the Normalized Difference in Vegetation Index
(NDVI) (calculated from multispectral RS imagery) to assess the suitability of land as pasture to
feed grazing animals. Aicardi et al. (2020) describes a python customization for farmers that
analyzes drone imagery and performs orthometric corrections for use of the imagery in real-time
field analysis. Apolo-Apolo et al. (2020) similarly used unmanned aerial vehicle (UAV) imagery
as the basis for a program that estimates apple tree health and expected yields. All these studies
use RS to document conditions on a farm and inform agricultural decision-making.
2.2.2. GIS Integration with Farms
Having farm information recorded and organized allows a farmer to manage a large area
of land and easily identify exceptional problem areas to work on. Norvell (2003) further
developed this idea, describing how Geographic Information Systems (GIS) can be integrated
with farm data to enhance farm management and record keeping. Norvell describes the improved
efficiency in recording, storing, retrieving, and communicating farm information that comes
from record keeping with GIS. Continuing this decades long trend of improving agriculture with
technology, Inwood (2019) identifies numerous computer apps for farming that help improve the
sustainability of agriculture. The apps address various aspects of the agricultural industry
including regulatory compliance, equipment optimization, information management, product
tracking, spatial mapping, and more. All these apps improve the documentation and organization
of agricultural data.
Nishiguch and Yamagata (2009) showcased how GIS can be applied on farms to improve
agricultural productivity. Their study analyzes a GIS that manages data about farm products,

15

yields, soil type, fertilizer usage, and more, all linked to locations on the farm. Nishiguch and
Yamagata (2009) found that GIS records are more effective than ledger-based management
systems (e.g., spreadsheets) for crop planning and productivity analysis. Bligaard (2014)
discussed a similar GIS-based FMIS for planning and documenting agricultural production.
Bligaard (2014) used Microsoft SQL Server Database Management Systems to build a
production database and incorporate that into a mobile application for use on farms in Denmark.
This database tracks and analyzes soil conditions (e.g., soil temperature, soil moisture), pesticide
use, fertilization rates, and more. This FMIS is hosted online and stores information on over 80%
of the farmland in Denmark (Bligaard 2014).
2.2.3. Precision Agriculture
Precision agriculture (PA) involves detailed computer record keeping of agricultural
assets and can represent a wide variety of technologies (Lindblom 2016). It generally involves
taking consistent, detailed measurements of the land and using that data to inform agricultural
management practices (Zhang 2016). A PA system is a comprehensive system with many
different technologies working together, including databases, RS, FMIS and more.
An et al. (2003) designed a GIS for PA that includes a database structure for four types of
data: weather, soil, crop, and farming practices. The attribute data are held in their own data
tables and separately related to a spatial database which stores GPS coordinates and spatial data.
This database structure represents one of five modules developed by An et al. (2003). The PA
application also covers data collection, spatial interpolation, general GIS, and decision-making
support modules. Databases and technologies used in PA can be adapted and applied to small
diverse farms, but the financial cost might be significant (Álvarez 2008; Zhang 2016). While PA

16

technology can be valuable to the agricultural industry, it is too complex a system and the cost is
too high for many smaller farms.
2.3. Spatial Databases
A spatial database allows users to record, store and manage data with locational
attributes. While often used in regional, continental, and global studies, spatial databases can be
effective at recording smaller areas such as individual farms (Milicic 2012). Spatial databases
underly many of the spatial technologies discussed earlier in this chapter (e.g., RS, GIS) and can
be used for broad scale agricultural record keeping and farm management. The end of this
section discusses some open-source solutions to farm management that rely on spatial databases.
2.3.1. Agricultural Record Keeping with Databases
Current and historic agricultural data informs decision-making at all levels of
government, many of which keep robust agricultural datasets. The Agricultural Baseline
Database (ABD) is a custom query application that is built and maintained by the United States
Department of Agriculture (USDA) (Dohlman 2022). It holds agricultural productivity data and
statistics for the entire nation for government and public use. This database is used for
agricultural data organization and recordkeeping to monitor and improve agriculture in the U.S.
As a federal database, it focuses on the yearly economics of the entire industry rather than on
individual farm management, so it is most useful for broad analyses of regional agricultural
productivity on the county and statewide scales.
Internationally, many countries around have their own databases to record agricultural
production. The Fars Comprehensive Agricultural Database (FCADB) is a similar
documentation effort to ABD but taking place in Iran (Kherad et al. 2013). Developed by the
Fars Agricultural Institute, the FCADB integrates agricultural data with climate data, soil data,

17

and survey responses. This database is used for yearly country-wide agricultural assessment and
planning rather than individual daily farm management.
Another role that databases can play in the agricultural industry is for land planning.
Álvarez et al. (2008) presented a land planning and classification approach for dairy farms in
Spain that uses Microsoft Access Database with Excel for ease of data entry. Alvarez combined
regional agricultural statistics, land cover, and population data all in the same database. The
database helps inform governmental decision makers on current farming practices as well as
where to develop new agricultural land.
2.3.2. Spatial Databases for Farm Management
Spatial databases are the foundation of GIS, and they are particularly effective at
representing features that exist over space and time (Sreekanth 2013). Agriculture represents
many features that fit those categories, such as a cow which is born, travels around grazing
throughout its life, and then dies. The spatial relationship between some of a farm’s features
(e.g., how close the cows are to the chickens) changes frequently and is extremely relevant to
everyday farming operations (Oates 2011). This type of spatial database is useful for reviewing
past data, for example, to know how many days a particular pasture has been grazed each season.
Spatial and temporal relationships can be leveraged by a GIS to produce higher quality and more
accessible data, which can help inform managerial decision making for better agricultural land
management (Norvell 2003). The starting place for getting these features integrated with a GIS is
recording their respective attributes in a spatial database (Yeung 2007; Zhang 2008).
While not specifically designed for agriculture, Motyka (2018) proposes a physical
design of a spatial database for documenting and managing landscape irrigation systems on a
college campus. Figure 4 shows the data structure that she proposed including pipe mainlines,

18

valves, sprinklers, irrigation zones, and more. Documentation of the location of irrigation lines
and valves is extremely useful for water management (e.g., to locate a broken line), especially in
areas with limited rainfall like Ventura County (Motoyka 2018; Shirley 2008). A spatial
database recording irrigation information can be applied to a farm’s overall record keeping
system.

Figure 4. ERD of database for management of an irrigation system (Image Source: Motyka
2018).
Magaya et al (2017) proposed a spatially based record-keeping prototype for farmers.
This database was created in Microsoft Access, and although it was not technically a spatial
database, the information was linked to a spatial data table prepared in ArcGIS 9.3. User
requirements were determined by semi-structured interviews and discussions with potential
users, which helped in the database development. Mayaga et al. (2017) identified three main
entities to track: Crops, Fertilizers, and Pest Management. Each of these entities was assigned
numerous attributes to describe them, such as planting dates and harvest amounts. Upon further

19

development, Mayaga et al. (2017) also included weed information and rainfall amounts. To
spatially relate these entities, they created another entity of crop fields. They include a Field_ID
in each table as a foreign key to spatially link the tables, as shown in Figure 5.

Figure 5. Entity Relationship Diagram for spatially based farm record keeping (Image Source:
Magaya 2017).
2.3.3. Open-Source Farm Management
While not widely popular, there are open-source farm management programs based in
spatial databases. Founded in 2016, FarmOS is an open-source web application that aims to
provide a standard platform for agricultural data collection and management. There is ample
documentation on their website (https://farmos.org/), which describes the data model used by the
program and eight types of records including assets (entities), logs (treatment and harvest
records), data streams (real-time sensor updates), users (personnel), and more (Stenta, 2022).

20

FarmOS is equipped to handle individual farm data as well as more regional data and is based in
a PostgreSQL database. It supports dozens of features and requires significant coding experience
to host, set up, and maintain.
Open Technology Ecosystem for Agricultural Management (OpenTEAM) is another
open-source farm management project founded in 2019. It is a relatively new project that is well
funded and growing with the goal of providing agricultural leaders with accessible, interoperable
technological tools to access agricultural knowledge and build soil health (Canning 2019). These
range from decision support tools to remote sensing tools and more, and while databases are not
explicitly mentioned on their website (https://openteam.community/), many of the technologies
they advertise (remote sensing and observation tools, agroecosystem models, interoperability)
are based in spatial databases (OpenTEAM 2022).

21

Chapter 3 Methods
The main work of this thesis involved designing, creating, and populating a spatial database for
individual record keeping of regenerative farms. The database represents dozens of entities
(physical objects on the farm), attributes (relevant information about said entities), and
relationships (how the entities are related and ways they can affect each other). This chapter
starts with an overview of the research design (Section 3.1) followed by describing how
qualitative and quantitative data were collected and prepared (Section 3.2). Section 3.3. focuses
on how to design the database, and Section 3.4. outlines the methods for creating the database
and accessing the data.
3.1. Research Overview
This research is connected to the wider community through input from local farmers.
Open-source software was used for the research to be easily replicable. This section gives an
overview of the research design of this thesis starting with a summary of the workflow and
following with some software considerations.
3.1.1. Workflow
The process of demonstrating a spatial database for regenerative farmers was separated
into three phases: conceptual database design, logical database design, and database
implementation. Figure 6 shows these phases and represents the general workflow of this thesis.

22

Figure 6. Workflow diagram showing the general steps required to create the demonstration
database (DB) for this thesis.
This research started by exploring the complex structure of regenerative agriculture using
literature review, focusing on finding approaches to keep records and store data efficiently (Phase
1). During this first phase, a conceptual database design was created. During Phase 2, the author
interviewed local farmers in the regenerative agricultural community in Ventura County,
California about their current record keeping systems, data analysis needs, and general farming
practices. The acquired information was used to update the initial logical schema before moving
to database implementation in Phase 3. Archival farm data from local farmers was cleaned and
edited to use in an example database. The spatial database was populated, and queries were
performed to demonstrate the various use cases of the spatial database for an individual
regenerative farm.
3.1.2. Software Considerations
The design of a database starts with the type of database and choice of software. A web-
based diagramming tool Lucidchart was used in the early phase of the database design for
diagraming and modeling steps for both conceptual and logical database diagrams. Since spatial
objects and their relationships are essential for agricultural operations, an object-relational
database that supports spatial data types such as SQL Server, PostGIS, or Esri Geodatabase was

23

required. Considering the accessibility to farmers, a PostgreSQL database management system
(DBMS) with a PostGIS extension was chosen for this research.
PostgreSQL V14.1, also known as Postgres, is an open-source relational database software.
Postgres has an active user community for troubleshooting and is well maintained. Postgres was
chosen because it is free, robust, reliable, and can support spatial data types and databases (with
the PostGIS extension). As an open-source software, it is easy to experiment with on any computer,
and could be implemented by a farm for no cost. The database can also be copied, shared, and
edited to meet the needs of a specific organization. With the spatial extension PostGIS, Postgres
has efficient integration with other open-source programs including QGIS which is particularly
useful for visualizing and analyzing spatial data. PostgreSQL, PostGIS extension, and pgAdmin
interface (for working with data in Postgres) can all be downloaded for free in a bundle, at which
point the physical database creation can begin.
3.2. Data Collection
Data for this thesis were collected as qualitative interview results and quantitative
archival farm records. This section discusses the methodology of the interviews and how the
resulting data were prepared to use in the database.
3.2.1. Interviews Methods
Interviews with farmers and leaders of the regenerative movement in Ventura County
were conducted to understand their farming practices and data management needs. The interview
subjects were also asked for copies of archival farm records. A set of questions were designed
prior to interview and detail notes were taken during the interview.
While there are numerous ways to organize and conduct an interview, this thesis uses an
individual semi-structured interview procedure. Semi-structured interviews involve topics that are

24

prepared in advance but use open ended questions (Fox 2009). This type of interview is useful for
collecting attitudinal information and opinions, but difficult to establish uniform responses; semi-
structured interviews require interpretation before being compared and analyzed.
In this research, regenerative farm is defined as polyculture farming under 1000 acres.
Farms and regenerative organizations were identified through online research as well as the
author’s professional connections in the regenerative farm industry. Thirteen farms and
regenerative organizations around Ventura County were contacted via email in January 2022 to
request for interview participation. These farms included: Apricot Lane Farms, Casitas Valley
Pastures, Churchill Orchard, Deardroff Family Farms, Earthtrine Farm, Farmer and the Cook
Farm, Kenter Canyon Farms, McGrath Family Farms, Ojai Center for Regenerative Agriculture,
Ojai Roots Farm, Sow a Heart Farms, Underwood Family Farms, and White Buffalo Land Trust.
Follow up phone calls were also made to organizations that did not initially respond.
Phone, video, and on-site interviews were conducted in January 2022. The interview
questions were focused on the following three topics:
a. General farming practices: e.g., what size is your farm? What do you grow?
b. Current data collection and storage methods: e.g., what information do you need to
know about each crop? Do you record location information?
c. Data and analytical needs: e.g., what type of analysis do you use? what is lacking in
current data management?
A review from the Institutional Review Board (IRB) was requested for both archival farm records
and interview questions, and an IRB exemption was officially granted by the USC Office for the
Protection of Research Subjects on December 17, 2021. The social behavioral IRB protocol,
approved exemption, and full interview script can be found in Appendices A, B, and C.

25

3.2.2. Data Preparation
The participants’ responses to the interview questions were documented, analyzed, and
used to revise the initial design of the spatial database. The interview notes helped finalize the
database’s entities and attributes to ensure the database’s relevance to farmers in Ventura
County. Individual participants’ and their feedback were kept anonymous in this thesis.
Test datasets of a farm’s records were acquired from interview participants, and prepared
and normalized to fit the structure of the finalized ERD. The datasets were sourced from farms as
spreadsheets, maps, and images of paper notes. Attribute data was reorganized into spreadsheets
to be imported into the database as .csv files, and spatial data was created as shapefiles in QGIS
to be imported to the database using a PostGIS import tool. The datasets were edited,
normalized, and referenced to location information before integrating into the spatial database.
For data not available in the existing farm records, supplemental data were created by the author
to prove the database concept. The geographic locations of the farm data used in this database
were moved to a different site within the county that has similar terrain. This was done for
privacy concerns, and to easily aggregate data from multiple farms.
3.3. Database Design
The design is an essential part of any database as it determines how and what information
the database can handle. This section discusses the initial conceptual and logical designs for a
spatial database of agricultural records and goes over the choice of data types used. The logical
design was updated in Chapter 4 to incorporate interview results.
3.3.1. Conceptual Design
A major focus of this work was to determine what types and forms of data should be
collected by the database in the first place. The author relied on both academic research and his

26

professional experience in the regenerative farm industry (i.e. working on a regenerative farm for
four years) to create a conceptual design of the database and entities to be recorded. These
entities represent a farm’s records and can be divided into five main groups classified as follows:
• The Crops class represents any plants that are being documented by the farm.
• The Livestock class represents all records pertaining to the animals on the farm.
• The Fertility class documents compost production and other fertilizer applications.
• The Infrastructure class details the roads, fences, buildings etc. throughout the farm.
• The Personnel class records employee information.
Table 1 demonstrates the initial conceptual framework and shows the general categories
of data that need to be collected and recorded in a database for regenerative farm management.
Each of these five groups – crops, livestock, fertility, infrastructure, and personnel – would be
made up of specific entities that represent physical resources on the farm. Each of the entities
(identified in the right-hand column of Table 1) would contain numerous attributes and must be
normalized for efficient data storage and retrieval.
Table 1. Conceptual framework of agricultural features in a regenerative farm database.
Groups of agricultural features Entities within group
Crops Garden, Orchards, Habitat
Livestock Cows, Chickens, Bees
Fertility Compost, Fertilizers
Infrastructure Buildings, Roads, Pastures, Irrigation
Personnel Owner, Employee, Contractor

27

As these entities needed some relevance to the overall system, the relationship classes
and cardinality between them were defined. Attribute relationships were identified, but all the
elements also inherently have some type of spatial relationships that could be used to combine
them.
A basic bubble diagram was created to show the conceptual database design (Figure 7).
This bubble diagram is the basis for creating a Entity-Relationship Diagram (ERD) for the spatial
database (Figure 8), which eventually becomes the logical schema (Figure 9).

Figure 7. Bubble diagram of major departments in diverse farms, the entities they manage, and
some of their relationships. Farm departments are shown by their color.
Figure 8 is the initial conceptual ERD that illustrates the entities, attributes, and their
relationships. For example, an entity relevant to agricultural production could be a single Cow;
this entity (i.e., Cow) may have attributes such as the herd or breed of the cow, or its birthdate.
The Cow entity may also have a relationship with an entity of Pastures where that Cow “grazes”

28

a pasture. Figure 8 is still a relatively simple idea for a spatial database of small farm
management.

Figure 8. Conceptual ERD of a spatial database for regenerative farm management. Squares
represent entities, ovals are their attributes, and diamonds show the relationships between
entities.
3.3.2. Logical Design
The entities in the major groups were further developed by listing potential attributes and
the types of data that are appropriate for describing those attributes. For example, an attribute
could specify the breed of each cow and its birth date, and a compost pile attribute may show its
weekly temperatures or its component material. The data type of each attribute was determined
based on the uses of that attribute. Attributes representing words used character types, and
attributes representing numbers or dates used numeric and temporal types.
Relationships among various data were identified such as “employees” that work on
“CompostTurns”, or “Cows” which are part of a “CowHerd”. The “CowHerd” is linked to

29

“HerdGraze” events which occurs on “Pastures”. Those “Pastures” can also overlap with
“CoopGraze” (where chickens are grazing) or “SeedSpread” where seeds have been distributed,
or even “CompostSpread” where compost fertilization has occurred. All these relationships are
defined with Primary and Foreign Keys. More details of these entities, attributes, and
relationships can be found in Figure 9.

Figure 9. Early stage of logical ERD for spatial database of farm records. Departments are
represented by different colors.
Spatial relationships are not explicitly shown in Figure 9 but exist between anything that
has or is connected to a “geometry” type (spatial) attribute. Each spatial attribute defines a
location and can be related to other locationally referenced data in the database. The more spatial
data in the database, the more spatial relationships naturally exist.
3.3.3. Data Types
The final logical schema of this spatial database used five main data types: text, numeric,
date, timestamp, and geometry. The first four are defined in the PostgreSQL documentation and

30

geometry is defined in the PostGIS documentation (PostgreSQL 2022; PostGIS 2022). The
decisions on data types to use was informed by farmer interviews and drew heavily on the online
documentation cited (PostgreSQL 2022; PostGIS 2022). The “text” data type in PostgreSQL can
represent any length string. Some other data types considered over “text” were “varchar()” and
“enum”. The “varchar()” stands for variable character which controls the length of the string
input. Unlike some other database systems, there is no difference in performance between
“varchar” and “text” in PostgreSQL. Data of the “enum” type must match a list of acceptable
strings that is created separately in the database. This can be useful for attributes such as animal
type which would always be one of a few options (cow, chicken, or bee for example). Ultimately
the “text” type was chosen over “varchar()” and “enum” data types to increase the flexibility and
limit the restrictions on data entry.
The “numeric” data type, used for most of the attributes that represent numbers, has a
variable storage size depending on the size of number being stored and can accommodate up to
131,072 digits before the decimal and up to 16,383 digits after the decimal. While numbers can
technically be displayed in strings within the "text" data type, “text” data are not recognized as
numbers and cannot be used with any mathematical operations. An alternate option for storing
numbers is the “integer” data type which is four bytes and can store whole numbers (no
decimals) between -2,147,483,648 and +2,147,483,647. The “real” data type is another option as
it is four bytes and stores numbers up to six digits (including decimals). The “real” data type is
inexact, so while appearing to function well, it cannot always be relied on for mathematical
operations. In this spatial database, the “numeric” type was used over “integer”, “real”, or other
number-based data types to keep open the flexibility to use decimals and mathematical
operations.

31

This database also holds temporal information about dates and times, which are given
their own data types to differentiate them from numbers and strings. The “date” type is four
bytes and holds a calendar date (month, day, and year). This data type represents actual dates on
the calendar and can be used to identify when things happened. Another data type for storing
dates in this database is “timestamp” which is 8 bytes and records a date along with a time of
day. This is useful for phenomenon that occur multiple times per day and for more detailed
tracking of time elapsed. Having dates recorded in the proper data type allows for temporal
queries which greatly enhance the analytical capabilities of the database.
The “geometry” data type holds locations based on a projected coordinate system and is
defined in the PostGIS extension. “Geometry” is a spatial data type, and its inclusion makes this
a spatial database. Data of the type “geometry” can represent points, lines, polygons, multipoint
and more. The data of this type is defined in a given spatial reference system and can be
combined and accessed through spatial queries. Alternatively, the “geography” data type holds
geographic location stored in geographic coordinates. Geographic coordinates account for a
spherical model of the earth and cannot be used directly with planar measurements. The
“geometry” type is used in this database instead of “geography” because a relatively small area is
being represented (a few hundred acres), and planar measurements and calculations are more
useful to farmers.
3.4. Database Input and Access
Once the logical database design was updated from farm interviews, a PostgreSQL
spatial database was created. This section outlines the database creation steps and introduces how
to input data and access that data through queries.

32

3.4.1. Data Input
The spatial database was created with the needs of regenerative farmers in mind. It was
created based on the finalized logical ERD that outlined each entities name, data type, attributes,
and primary/foreign key relationships. A PostgreSQL server was downloaded and set up in the
author’s computer, SQL queries were used to create an empty database, and the database was
ready to be populated with data.
The most basic way to input data into the database is to add values directly using SQL
commands INSERT INTO and VALUES as shown in Figure 10. This method of data entry is
time consuming for large amounts of data and is highly prone to human typing errors.

Figure 10. Image of an example SQL query for adding data into the CompostTurn table.
Figure 11 shows a portion of a CompostTurn table created as a Google spreadsheet that
mirrored the structure of the table design from the final ERD. The spreadsheet was saved as a
.csv file and imported to the PostgreSQL database (Figure 12). This process was repeated for
each table to populate the database.

33

Figure 11. Image of the CompostTurn table that was uploaded to the database.

Figure 12. Image of SQL used to copy data from a .csv file and populate it into the CompostTurn
table.
Spatial data can only be added to the database if the PostGIS extension is downloaded.
The simplest way to create spatial features is to digitize them over a map by drawing the feature
on a GIS (based on base map satellite imagery). This process gives the entities their spatial
attributes, and the rest can be entered manually with SQL or added from an existing table. Figure
13 shows a polygon shapefile displaying farm plot locations in QGIS. The shapefile was
imported into the PostgreSQL database as ‘farmplotq’ and joined to the database table
‘FarmPlot’ by the common key ‘names’ (Figure 14). This gave the entity FarmPlot a spatial
attribute. More spatial data could be created to track more specific grazing or planting areas as
needed, but these farm plots are enough to demonstrate the spatial capabilities of the database.

34

Figure 13. The spatial attributes of the FarmPlot table created as a polygon shapefile in QGIS.

Figure 14. Image of an example SQL query for joining the spatial data from “farmplotq” to an
existing table “farmplot”.
3.4.2. Querying the Database
A functioning database was created and populated with data, now it is time to interact
with and visualize that data. Queries are instructions to the computer written in Structured Query
Language (SQL) and are the simplest way to interact with a database. They allow the user to
view, create, update, filter, join, delete data, and more. Most of the queries in this thesis focus on
accessing and analyzing data once it is in the database. They are based on the SELECT statement
which can display and manipulate data but does not permanently modify the underlying data
values. The queries are organized into four main categories:
• Simple Database Queries access data from a single table.

35

• Relational Queries combine data from multiple tables.
• Temporal Queries analyze the temporal qualities of date and timestamp attributes.
• Spatial Queries combine, filter, and display data geographically using geometry
attributes.

36

Chapter 4 Results
This chapter presents the interview results from the regenerative farm practitioners in Ventura
County (Section 4.1) and describes how these interviews informed the final physical design,
creation, and implementation of the spatial database (Section 4.2). Section 4.3 showcases the
combination and visualization of data through various database queries.
4.1. Interview Results
Out of 13 regenerative farm organizations contacted, nine practitioners representing
various departments on five farms were interviewed in January 2022 (Table 2). This section
details the interview responses starting with an overview of the farms’ general farming practices,
followed by information about their current record keeping systems and data needs.
Table 2. Regenerative farms and organizations interviewed for this research.
Farm/ Organization Name Established Description/ Mission
Apricot Lane Farms

2011 Working farm integrated within an
intentionally reawakened
ecosystem.
https://www.apricotlanefarms.com/
Farmer and the Cook Farm

2001 Farm and restaurant with focus on
farmer training and consumer
education.
https://www.farmer-and-the-
cook.com/
McGrath Family Farms

1971 Fourth-generation direct market
organic farm.
http://www.mcgrathfamilyfarm.co
m/

37

Farm/ Organization Name Established Description/ Mission
Ojai Center for Regenerative Agriculture

2002 Non-profit for environmental
education.
https://www.ojaicra.org/
White Buffalo Land Trust

2018 Non-profit that practices, promotes,
and develops systems of
regenerative agriculture.
https://www.whitebuffalolandtrust.
org/

Interviews with regenerative farmers validated many concepts brought up in the literature
reviewed in Chapter 2 such as the importance of biodiversity and soil health. The farms being
interviewed ranged in size from 12 to 1000 acres and employed between 4 and 30 farmers. The
total of nine interview subjects had various roles within their farm organizations, ranging from
crop manager, to compost and fertility worker, to research lead, to farm owner, to founder, and
more. Interview responses including archival farm data were combined and anonymized for data
privacy. This section expands on the interview results by describing the farmer’s agricultural
practices, record keeping strategies, and data needs.
4.1.1. Farming Practices
As expected, every farm in this study cultivated a wide diversity of farm products. Each
of the farms grew dozens of species of annual and perennial crops. Most also grew fruit trees,
and raised animals including chickens, cows, sheep, and bees. The two things that stood out most
in these interviews were the emphasis on biodiversity and soil health on the farms. These farms
all embraced polyculture farming, and diversity was integral to their fertilization, pest, and
disease management strategies.

38

The farms overall had similar strategies for dealing with fertilization, pests, and diseases,
though some had more targeted regimes than others. These farms focused on diverse cover
cropping and successional planting to grow soil fertility and limit the impact of pests and
diseases while maintaining areas of native habitat to sustain populations of beneficial insects. All
the farms followed cover cropping and successional planting strategies and while some used very
few soil amendments or inputs, other larger and more research-focused farms regularly apply
compost, amendments, foliar leaf sprays, ground sprays, root drenches, and more. The
amendments and sprays are targeted to specific crop varieties at certain times in their growing
cycles.
All the farmers interviewed emphasized community engagement, whether through
research, education, or selling directly to local consumers. Many viewed regenerative agriculture
as a whole systems approach that values human health and community along with biodiversity
and soil health. One retired farmer thinks of regenerative farming as a form of estate planning…
setting up the land to be able to support the next generation. All the regenerative farms
interviewed had similar farming ideals and practices for encouraging biodiversity and soil health,
but the same cannot be said of their data collection and storage methods.
4.1.2. Current Record Keeping
Four main formats of data collection were used by the farmers: memory, handwritten
paper notes, electronic spreadsheets or pdf documents, and Farm Management Information
Systems (FMIS). Most farms applied a combination of these methods, with none storing all their
records in one system. Half of the farms primarily used memory and handwritten notes, while the
other half kept most records on spreadsheets and computer documents in the Google suite. The

39

farms using Google Sheets spreadsheets were in the early stages of implementing FMIS and GIS
programs.
The farms relying on worker memory and handwritten notes were generally older and
kept less detailed records. The handwritten notes were relatively simple where one piece of paper
would record the date, the general location, and the family of the crops being planted (Figure
15(a)). Similarly, Figure 15 (b) shows another handwritten note documenting harvests with the
date, the general location, and the name of the crop being harvested. The planting locations were
recorded as alphanumeric codes and, in some cases, had no physical map associated with them.
The planting areas and subplots were instead held in the memory of farm workers and owners.

a. Planting Records b. Harvest Records
Figure 15. Images of hand-written planting (a) and harvest (b) records from a regenerative farm
in Ventura County.
The farms with larger operations primarily used electronic spreadsheets (e.g., Google
sheets) to manage their farm records. The details and complexity of the spreadsheets varied but
they represented data from most farm departments including pasture, compost, garden records,

40

and more (Figure 16). All the attribute data for these departments (e.g., crop disease and pest
pressure, harvest quality, nutritional spray dates, animal grazing, compost temperatures etc.)
were recorded in the respective spreadsheet and could only be accessed by viewing that
spreadsheet.

a. Pasture Records

b. Compost Records

41

c. Garden Records
Figure 16. Portions of spreadsheets used to track pasture (a), compost (b), and garden (c) records
on a regenerative farm in Ventura County.
A majority of location information was stored as alphanumeric codes in the spreadsheets.
These codes corresponded to locations on the farm which were noted on some type of map. One
farm used a computer-drawn image of the garden bed shapes (Figure 17 (a)) and an aerial photo
of the property (Figure 17 (b)) as spatial references for the codes. Because of the diversity of the
farm operations, these regenerative farms recorded a variety of data without a uniform,
systematic storage approach. Often, each department within a farm (e.g., compost, garden) had
its own mixed system, with some records stored on paper, some in digital spreadsheets, and some
in a FMIS or GIS. A positive development is that several of the farms interviewed had recently
transitioned some records from mental and paper notes to digital spreadsheets, and some had
even started to integrate FMIS and GIS programs into their records.

42

a. Computer-drawn garden beds b. Aerial photo with labels
Figure 17. Physical hardcopies of farm locational references using (a) computer-drawn garden
beds and (b) printed aerial photo imagery.
Farms that were comfortable using spreadsheets were experimenting with more advanced
FMIS and GIS programs for their data management. These farms kept a subset of their records
on programs including Google MyMaps, CropTracker, and ArcGIS Pro. Google MyMaps is a
simple GIS program that can be used to create and display point, line, and polygon features
representing farm entities. Each entity had a URL link to an online spreadsheet with attribute
information about that entity. The computational limitations (e.g., a limit of ten layers per map),
however, prevent MyMaps from scaling up to managing an entire farm’s records. The same farm
that used MyMaps also tested Crop Tracker, an online FMIS. Crop Tracker was able to meet
some of the farmers’ needs but had a difficult user interface and did not include any animal
records. Figure 18 shows the user interface and some of the data stored in MyMaps and Crop
Tracker from the farms interviewed.

43

a. A farm’s location records on Google MyMaps.

b. A farm’s records using CropTracker
Figure 18. Examples of farm records stored in (a) Google MyMaps and (b) CropTracker.
Another farm was in an early stage of adopting a more advanced GIS, ArcGIS Pro. This
research-oriented farm had a 3
rd
party ESRI consultant and planned to use ArcGIS Pro for

44

storing and analyzing data that contributes to the wider agricultural research community. Both
farms using ArcGIS Pro and Crop Tracker had paid subscriptions to the software and collected
geospatial data through a combination of digitizing over imagery, GNSS receivers, and iPhoto
location data.
4.1.3. Farmer’s Data Needs
The interviewed regenerative farms were varied in their record keeping styles and had
different outlooks on their data needs. While the complexity of record keeping varied, all
essentially boiled down to recording a timeline of practices and observations before and after
planting. Based on the limitations of the farms’ current systems, the farms were able to record
and access this type of data in various degrees. While some farms kept minimal planting records,
just enough to pass their certifications (e.g., “Organic”, “Certified Humane”), some farms kept
more detailed planting / treatment / harvest records, including the quality and performance of
crops, the severity of pest and disease pressures, foliar spray and treatment schedules, timing of
sap tests, bloom times, harvesting and watering amounts, equipment used, agronomy records,
block observations, plant family succession records, and more.
Overall, the farmers wanted complete records of their farm to analyze their data and
inform their farming practices and decision making. While some farmers were content with their
current record keeping systems, they all acknowledged limitations that prevented them from
easily storing and effectively accessing farm data. The farms using hand-written records were
generating new planting/harvest pages daily, so the records quickly piled up and became difficult
to organize and reference. The farms using digital spreadsheets and early FMIS struggled to keep
track of the timing and history of targeted foliar sprays. Some farmers also wanted simpler data
entry and were bothered by many unnecessary fields in FMIS and spreadsheets that complicated

45

their data input and retrieval. Another common issue reported by the farmers was that record
systems were still being developed, so the records needed to be stored across multiple platforms.
These difficulties lead to poor accessibility (or nonexistence) of past records which was
exacerbated by the relatively high turnover of farm employees.
4.2. Final Database Design and Implementation
The final ERD design was created with the needs of regenerative farmers in mind. The
finalized ERD is described below, followed by a summary of the normalized farm data. Next the
final physical database is created along with metadata describing the database attributes. The full
code, queries, and data used for this database are stored in a GitHub repository and can be found
at https://github.com/PhilipTHess/FarmDB.git.
4.2.1. Final ERD
The final ERD described below is an updated version of the ERD from Section 3.3.2. It
was revised based on the interview results to better meet farmers’ needs. The farmers wanted to
track pest and disease pressures, general observations, harvest quantities and qualities, bloom
times, and applications of fertility materials. They also expressed a desire for fewer and less
complicated data tables and fields. The biggest changes made to the ERD involved standardizing
data types, narrowing the database’s focus to only agricultural records (rather than including
personnel and infrastructure records), and condensing the data from each department into fewer
tables with more useful attributes. The final ERD showing the logical schema of the database is
illustrated in Figure 19.

46

Figure 19. The final ERD showing the logical schema of a spatial database for a regenerative
farm. General type of records are grouped by color.
As a spatial database, this database was organized around land area. The FarmPlot table
holds polygons representing the farm’s land areas, and everything recorded on the farm can
relate back to these spatial entities. The Observations table records the date and location of any
miscellaneous note or observation that warrants recording. PackGraze, PlantingEvent, and

47

FertilitySpread are the three other tables that relate to the FarmPlot table, and each of them
connects to a group of farm records about animals, plants, and fertility respectively.
The PackGraze table connects the animal records to other farm data by describing which
FarmPlot the animals have been in. An AnimalPack can be made up of many individual Animals
(which is the smallest unit of record for animals, and it may for example represent one cow, or a
shipment of 50 chickens). The “type” attribute of the Animals table tells the general species of
the animal (such as chicken, cow, sheep, or bee). An AnimalPack can either graze (through the
PackGraze table) or be fed supplemental feed (through the PackFeed table). Animals can be
given medicine or specialized treatment (through the AnimalMedicine table), and they can be
harvested or processed (including milk, egg, and honey collection or meat processing) through
the AnimalHarvest table. As far as the relationships present in the animal portion of the database,
one FarmPlot can contain zero to many PackGraze events. One AnimalPack can have one to
many Animals in it and do zero to many PackGraze and PackFeed events. Animals can have zero
to many AnimalMedicine treatment and AnimalHarvest events. The animal data have a similar
relationship to the farm plots as the plant data.
The PlantingEvent table relates orchard, garden, pasture, habitat, and all other plant
records to the FarmPlot table. Planting events involve a Species (the base unit of plant records)
and describe when and where the planting occurred, how many individuals were planted, and
when they were removed or died. Species can be combined in a mixture through the SpeciesMix
table, and that mixture can be created and named by the SeedMix table which can be planted
directly in a PlantingEvent. These two tables are necessary to avoid creating ambiguity in the
relational database stemming from a many-to-many relationship. The “type” attribute classifies
the purpose of the PlantingEvent (either for the garden, orchards, pasture, or habitat).

48

Information about the amount and quality of harvests, as well as pest and disease notes for each
planting event can be stored in the PlantHarvest and PlantPressure tables, respectively. One
FarmPlot can contain zero to many PlantingEvent records. One Species can be represented in
zero to many PlantingEvent records, and one PlantingEvent can relate to zero or many
PlantHarvest and PlantPressure records. One Species can also be in zero or many SpeciesMix;
one SeedMix can be in zero to many PlantingEvents and can relate to one or many SpeciesMix
records. Besides plants and animals, another main source of data generated by farms is related to
fertility treatments that counter both nutritional deficiencies and pest and disease pressures.
Fertility data relates to the rest of the farm through the FertilitySpread table which
describes applications of all kinds of materials (compost, pesticides, amendments etc.) referenced
to which FarmPlot on which it is spread. Basic information about these materials is stored in the
FertilityMaterial table including the name, source, type, and arrival date of the material.
Similarly to Species and SeedMix in a PlantingEvent, FertilityMaterial can be spread
individually or be combined in a mixture and spread together. Both the FertilityMix and
MaterialMix tables are used to create a mixture and are required by the database to deal with an
ambiguous many-to-many relationship. The FertilityMix table holds the materials
(FertilityMaterial) in a mixture while the MaterialMix table creates and names the mixture so it
can be spread (FertilitySpread).
There are two more tables of fertility records that store detailed information and notes
about compost piles. The CompostTurn table records the building, turning, and watering of a pile
and the CompostTemps table records the pile’s temperature changes. One FarmPlot can have
zero or many FertilitySpread events and one FertilityMaterial can be spread zero or many times.
One FertilityMaterial can correspond to zero or many CompostTemps or CompostTurn records

49

and can also be in zero or many FertilityMix records. One MaterialMix name corresponds to one
or many FertilityMix records and can relate to zero or many FertilitySpread records.
4.2.2. Farm Data
The raw data obtained from the farms (such as those in Figure 15, 16, 17, and 18) were
organized, converted, supplemented, and normalized to the structure of the desired database
tables. This demonstration database is designed to meet the record keeping needs of a diverse
regenerative farm. The database is based on aggregated existing farm records and describes three
years of records for a roughly 350-acre-sized farm. The farm consisted of ten animal pastures,
five garden beds, five orchard blocks, five habitat zones, and five animal holding areas. There
were two cow herds, two chicken coops, and six beehives which grazed the various farm plots
throughout the years. The cow herds and chicken coops were made up of numerous animals and
were regularly fed supplemental feed. Most animals were given some and possibly recurring
medical treatments and were harvested either for meat or for by products (such as eggs, honey, or
milk). The farm plots were planted with any one of the plant species, including multiple species
at once. Some plantings had disease and pest notes, and some were harvested. Twelve compost
piles as well as other pesticides and fertilizers were among the fertility materials spread on farm
plots throughout the three years. These fertility materials were applied singularly and in
combination, and each of the compost piles had regular turning and temperature notes.
The test data described above was aggregated from multiple farms, edited, and
supplemented. It represents a demonstration database that could suit any regenerative farm’s
needs, it would just need to be populated with that farm’s own data. Once interpreted and
organized, these farm records were added to the database for easy retrieval and analysis.

50

4.2.3. Physical Implementation and Metadata
The database was coded using SQL based on the ERD outlined in 4.2.1. CREATE was
the main function used to make both the database itself, and all the tables in the database. For
every table created each of its attributes were also named and assigned data types. Primary keys,
and foreign key constraints were added to the tables to enforce their relationship integrity. These
constraints ensure that the data entered will be comparable throughout the database, and they
define the connections between tables. Figure 20 shows the SQL scripts for the database
creation, the addition of the PostGIS extension, and the creation of database tables. A full text
version of the SQL creation scripts can be found in Appendix D.

Figure 20. A Screenshot of SQL used in pgAdmin 4 interface to create a farm database, add the
PostGIS extension, and create database tables.

51

A database is only useful if people understand what data it is meant to hold. For this
reason, as soon as a table was added into the database, its metadata was documented to describe
what that table and its attributes represented. For this database, the metadata was entered as a
Microsoft Excel spreadsheet, saved as a .csv text file, and included as a table in the database
itself for easy reference. This metadata lists every table in the database, describes its purpose,
and notes every attribute including a description of what information should be stored in that
field as well as its data type. Relationships were described in the metadata so that it is clear how
to join tables and when it is appropriate to do so. Figure 21 shows an example of these metadata
spreadsheets. The full metadata of attribute descriptions can be found in Appendix E.

Figure 21. A spreadsheet of metadata describing the database tables, attributes, and relationships.

52

4.3. Data Queries and Visualization
This section focuses on retrieving the farm data from the database through four types of
SQL queries: simple, relational, temporal, and spatial queries. The syntax of all queries in this
section were demonstrated in the top half of each associated figure under “query editor”; the text
format of these queries can be found in Appendix F.
4.3.1. Simple Database Queries
Figure 22 shows an example SQL query that accesses the data stored in one single table
‘Animals’. An asterisk (*) in the SELECT statement selects all the columns in the table as the
output. The resulting table in the lower half of Figure 22 displays this output of all columns in
the Animals table. This simple query (Query 1) can be applied to any table for retrieving all the
data stored in the table effectively.

Figure 22. Simple query (Query 1) with a portion of its data output. Query 1 retrieves all records
from the Animals table.
If a farmer is interested in only certain records in the Animal table, the SQL query can
filter the data by specifying the columns desired within a table. Figure 23 shows a query (Query
2) to only display the type, name, and breed of the Animals table.

53

Figure 23. Simple query (Query 2) with a portion of its data output. Query 2 retrieves certain
columns from the Animals table.
To further narrow down a single data table, Figure 24 shows a SQL query (Query 3) that
displays certain records from animals that are cows (therefore excluding all chicken and bee
records) by using the WHERE clause (i.e., WHERE an.type = ‘cow’).

54

Figure 24. Simple query (Query 3) with a portion of its data output. Query 3 retrieves specific
columns from the Animals table that represent cows.
The examples presented in this subsection are relatively simple but can be much more
complex if needed. Other logical operators (e.g., AND, OR) and aggregate functions (e.g.,
COUNT, SUM) can further customize the data query output, and multiple functions and tables
can be combined into a single query.
4.3.2. Relational Queries
Relational queries utilize the JOIN function to retrieve data records from different tables
in the database. Such queries increase the efficiency of data access. For example, if a farmer
wanted to know what treatments they have given to each of their cows in the AnimalMedicine
table, but there was no animal type information stored in the AnimalMedicine table, a solution is
to join the Animals and AnimalMedicine tables. Figure 25 shows an example SQL query (Query
4) that finds all the columns from the Animals table that are cows, then matches the common key

55

“anID” (which is an ID number for each animal) between the animal table Animals and the
treatment table AnimalMedicine. The query temporarily combines and displays only the relevant
data. In this case, the query shows what treatments were given to cows, what those cows’ names
were, and their breed.

Figure 25. Relational query (Query 4) with a portion of its data output. Query 4 retrieves name,
breed, and medical treatments of all cows.
4.3.3. Temporal Queries
Temporal queries are used to connect and track data through time. In this database, they
are possible because of the “date” and “timestamp” data types. As mentioned in Section 3.3.3,
date and timestamp represent actual dates on the calendar. The database can recognize the
relationships between dates or timestamps, such as if one date is before or after another, how
long between two dates, and more. Temporal queries can be particularly effective at filtering
through data once there are years of accumulated records. Figure 26 shows an example SQL

56

query (Query 5) that focused on a specific time period of records. In this example, the query
retrieved the varieties, common names, and planting dates of all garden crops that were planted
in the first two months of 2018 (i.e., BETWEEN ‘2018-01-01’ AND ‘2018-03-01’).

Figure 26. Temporal query (Query 5) with its data output. Query 5 retrieves variety, common
name, and plant date of garden crops planted in the first 2 months of 2018.
Another use of temporal queries is to calculate the time between specific event dates.
Figure 27 shows a query (Query 6) which calculates and displays the age of each cow (i.e., the
time between the animal’s birth and the queried date).

57

Figure 27. Temporal query (Query 6) with resulting data output. Query 6 finds the age in weeks
of all cows on farm (on Nov. 15, 2019).
4.3.4. Spatial Queries
Spatial queries use shared geometry between spatial entities for data retrieval. They are
possible because of the “geometry” data type enabled by the PostGIS extension. Figure 28 shows
a SQL query (Query 7) of a spatial entity “FarmPlot”, the resulting table and the map view
(“geometry viewer”) of the data locations (a and b, respectively).

58

(a) (b)
Figure 28. Spatial query (Query 7) with a portion of its data output (a) and map view (b). Query
7 shows name, type, and location of all farm plots.
Similarly to temporal queries, spatial queries can also apply mathematical functions.
Figure 29 shows a spatial query (Query 8) that retrieves the area and perimeter of each farm plot
while converting the units of area to acres. Spatial queries can be combined with relational and
temporal queries. Figure 30 shows a query (Query 9) that finds the location and pack name of all
the animal packs currently on the farm (for a given date- April 16, 2019). This could be useful to
fertility workers collecting manure, the livestock team planning grazing routes, the pasture
manager deciding on seeding time and more.

59

Figure 29. Spatial query (Query 8) with a portion of its geometry viewer output. Query 8 finds
the area (in acres) and perimeter (in meters) of each farm plot.

60

Figure 30. Spatial query (Query 9) with a portion of its geometry viewer output. Query 9 finds
current location of animal packs (April 16, 2019).
The example SQL queries above demonstrate the retrieval and analysis of data between
tables of one department, but spatial queries can also combine data from multiple departments at
once (i.e., animals, plants, and fertility). The final example, Figure 31 shows a more complex
spatiotemporal query(Query 10) that accesses data from every farm department and displays the
results together. This query provides a summary of the farm in 2018 including the name and
location of each FarmPlot, the total number of days it has been grazed by cows and by chickens,
the amount of compost spread per acre, and the number of times planted that year.

61

Figure 31. Spatial query Query 10) with a portion of its geometry viewer output. Query 10
displays a summary of each farm plot in 2018 including the name and location of the plot, the
total number of days it has been grazed by cows and by chickens, the amount of compost spread
per acre, and the number of times planted that year.

62

Chapter 5 Conclusion and Discussion
The objective of this thesis is to create a spatial database for agricultural record keeping designed
to effectively utilize the spatial and temporal nature of agricultural data to support decision-
making on a regenerative farm. The database accomplishes these goals but there is room for
improvement. This final chapter presents some conclusions about the creation of this spatial
database and its effectiveness for regenerative farmers (Section 5.1). It then identifies the
database’s limitations (Section 5.2) and concludes by discussing future directions (Section 5.3)
that would make the database more valuable to farmers.
5.1. Conclusions
Farms are an integral part of society and have a long history of management and record
keeping practices. Improvements in recork keeping, from spreadsheets to computer databases to
GIS management systems are particularly worthwhile for regenerative farms which have many
variables to keep track of in their diverse systems. The layered interaction between entities
makes it more challenging to set up a data management system for a regenerative farm than for
an industrial monoculture operation.
Spatial databases are an effective way for farmers to store, access, and analyze their data
but face barriers to adoption based on the technical expertise required to maintain the database.
The spatial database created for this thesis stores spatial, temporal, and other attribute data
representing entities and relationships relevant for record keeping and management of a
regenerative farm. All this data can be combined, analyzed, and viewed using SQL queries.
Accessing all a farm’s data in one place was a common goal for farmers when asked about their
data needs, and this database accomplished that goal. Though this research was developed for

63

regenerative farms in Ventura County, the database and farming principles applied can be useful
to farms anywhere in the world. This section elaborates on the conclusions of this research in
terms of the effectiveness of the database, and its overall value to regenerative farmers.
5.1.1. Effectiveness of Database
Recent data from one table may be easy to access with a farm’s previous record keeping
methods, but querying a spatial database is more effective when combining data from multiple
tables, departments, or time periods. Most spatiotemporal queries (such as the final query in
Chapter 4) represent information that would be near impossible to reliably calculate without a
spatial database. It would involve sorting through, calculating, and summarizing values from
thousands of records and across various tables. Even if it were possible to calculate that
information from previous record keeping methods, it would be prohibitively time consuming to
replicate those calculations for all farm plots, and the process would have to restart to account for
a different time period than the year 2018.
Alternatively, queries to this database are easily saved, copied, and edited, so they can be
useful to a farm for a long time. Even a farmer who is not comfortable writing queries from
scratch could make small tweaks to an existing query for newly customized results. As an
example, any of the temporal queries from Chapter 4 can be changed to find information from a
different time period simply by changing the dates that appear in the query text itself. In this
way, one query can be edited and re-run to regularly provide valuable information to a farm
throughout the years.
5.1.2. Overall Value
The most direct value of these systems comes from their ability to inform decision
making. They allow farmers to answer questions with data. Even when storing limited amounts

64

of records, a spatial database can be more effective than some current storage methods. Still, the
more data that farms put into the database, the more value they can ultimately get out of it. A
complete spatial database is particularly useful for farm research projects because of its ability to
store and organize large amounts of historical records. These records can represent multiple farm
departments and span numerous years. This database organization could also help facilitate the
record retrieval required for certifications.
Ultimately, value comes from the ability to access, combine, and filter data in ways that
open the door to new analyses and inform decision making. Queries do not have to be long and
complex to be valuable for farmers. The following describes a few queries (drawing on those
detailed in Chapter Four) and what specific farming decisions they could help inform:
- Finding the perimeter of a planned grazing area could inform how much fencing
needs to be set up in the field.
- Grazing, seeding, and compost history could inform many farm decisions such as the
timing and amounts of future animal grazing, and compost or seed spreading.
- Finding the current location of all animals can inform where to start harvesting or
applying pesticides or compost (to make sure animals do not get in the way of the
application or harvest).
These are only a few examples, but there are endless possible farm management insights that
could come from regular use of a spatial database. Getting the most value out of the database
would require some farmers to collect more frequent and detailed data, which is a factor in the
system’s overall value.
While many improvements in data access and analysis come from using a spatial
database, the overall value that these systems bring is more nuanced and varies depending on the

65

farm involved. Any kind of organizational system is only useful if it is used and maintained
regularly. Those farms which employ this type of spatial database should be willing to upkeep
the system, including inputting new data as it is generated. While there are many levels of
database use that can be effective, forcing the use of a database will not bring positive results,
and the database will only be valuable if farm workers and managers engage with it.
5.2. Limitations
This database has some limitations that hold back its effectiveness for farmers. The
following section discusses some of this database’s limitations including difficult data entry and
output, some computational inefficiencies, and the fact that it is not currently optimized for a
specific farm.
The main methods for data entry in this database involve filing out spreadsheets,
digitizing satellite imagery, importing .csv and shapefiles, and using SQL to add them to existing
tables, all of which can be tedious and are prone to error. These methods of data entry require
some technical knowledge and involve working directly with the database software. Query
results are the main form of data output from this database. While they can be very powerful,
queries also require a certain level of technical proficiency to create, and those constructing the
queries must be familiar with the database structure and relationships. This represents a steep
learning curve for farmers using the database before they can effectively access and filter their
data. The visual output is less than ideal because the graphical interface of pgAdmin is designed
to build and maintain a database, not for complex data visualization or mapping. Along with the
difficulties involved in data entry and output, this database also has a few computational
inefficiencies that hinder its scalability.

66

This database was designed to be flexible and to fit many farmer’s needs, but some of
that flexibility comes at the cost of computational efficiency. One source of inefficiency comes
from unnecessary fields. The notes fields were included in many tables so that it is easier for a
farm to start using this database, essentially giving them an open column in most tables to record
any extra information not accounted for in the table. Some of the tables have an alphanumeric
code connecting them spatially as well as a geometry field. The alphanumeric code allows
farmers to keep their data spatially referenced without needing to create a new polygon for every
record. It is simple to use but less functional than including actual location in “geometry” data
type. These gains in flexibility and user friendliness limit the computational efficiency of the
database. Adding indexes (including spatial indexes) is an extra programing step but would
improve the efficiency of relational queries. From a surplus of attribute fields to a lack of
indexes, these processing inefficiencies are manageable while the database is small but will slow
the system down as more data is added and records accumulate over time. The limitations
discussed up to this point are all a reflection of the database not being designed and optimized
for one specific farm.
The decisions behind its design and the data stored in the database were aggregated from
multiple sources, thus this database represents only a theoretical farming operation. The 30-
minute semi-structured interviews were effective but did not include the level of access that
would be required to create a farm-specific custom database. Customization to that extent would
require regular stakeholder input about the data volume expected, specific data needs, and more.
Instead, the database was designed and populated as a model and demonstration of a spatial
database for agricultural recordkeeping. The database was designed to be usable by any
operation, but was not optimized to any specific one, which explains some of the limitations

67

described earlier in this section. Addressing these limitations would improve the effectiveness of
the current database. There are also several future directions and additions to the database that
could provide new functionality and make the database more practical and integral to farm
management.
5.3. Future Directions
Beyond the efficiency and optimization improvements discussed in the previous section,
this database could be built on to expand its capabilities, improve its accessibility, and increase
its overall value to farmers. While not within the scope of this paper, there are many directions
that this database could grow to improve its usefulness to farmers. Some examples of these
future directions include incorporating more sources of spatial data (such as GNSS and RS data),
developing an application for easier user interface, and integrating the database with a certifying
body to easily judge certification compliance.
Spatial queries connect data across all departments, even while utilizing just one table
(FarmPlot) with spatial data. The more spatial data being held in the database, the more
advanced the spatial queries can get. GNSS data is locational data from a satellite gathered at a
particular receiver’s location. GNSS receivers could be carried in the field to easily mark
locations of animal fencing, pest problems, and more. RS data can come directly from sensors on
satellites, aircraft, or drones. RS data can include multispectral imagery, lidar imagery, and more,
all of which can give valuable insights into the land. Multispectral imagery can be used to
analyze plant health, and lidar can create highly accurate elevation data. Both GNSS and RS data
can be georeferenced and combined with other farm data in queries and visualizations.
While this thesis focuses on the Postgres, PostGIS, and pgAdmin interfaces, a spatial
database can also be recognized by separate GIS programs (such as QGIS) for more advanced

68

mapping outputs. These GIS programs would help display a more visually appealing graphical
overlay of the data compared to the pgAdmin Geometry Viewer. A custom-built application
could further address the database’s data input and output limitations. This application should be
designed with a simple user interface to improve farmers’ interactions with their data. To
maintain its functionality to farmers, the app should support complex, customizable queries. A
mobile version of the app could allow data access and entry in the field, making it much more
practical for daily use. Frequent use of the database (through the application) increases the
amount of data coming into the system, which improves the database’s analytical potential.
A spatial database like the one outlined in this thesis could document information needed
for farm certifications. The certifying body could use preset queries to assess certification
compliance, and such queries could be readily edited to reflect changing standards over time.
Many regenerative farms focus on selling locally and directly to consumers. Including sales and
customer records in the database would allow farmers to track their crops’ life from planting to
consumption and begin to quantify how local their food distribution is. This type of
quantification could also help certifiers verify which farms are selling to local and direct markets
(which could be a requirement for certification).
The database could also incorporate a higher level of organization where all the tables
have a specific FarmID code. This would allow multiple farms to keep their data separated, but
on the same database system. With the proper permission restrictions to preserve privacy, data
could be aggregated across farms to assess more regional impacts of farming and food
distribution. This type of system could have serious implications for certifiers, researchers, city
planners, and more.

69

Given agriculture’s widespread use and value to humans, improvements in agricultural
management have the potential to impact everyone in the world. A spatial database can aid in
farm management but is only useful to farmers if they use it. The database and organizational
system proposed in this thesis would only be successful in the field with intentional development
and the continuing support of both farm management and workers.

70

References
Aicardi I., Angeli S., Milazzo R., Lingua A.M., Musci M.A. 2020. “A Python Customization of
Metashape for Quasi Real-Time Photogrammetry in Precision Agriculture Application”.
R3 in Geomatics: Research, Results and Review. R3GEO 2019. Communications in
Computer and Information Science, vol 1246. Springer, Cham.
https://link.springer.com/chapter/10.1007/978-3-030-62800-0_18.

Altieri, Miguel A., Clara I. Nicholls, and Rene Montalba. 2017. "Technological approaches to
sustainable agriculture at a crossroads: an agroecological perspective." Sustainability 9,
no. 3: 349.

Álvarez López, C.J, J.A Riveiro Valiño, and M.F. Marey Pérez. 2008. “Typology, Classification
and Characterization of Farms for Agricultural Production Planning.” Spanish journal of
agricultural research: SJAR 6, no. 1: 125–136.

An, Kai, Gao-di Xie, Yun-fa Leng, and Yu Xiao. 2003. “Design of Farmland GIS for Precision
Agriculture.” Chinese geographical science 13, no. 1: 20–24.

Apolo-Apolo, Orly Enrique, Manuel Pérez-Ruiz, Jorge Martínez-Guanter, and João Valente.
2020. “A Cloud-Based Environment for Generating Yield Estimation Maps From Apple
Orchards Using UAV Imagery and a Deep Learning Technique.” Frontiers in plant
science 11: 1086–1086.

Bailey, Steve. 2011. “Measuring the Impact of Records Management.” Records management
journal (London, England) 21, no. 1: 46–68.

Berglund, Björn E, Junko Kitagawa, Per Lagerås, Koji Nakamura, Naoko Sasaki, and Yoshinori
Yasuda. 2014. “Traditional Farming Landscapes for Sustainable Living in Scandinavia
and Japan: Global Revival Through the Satoyama Initiative.” Ambio 43, no. 5: 559–578.

Bhakta, Ishita, Santanu Phadikar, and Koushik Majumder. 2019. “State‐of‐the‐art Technologies
in Precision Agriculture: a Systematic Review.” Journal of the science of food and
agriculture 99, no. 11: 4878–4888.

Bohon, Robert Kevin. 2014. “Comparing Landsat7 ETM+ and NAIP Imagery for
Precision Agriculture Application in Small Scale Farming: A Case Study in the South
Eastern Part of Pittsylvania County, VA”. ProQuest Dissertations Publishing.

Bligaard, Jens. 2014. "Mark online, a full scale GIS-based Danish farm management information
system." International Journal on Food System Dynamics 5, no. 4: 190-195.

Brownly, Julia. 2018. “United States: Brownley Votes to Invest in Ventura County Agriculture.”
MENA Report.

Canning, Kathie. 2019. “Simplify the Sustainability Message.” Dairy foods 120, no. 9: 10.

71

Claiborne, David. 1991. “Still in Their Infancy, Spreadsheets Hold Promise; Spreadsheets Take
Advantage of Windows’ GUI, DDE and Memory Management Abilities.” PC week (U.S.
ed.) 8, no. 20: S22.

Crews, Timothy E, Wim Carton, and Lennart Olsson. 2018. “Is the Future of Agriculture
Perennial? Imperatives and Opportunities to Reinvent Agriculture by Shifting from
Annual Monocultures to Perennial Polycultures.” Global Sustainability 1: 1–18.

Croswell, P.L. 2019. “The GIS Management Handbook (Second Edition)”. Des Plaines, IL:
Kessey Dewitt Publications in association with URISA.

Dohlman, Erik, and James Hansen. 2022. Agricultural Baseline Database. Washington, D.C:
Economic Research Service, U.S. Dept. of Agriculture. N.d.

Fitzgerald-Moore, P., and B. J. Parai. 1996. "The green revolution." University of Calgary: 1-17.

Foley, J. A. et al. 2005. “Global Consequences of Land Use”. Science (American Association for
the Advancement of Science). [Online] 309 (5734), 570–574.

Fox, Nick. 2009. "Using interviews in a research project." The NIHR RDS for the East
Midlands/Yorkshire & the Humber 26.

Flynn, Ernest Scott. 2006. "Using NDVI as a Pasture Management Tool". University
of Kentucky Master's Theses. 412. https://uknowledge.uky.edu/gradschool_theses/412.

Gibson, R. H, S. Pearce E, R. J Morris, W. O. C Symondson, and J Memmott. 2007.
“Plant Diversity and Land Use Under Organic and Conventional Agriculture: A Whole-
Farm Approach.” The Journal of applied ecology 44, no. 4: 792–803.

Giller, Ken E, Renske Hijbeek, Jens A Andersson, and James Sumberg. 2021. “Regenerative
Agriculture: An Agronomic Perspective.” Outlook on agriculture 50, no. 1: 13–25.

Giua, C., Materia, V.C. and Camanzi, L. 2021. "Management information system adoption at
the farm level: evidence from the literature", British Food Journal, Vol. 123 No. 3,
pp. 884-909. https://doi-org.libproxy2.usc.edu/10.1108/BFJ-05-2020-0420.

Grisham, Elisabeth, and Jeffrey M. Gillespie. 2007. “Record-keeping technology adoption in the
Louisiana dairy industry”, Southern Agricultural Economics Association Annual Meeting.
No. 1367-2016-108407.

Hammond, John C. 1942. "Cow manure as a source of certain vitamins for growing
chickens." Poultry Science 21, no. 6: 554-559.

Harfoot, C. G. 1981. "Anatomy, physiology and microbiology of the ruminant digestive
tract." Lipid metabolism in ruminant animals: 1-19.

72

Inwood, Sarah E. Eichler, and Virginia H. Dale. 2019. "State of apps targeting management for
sustainability of agricultural landscapes. A review." Agronomy for sustainable
development 39, no. 1: 8.

Joly, Nathalie. 2011. “Shaping Records on the Farm: Agricultural Record Keeping in France
from the Nineteenth Century to the Liberation.” Agricultural history review 59, no. 1:
61–80.

Jones, Joshua. 2019. "GIS project management approach for implementation of GIS for planning
organisations." Research Paper Presented to the Department of Humanities and Social
Sciences in Candidacy for the Degree of Master of Science (2019).

Kammer, Anthony. 2012. “Cornography: Perverse Incentives and the United States Corn
Subsidy.” Journal of food law & policy 8, no. 1: 1–65.

Kherad, Mehrzad, Hossein Ahani, Mohammad Reza Kousari, Arman Beyraghdar, Kashkooli,
and Mohammad Amin Karampour. 2013. “Evaluation of Education and Water Resource
Types on Some Wheat Land Features, Using Fars Comprehensive Agricultural Database
(case Study; Pasargad, Iran).” Environment, development and sustainability 15, no. 4:
1129–1142.

Kok, Robert, and Laurent Gauthier. 1986. "Development of a prototype farm information
management system." Computers and Electronics in Agriculture 1, no. 2: 125-141.

LaCanne, Claire E, and Jonathan G Lundgren. 2018. “Regenerative Agriculture: Merging
Farming and Natural Resource Conservation Profitably.” PeerJ (San Francisco, CA), no.
2: e4428–e4428.

Lehman, R. Michael, Cynthia A Cambardella, Diane E Stott, Veronica Acosta-Martinez, Daniel
K Manter, Jeffrey S Buyer, Jude E Maul, et al. 2015. “Understanding and Enhancing Soil
Biological Health: The Solution for Reversing Soil Degradation.”Sustainability (Basel,
Switzerland) 7, no. 1: 988–1027.

Lindblom, Jessica, Christina Lundström, Magnus Ljung, and Anders Jonsson. 2016. “Promoting
Sustainable Intensification in Precision Agriculture: Review of Decision Support Systems
Development and Strategies.” Precision agriculture 18, no. 3: 309–331.

Magaya, A., R. Maruziva, S. Togarepi, and L. T. Buka. 2017. "A spatially based field specific
crop recordkeeping system prototype for farmers." Ethiopian Journal of Environmental
Studies and Management 10, no. 3: 351-360.

McCluskey, Jill, and George Goldman. 1995.“Agriculture in Ventura County: Its Impact on the
County Economy” Department of Agricultural and Resource Economics University of
California Berkeley, No. 783: 1-48.

73

Milicic, Vesna, and Andrej Udovc. 2012. “Spatial Data Utilization of the Agricultural Sector for
the Purposes of Agricultural Land Use Change in the Case of a Selected Nature
Protection Area in Slovenia.” Geodetski vestnik 56, no. 1: 83–104.

Motyka, Alexandra. 2018. "Applying GIS to Landscape Irrigation Systems: A Case Study of the
Music Academy of the West Campus in Montecito, CA." ProQuest Dissertation
Publishing. PhD diss., University of Southern California.

Moula, Nassim, Marie-Louise Scippo, Caroline Douny, Guy Degand, Edwin Dawans, Jean-
François Cabaraux, Jean-Luc Hornick et al. 2018. "Performances of local poultry breed
fed black soldier fly larvae reared on horse manure." Animal Nutrition 4, no. 1: 73-78.

Nishiguchi, Osamu, and Noriko Yamagata. 2009. "Agricultural information management system
using GIS technology." Hitachi Review 58, no. 6: 265.

Norvell, Jonathan. 2003. "Enhancing Farm Management and Record Keeping with GIS." In
ESRI International Users Conference.

Oates, Lawrence G, Daniel J Undersander, Claudio Gratton, Michael M Bell, and Randall D
Jackson. 2011. “Management‐Intensive Rotational Grazing Enhances Forage Production
and Quality of Subhumid Cool‐Season Pastures.” Crop science 51, no. 2: 892–901.

OpenTEAM. 2022. “Welcome to OpenTEAM.” March. https://openteam.community/.

Palisson, Aurore, Aurélie Courcoul, and Benoit Durand. 2017. “Analysis of the Spatial
Organization of Pastures as a Contact Network, Implications for Potential Disease Spread
and Biosecurity in Livestock, France, 2010.” PloS one 12, no. 1: e0169881–e0169881.

Paredes, Daniel, Jay A Rosenheim, Rebecca Chaplin‐Kramer, Silvia Winter, Daniel S Karp, and
Akira Mori. 2020. “Landscape Simplification Increases Vineyard Pest Outbreaks and
Insecticide Use.” Ecology letters 24, no. 1: 73–83.

Pimentel, David, Paul Hepperly, James Hanson, David Douds, and Rita Seidel.
2005. “Environmental, Energetic, and Economic Comparisons of Organic and
Conventional Farming Systems.” Bioscience 55, no. 7: 573–582.

Pingali, Prabhu L. 2012. “Green Revolution: Impacts, Limits, and the Path Ahead.” Proceedings
of the National Academy of Sciences - PNAS 109, no. 31: 12302–12308.

PostGIS. 2022. “Chapter 4. Data Management.” PostGIS Documentation. March.
https://postgis.net/docs/using_postgis_dbmanagement.html.

PostgreSQL. 2022. “Chapter 8. Data Types.” PostgreSQL 14 Documentation. March.
https://www.postgresql.org/docs/current/datatype.html.

74

Rane, Dipesh, Nabil Ahmed, Daanish Sarguru, and Shabina Sayed. 2017. “Sales Performance
Management System.” International journal of computer applications 162, no. 11: 25–
30.

Rigaux, Philippe., Michel O. Scholl, and Agnè s. Voisard. 2002. “Spatial Databases
with Application to GIS” 2nd ed. San Francisco: Morgan Kaufmann Publishers.

Rhodes, Christopher J. 2017. “The Imperative for Regenerative Agriculture.” Science progress
(1916) 100, no. 1: 80–129.

Salatin, Joel. 2018. “Preparing For Spring at Polyface Farm: Spring Starts Early on the Salatin
Homestead, Where Winter Is Packed with Preparations for Next Season’s New
Growth.” The Mother earth news, no. 286: 83+.

Saleem, Muhammad, Jie Hu, and Alexandre Jousset. 2019. “More Than the Sum of Its
Parts: Microbiome Biodiversity as a Driver of Plant Growth and Soil Health.” Annual
review of ecology, evolution, and systematics 50, no. 1: 145–168.

Savory, Allan. 1983. "The Savory grazing method or holistic resource
management." Rangelands Archives 5, no. 4: 155-159.

Savory, Allan. 2013. "Response to request for information on the science and methodology
underpinning Holistic Management and holistic planned grazing." Savory
Institute. http://www. savoryinstitute.com.

Shirley, Ralph. 2008.“Ventura County’s Changing Agriculture.” Resource (Saint Joseph,
Mich.) 15, no. 1: 5–11.

Smith. Ron. 2019. “Microsoft Offers AI for Agriculture.” Corn and soybean digest.

Sreekanth, P.D, S.K Soam, K.V Kumar, and N.H Rao. 2013. “Spatial Decision Support System
for Managing Agricultural Experimental Farms.” Current science (Bangalore) 105, no.
11: 1588–1592.

Stenta, Michael. 2022. “farmOS.” farmOS. March. https://farmos.org/.

Tummers, Joep, Ayalew Kassahun, and Bedir Tekinerdogan. 2019. "Obstacles and features
of Farm Management Information Systems: A systematic literature review." Computers
and electronics in agriculture 157: 189-204.

Van der Aalst, Wil. 2018. “Spreadsheets for Business Process Management.” Business process
management journal 24, no. 1: 105–127.

Weiss, Marie, Frédéric Jacob, and G. Duveiller. 2020. "Remote sensing for
agricultural applications: A meta-review." Remote Sensing of Environment 236: 111402.

75

White, Courtney. 2020.“Why Regenerative Agriculture?” The American journal of
economics and sociology 79, no. 3: 799–812.

Yao, Huang, Rongjun Qin, and Xiaoyu Chen. 2019. “Unmanned Aerial Vehicle for Remote
Sensing Applications - A Review.” Remote sensing (Basel, Switzerland) 11, no. 12
(1443): 1–22.

Yeung, Albert K. W., and G. Brent. Hall. 2007. Spatial Database Systems
Design, Implementation and Project Management Dordrecht: Springer Netherlands.

Zandbergen, Paul A. 2020. “Python Scripting for ArcGIS Pro” First edition. Redlands,
California: Esri Press.

Zhang, Hao, Lei Xi, Xinming Ma, Zhongmin Lu, Yali Ji, and Yanna Ren. 2008. “Research and
Development of the Information Management System of Agricultural Science and
Technology to Farmer Based on GIS.” Computer And Computing Technologies In
Agriculture, Volume I, 258:141–150. Boston, MA: Springer US.

Zhang, Qin. 2016. Precision Agriculture Technology for Crop Farming Boca Raton, FL: CRC
Press.

76

Appendix A Social Behavioral IRB Protocol
Social Behavioral Protocol

Study Title: Spatial Databases for Diverse Agricultural Record Keeping

PI Name: Philip Hess

Study Procedures

1. Background/Rationale

Much research combining Geographic Information Systems (GIS) and agriculture is focused
on expensive precision agriculture (PA) systems for large monoculture farms. While this is a
valid use of the technology, GIS has often been overlooked by (but can still greatly benefit)
small, diverse farms. Regenerative agriculture (which describes the aforementioned small
diverse farms) involves many moving parts, all of which exist on the land— from crop
placement and succession planting to livestock rotation, grazing patterns, and irrigation plans.
These all have significant temporal and other attribute data associated with them and would be
best organized with a spatial database.

Many studies and projects have the goal of improving agriculture, and they approach those
improvements in various ways, from real time productivity analysis to national recordkeeping
to GIS integration. This thesis will focus on agricultural spatial databases for individual farm
management and recordkeeping on diverse farms as an area that needs development.

2. Purpose/Objectives/Aims/Research Questions

This thesis outlines the creation of a spatial database as a system of record keeping for a small
diverse farm. It will involve research and interviews with experts in the field to design said
database and assess its effectiveness compared to other record keeping methods.

3. Participants (sample)
a.
This study is interested in talking with farmers and researchers in Ventura County who
practice regenerative agriculture (Polyculture farming under 500 acres). The farms will
be selected from location-based internet searches (within Ventura County). I will use a
regenerative agriculture website (RegenerationInternational.org) which documents the
location of regenerative farms around the world, as well as google maps search for
regenerative agriculture. I will also research any official certifying bodies for
regenerative agriculture in Ventura County.

77

c. Participants are not part of a spatial subject population.

4. Recruitment/Screening Process (sampling strategy)

The initial search results were narrowed down based on how diverse the farms are. Farms
that only grew one type of crop will be removed from the study. For example,
regenerationinternational.org showed 6 farms in Ventura County, but upon further
investigation most of them did not meet the diversity requirement of this study. One
produces only beef, one only pork, one only orchard trees, and one only produces
vegetables. The Google Maps results require a similar screening process. An initial
search of “regenerative agriculture in Ventura County” produced many results including
agricultural service companies and farmers markets. I narrowed this down to only the
businesses listed as “Farm” or “Education Center”. I only found one Regenerative
certification, and while it is still in its early development stages, two of its founding
companies are based in Ventura County.

Overall, I am hoping to identify 5 or 6 qualifying organizations in hopes that at least 3
will be available and willing to participate.

Recruitment will take place via email to the companies involved. Appropriate consent
will be shown in the email and verified in each interview. Interviews will take place over
the phone. Site visits may be scheduled after the initial phone interview if needed to
collect or verify data.

5. Methods
a.
I started with research on the many complexities of regenerative agriculture. I
Specifically focused on efficient ways to keep records and store data. I am
continuing that research by talking with local regenerative farmers and others in
the regenerative agricultural community about their current record keeping
systems, data analysis needs, and general farming practices. I will use this
information to design a spatial database to hold agricultural records. I will also
get copies of archival farm data from the organizations interviewed (in the form
of spreadsheets, word processing documents, and handwritten notes). This data
will be cleaned and edited to populate the newly created database. The database
will be tested with queries that would be useful for day-to-day farm
management. There will be follow up interviews to demonstrate the database
and assess its effectiveness.

i. Regenerative farms and leaders in the regenerative movement in Ventura
County CA were contacted via email. They will be informed of the
study, and be invited to be interviewed in said email. One representative
from each organization will be interviewed over the phone for
approximately 20 minutes based on the semi structured interview
procedure attached to this application (istar application section 13) which
includes requesting access or copies of archival farm records. Their

78

consent will be verified over the phone during the interview. Site visits
may be scheduled after the initial phone interview if needed to collect or
verify any data.

Responses to interviews will be documented in notes by the interviewer.
These notes will be used to develop a spatial database to hold records for
diverse farms and assess if it meets their needs. Once the database is created,
it will be populated with cleaned archival farm records. The database’s
usefulness will be demonstrated through queries and shown to farmers to get
their reactions in a follow up interview (~2 months after initial interview).

The final results and conclusions will be prepared in a thesis paper and
distributed through standard USC MS GIST theses.

iv.
A.
Notes will be identifiable by name of the organization being interviewed.
Archival farm records may also be used to demonstrate the use of the
database. This may include location data that could be linked to the
address of the business.
v.
All data will be stored on the personal computer of the principal investigator.
Eventually the archival database may be stored on ArcGIS Online, as a
PostGIS database or in another database system per institutional policy.
vi.
This project involves writing and defending a Masters thesis paper. Findings
and results will be disseminated through the standard methods of a USC
GIST Masters Thesis.
b. Instrumentation
Questionnaires/Survey Measures (names and citations)

General exploratory interview questions were developed by the
investigator. It is a semi-structured, open ended interview with questions
such as “what information do you record about the crops you grow” and
“what is most lacking about your current record keeping system?”. The
questions are designed to get an understanding of the current farming
practices and record keeping systems on regenerative farms, and to
explore how they can be improved. (see interview script Section 13 of
istar application).

Qualitative instruments

Some qualitative questions will be introduced in this semi structured/
open ended interview process.

c. Data Analysis

79

i. The survey results will be compared with the authors research as well as
their experiences working in the industry to describe the need for a
spatial database and to design and implement a demonstration of said
database. Archival farm records will be used to populate the database and
show its benefits to farm management.

ii. Primary data will be collected as notes taken during interview.
Secondary data already exists as spreadsheets and word documents.

Protocol Created by Philip Hess from IRB Template.

80

Appendix B IRB Approved Exemption

Screenshot of approved exemption from the IRB through iStar interface.

81

Appendix C Interview Script
Interview Script:

Hello __ This is Philip Hess, I am a masters student in the GIST program at USC. I am doing
my thesis project about regenerative agriculture, more specifically data management and
record keeping for diverse farms.

Thank you for participating in this research!

There are a few main topics I would like to discuss with you: general farming practices,
current data collection and storage methods, and data/analytical needs.

General farming Practices
• What size is your farm? How many employees? Full time, seasonal, or contractors?
• What do you grow? How many species? How many types of crops?
• Do you think the spatial relationship between features is important on a diverse farm?
• What are your main fertilization, pest, and disease management strategies?

Current data collection and storage methods
• What information do you need to know about each type of crop?
• Do you record this information? If so, is it on paper or electronically? If electronically,
is it stored in a word processing document, spreadsheet, database, or other form? Is
this how you have always stored records?
• Do you record the location of everything you grow?
• How accessible is this information to other departments or other farm workers?
• How accessible/complete is data from previous years?
• Do you have any old farm records or documents that I can review and use to populate
a sample database?

Data/ analytical needs
• What is most lacking about your current record keeping system?
• Do you, or would you be willing to, pay licensing fees for a data management system?
• What kind of analysis can you do using your data (what do you use it for, what
decisions does it inform)?
• Is there any type of data that you do not currently collect or record but would be useful
to have? Would this help with any new analysis or insights?

Do you have any comments, questions, suggestions, or ideas that you would like to share
with me about anything we have talked about?

I value your opinion as an expert and daily practitioner in the regenerative agriculture
movement. I plan to use some of the information from this interview, along with other
research I have done to develop and populate a spatial database to hold agricultural records
for a small diverse farm. I hope this work is interesting and useful to you, and if so I can send
you a copy of the thesis when it is complete.

Thank you again for your time, and feel free to contact me with any further questions.
Script created by Philip Hess.

82

Appendix D Database Creation Script
--*** FarmDB Master Script ***--
--** DB Creation **--
CREATE DATABASE "FarmDB"
WITH
OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'English_United States.1252'
LC_CTYPE = 'English_United States.1252'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;
COMMENT ON DATABASE "FarmDB"
IS 'Demonstration database holding agricultural records for farm
management';
--** Add Extensions **--
CREATE EXTENSION IF NOT EXISTS postgis
SCHEMA public
VERSION "3.1.4";
CREATE EXTENSION IF NOT EXISTS postgis_raster
SCHEMA public
VERSION "3.1.4";
CREATE EXTENSION IF NOT EXISTS postgis_topology
SCHEMA topology
VERSION "3.1.4";
--** Create tables and relationship constraints **--
--* Farm Plot and Observations*--
CREATE TABLE "FarmPlot" (
"plotID" numeric PRIMARY KEY,
"name" text UNIQUE,
"type" text,
"loc" geometry
);
CREATE TABLE "Observations" (
"obsID" numeric PRIMARY KEY,
"plot" text,
"date" date,
"notes" text,
"loc" geometry,
FOREIGN KEY ("plot")
REFERENCES "FarmPlot"("name")
ON DELETE SET NULL
);
--** Animal Records **--
CREATE TABLE "AnimalPack" (
"packID" numeric PRIMARY KEY,
"name" text UNIQUE,
"type" text
);
CREATE TABLE "PackGraze" (
"grazeID" numeric PRIMARY KEY,
"pack" text,
"plot" text,
"start" timestamp,
"end" timestamp,
"loc" geometry,
FOREIGN KEY ("plot")
REFERENCES "FarmPlot"("name")
ON DELETE SET NULL,
FOREIGN KEY ("pack")
REFERENCES "AnimalPack"("name")
ON DELETE SET NULL
);
CREATE TABLE "PackFeed" (
"feedID" numeric PRIMARY KEY,
"pack" text,
"date" date,
"food" text,
"amt" numeric,
FOREIGN KEY ("pack")
REFERENCES "AnimalPack"("name")
ON DELETE SET NULL
);
CREATE TABLE "Animals" (
"anID" numeric PRIMARY KEY,
"type" text,
"name" text,
"breed" text,
"gender" text,
"bDay" date,
"arrivDay" date,
"starNumb" numeric,
"pack" text,
"prosDate" date,
"prosNumb" numeric,
"notes" text,
FOREIGN KEY ("pack")
REFERENCES "AnimalPack"("name")
ON DELETE SET NULL
);
CREATE TABLE "AnimalMedicine" (
"treatID" numeric PRIMARY KEY,
"anID" numeric,
"date" date,
"treat" text,
"notes" text,
FOREIGN KEY ("anID")
REFERENCES "Animals"("anID")
ON DELETE SET NULL
);
CREATE TABLE "AnimalHarvest" (
"harvID" numeric PRIMARY KEY,
"anID" numeric,
"date" date,
"prod" text,
"amt" numeric,
"unit" text,
"notes" text,
FOREIGN KEY ("anID")
REFERENCES "Animals"("anID")
ON DELETE SET NULL
);
--** Plant Records **--
CREATE TABLE "Species" (
"specID" numeric PRIMARY KEY,
"comName" text,
"variety" text,
"type" text,
"bloomSzn" text,
"notes" text
);
CREATE TABLE "SeedMix" (
"sedMixID" numeric PRIMARY KEY,
"notes" text
);
CREATE TABLE "SpeciesMix" (
"speMixID" numeric PRIMARY KEY,
"sedMix" numeric,
"specID" numeric,
"amt" numeric,
FOREIGN KEY ("specID")
REFERENCES "Species"("specID")
ON DELETE SET NULL,
FOREIGN KEY ("sedMix")

83

REFERENCES "SeedMix"("sedMixID")
ON DELETE SET NULL
);
CREATE TABLE "PlantingEvent" (
"plantID" numeric PRIMARY KEY,
"date" date,
"type" text,
"plot" text,
"species" numeric,
"sedMix" numeric,
"stage" text,
"numb" numeric,
"unit" text,
"endDate" date,
"notes" text,
"loc" geometry,
FOREIGN KEY ("plot")
REFERENCES "FarmPlot"("name")
ON DELETE SET NULL,
FOREIGN KEY ("sedMix")
REFERENCES "SeedMix"("sedMixID")
ON DELETE SET NULL,
FOREIGN KEY ("species")
REFERENCES "Species"("specID")
ON DELETE SET NULL
);
CREATE TABLE "PlantPressure" (
"presID" numeric PRIMARY KEY,
"plantID" numeric,
"date" date,
"genPres" text,
"specPres" text,
"damage" text,
"notes" text,
FOREIGN KEY ("plantID")
REFERENCES "PlantingEvent"("plantID")
ON DELETE SET NULL
);
CREATE TABLE "PlantHarvest" (
"harvID" numeric PRIMARY KEY,
"plantID" numeric,
"date" date,
"units" text,
"amt" numeric,
"quality" text,
"notes" text,
FOREIGN KEY ("plantID")
REFERENCES "PlantingEvent"("plantID")
ON DELETE SET NULL
);
--** Fertility Records **--
CREATE TABLE "FertilityMaterial" (
"matID" numeric PRIMARY KEY,
"type" text,
"name" text,
"startDate" date,
"endDate" date,
"source" text,
"notes" text,
"loc" geometry
);
CREATE TABLE "MaterialMix" (
"matMixID" numeric PRIMARY KEY,
"notes" text
);
CREATE TABLE "FertilityMix" (
"fertMixID" numeric PRIMARY KEY,
"matID" numeric,
"matMix" numeric,
"amt" numeric,
FOREIGN KEY ("matID")
REFERENCES "FertilityMaterial"("matID")
ON DELETE SET NULL,
FOREIGN KEY ("matMix")
REFERENCES "MaterialMix"("matMixID")
ON DELETE SET NULL
);
CREATE TABLE "FertilitySpread" (
"applicID" numeric PRIMARY KEY,
"date" date,
"plot" text,
"method" text,
"material" numeric,
"matMix" numeric,
"unit" text,
"amt" numeric,
"notes" text,
"loc" geometry,
FOREIGN KEY ("plot")
REFERENCES "FarmPlot"("name")
ON DELETE SET NULL,
FOREIGN KEY ("material")
REFERENCES "FertilityMaterial"("matID")
ON DELETE SET NULL,
FOREIGN KEY ("matMix")
REFERENCES "MaterialMix"("matMixID")
ON DELETE SET NULL
);
CREATE TABLE "CompostTurn" (
"turnID" numeric PRIMARY KEY,
"pileID" numeric,
"date" date,
"wtrAmt" text,
"notes" text,
FOREIGN KEY ("pileID")
REFERENCES "FertilityMaterial"("matID")
ON DELETE SET NULL
);
CREATE TABLE "CompostTemps" (
"tempID" numeric PRIMARY KEY,
"pileID" numeric,
"date" date,
"high" numeric,
"low" numeric,
"avg" numeric,
"moisture" text,
"notes" text,
FOREIGN KEY ("pileID")
REFERENCES "FertilityMaterial"("matID")
ON DELETE SET NULL
);

SQL code written by Philip Hess.

84

Appendix E Table and Attribute Descriptions
TableName AttributeName DataType Description Notes
FarmPlot

Outlines areas on the farm (garden plots,
pastures, orchard blocks etc.)

plotID numeric Number assigned to each farm plot Primary Key

name text Name of farm plot Alternate Key

type text
General farm area or department (garden,
orchard, pasture etc.)

loc geometry Location of farm plot (polygon)

Observations

Records general observations and notes about
the farm

obsID numeric Number assigned to each observation Primary Key

plot text Farm plot where observation was made
Foreign Key
(FarmPlot.name)

date date Date of observation

notes text Description of observation

loc geometry Location of observation

AnimalPack

Groups animals together in a pack (cow herds,
chicken coops, bee hives etc.)

packID numeric Number assigned to each animal pack Primary Key

name text Name of animal pack Alternate Key

type text
Type of animal in pack (Chickens, cows, bees
etc.)

PackGraze

Records time and location of animal pack
movements

grazeID numeric Number assigned to each pack grazing event Primary Key

pack text Name of animal pack grazing
Foreign Key
(AnimalPack.na
me)

plot text Name of farm plot being grazed
Foreign Key
(FarmPlot.name)

start timestamp Date and time that grazing this location begins

end timestamp Date and time that grazing this location ends

loc geometry Location of grazing event

PackFeed

Records supplemental feed given to animal
packs

feedID numeric Number assigned to each pack feeding event Primary Key

85

pack text Name of animal pack being fed
Foreign Key
(AnimalPack.na
me)

date date Date that pack is fed

food text Type of food fed (alfalfa, corn, minerals etc.)

amt numeric Number of servings of food fed

Animals

Records general information about individual
farm animals

anID numeric
Number assigned to each animal or group of
animals Primary Key

type text General species of animal (chicken, cow, bee)

name text Name of animal

breed text Breed or species of animal

gender text
Gender and fertility of animal (hen, bull, heifer
etc)

bDay date Birthday of animal

arrivDay date Date animal arrived on farm

starNumb numeric
Starting number of animals represented (1
cow, or 15 chickens)

pack text Name of animal pack that animal belongs to
Foreign Key
(AnimalPack.na
me)

prosDate date
Date that animal is processed/ sold (last day on
farm)

prosNumb numeric
Number of animals processed (shows if
chickens have died)

notes text Additional information about animal

AnimalMedicine

Records individual medical treatments given
to farm animals

treatID numeric
Number assigned to each time an animal is
given a medical treatment Primary Key

anID numeric
Number identifying which animal is receiving
treatment
Foreign Key
(Animals.anID)

date date Date of treatment

treat text Description of medical treatment or medicine

notes text
Additional information about treatment
application

AnimalHarvest

Records harvesting of animal products (eggs,
beef, honey)

harvID numeric
Number assigned to each animal harvesting
event Primary Key

anID numeric
Number identifying which animal is being
harvested
Foreign Key
(Animals.anID)

86

date date Date of harvesting animal products

prod text
Product harvested (eggs, beef, chicken meat,
honey etc.)

amt numeric Amount harvested

unit text Unit of amount harvested (dozen, lbs etc.)

notes text
Additional information about animal
harvesting event

Species

Records general information of plant species
on farm

specID numeric Number assigned to each plant species Primary Key

comName text Common or general name of plant species

variety text General plant family or plant variety name

type text
General area or department using species
(garden, habitat, orchard etc.)

bloomSzn text
General bloom season (early summer, mid
spring, late fall etc.)

notes text Additional information about plant species

SeedMix

Database table to access a mixture of species

sedMixID numeric Number assigned to each seed mixture Primary Key

notes text
Information about seed mix (nitrogen cover
crop, pollinator row mix etc.)

SpeciesMix

Database table to define a mixture of species

speMixID numeric Number assigned to each species mix Primary Key

sedMix numeric
Number identifying which seed mix this
belongs to
Foreign Key
(SeedMix.sedMi
xID)

specID numeric
Number identifying which species used in this
seed mix
Foreign Key
(Species.specID)

amt numeric
Number of seeds of this species used in
mixture

PlantingEvent

Documents when, where, and what species are
planted

plantID numeric Number assigned to each planting event Primary Key

date date Date of planting

type text
General purpose of planting (cover crop,
habitat, garden, orchard etc.)

plot text Name identifying plot being planted on
Foreign Key
(FarmPlot.name)

species numeric
Number identifying which species is being
planted (instead of seed mix)
Foreign Key
(Species.specID)

87

sedMix numeric
Number identifying which seed mixture is
being planted
Foreign Key
(SeedMix.sedMi
xID)

stage text
Stage of plants being planted (seeds, 4in pot, 1
gal pot)

numb numeric Number of plants planted

unit text unit of number planted (lbs, seeds, trees etc.)

endDate date Date of removal or plant death

notes text Additional information about planting event

loc geometry Location of planting event

PlantPressure

Records pest and disease damage for planting
events

presID numeric
Number assigned to each record of plant
damage Primary Key

plantID numeric
Number identifying which plants are
experiencing pressure
Foreign Key
(PlantingEvent.pl
antID)

date date Date of pest or disease observation

genPres text General pressure (pest, disease, weather etc.)

specPres text
Specific pressure (aphids, root rot, sunburn
etc.)

damage text
Level of damage caused so far (low, medium,
high etc.)

notes text Additional information about plant damage

PlantHarvest

Records harvesting of plant products (orchard
fruits, garden crops etc.)

harvID numeric
Number assigned to each time plants are
harvested Primary Key

plantID numeric
Number identifying which plant is being
harvested
Foreign Key
(PlantingEvent.pl
antID)

date date Date that plant is harvested

units text Unit of harvest (lbs, crates etc.)

amt numeric Amount harvested

quality text Quality of produce harvested

notes text
Additional information about plant harvest
event

FertilityMaterial

Records general information of fertility
material used on farm (compost, minerals etc.)

matID numeric Number assigned to each fertility material Primary Key

type text
Type of fertility material (pesticide, fertilizer,
compost pile, compost tea etc.)

88

name text
Name of fertility material (seaweed, zinc, pile
name etc.)

startDate date Start date of material on farm

endDate date Final date of use on farm

source text
Source of fertility material (on farm, from
online, from neighbor etc.)

notes text Additional Information about fertility material

loc geometry
point, line, or polygon location where material
is stored

MaterialMix

Database table to access a mixture of fertility
materials

matMixID numeric Number assigned to each material mixture Primary Key

notes text
Information about material mix (Tea with
prep, etc)

FertilityMix

Database table to define a mixture of fertility
materials

fertMixID numeric Number assigned to each fertility mixture Primary Key

matID numeric
Number identifying which materials used in
this fertility mix
Foreign Key
(FertilityMaterial
.matID)

matMix numeric
Number identifying which material mix this
belongs to
Foreign Key
(MaterialMix.mat
MixID)

amt numeric
Number of seeds of this species used in
mixture

FertilitySpread

Documents when, where, and what fertility
materials are applied on the farm

applicID numeric
Number assigned to each application of
fertility materials Primary Key

date date Date of fertility application

plot text Name of plot being spread on
Foreign Key
(FarmPlot.name)

method text
Method of application (foliar spray,
fertigation, spread, root soak etc.)

material numeric
Number identifying material applied (Instead
of material mixture)
Foreign Key
(FertilityMaterial
.matID)

matMix numeric Number identifying material mixture applied
Foreign Key
(MaterialMix.mat
MixID)

unit text Unit of material applied

amt numeric Amount of material applied

notes text
Additional information about fertility
application event

89

loc geometry Location that fertility was applied

CompostTurn

Records information about turning of compost
piles

turnID numeric Number assigned to each compost turn Primary Key

pileID numeric
Number identifying which compost pile is
being turned
Foreign Key
(FertilityMaterial
.matID)

date date Date of compost turn

wtrAmt text
Amount of water used in turn (low, medium,
high etc.)

notes text
Additional information about the compost turn
or pile

CompostTemps

Records information on temperature of
compost piles

tempID numeric
Number assigned to each time compost
temperatures are measured Primary Key

pileID numeric
Number identifying which compost pile is
being measured
Foreign Key
(FertilityMaterial
.matID)

date date Date that temperature measured

high numeric High temperature of pile

low numeric Low temperature of pile

avg numeric Average temperature of pile

moisture text Moisture of pile

notes text
Additional information about compost temps
or pile

Created by Philip Hess.

90

Appendix F Database Queries Script
--*** Queries In Thesis ***--

--Database Queries –

/* Query 1: Shows all records from Animals table.
SELECT * FROM "Animals" ;
*/

/* Query 2: Shows certain columns from Animals table.
SELECT an."type", an."name", an."breed"
FROM "Animals" an ;
*/

/* Query 3: Shows specific columns from Animals table that represent cows.
--SELECT an."type", an."name", an."breed" FROM "Animals" an WHERE an.type = 'cow' ORDER BY
an."arrivDay" ASC ;
SELECT an."type", an."name", an."breed"
FROM "Animals" an
WHERE an.type = 'cow';
*/

-- Relational Queries --

/* Query 4: Shows name, breed, and medical treatments of all cows.
SELECT an.name, an.breed, am.treat
FROM "Animals" an JOIN "AnimalMedicine" am
ON an."anID" = am."anID"
WHERE an."type" = 'cow';
*/

-- Temporal Queries --

/* Query 5: Shows variety, common name, and plant date of garden crops planted in first 2 months of 2018.
SELECT sp."variety", sp."comName", pe."date" as planted
FROM "Species" sp JOIN "PlantingEvent" pe
ON pe."species" = sp."specID"
WHERE pe."date" BETWEEN '2018-01-01' AND '2018-03-01'
AND sp."type" = 'garden';
*/

/* Query 6: Finds age of all cows currently on farm (Nov. 15 2019) in weeks.
SELECT an."name",
('2019-11-15'- an."bDay")/7 as weeksOld
FROM "Animals" an
WHERE an."type" = 'cow'
AND an."prosDate" IS NULL
AND ('2019-11-15'- an."arrivDay")>= 0;
*/

-- Spatial Queries --

/* Query 7: Shows name, type and location of all farm plots.

91

SELECT fp."name", fp."type", fp."loc"
FROM "FarmPlot" fp;
*/

/* Query 8: Finds area (acres) and perimeter (meters) of each farm plot.
SELECT n."name",
ST_AREA(n.loc::geography)*0.00024710538 as acres,
ST_Perimeter(n."loc"::geography)as meters,
n."loc"
FROM "FarmPlot" n;
*/

/* Query 9: Finds current location of animal packs (April 16, 2019).
SELECT ap."name" pack, fp."name" location, fp."loc"
FROM "AnimalPack" ap JOIN "PackGraze" pg ON ap."name" = pg."pack"
JOIN "FarmPlot" fp ON pg."plot" = fp."name"
WHERE ('2019-04-16' BETWEEN pg."start" AND pg."end")
OR ('2019-04-16' <= pg."start" AND pg."end" IS NULL);
*/

/* Query 10: Query 10: Displays a summary of each farm plot in 2018 including the name of the plot, the total
number of days it has been grazed by cows and by chickens, the amount of compost
spread per acre, and the number of times planted that year.
WITH gz as ( SELECT pg."plot",
justify_hours (SUM( CASE WHEN ap."type" LIKE '%chickens' THEN (pg."end" -
pg."start") ELSE '0h' END)) AS "Chicken Graze",
justify_hours(SUM( CASE WHEN ap."type" = 'cows' THEN (pg."end" - pg."start")
ELSE '0h' END)) AS "Cow Graze"
FROM "PackGraze" pg JOIN "AnimalPack" ap on pg."pack" = ap."name"
WHERE pg."start" BETWEEN '2018-01-01' AND '2018-12-31'
GROUP BY pg."plot"),
cmt as ( SELECT frs."plot",
SUM ( frs."amt" * CASE frs."unit" WHEN 'tons' THEN 2000 WHEN
'wheelbarrows' THEN 175 WHEN 'buckets' THEN 25 ELSE 0 END
/ (ST_AREA(fp."loc"::geography)*0.00024710538 )) AS "Compost
lbs/acre"
FROM "FertilitySpread" frs JOIN "FarmPlot" fp ON frs."plot" = fp."name"
WHERE frs."date" BETWEEN '2018-01-01' AND '2018-12-31'
GROUP BY frs."plot"),
ar as (SELECT * FROM "FarmPlot" fp),
ptd as (SELECT pe."plot", COUNT( pe."plantID") AS "Times Planted"
FROM "PlantingEvent" pe
WHERE pe."date" BETWEEN '2018-01-01' AND '2018-12-31'
GROUP BY pe."plot")
SELECT ar."name", gz."Cow Graze", gz."Chicken Graze", cmt."Compost lbs/acre", ptd."Times Planted", ar."loc"
FROM ar FULL JOIN gz on ar."name" = gz."plot" FULL JOIN cmt ON ar."name" = cmt."plot" FULL JOIN ptd
ON ar."name" = ptd."plot";
*/

Code written by Philip Hess.

Abstract (if available)

Abstract Identified by polycultures of plants and animals, regenerative farms are made up of complex interrelated systems and face challenges with data management and record keeping. Despite regenerative farms having more complex record keeping needs than industrial (monoculture) farms, they are not well supported by existing farm management software. A spatial database can be a powerful tool for organizing, accessing, and analyzing farm data. The objectives of this research are to design and create a functional demonstration of a spatial database for agricultural record keeping that is tailored to the needs of regenerative farmers. The initial database design was informed by an extensive literature review of record keeping technologies in agriculture as well as the author’s professional experience working on regenerative farms. The database’s logical schema was finalized after conducting interviews with farmers and leaders of the regenerative movement in Ventura County, CA. Nine interview subjects representing five regenerative agriculture organizations participated in this study. The farms had varying record keeping practices, from memory, to spreadsheets, to Farm Management Information Systems (FMIS). A spatial database was created in PostgreSQL with the PostGIS extension and populated with archival farm data to demonstrate the database’s usefulness to regenerative farmers. The data was combined and visualized through SQL queries that leveraged the relational, temporal, and spatial qualities of the farm data. While this spatial database requires technical proficiency to set up and maintain, it was found to be more effective at handling a farms’ data than their current record keeping systems. Spatial databases are well equipped to handle the data needs of a regenerative farm.

Linked assets

University of Southern California Dissertations and Theses

Conceptually similar

PDF

Utilizing existing museum collections and GIS for paleontological site assessment and management

PDF

Operational optimization model for Hungry Marketplace using geographic information systems

PDF

Building a spatial database of biochar research and practice with Web-GIS

PDF

Design and implementation of an enterprise spatial raster management system

PDF

Scenario-based site suitability analysis and framework for biodiversity conservation: agricultural zone, Galapagos Archipelago, Ecuador

PDF

The movement of Mexican migration and its impact based on a GIS geospatial database

PDF

Enriching the Demographic Survey sampling for the Los Angeles County Annual Homeless Count with spatial statistics

PDF

Applying GIS to landscape irrigation systems: a case study of the Music Academy of the West campus in Montecito, CA

PDF

That sinking feeling: predicting land subsidence in California’s San Joaquin Valley with a spatial regression model

PDF

Exploring commercial catch: creating a responsive Florida fisheries web GIS using ASP.NET, the Esri JavaScript API 4.x, and Calcite Maps

PDF

The role of GIS in asset management: County of Kauai Department of Parks and Recreation a need for an asset management program

PDF

Bringing GIS to a small community water system

PDF

Identification and analysis of future land-use conflict in Mecklenburg County, North Carolina

PDF

Modeling the spatio-temporal variability of solar radiation on buildings: a case study of Lewis Hall

PDF

Comparing Landsat7 ETM+ and NAIP imagery for precision agriculture application in small scale farming: a case study in the south eastern part of Pittsylvania County, VA

PDF

A cartographic exploration of census data on select housing challenges among California residents

PDF

The impact of definition criteria on mapped wildland-urban interface: a case study for ten counties along the Oregon-California border

PDF

Suitability analysis for wave energy farms off the coast of Southern California: an integrated site selection methodology

PDF

Visualizing historic space through the integration of geographic information science in secondary school curriculums: a comparison of static versus dynamic methods

PDF

A Web GIS application for airport pavement management

Asset Metadata

Creator Hess, Philip Thomas (author)

Core Title Building a spatial database for agricultural record keeping and management on a regenerative farm

School College of Letters, Arts and Sciences

Degree Master of Science

Degree Program Geographic Information Science and Technology

Degree Conferral Date 2022-08

Publication Date 05/08/2022

Defense Date 04/08/2022

Publisher University of Southern California (original), University of Southern California. Libraries (digital)

Tag database design,Farming,OAI-PMH Harvest,recordkeeping,regenerative agriculture,spatial database

Format application/pdf (imt)

Language English

Contributor Electronically uploaded by the author (provenance)

Advisor Wu, An-Min (committee chair), Ruddell, Darren (committee member), Swift, Jennifer (committee member)

Creator Email philipthess@gmail.com

Permanent Link (DOI) https://doi.org/10.25549/usctheses-oUC111297280

Unique identifier UC111297280

Document Type Thesis

Format application/pdf (imt)

Rights Hess, Philip Thomas

Type texts

Source 20220509-usctheses-batch-940 (batch), University of Southern California (contributing entity), University of Southern California Dissertations and Theses (collection)

Access Conditions The author retains rights to his/her dissertation, thesis or other graduate work according to U.S. copyright law. Electronic access is being provided by the USC Libraries in agreement with the author, as the original true and official version of the work, but does not grant the reader permission to use the work if the desired use is covered by copyright. It is the author, as rights holder, who must provide use permission if such use is covered by copyright. The original signature page accompanying the original submission of the work to the USC Libraries is retained by the USC Libraries and a copy of it may be obtained by authorized requesters contacting the repository e-mail address given.

Repository Name University of Southern California Digital Library

Repository Location USC Digital Library, University of Southern California, University Park Campus MC 2810, 3434 South Grand Avenue, 2nd Floor, Los Angeles, California 90089-2810, USA

Repository Email cisadmin@lib.usc.edu