University of Southern California Dissertations and Theses

Web-based relational spatiotemporal geodatabase of glaciers in California

(USC Thesis Other)

Web-based relational spatiotemporal geodatabase of glaciers in California

PDF

Download a page range

Download transcript

Copy asset link

Request this asset

Transcript (if available)

Content

Web-Based Relational Spatiotemporal Geodatabase of Glaciers in California

by

Chase Van Schoonhoven

A Thesis Presented to the
FACULTY OF THE USC DORNSIFE COLLEGE 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 2020

Copyright © 2020 Chase Van Schoonhoven
ii

To my wife, who never lost faith in me. Without her love, patience, and support I would not be
here.

iii
Acknowledgements
I would first like to thank my thesis advisor, Dr. Bernstein, for her guidance throughout
the entire process as well as for keeping me on tract when needed and for helping when I felt
stuck. I would also like to thank my committee members, Dr. Wu and Dr. Swift, for their input
and support during the foundational stages all the way through to the finalization of this project.

iv
Table of Contents
Dedication ....................................................................................................................................... ii
Acknowledgements ........................................................................................................................ iii
List of Tables ................................................................................................................................ vii
List of Figures .............................................................................................................................. viii
List of Abbreviations ...................................................................................................................... x
Abstract .......................................................................................................................................... xi
Chapter 1 Introduction .................................................................................................................... 1
1.1. Motivation ...........................................................................................................................3
1.2. Study Area ..........................................................................................................................5
1.3. Research Objectives ............................................................................................................7
1.4. Thesis Organization ............................................................................................................7
Chapter 2 Related Work & Datasets ............................................................................................... 9
2.1. Intended User Groups and Use Case ..................................................................................9
2.1.1. Geologists / Glaciologists ..........................................................................................9
2.1.2. Policy Makers ..........................................................................................................10
2.1.3. General Public ..........................................................................................................10
2.2. Spatiotemporal Database Design ......................................................................................11
2.3. Glacier Databases and Inventories ....................................................................................11
2.3.1. GLIMS .....................................................................................................................12
2.3.2. IAD ..........................................................................................................................13
2.4. Background on the Glacial Datasets in the Geodatabase..................................................14
2.4.1. GLIMS .....................................................................................................................14
2.4.2. RGI ...........................................................................................................................15
2.4.3. WGI..........................................................................................................................16
v
2.4.4. USGS NHD ..............................................................................................................16
2.4.5. Glaciers of the American West ................................................................................17
2.4.6. Insight from Glacier Studies ....................................................................................17
2.4.7. Insight from Key Glacier Database Models.............................................................18
Chapter 3 Database Design and Implementation .......................................................................... 20
3.1. Design Principles ..............................................................................................................20
3.1.1. Refinement of Existing Database Models................................................................21
3.1.2. Identification of Features .........................................................................................24
3.1.3. Choice of Software ..................................................................................................29
3.2. Implementation of Geodatabase .......................................................................................30
3.2.1. Geodatabase structure ..............................................................................................30
3.2.2. AWS Set Up .............................................................................................................31
3.2.3. Database Connections ..............................................................................................34
3.2.4. Creation of SQL Tables from Source Data ..............................................................35
3.2.5. Importing Shapefiles ................................................................................................36
3.3. Web Map Creation ............................................................................................................37
3.3.1. Domain Acquisition & Construction .......................................................................37
3.3.2. Web Application ......................................................................................................38
Chapter 4 Results .......................................................................................................................... 40
4.1. Geodatabase Final Design.................................................................................................40
4.2. Final Web Map .................................................................................................................42
4.3. GitHub Repository ............................................................................................................43
Chapter 5 Conclusion .................................................................................................................... 45
5.1. Evaluation .........................................................................................................................45
5.2. Future Work ......................................................................................................................46
vi
5.2.1. Web GIS Evaluation Survey ....................................................................................46
5.2.2. Additional Web Map Functionality .........................................................................47
5.2.3. National Implementation .........................................................................................48
5.3. Final Thoughts ..................................................................................................................48
References ..................................................................................................................................... 50
Appendix A List of Attributes from GLIMS ................................................................................ 52
Appendix B List of Attributes from RGI ...................................................................................... 54
Appendix C List of Attributes from WGI ..................................................................................... 57
Appendix D List of Attributes from NHD Waterbody ................................................................. 67
Appendix E List of Attributes from Glaciers of the American West ........................................... 69
Appendix F glaciers_variable Data Dictionary ............................................................................. 70
Appendix G SQL Code ................................................................................................................. 73

vii
List of Tables
Table 1 Common Attributes in Glacial Inventories ...................................................................... 21
Table 2 Data Description .............................................................................................................. 23
Table 3 GitHub Repository Contents ............................................................................................ 43

viii
List of Figures
Figure 1 Glacial Regions of California. Source: Basagic 2011 ...................................................... 6
Figure 2 glacier_static Table ......................................................................................................... 23
Figure 3 glac_region Features ....................................................................................................... 24
Figure 4 Example of wbdhu6 Feature Class Attributes ................................................................ 25
Figure 5 Example of ca_counties Feature Class Attributes .......................................................... 25
Figure 6 Example of ca_places Feature Class Attributes ............................................................. 26
Figure 7 submission_info Table Attributes................................................................................... 26
Figure 8 regional_center Table Attributes .................................................................................... 27
Figure 9 people Table Attributes .................................................................................................. 28
Figure 10 attachments Table Attributes ........................................................................................ 28
Figure 11 Postgre SQL Connection in ArcGIS Pro. Source: ESRI 2019. .................................... 29
Figure 12 Geodatabase Simplified ERD ....................................................................................... 31
Figure 13 Creating a PostgreSQL DB Instance in AWS .............................................................. 32
Figure 14 DB Instance Settings .................................................................................................... 33
Figure 15 AWS Database Options ................................................................................................ 34
Figure 16 Database Connection for AWS to ArcGIS Pro ............................................................ 34
Figure 17 Database Connection for AWS to pgAdmin ................................................................ 35
Figure 18 Example of PostGIS 2.0 Shapefile and DBF Loader and Exporter Tool ..................... 36
Figure 19 Example of Creating a New DNS Record for Domains Registered at Hover.com ...... 37
Figure 20 Selecting a Web Map in Web Application Builder for ArcGIS ................................... 39
Figure 21 Final ERD ..................................................................................................................... 41
Figure 22 California Glaciers Web Map ....................................................................................... 43
ix
Figure 23 California Glaciers GitHub Repository ........................................................................ 44

x
List of Abbreviations
AWS Amazon Web Services
CDP Census-designated place
ERD Entity Relationship Diagram
GAW Glaciers of the American West
GIS Geographic information system
GISci Geographic information science
GLIMS Global Land Ice Measurements from Space
IAD A web-based, relational database for studying glaciers in the Italian Alps
NPS National Park Service
NSIDC National Snow and Ice Data Center
RDS Relational Database Service
RGI Randolph Glacier Inventory
SQL Structured Query Language
SSI Spatial Sciences Institute
USC University of Southern California
USGS United States Geological Survey
WGMS World Glacier Monitoring Service

xi
Abstract
Glaciers are an important indicator of global and local climate change, an important factor in
regional environmental health, and a possible natural hazard. The importance of glaciers has led
to the creation of many glacial inventories ranging in scale. The datasets from these glacial
inventories are often outdated or incomplete, especially for those in California. Additionally,
none of the glacier inventories track change in the glaciers over time, but rather they are limited
to the data at a single point in time in California. The objective of this project was to create a
web-based relational geodatabase and web map to track spatiotemporal information of glaciers in
California. The geodatabase was designed with three user groups in mind: geologists and
glaciologists, policy makers, and the general public. The geodatabase was also designed to meet
several goals. The first was to archive and store the large amounts of glacial data collected by
other sources, add temporal attribute onto current glacial data, and plan for new types of data.
This was done by developing the database in such a way that it meets current requirements and
standards of existing global glacial databases. Additionally, another goal was to create a web
map that allowed the data to be easily accessible and useful for various users with different
goals. A web map was constructed to display the data within the geodatabase and allow the user-
groups to interact with and download the data. The results of this project including the
geodatabase, data, methods, images and documentation are published at mapping.cool/california-
glaciers and on GitHub, along with the web map for public use. This geodatabase and web map
can be used for different applications, ranging from the simple uses like as monitoring glaciers to
detect change, to more advanced use of the data such as hazard detection and assessment.

1

Chapter 1 Introduction
Glaciers are formed from snow that is compressed over many years, into thickened ice masses.
Glaciers, unlike stagnant ice masses, flow like extremely slow-moving rivers. Glaciers usually
lie within mountain ranges, make up nearly 10 percent of the worlds land area, and store about
75 percent of the world’s fresh water (NSIDC 2019). Since the early twentieth century, glaciers
across the globe have been receding at remarkable rates (NSIDC 2019). Glaciers are partly of
concern because provide a myriad of benefits to us, the natural environment, and other species.
Further, they can pose a hazard to human populations. Glacial monitoring is beneficial for the
economy, the environment, and human health and welfare. Thus, glacial monitoring is a priority.
Given this, the goal for this project was to build a web-based relational spatiotemporal
geodatabase for studying glaciers. The geodatabase can be used as a framework for studying and
analyzing changes to glaciers over time. Additionally, the glacial database is built in such a way
that it can be utilized by multiple types of users. Ideally the database will help monitor changes
to glaciers and assist scientists with more advanced analyses like hazard detection and
assessment. This type of relational spatiotemporal geodatabase can facilitate the monitoring,
study, and analysis of glacial change, and ultimately facilitate our ability to protect the
environment, better predict and prevent hazards, as well as navigate complex economic and
geopolitical issues that can stem from glacial deterioration.
Glaciers are an integral part of our environment that can, in specific instances, pose
hazards to humans and other species. For example, glacial retreat and growth impacts many
different ecosystems since the ice serves as a reservoir of fresh water and provides habitats for
many species (National Park Service 2017). Additionally, glaciers pose natural hazards, like
when a glacier advances several miles in only a few months, an effect known as surging.
2

Flooding can also occur from the bursting of ice in dammed lakes. Glacial monitoring can help
track the changes in glaciers to better predict potential hazards. Glaciers also play an important
role in global economy, and glacial monitoring can help determine the commercial value in
glaciers for power generation, tourism, as well as the export of glacial ice.
Aside from the natural benefits and hazards of glaciers there are also geopolitical reasons
to monitor the change in glaciers. An example of this can be seen in Italy where their northern
land border is melting. The northern border passes through high alpine terrain including rock,
ice, and seasonal snow. Since 1850, Italy’s glaciers have shrunk by approximately fifty percent.
As the ice continues to shrink, the watershed between Italy and Austria has shifted. In some areas
along the border, the watershed shifted by up to 100 meters. At present, Italy and Austria agreed
upon a moveable border on their ice frontiers that fluctuates depending on the location of the
watershed and how the ice melts (O'Sullivan 2017). A spatiotemporal geodatabase that looks at
the changes in glaciers over time could help these two countries, as well as other territories with
similar land-border issues, determine new border lines based on the analysis of the information
collected regarding specific glaciers.
Changes in glaciers can aid in monitoring global climate change and can be an indicator
of changes in regional and global climate (National Park Service 2017). Basagic (2011) observed
that since 1911, fourteen glaciers in the Sierra Nevada Mountains of California decreased in area
on average by 55%. The significant degradation of those fourteen glaciers, along with the fact
that the last comprehensive inventory of glaciers in the area was done in 2006, demonstrates the
need to consistently keep track of these glaciers over time (Basagic 2011).
3

1.1. Motivation
The growing need for improved glacial monitoring and a modernized spatiotemporal
geodatabase for glaciers stems from the fact that similar databases are outdated or contain limited
datasets. By integrating the existing data from current glacial databases together and allowing
for the incorporation of new data types into the modernized spatiotemporal database, the new
database would be far more comprehensive than the other databases themselves. Additionally,
this new database would have a chronological record of data for the glaciers which would
provide a better picture of the change in the glacier over time rather than just a snapshot, which
is what is the current glacial inventories provide. Lastly, the database would improve upon the
existing databases in that it would be formatted in such a way that it would be easy to access by
various entities and researchers and could serve as a centralized database for glacial monitoring
in California.
Currently, the largest glacial database is Global Land Ice Measurements from Space
(GLIMS). The National Snow and Ice Data Center (NSIDC) develops tools, including GLIMS,
to aid in glacier mapping and transfers the analysis results of measurements to the NSIDC. Over
sixty institutions from around the world participate with GLIMS, while most of the coordination,
technical development, and data management occurring at the NSIDC in Boulder, Colorado.
According to GLIMS, their primary method of monitoring the world’s glaciers is using optical
satellite imagery (Global Land Ice Measurements 2018).
GLIMS database and web map are similar to this project, but one of problems with the
GLIMS data for California is that many of the fields used to describe and monitor glaciers have
NULL values. Specifically, fields such as Glacial Form, Frontal Characteristics, Longitudinal
Profile, and Source Nourishment all have NULL values. This is due to the analyst at the time of
4

input either not being able to determine the value or not taking the time to determine the value of
the attribute. Additionally, the data from GLIMS and other glacial inventories provide only a
snapshot in time and lack the ability to view a glacier change over time. Having more temporal
data would allow for This project built a more complete geodatabase of glaciers in California,
while also providing a temporal dimension to the data.
Another example of an existing glacial inventory is the Randolph Glacier Inventory
(RGI), which was created in response to the Fifth Assessment Report of the Intergovernmental
Panel on Climate Change (IPCC AR5). The inventory prioritized completeness of coverage, but
the attributes were limited. Satellite imagery from 1999 to 2010 was the basis of most of the
outlines (Pfeffer et al. 2014). The main contributors of uncertainty are debris and seasonal snow
that cover the glaciers, but there does not appear to be a normal distribution of these errors
(Pfeffer et al. 2014). Currently, many glaciers only have information on area and location, and
still need other data like topography and hypsography. One of the disadvantages of this inventory
is that it was created in a short period of time with limited resources (Pfeffer et al. 2014).
Additionally, the glaciers in the inventory are only from a snapshot in time and do not contain a
temporal element (Dempsey 2014). Once again, the present project aims to create a geodatabase
of California’s glaciers that will provide a temporal element to the data collected as well as
provide a more comprehensive inventory of the glaciers than the existing databases.
Despite the work done by others collecting the current glacier inventory data, more data
can be gathered at a greater spatial and temporal resolutions than what was previously collected.
In the past, the advantage to using satellite data was that it was much more convenient to
inventory glaciers in large remote areas (Aninya et al. 1996); however, modern technological
advancements such as newer satellites, UAVs and LiDAR have allowed for greater resolutions.
5

For example, in the Trinity Alps of California, there are over thirty-five perennial snow fields
and several small glaciers mapped by the USGS, though it seems many of the bodies that USGS
maps do not remain at the end of the second half of the year, and these glaciers are not currently
mapped by either GLIMS or the RGI (Basagic 2011). This database integrates existing
inventories, as well as aims to allow for the incorporation of new data collected with more
advanced technology and improved resolutions.
1.2. Study Area
This project creates a geodatabase and web map for glaciers within the state of
California. In California there are four glacial regions: Sierra Nevada, Trinity Alps, Mount
Lassen and Mount Shasta. Mount Shasta and Mount Lassen are both within the Cascade Range
(Figure 1). In California there are over 1700 snow or ice bodies which cover more than 46km
2
of
California (Basagic 2011). 20 of them are named, 13 of which are in Sierra Nevada while the
other seven are around Mount Shasta. The Sierra Nevada Mountain Range is approximately 640
miles long and located along California’s eastern border. The USGS mapped more than 1600
glaciers and perennial icefields in the Sierra Nevada Mountains (USGS 2018). The Trinity Alps
are located to the west of the Cascade Range and they contain thirty-nine small glaciers, as well
as perennial snow fields with an area of approximately 1.9km
2
. None of the glaciers or snow
fields have USGS names (USGS 2018). Mount Shasta is a stratovolcano located in northern
California in the Cascade Range (Basagic 2018). There are seven glaciers around the peak of
Mount Shasta including Whitney, Bolam, Hotlum, Konwakiton, Watkins, Mud Creek, and
Wintun. The total area of these seven glaciers is approximately 4.9km
2
, the largest of which
resides on the north face of Mount Shasta. The last glacial region is Mount Lassen which
6

contains nineteen small snow and ice bodies that have an area of less than 0.3km
2
, none of which
have names (USGS 2018).
Both the Sierra Nevada and Mount Shasta glacial regions have multiple cities very close
to the glaciers, such as Mammoth Lakes in the Sierra Nevada Mountain Range and the town of
Mount Shasta in the Cascades. Changes to the local glaciers would affect these cities and many
others. Additionally, many of the glaciers in Sierra Nevada belong to the same watershed as
Owens River, Owens Lake and Mono Lake, all of which are used to provide water to the Owens
Valley Aqueduct. The Owens Valley Aqueduct provides large amounts of water to Los Angeles.
Figure 1 Glacial Regions of California. Source: Basagic 2011
7

Creating a database with an updated inventory of the glaciers in California would aid in
glacial studies and increase our understanding of the changes occurring to the glaciers. Tracking
and mapping glacial changes is becoming more important than ever and will greatly improve our
understanding of the role of glaciers have in the economy, the environment, and in the welfare of
countless species. The present project provides one step towards improving glacial monitoring
and highlighting the need for spatial-temporal glacier monitoring globally.
1.3. Research Objectives
The present study incorporates three main criteria to shape the design and implementation
of this web-based geodatabase for California glaciers. The first goal is to develop the database in
such a way that it can catalog data sources for glacial data that meets current requirements and
standards of existing global glacial databases. The second goal is to archive and store the large
amounts of glacial data collected by USGS, Global Land Ice Measurements from Space
(GLIMS), Randolph Glacier Inventory (RGI), and other sources, as well as to plan for new data
types in addition to the data types and formats currently being used. The archived data will
include information of California glaciers from various snapshots in time and provide a
chronological record of data from each glacier. The third and final goal for the design of the
database is to make the data easily accessible through a web map and Githuband for various
users. These goals will help make the spatiotemporal geodatabase for California glaciers
comprehensive, easy to access and use, and help to highlight the changes to the glaciers over
time.
1.4. Thesis Organization
This thesis contains four additional chapters. Chapter 2 examines related works and the
datasets used for this project. It looks at the intended user groups and their various use cases. It
8

also discusses spatiotemporal database design and evaluation, as well describes GIS and how it is
used to visualize glacial and ice mass data. Chapter 2 also examines the various glacial and
hydrological databases and datasets which are used in the present project. Chapter 3 discusses
the methods used to design and create the project geodatabase and web map. Chapter 3 is broken
down into five subsections: Design Principles; Design and Implementation of Geodatabase; Map
and Feature Service Creation, Web Mapping Application Creation, and lastly a Summary of
Database Design Steps Completed. The subsections explain the work and through process used
to complete this project. Chapter 4 examines and discusses the success and limitations of the
final geodatabase design, as well as the final web map. The last chapter, Chapter 5, looks at
suggestions from the survey that can be implemented in the future; as well as future directions
this project can go. The final chapter also discusses lessons learned throughout the process of this
project.

9

Chapter 2 Related Work & Datasets
This chapter provides a framework to understand how the spatial-temporal database for this
thesis project was designed, organized, and implement; as well as introduce each dataset and
explain why each dataset was chosen for inclusion in the project. This chapter is broken up into
four sections: Intended User Groups and Use Case, Spatiotemporal Database Design, Glacier
Databases and Inventories, and Background on the Glacial Datasets in the Geodatabase. The first
discusses the various user groups and how they may use the database. The second section is a
brief overview of the texts that guided the spatiotemporal design of this project’s geodatabase.
That is followed by an overview of existing glacial geodatabases focusing specifically on
GLIMS and a web-based, relational database of glaciers in the Italian Alps. These were selected
because of their well-developed status and similar design goals to those of this project. Finally,
there is a discussion of the datasets included in the project, why they were included, and what
they lack.
2.1. Intended User Groups and Use Case
In order to ensure that the design of this project’s geodatabase and web map proved
useful, three user groups were identified: geologists/glaciologists, policy makers, and the general
public. Each user group has a different level of expertise in GIS; for example, geologists and
glaciologists would be considered expert to intermediate while policy makers and the general
public would be considered novice GIS users.
2.1.1. Geologists / Glaciologists
The primary users of this geodatabase will be glacial researchers. These users will likely
have proficiency in GIS and an in-depth knowledge about glaciers and ice masses. Glacial
10

researchers can use this geodatabase in conjunction with their own data to perform complex
analysis such as hazard detections and assessments. Additionally, glacial researchers can use data
obtained from the geodatabase to assist in conduct various types of spatial analysis, such as
detecting change in glacier system and assessing the impact of said change or examining an
abrupt change in an ice shelf. For the present project, external attributes such as the source of
nourishment for a glacier were excluded because glacial researchers can join their data of
external attributes to the glaciers, rather than this project trying to include every external attribute
possible. The present project also excluded all data outside of the spatial-temporal location of
glaciers because the amount and types of analysis that glacial researchers perform is so varied
that the researchers can integrate their own data for their specific project with the glacial data this
project provides. This particular user group can use the geodatabase in both its file form or as a
web map.
2.1.2. Policy Makers
While some policy makers may harbor basic proficiency in GIS and glacial research, it is
unlikely that most have a strong foundation. Policy makers can use the web map to see where
glaciers are, both within and in relation to, their jurisdiction. Being able to observe the glaciers or
any glacial change relevant to their jurisdiction allows policy makers to create policies that will
help protect their constituents as well as the natural environment. Because of the potential
limited knowledge of GIS among policy makers, both the geodatabase and web map are
designed to be functional for both the novice and the expert.
2.1.3. General Public
The general public user group is made up of users who are citizen scientists as well as
individuals who just want to know more about glaciers. The use case for this type of user will
11

likely be motivated by curiosity and/or concern about changes in glaciers. The general public
will have the simplest GIS needs of the geodatabase; for example, users in this group will likely
be concerned with glaciers in the nearby vicinity. Furthermore, it is less likely that these users
have be a specific research goal, in contrast to the research of the geologists and glaciologists
user group. As such, the data included for the general public user group will be more general in
nature, such as including a base map with common reference locations like nearby cities.

2.2. Spatiotemporal Database Design
The spatial-temporal database design research included reviewing textbooks such as
Yeung and Hall’s Spatial Database Systems (2007), which provides a comprehensive look at the
methods, implementation, and management of spatial database systems. It also involved
examining articles by Microsoft (2019) and ESRI (2019) on database design basics. The
aforementioned texts discuss important aspects of design such as development, management, and
maintenance, but they did not provide information on how to capture the temporal aspect of data.
Wachowicz (2014)’s Object-oriented design for temporal GIS, on the other hand, is a textbook
entirely devoted to the subject of temporal data. Specifically, the textbook discusses object-
oriented analysis and design methods, how object-oriented design can be used to model spatial-
temporal data, and it outlines methods to develop and maintain this data within a GIS. These
texts on spatial-temporal database creation acted as a guide for the development and design of
the geodatabase.
2.3. Glacier Databases and Inventories
This section examines other glacial inventories, the advantages and disadvantages of
each, and how they contribute to the thesis goals. GLIMS and the design from the article "A
12

web-based, relational database for studying glaciers in the Italian Alps" (referred to hereafter as
IAD) are discussed in detail, as they form the foundation of the geodatabase and web-viewer
design. Research was conducted through online scholarly databases and internet web searches on
other glacial and hydrology databases and datasets to help provide the necessary design
consensus on what data was incorporated into the final thesis project. Additionally, the research
highlighted the fact that the current datasets are missing elements that the final geodatabase
included to make it more comprehensive and user-friendly.
2.3.1. GLIMS
GLIMS is an example of a “complete” inventory of glaciers. This thesis builds upon this
type of inventory but provides greater temporal resolution. Currently the largest glacial database,
GLIMS develops tools to aid in glacier mapping and transfers the analysis results of
measurements to the National Snow and Ice Data Center (NSIDC). Over 60 institutions from
around the world are involved with GLIMS, while most of the coordination, technical
development, and data management are done at the NSIDC in Boulder, Colorado. According to
GLIMS, their primary method of monitoring the world’s glaciers is using optical satellite
imagery (Global Land Ice Measurements, 2018).
GLIMS employs two main tables to store data about the glaciers, Glacier_Static and
Glacier_Dynaimic. The former stores information about the glacier that does not change, such as
the glacier name, while the latter stores the measured attributes and the glacial outline. In
addition to the two main tables there are additional tables that hold related information such as
the participants in the GLIMS project. One problem with the GLIMS data of California is that
many of the fields used to describe and measure the glaciers have NULL values. These fields
include Glacial Form, Frontal Characteristics, Longitudinal Profile, and Source Nourishment.
13

Definitions of these attributes, along with the full list of attributes in GLIMS, can be found in
Appendix B. One thing that GLIMS does that this project emulates is their viewer, as it is
extremely functional and allows the user to quickly find a glacier, view it, and download the
data.
2.3.2. IAD
In the article, "A web-based, relational database for studying glaciers in the Italian Alps",
the authors outline their method for creating a geodatabase to monitor and catalogue the glaciers
of the Italian Alps (Nigrelli et al 2013). This database’s design compares similarly to how
GLIMS is designed which is pointed out in the article. The authors discussed their methods of
how they built a relational database with a web-interface. They had 59 different tables with the
main schema at the center (“glaciers”) after which they partitioned the tables into six different
groups: Location, Morphologic/Metric Info, Orographic Info, Attachments, Campaigns & Mass
Balances, and Users. These groups were further partitioned into subsections. They point out that
the purpose of the glacier table is to provide basic information about the glacier, but more
importantly, to link to additional information held in the other table. The authors go onto
describe the sub tables of each group and what their purpose is and why they included them. This
helped the author decide which tables to include in their geodatabase. After a discussion of the
design and structure of the database, they describe the web-interface and how it is used,
accessed, and searched. This is article provides insight on the integration of new types of data
and how to associate it with the glacial data, and how to collect and disseminate the data that has
been amassed (Nigrelli et al 2013). This geodatabase is an example of a model built to integrate
new data, maps, and imagery used to guide the design of the geodatabase for this thesis project.
Unfortunately, the actual database is no longer available for viewing as the URL provided does
14

not go anywhere, and the author had to go by methods discussed in the article to follow their
process and incorporate it into this project.

2.4. Background on the Glacial Datasets in the Geodatabase
There are currently several global, national, and multi-state databases that exist which
contain datasets for California-specific glaciers that the author incorporated and expanded upon
in this thesis project. There are five databases included in this study and of those five only one
has glacial data stored at more than one point in time, and none of them do for California. This is
what the author believes the main difference between this thesis and other similar works is.
Three of these are global: GLIMS, RGI, and WGI, one is national: USGS NHD, and one is
multi-state: Glaciers of the American West. This section discusses each of these datasets, what
they add to the project, and what they may be missing.
2.4.1. GLIMS
The most formidable of the current global glacial databases is GLIMS. Hence, GLIMS is
a seminal database for this thesis. GLIMS is not only a geodatabase of glaciers but includes the
GLIMS Glacier Viewer which is an interactive map that allows the user to view the data. The
GLIMS Glacier Viewer contains layers that are viewable and searchable, and outlines can be
downloaded in a variety of file types. The GLIMS database is currently the foremost web viewer
and interactive map containing glacial data.
GLIMS has 960 glacial features in California, none of which have multi-temporal
coverage. The data within in the database are vector shapefiles containing both points and
polygons. The data features are from 2006 analysis that used imagery from 1953 to 1984. There
are 37 attributes for each feature within this geodatabase, but only eight attributes are measured
15

attributes of a glacier. These include DB_AREA, AREA, WIDTH, LENGTH, PRIMECLASS,
MIN_ELEV, MEAN_ELEV, and MAX_ELEV; to see descriptions of these attributes see
Appendix A List of Attributes from GLIMS. Of the eight measured attributes, only three have a
non-zero or NULL values. While GLIMS is a strong example for how to build and allow for
viewing of glacial data, its own data for California is lacking in terms of temporal coverage and
completeness of attributes.
2.4.2. RGI
Another example of a current global database is the Randolph Glacier Inventory (RGI),
which is a collection of digital outlines of glaciers and is meant to be globally complete.
Approximately 198,000 glaciers in the inventory were derived from satellite imagery from 1999-
2010. Version 1.0 was released on February 22, 2012, and the current version (6.0) was released
July 28, 2017. Updates to RGI are now made in parallel with GLIMS during a transition period,
after which RGI will evolve into a subset of GLIMS offering complete one-time coverage,
version control, and a standard set of attributes.
Because RGI is now being developed in parallel with GLIMS it contains the same
number of features as GLIMS (960). Though RGI contains additional measured attributes that
are not a part of GLIMS, such as Slope, Aspect, Form, Surging, and Linkages. A full list of
attributes and descriptions for RGI data can be found in Appendix B List of Attributes from RGI.
In addition to having the same number of features as GLIMS, RGI also uses the GLIMS ID
number as the unique identifier for each glacier. Like GLIMS, RGI data is a vector shapefile that
contains polygons. RGI is a much more attribute complete dataset than GLIMS, but by design
lacks temporal data and tracking of glaciers.
16

2.4.3. WGI
The last dataset included in this project is Version 1 of the World Glacier Inventory. The
entirety of this data set contains data for over 130,000 glaciers. Most glaciers only have a single
entry, including all the glaciers within California, and therefore offer only a snapshot of glaciers
during the second half of the 20th century. The data in this data set is primarily based off aerial
photographs and maps. Unlike the other data sources for this project, WGI contains vector point
rather than polygon outlines of the glaciers. In total, WGI contains 497 features, with updates in
2008 from 1989 WGMS and NSIDC data. This data set contains additional measured attributes
not found in the other datasets that are part of this project such as SOURCE_NOURISH and
TONGUE_ACTIVIY. SOURCE_NOURISH is a 1-digit code that describes the glaciers source
of nourishment such as snow, avalanche, or superimposed ice. TOUNGE_ACTIVITY is a 1-
digit code that describes the activity of the tongue of the glacier such as retreating, stationary,
advancing, or surging. A full description of the WGI attributes can be found in Appendix C List
of Attributes from WGI. Even though this data source has few features within it, it adds to this
project because it adds another snapshot in time for the glaciers that it shares with the other data
sets. WGI also adds additional measured attributes that are considered and added to the final
geodatabase.

2.4.4. USGS NHD
In addition to strictly glacial datasets, one hydrology dataset and the associated glacial/ice
mass data was included. The USGS includes the National Hydrography Dataset (NHD), which is
a geospatial dataset of the surface water of the United States. It models features such as rivers,
streams, lakes, and glaciers, and it is currently the most up-to-date hydrography dataset for the
17

United States. As such, the National Hydrology Dataset is the most complete hydrographic
dataset in the United States and contains the most current data on glaciers and other water
features in the United States. However, like GLIMS and RGI, the USGS data does not contain a
temporal aspect.
The USGS NHD contains 1710 Features that are classified as glaciers or permanent ice
masses. This dataset contains features updated from 2002 to 2018. NHD contains information
about not only glaciers and ice masses, but also rivers, lakes, streams, etc. Therefore, there are
only three measured attributes directly pertaining to the glaciers in this database. A full list of
attributes and their descriptions can be found in Appendix D. So, while this while this data is the
most update it holds less measured attributes then any of the other included datasets.
Additionally, having a feature count that is nearly double that of GLIMS indicates that many of
the features are not glaciers, but rather permanent ice masses.
2.4.5. Glaciers of the American West
Glaciers of the American West is a small dataset provided by Portland State University. It
contains 95 glacier features within California, none of which have a temporal element. This
dataset is based off USGS 1:1000,000 scale maps using features identified as glaciers, which
according to the authors may or may not meet the criteria of an active glacier. A full list of
attributes and their descriptions can be found in Appendix E List of Attributes from Glaciers of
the American West
2.4.6. Insight from Glacier Studies
Previous glacier databases and inventories, like those discussed in Chapter 2 which
catalogue and inventory glaciers around the world, suggest that completeness of attributes and
data relating directly to the glacier features is important and useful to quantify, especially over
18

time. Adding this information into a main glacier database will increase the value of said
database to anyone who wants to study changes to glaciers. While data about the glacier itself is
important, other glacial databases and inventories suggest that there is little need to include a
great deal of information that glaciologists and geologists may obtain or use from other sources.
Adding more information than necessary to the database would needlessly increase its size.
Therefore, layers such as land use, surface water data, watersheds and drainage basins, principle
aquifers, drainage networks, etc. were excluded from this database. Data such as those listed
previously can be found and spatially related relatively easily as needed for additional studies.
2.4.7. Insight from Key Glacier Database Models
There is data from five glacial databases included in this project. Of those five databases,
only GLIMS has the ability to store the changes to glaciers overtime, but only has one point in
time for the glaciers in California. IAD is another relational database that stored data on glaciers
over multiple periods of time. These two databases provided insight on this geodatabase was
built.
Both the GLIMS and IAD databases have a schema that is centered around a table(s) that
represents the main domain of interest, the glaciers. For GLIMs, this is two tables- glacier_static
and glacier_dynamic. The former stores information about the glacier that is unchanging, while
the later stores measured attributes of the glacier such as the outline, size, form, and frontal
characteristic. In IAD, they use a single table to represent the glaciers. GLIMS and IAD also use
additional tables to hold related information. For GLIMS this includes tables of information on
the participants in the GLIMS project, as well as information such as the Point_Measurement
table which holds physical records of glaciers, such as temperature or debris thickness, but that
are not part of what they call an analysis or “snapshot” of the glacier. IAD has a total of 59 tables
19

that contain information related to the glaciers or management of access to the database. The 59
tables are partitioned into six groups. Four of the groups of tables contain additional information
related to the glaciers, one contains attachments, and the last manages user access.
After examining the approaches that GLIMS and IAD used in developing their
geodatabases, it was determined that the geodatabase would use a similar schema, in which there
is a central table(s) that holds information about the glaciers and additional tables that contain
related information. Like GLIMS, the author chose a similar structure with a glacier static and a
glacier variable table, rather than the more complex structure of IAD with many tables and those
tables broken down into groups. One of the reasons for this was because the data came from
many sources with varying levels of completeness with a wide variety of attributes, which meant
that complexity of linking, filling, and updating those tables would be much more difficult than a
unified glacier variable table.
20

Chapter 3 Database Design and Implementation
The primary goal for this thesis project was to build a spatial-temporal database of glaciers in
California with a web interface. The project had three primary criteria that fell under the main
goal. The first criteria was to develop the database in such a way that it can catalog data sources
for glacial data that meets current requirements and standards of existing global glacial
databases. The second was to archive and store the large volume of glacial data, as well as plan
for new data types beyond the data types and formats currently being used. The third objective
was to make the data easily accessible through a web map and Github repository. These
objectives were accomplished by operationalization of this thesis project, which was done
through the creation of the geodatabase and the web interface. The first operationalization,
geodatabase creation, accomplished the first two objectives. The web interface,
operationalization two, achieved the third objectives. Adapted from existing glacial inventories,
the geodatabase was designed to incorporate key attributes and functions while also storing the
ways in which the glaciers change over time.1 This chapter covers the design principles that
guided the geodatabase and web map creation, explains how the author designed and
implemented the geodatabase, and discusses the web map creation.
3.1. Design Principles
The geodatabase and web map design were guided by three factors: the needs of the user
groups, insights from previous glacier studies, and existing glacial database models and
inventories. User groups were discussed in Chapter 2 and the rest of this section will summarize
design principles that directed the geodatabase and web map designs based off the research done
in Chapter 2 of this thesis. This section reviews the choices made about the essential principles
that directed the database design.
21

3.1.1. Refinement of Existing Database Models
GLIMS served as the primary model for the design of the tables, static glacier table
(glaciers_static) and entity relationship diagram (ERD) for the geodatabase, but the attributes for
glacier variable table (glaciers_variable) came from all five of the databases’ glacial inventory
sources. Because the completeness of attributes associated with each glacier is extremely
important, any measured attribute from each of their data sources was included. Table 1 shows
attributes that were included in other glacial inventories that were included in glaciers_variable,
and a full list of attributes included can be found in Appendix F glaciers_variable Data
Dictionary.
Table 1 Common Attributes in Glacial Inventories
glaciers_var GLIMS RGI WGI NHD GAW
glacier_id GLAC_ID GLIMSId

wgi_gl_id Permanent
Identifier

submission_id
glac_name GLAC_NAME Name gl_name GNIS_Name GLACNAME
area AREA Area total_area Area
perimeter PERMIETER
min_elev MIN_ELEV Zmin min_elev
max_elev MAX_ELEV Zmax max_elev
mean_elev MEAN_ELEV Zmed mean_elev Elevation
in_basin drain_code
num_basins num_basins
fcode FCode
date SRC_DATE BgnDate photo_year FDate
max_width WIDTH mean_width
max_length LENGTH Lmax max_length
Slope Slope
aspect Aspect
connect Connect
surging Surging
linkages Linkages
form Form
22

term_type TermType
x_coord CenLon lon X_COORD
y_coord CenLat lat Y_COORD
prime_class PRIMECLASS
frontal_char FRONTAL_CHAR
long_char long_char
tongue_act tongue_activity
moraines1 moraines1
moraines2 moraines2
source_nourish src_noursh

min_elev_exp minelv_exp
mean_elev_acy meanelv_ab
mean_elev_abl meanelv_ac
activity_start activ_strt
activity_end activ_end
snowline_acy
snwln_accu

snowline_date
snwln_date

mean_depth mean_depth
deapth_acy depth_acy
area_acy area_acy
area_exp area_exp
mean_width mean_width
mean_length max_length
max_len_ex max_len_ab
max_len_ab max_len_ex
orient_acc orient_abl
orient_abl orient_acc
Remarks PROC_DESC
Shape_length Shape_Length Shape_Length Shape_Length Shape_Length
Shape_area Shape_Area Shape_Area Shape_Area Shape_Area
geom geom geom geom geom

By cross-referencing all the measured attributes in each glacial inventory, it enabled a
completeness of attributes was met and that as many attributes as possible were included. This
came to a total of 56 attributes for glaciers_variable. The other glacier table, glaciers_static, only
contains 10 attributes shown in Figure 2. The attributes for glaciers_static are as follows: the
23

glacier’s ID in this database, ID’s from the various glacial inventories, the glaciers name (if
applicable), and the ID of a parent icemass (if applicable).
The data in this project can be broken down into five categories, shown in Table 2.
Glaciers, Jurisdictional Boundaries, Glacial/Hydrological Regions, Submission Information, and
Attachments.
Table 2 Data Description
Dataset Content Data Format Spatial Display Data Source
California Glacial
Inventory
This will contain a
shapefile with
attributes about
each glacier.
Shapefile/GD
B
Point and
Polygon
GLIMS, RGI,
WGI, USGS,
Glaciers of the
American West
Drainage
Basin/Hydrology
Hydrology of
California
Shapefile/GD
B

Polygon USGS
Jurisdictional
Boundaries
(County, City,
Etc.)
Various
Jurisdictional
Boundaries within
California
Shapefile Polygon Census Bureau
Submission
Information
Information about
who/what/when
glacial data was
submitted.
Tables Table Glacial
Inventories
Attachments Imagery and
LIDAR of Glaciers
Raster,
Various File
Formats (pdf
etc.)
Varies Various
Figure 2 glacier_static Table
24

The next section will cover the identification of these features, how they were obtained, why
they were chosen, what they contain, and how they were integrated into the geodatabase.
3.1.2. Identification of Features
In addition to the two glacier features, there are four polygon feature classes and four
tables included in the geodatabase.
3.1.2.1. glac_regions (Polygon Feature Class)
This polygon shapefile was obtained through Glaciers of the American West and is a
polygon feature that outlines the glacial regions within California (Basagic 2011). The two
features shown in Figure 3 are the mountain ranges with areas that contain glaciers. The
boundaries of this feature closely match the current glacier extents and help to lead the user of
the web map more directly to the glaciers. This data was imported using the using the PostGIS
2.0 Shapefile and DBF Loader and Exporter tool.
3.1.2.2. wbdhu6 (Polygon Feature Class)
This dataset was obtained through the U.S. Geological Survey National Hydrography
Dataset. This the boundary data for the six-digit or third level hydrologic unit or basin for the
California hydrologic region. In Figure 4, a selection of attributes from five of the 24
hydrological basins that are in some part contained within California. This data was included to
Figure 3 glac_region Features
25

show the areas which a glacier could drain, but additional levels of the hydrography data set
were excluded because data is easily obtained and is unnecessary for many types of analysis.
This data was imported using the PostGIS 2.0 Shapefile and DBF Loader and Exporter tool.
3.1.2.3. ca_counties (Polygon Feature Class)
This dataset was obtained through the U.S. Census Bureau’s Tiger/Line
Shapefiles. This contains the adjusted boundaries of California counties. This feature was
included so that users such as policy makers can determine which glaciers fall within their
jurisdictional boundaries. Figure 5 below, shows a sample of attributes of the 58 counties within
California. This data was imported using the PostGIS 2.0 Shapefile and DBF Loader and
Exporter tool.
3.1.2.4. ca_places (Polygon Feature Class)
This dataset was obtained through the U.S. Census Bureau’s Tiger/Line Shapefiles.
ca_places includes incorporated cities as well as census designated-places (referred to hereafter
as CDP). A census-designated place is a population that is identified by the Census Bureau for
statistical purposes only but is currently an unincorporated community. While there are no cities
Figure 4 Example of wbdhu6 Feature Class Attributes
Figure 5 Example of ca_counties Feature Class Attributes
26

or CDPs that contain glaciers, there are cities and CDPs that are close to glaciers, both in the
Sierra Nevada and Cascade Ranges. This data was imported using the PostGIS 2.0 Shapefile and
DBF Loader and Exporter tool.
3.1.2.5. submission_info (Table)
This table was created to track submissions of data sources for glacial features so that in
the future changes can be updated and tracked over time. It includes attributes, seen in Figure 7,
such as file name, submission time, coordinate description, ID of the submitter who contributed
the data, a unique ID to track changes over time, etc. The initial information in this tables comes
from the five glacial inventories that were used to populate this database. Because the data
sources generally had a single analyst submitting the information at a single point in time, rather
than trying to extract the information from the sources attributes tables, the information in this
table was manually added into table using SQL in pgAdmin.
Figure 6 Example of ca_places Feature Class Attributes
Figure 7 submission_info Table Attributes
27

3.1.2.6. regional_center (Table)
Because multiple individuals from a single group can perform or submit analysis, the
regional_center table was created to track the groups in which the users belong. This table
contains the ID for the regional center, primary contact, and URLs of the regional center. This
table can reference itself if there are sub-groups within a primary group that need to be recorded.
Like the submission_info table above the information in this table was manually added into table
using SQL in pgAdmin.
3.1.2.7. people (Table)
This table contains general information on the persons who have submitted data to the
data sets that were used as a part of this project. The attributes, shown in Figure 9, contains
information such as name, address, URL, and phone numbers. This information is included for
multiple reasons, such as: to track who has updated or added information, track users’
permissions, and helps to get in contact with the submitter of an analysis in case of any
questions. Because of the initial low number of users to be added, the information was manually
added into table using SQL in pgAdmin.
Figure 8 regional_center Table Attributes
28

3.1.2.8. attachments (Table)
This table contains links to attachments that are linked to each glacier’s ID and has a
URL of where the attachment is hosted, attachment type, and date of attachment. This enables
the web interface of attachments pertaining to each glacier to be displayed by selecting an outline
or performing a query.
Figure 9 people Table Attributes
Figure 10 attachments Table Attributes
29

3.1.3. Choice of Software
The software that was used for this project is typical of many open source database GIS
projects. The only non-open source software is the ESRI ArcGIS suite of software. The final
output of this project is a geodatabase and a web map interface. The final output plus the author’s
familiarity with ESRI applications lead the choices of software used. That, along with the fact
that the data from this project’s database and web map should be preserved for use past the
lifetime of the project, lead to the combination of open-source and ESRI software.
3.1.3.1. Database Software
This project used pgAdmin to manage the spatial database. pgAdmin is the administrative
and development platform on the popular open-source database system PostgreSQL. It allows for
the storing and maintenance of spatial and non-spatial data. This software is well supported and
provides a long-term stable database solution.

Figure 11 Postgre SQL Connection in ArcGIS Pro. Source: ESRI 2019.
30

Amazon Web Service (AWS) provides a stable hosting platform for the geodatabase and
provides easy linkage between PostgreSQL and ArcGIS Pro. This is done simply by using the
Add Database Connection Function and then filling in required fields, see an example in Figure
11. The Instance box will contain the web address of the AWS instance server.
3.1.3.2. GIS Software
The GIS software used for this project was ArcGIS Pro. This software was used to create
and manage the spatial data along and create maps for the web map application.
3.1.3.3. Web Application Software
ArcGIS Online was used as the web application software. This software displays the web
map and feature services in internet browsers such as Google Chrome or Mozilla Firefox. Esri
and Amazon have created a workflow that allows for easy data connection between AWS,
PostgreSQL, and ArcGIS Pro. This ease of workflow allowed for a seamless deployment of the
web map.
3.2. Implementation of Geodatabase
3.2.1. Geodatabase structure
Having defined the features and tables above, along with their attributes, Figure 12 shows
a simplified ERD of the features classes and tables that make up the database. Additionally, all
the keys and relationships between the components of the database are displayed in Figure 12,
showing the overall structure of the database.

31

pgModeler was used to create and edit the database model. This allowed for the ability to
generate SQL based on the model built, and then use that SQL to create the tables in pgAdmin.
An important step in this process was to make sure that the primary, foreign, and unique keys
were identified to allow the relations between the different tables to ensure quality and that data
is not duplicated. Additionally, pgModeler can export the designed model to PostgreSQL. Once
the database model was designed and built, the AWS instance was set up and the database
connections were established.
3.2.2. AWS Set Up
To create a PostgreSQL database in AWS there were several steps that needed to be
followed. First, was to go into the AWS management console and select RDS (Relational
Database Service) under Database to open the Amazon RDS console. From there the chose
Figure 12 Geodatabase Simplified ERD
32

Create database, and the options presented in Error! Reference source not found. were chosen.
At this point the database instance was configured, many of the Instance specification options
were kept at the default, but DB instance class was changed to “db.t2.micro --- 1 vCPU, 1 GIB
RAM” Figure 14 shows the instance settings that were chosen, namely the name of the instance,
a master username, and a master password. The name, username, and password were later used
to create the database connections with the software used in this project.

Figure 13 Creating a PostgreSQL DB Instance in AWS
33

After the initial instance settings were configured, the advance settings had to be chosen.
Again, while most options were left on the default, a few items had to be changed because they
had to have the proper item selected to insure interoperability between the software and database.
Under Network & Security, public accessibility needed to be changed to ‘Yes’ and under
Database options a Database name had to be chosen the DB parameter group changed to
‘default.postgres11’ and ‘IAM DB authentication’ enabled. Which can be seen in Figure 15.
Once all the settings were configured the database could be created and the connections between
it and the software made.

Figure 14 DB Instance Settings
34

3.2.3. Database Connections
There were four connections that had to be established for this database: AWS to ArcGIS
Pro, AWS to pgAdmin, AWS to pgModeler, and AWS to PostGIS Shapefile Import/Export
Manager. Shown below in Figure 16 and Figure 17 are the screens for connection ArcGIS Pro
and pgAdmin to a database instance server. The process is relatively simple, all that needed to be
done was enter the instance or host address, port, username, and password. Creating the
connections allowed for importing, editing, and managing for the data on the AWS server.

Figure 15 AWS Database Options
Figure 16 Database Connection for AWS to ArcGIS Pro
35

3.2.4. Creation of SQL Tables from Source Data
Once the connections were established, the author was able to begin establishing the table
structure within the database. This was first done by using the SQL produced from pgModeler in
pgAdmin’s Query Tool to create the glaciers_static, glaciers_variable, attachments, people,
regional_center, and submission_info tables. The code was saved as a SQL file then copied into
the Query Tool and run, creating the table with the required keys, constraints, and dependencies.
Below is a sample of SQL code was used to create the attachments table. SQL code for the
remaining tables can be found in Appendix G SQL Code.
CREATE TABLE public.attachments (
attach_name text NOT NULL,
glacier_id character varying(20),
file_type character varying(4),
"URL" text,
adate date,
editor text,
author text,
description text,
glacier_id_glaciers_variable character varying(20),
CONSTRAINT attachments_pk PRIMARY KEY (attach_name)
Figure 17 Database Connection for AWS to pgAdmin
36

);
COMMENT ON COLUMN public.attachments.adate IS 'data of publication';
ALTER TABLE public.attachments OWNER TO cvanschoonhoven;

3.2.5. Importing Shapefiles
The shapefiles glac_regions, wbdhu6, ca_counties, and ca_places were then imported
into the geodatabase using the Shapefile and DBF Loader and Exporter Tool. This tool comes
with the standard PostGIS installation. To import files using this tool the author created a
connection to the AWS geodatabase. Figure 18, shows and example connection screen for the
shapefile loader. Once the shapefiles were imported, the constraints of each feature class needed
to be honored. This was done using the Query Tool in pgAdmin to run SQL code that altered the
tables based on the required constraints. Additionally, in pgAdmin the properties of each table
were examined to make sure the columns, constraints and other parameters matched those from
the ERD, and corrections were made as required. Once the database was designed and
implemented, the web map was created.
Figure 18 Example of PostGIS 2.0 Shapefile and DBF Loader and Exporter Tool
37

3.3. Web Map Creation
3.3.1. Domain Acquisition & Construction

In undertaking the creation of a web map, a domain was purchased and constructed. Web
access allows more people within the specified user groups to easily access the data that this
project presents. The author owns several domains, including mapping.cool which is currently
being used to host the web map at the subdomain california-glaciers at the following link:
mapping.cool/california-glaciers. This was done by editing the DNS record for the domain and
creating a new DNS record that points to the target URL of the ArcGIS Web App. An example
of this can be seen in Figure 19 for hover.com which is the authors domain registrar of choice.
The hosting of the data for the web map was done through AWS which has several tiers
including free tiers which will allow for a low-cost hosting of the project into the future.
Figure 19 Example of Creating a New DNS Record for Domains Registered at Hover.com
38

This project is also stored at GitHub, which is a free online storage repository for version
control, this GitHub repository holds the geodatabase, data, methods, maps, and layouts used for
this project, and can be found at the following link: https://github.com/krevee/california_glaciers.
This allows this project to be continued and iterated on even if the author is no longer able to
host the data and web map.
3.3.2. Web Application
The web application used to share this project was ArcGIS web maps and web map
application. These two applications allowed seamless integrations between AWS, pgAdmin, and
ArcGIS Pro. To integrate the data from the database into the web application the author had to
add the data from the geodatabase into ArcGIS Pro and then share each data layer as a web layer
in ArcGIS Pro. Once this was done the layers were added to a web map. Inside the web map the
author was able to symbolize and layout the map.
Once the data layers were added to the web map, the web map was then brought into the
Web AppBuilder for ArcGIS where the theme, layout, map, widgets, and attributes where able to
be configured to the desired parameters. Figure 20 shows the option to ‘Choose web map’ and
select from ‘My Content’ which web maps to add. The top left corner of Figure 20 also shows
the ‘Theme’ and ‘Widget’ tabs which contain various tools for configuring the Web App. A
simple template was chosen to allow for easy navigation by any user. Additionally, only a small
selection of tools, Select, Summary, and Measurement, were chosen. These tools were chosen for
their important but simple functionality in the initial iterations of the web map.
The next section covers the geoprocessing methods of the glacial data final geodatabase
design that allowed the final web map and GitHub repository to be created. Along with an
evaluation perspective on the final database and web map.
39

Figure 20 Selecting a Web Map in Web Application Builder for ArcGIS
40

Chapter 4 Results
The glacial geodatabase was not considered finalized until it was presented to a wider audience.
This was done through the web map and GitHub repository initially discussed in section three of
Chapter 3. This chapter presents the final geodatabase design, the final web map, and GitHub
repository. The final results of the work completed in this thesis are available through either the
web map or GitHub repository, which includes: a geodatabase, documents covering the
methodology used, a collection of other documents and source materials, and a number of maps
that may be used to share and visualize the data presented. All the glacial data used was acquired
from public sources and remains available to the public through the availability at the GitHub
repository.
4.1. Geodatabase Final Design
In the previous chapter, the attribute, features, design, and implementation of the
geodatabase was explained. Now that all of the parts of the geodatabase have been explained, the
final geodatabase design is shown in Figure 21. This shows an expanded ERD of the features
classes and tables that make up the database along with their corresponding attributes and
domain values. All the keys and relationships between the components of the database are
displayed in Figure 21, showing the complete structure of the database.

41

Figure 21 Final ERD
42

The ERD in Figure 21 shows how the tables and attributes of the tables are connected in
the geodatabase through primary and foreign keys, as well the unique values in each table. This
shows the linkage between the data tables and shows how each table relates and works together.
This was determined by the design and implementation that was discussed in the previous
chapter.
4.2. Final Web Map
A view of the final web map displaying glaciers near the June Lake area along with the
California places polygons is shown in Figure 22. The top right corner of the web map has five
layers that can be turned on or off, glaciers_variable, ca_counties, ca_places, wbhu6, and
glac_regions. The top right also has a Legend that can be expanded or contracted. The top left of
the web map contains various tools that can be used to navigate and search the web map,
including a search bar that can be used to search the feature layers as well as the ArcGIS World
Geocoding Service. The bottom of the web map shows a scale, the current location of the map
and sources.
The web map shows 5435 glacial features at various points in time, and contains 2670
unique glaciers, two glacial regions, 24 water basins, 58 counties, and 1552 census places. Each
of these layers’ attribute tables can be viewed, and item details can be brought up which includes
additional details, sources, and metadata.
43

4.3. GitHub Repository
In addition to the web map, the final result of the work completed in this thesis project
can be found in a GitHub Repository. GitHub is free online storage repository that provides
project hosting and version control which now holds the geodatabase, data, methods,
documentation, maps, and sources for this project. The GitHub account is krevee and the project
is california_glaciers and the contents of the project are summarized in Table 3.
Table 3 GitHub Repository Contents
Folder Names Content
Data Project data and source
data
ERD_Files ERD Images and dbm
files
GDB_Files Geodatabase files
Maps_Layout_Files Project maps and
layours
SQL Queries SQL queries and tasks
README.md Read me file
Figure 22 California Glaciers Web Map
44

To view the contents of a folder, one clicks on the desired folder name. For example, one
might choose the ‘Data’ folder to the display data and source data of the project. To download or
view the files in a repository, GitHub provides a large green button on the right-hand side shown
in Figure 23 California Glaciers GitHub Repository. The README file contains an explanation,
details, and versioning information on this project. A README is normally the first place to
look when beginning a project.

Figure 23 California Glaciers GitHub Repository
Repository
Name
Project
Folders
Download and
View Files
45

Chapter 5 Conclusion
As glaciers are changing rapidly, mapping glacial changes is becoming more and more
important. This database enables better monitoring and tracking of changes to glaciers in
California. By diving into existing glacial datasets and models and refining them, this thesis
shows that the goals outlined at the beginning of this project were met. This chapter evaluates the
project, explores ways it may be enhanced in the future, and discusses lessons learned
throughout.
5.1. Evaluation
There are many ways in which a database can be evaluated. It can be measured by a
variety of benchmarks such as depth of the information contained within, width or how many
topics the database covers, the accuracy of the data it holds, how recent the data it contains is, the
level of completeness of the stored data, the connectivity and design quality, the usability, and
the cost to build, host, and manage the database. Many of these topics are discussed earlier in this
thesis when establishing the parameters and goals of the project. Cost is not covered because
there was little cost as the software licenses and data were freely available or made free through
student licenses and only the fee was for hosting the AWS. This section will be an assessment of
the database level of completeness.
When examining the completeness of a glacial inventory database, it can be complete in a
variety of manners such are all of the glacier in the studied region contained in the database or is
there a completeness of attributes about each glacier. Though, because of the nature of a glacier
they are constantly changing in size, shape, etc. which makes a database never truly complete. In
addition, because of climate change, many glaciers are disappearing which means there may be
less glaciers than what an inventory shows.
46

This project includes all publicly available glacial data for California, so in terms of
number of glaciers tracked it is complete in that regard. This project also attempted to have a
completeness of attributes by including and refining existing glacial data. Though in terms of
temporal this coverage this project is more complete than other glacial inventories, it is still
lacking because of this type of data. The most recent data collection was from 2017 by the NHD
which means it has been over 2 years since the glaciers have been inventoried. In the time period
between data collection, many changes to or loss of glaciers may go unrecorded. One
improvement to this lack of data could come from collecting glacial changes via volunteered
public information. Of course, this data would need to be verified by an authority, however it
could aid in more rapid collection and analysis of changes to the glaciers. A multi-source
approach could provide an improvement over the authoritative-only approach which currently
has long periods between updates. This is discussed further in the next section.
5.2. Future Work
There should be three main areas of focus were this project to continue further. The first
would be to conduct a survey with potential users to help improve use cases and functionality.
Next would be additional web map features and functionality. The last area of improvement
would be an increase of the scope to a national implementation. These improvements would help
to strengthen this project for not only California, but also the entire country.
5.2.1. Web GIS Evaluation Survey
The survey would be constructed to help determine the effectiveness of the web map.
This survey would be helpful in connecting the demographics and background of users with how
they interact with the web map. It could also be used to determine if the web map was useful to
the various user groups outlined in Chapter 2. It could also allow for respondents to suggest ways
47

in which the web map could be improved. This could help refine the user interface and user
experience. It could also help with additional tool and widget choices that would be the most
useful. The survey could use a variety of questions types such as multiple choice, short answers,
and scale-based in its evaluation. The results, even from a small sample, would help further
iterations of the web map be more usable to the target audience.
5.2.2. Additional Web Map Functionality
There are several improvements that would help the functionality of the web map. First
would be the addition of analysis widgets in the web map. There are currently some analysis
tools available in Web AppBuilder for ArcGIS but the testing and implementation was too
prohibitive time-wise to include them in the current project. One such tool would be the Time
Slider widget; this tool enables a better view of the temporal elements of the map and could play
an animation that shows how the data changes over time. To add this widget would require
reformatting of the date/time elements of the 5435 glacier features, which vary in format from
each data source. One method to automate the reformatting would be using a python script to
help simply the task.
Another improvement would be the addition of the ability for users to submit data to this
project. Currently, the author is the only one who can add, remove, or edit data, but since so
many others are studying glaciers it would be good to have the ability for others to submit new
data to this project. This would be an extremely complicated addition that would need serious
vetting so that the integrity of the data would be maintained. This would likely need to be done
through a different application than Web AppBuilder and brought back into the model after the
data was evaluated.
48

5.2.3. National Implementation
Another improvement would be to implement the project on a national scale. A unified
geodatabase of all glaciers in the United States would be a complex project that would involve
many interest groups and municipalities. For a project of such a large scope to succeed, all the
groups would need to agree on the database design, structure, and implementation, as well as
deliverable milestones. These were all things that were either determined by the author or part of
the constraints of this thesis project, but for a public project those tasks would be navigated and
negotiated by many parties. There would be many decisions as what attributes to include and
what their definitions are, for example what qualifies as “surging” for a glacier, in addition to
others. There are many important choices to be made before a glacial geodatabase could be
implemented on a national level. Once a consensus is reached as to the design, structure,
implementation, and data collection for the geodatabase, data from projects such as this one
could be incorporated into it. It would then be the responsibility of municipalities, other
government agencies, private institutions and even citizen scientists to contribute. This type of
collaboration tactic is like the U.S. Census Bureau’s Community TIGER portal discussed by
Otto (2015) for the National Address Database.
5.3. Final Thoughts
This project set out to meet three criteria. The first was to develop the database in such a
way that it can store glacial data in such a way that meets the current requirements and standards
of existing global glacial databases. Next was to archive and store the large amounts of glacial
data collected by USGS, GLIMS, RGI, WGI, and GAW. The third was to make the data easily
accessible through a web map and Github repository. As a result of the work completed in this
thesis, these goals have been met. This project made a spatiotemporal geodatabase for California
49

glaciers that is comprehensive, easy to access and use, and helps to highlight the changes to the
glaciers over time. By researching existing databases and datasets, design was improved, and a
greater temporal resolution was stored and archived. But this is only a first step, and the
institutions responsible for glacier stewardship need to take up the task of collecting and
managing glacial data and building upon the framework that this project presents.
As pointed out in the introduction to this thesis, glaciers can provide a multitude of
benefits and hazards to people and the environment. Improved glacial monitoring using glacial
databases can help to track the changes over time. As well as study those changes, we can
implement policies that can help protect this valuable natural resource.

50

References
Aniya, Masamu, Hiroaki Sato, R. E. N. J. I. Naruse, P. E. D. R. O. Skvarca, and G. Casassa.
1996. "The use of satellite and airborne imagery to inventory outlet glaciers of the Southern
Patagonia Icefield, South America." Photogrammetric Engineering and Remote
Sensing 62, no. 12 (1996): 1361-1369.
Basagic, Hassan Jules, and A. G. Fountain. 2011. "Quantifying 20th century glacier change in
the Sierra Nevada, California." Arctic, Antarctic, and Alpine Research 43, no. 3 (2011):
317-330.
Basagic, Hassan Jules. 2011. "Glaciers of California" Glaciers of the American West. Accessed
January 2019. http://glaciers.us/Glaciers-California.
Fountain, Andrew G. (submitter); Fountain, Andrew G. (analyst). 2006. GLIMS Glacier
Database. Boulder, CO. National Snow and Ice Data Center.
http://dx.doi.org/10.7265/N5V98602.
ESRI. 2018. “Connect to PostgreSQL from ArcGIS.” ArcGIS Pro Help. Accessed March 2019.
https://pro.arcgis.com/en/pro-app/help/data/databases/connect-postgresql.htm.
GLIMS, and NSIDC. "The GLIMS Glacier Viewer." Global Land Ice Measurements from Space
Glacier Database, 2005. Accessed January 2019. http://glims.colorado.edu/glacierdata/.
Lindsey, Rebecca. 2020. “Cimate Change: Glacier Mass Balance”. NOAA Climate.gov.
Accessed April 2020. https://www.climate.gov/news-features/understanding-
climate/climate-change-glacier-mass-balance.
Microsoft. 2019. “Database design basics.” Accessed January 2019.
https://support.office.com/en-US/article/Database-design-basics-EB2159CF-1E30-401A-
8084-BD4F9C9CA1F5.
National Parks Service. 2017. "Views of the National Parks Monitoring Glaciers." Views of the
National Park. Accessed January 2019.
https://www.nature.nps.gov/views/KCs/Glaciers/HTML/ET_Monitoring.htm.
Nigrelli, Guido, M. Chiarle, A. Nuzzi, L. Perotti, G. Torta, and M. Giardino. 2013. "A web-
based, relational database for studying glaciers in the Italian Alps." Computers &
geosciences 51 (2013): 101-107.
O'Sullivan, Feargus, and CityLab. 2017"The Italian Border Is Melting." CityLab. January 13,
2017. Accessed January 2019. https://www.citylab.com/design/2017/01/when-borders-
melt/513077/.
51

Otto, Greg. 2015. Fed Scoop. “Coming Soon: An open database of every U.S. address.”
September 28, 2015. Accessed December 2019. http://fedscoop.com/national-address-
database.
Pfeffer, W. Tad, Anthony A. Arendt, Andrew Bliss, Tobias Bolch, J. Graham Cogley, Alex S.
Gardner, Jon-Ove Hagen, et al. 2014. “The Randolph Glacier Inventory: a Globally
Complete Inventory of Glaciers.” Journal of Glaciology 60, no. 221 (2014): 537–52.
doi:10.3189/2014JoG13J176.
Raup, B.H.; A. Racoviteanu; S.J.S. Khalsa; C. Helm; R. Armstrong; Y. Arnaud. 2007. "The
GLIMS Geospatial Glacier Database: A New Tool for Studying Glacier Change". Global
and Planetary Change 56:101--110. doi:10.1016/j.gloplacha.2006.07.018.
RGI Consortium. 2017. “Randolph Glacier Inventory (RGI) – A Dataset of Global Glacier
Outlines: Version 6.0.” Technical Report, Global Land Ice Measurements from Space,
Boulder, Colorado, USA. Digital Media. DOI: https://doi.org/10.7265/N5-RGI-60.
Singh, A. J., Balvir Singh Thakur, Rajesh Chauhan, and Vikas Sharma. 2011. "Comprehensive
Framework for quality Database Applications." Accessed January 2019.
http://ijoes.vidyapublications.com/paper/SI2/52.pdf.
United States Geological Survey. 2018. "National Hydrography Dataset." Accessed January
2019. http://nhd.usgs.gov.
WGMS, and National Snow and Ice Data Center. 1999, updated 2012. "World Glacier Inventory,
Version 1." Boulder, Colorado USA. NSIDC: National Snow and Ice Data Center.
Accessed March 2019. https://doi.org/10.7265/N5/NSIDC-WGI-2012-02.
Wachowicz, Monica. 2014. Object-oriented design for temporal GIS. CRC Press.
Yeung, Albert KW, and G. Brent Hall. 2007. Spatial database systems: Design, implementation
and project management. Vol. 87. Springer Science & Business Media.

52

Appendix A List of Attributes from GLIMS
Field name Description
LINE_TYPE Category of line segment. Possible values include: "glac_bound" (glacier
boundary), "intrnl_rock" (internal rock outcrop, or nunatak), "snowline",
"centerline" (center flowline of the glacier).
ANLYS_ID The ID assigned within GLIMS for a particular outline of a glacier at a
particular time.
GLAC_ID The GLIMS glacier ID
ANLYS_TIME Representative time the analysis was carried out.
AREA Map-plane area of the glacier, as provided by the analyst, km2.
DB_AREA Map-plane area of the glacier, as calculated within the GLIMS Glacier
Database, km2.
WIDTH Representative width of the glacier, meters.
LENGTH Representative length of the glacier, meters.
PRIMECLASS Primary WGMS classification of the glacier.
MIN_ELEV Elevation of the lowest part of the glacier, in meters above sea level.
MEAN_ELEV Mean elevation of the glacier, in meters above sea level.
MAX_ELEV Elevation of the highest part of the glacier, in meters above sea level.
SRC_DATE The as-of date for the outline. Usually the acquisition date of the image.
REC_STATUS Record status (should always be "okay" for downloaded data).
GLAC_NAME Glacier name.
WGMS_ID Glacier ID assigned by the World Glacier Monitoring Service.
LOCAL_ID An ID assigned by the GLIMS Regional Center or institution that supplied
the data.
SUBM_ID ID assigned by GLIMS to the entire data submission.
RELEASE_OK Date after which the data is released.
PROC_DESC Description of the processing done to create the glacier outlines.
SUBMIT_SUR Surname of the person submitting the data.
SUBMIT_GIV Given names of the person submitting the data.
SUBMIT_AFF Affiliation of the person submitting the data.
SUBMIT_URL URL for the submitting institution.
SUBMIT_CCO Country code for the submitting institution.
ANLST_SURN Surname of the analyst.
ANLST_GIVN Given names of the analyst.
ANLST_AFFL Affiliation of the analyst.
ANLST_URL URL related to the analyst.
ANLST_CCOD Country code for the analyst.
CHIEF_SURN Surname of the chief of the Regional Center.
CHIEF_GIVN Given names of the chief of the Regional Center.
CHIEF_AFFL Affiliation of the chief of the Regional Center.
RC_URL URL for the Regional Center.
RC_CCODE Country code for the Regional Center.
RC_ID GLIMS ID for the Regional Center.
53

GEOG_AREA Geographic region covered by the Regional Center.

54

Appendix B List of Attributes from RGI
Field Name Description
RGIId A 14-character identifier of the form RGIvv-rr.nnnnn, where vv is the
version number, rr is the first order region number and nnnnn is an
arbitrary identifying code that is unique within the region. These codes
were assigned as sequential positive integers at the first-order (not second-
order) level, but they should not be assumed to be sequential numbers, or
even to be numbers. In general, the identifying code of each glacier,
nnnnn, should not be expected to be the same in different RGI versions.
GLIMSId A unique 14-character identifier in the GLIMS format GxxxxxxEyyyyyΘ,
where xxxxxx is longitude east of the Greenwich meridian in
millidegrees, yyyyy is north or south latitude in millidegrees, and Θ is N
or S depending on the hemisphere. The coordinates of GLIMSId agree
with CenLon and CenLat (see below).
BgnDate,EndDate The date of the source from which the outline was taken, in the form
yyyymmdd, with missing dates represented by -9999999. When a single
date is known, it is assigned to BgnDate. If only a year is given, mmdd is
set to 9999. Only when the source provides a range of dates is EndDate
not missing, and in this case the two codes together give the date range. In
version 6.0, 98% of glaciers (by area; 99% by number) have date
information (Figure 2). Figure 3 shows date-range spans for glaciers with
date ranges. 85% of the ranges are shorter than four years. Many of the
ranges of three years (36-47 months) are from the 1999–2003 period
between the launch of Landsat 7 and the failure of the scan-line corrector
of its ETM+ sensor.

CenLon, CenLat Longitude and latitude, in degrees, of a single point representing the
location of the glacier. These coordinates agree with those in GLIMSId.

O1Region,
O2Region
The codes of the first-order and second-order regions (Table 2) to which
the glacier belongs.

Area Area of the glacier in km2, calculated in cartesian coordinates on a
cylindrical equal-area projection of the authalic sphere of the WGS84
ellipsoid, or, for nominal glaciers, accepted from the source inventory.
Zmin, Zmax Minimum and maximum elevation (m above sea level) of the glacier,
obtained in most cases directly from a DEM covering the glacier. For
most of the nominal glaciers Zmin and Zmax were taken from the parent
inventory, WGI or WGI-XF.

Zmed Median elevation (m) of the glacier, chosen by sorting the elevations of
the DEM cells covering the glacier and recording the 50th percentile of
their cumulative frequency distribution. The mean elevation of the glacier
55

is not provided explicitly in the RGI but can be recovered with fair
accuracy from the hypsometric list.
Slope Mean slope of the glacier surface (deg), obtained by averaging single-cell
slopes from the DEM.
Aspect The aspect (orientation) of the glacier surface (deg) is presented as an
integer azimuth relative to 0° at due north. The aspect sines and cosines of
each of the glacier’s DEM grid cells are summed and the mean aspect is
calculated as the arctangent of the quotient of the two sums.
Lmax Length (m) of the longest surface flowline of the glacier. The length is
measured with the algorithm of Machguth and Huss (2014). Briefly,
points on the glacier outline at elevations above Zmed are selected as
candidate starting points and the flowline emerging from each candidate is
propagated by choosing successive DEM cells according to an objectively
weighted blend of the criteria of steepest descent and greatest distance
from the glacier margin. The latter criterion can be understood as
favouring “centrality”, especially on glacier tongues. The longest of the
resulting lines is chosen as the glacier’s centreline.
Status The Status attribute flags glaciers whose outlines await subdivision or are
nominal circles:
Value Status
0 Glacier or ice cap
1 Glacier complex
2 Nominal glacier
9 Not assigned

Connect The Connect attribute records the connectivity level developed by Rastner
et al. (2012) for glaciers in Greenland. Glaciers that are physically
detached from the ice sheet have a connectivity level of 0. A glacier is
weakly connected if it is in contact with the ice sheet only at a well-
defined divide in the accumulation zone, and strongly connected if the
divide is indistinct in the accumulation zone and/or confluent with an ice-
sheet outlet in the ablation zone:
Value Connect
0 No connection
1 Weak connection
2 Strong connection
9 Not assigned

Form The Form attribute contains information on the form of the ice body:
Value Form
0 Glacier
1 Ice Cap
2 Perennial snowfield
3 Seasonal snowfield
9 Not assigned

TermType The TermType attribute contains information on terminus type. Lake-
terminating glaciers are identified as such only in Alaska, the Southern
Andes and Antarctica; elsewhere they currently have TermType equal to
56

0. Where more than one value applies to a given terminus, the dominant
type as interpreted from satellite imagery is chosen.
Value TerminusType
0 Land-terminating
1 Marine-terminating
2 Lake-terminating
3 Dry calving
4 Regenerated
5 Shelf-terminating
9 Not assigned

Surging The Surging attribute contains information on evidence for surging and is
based on the inventory of Sevestre and Benn (2015). Value Surging 0 1 2
Probable 3 Observed 9 Not assigned
Value Surging
0 No evidence
1 Possible
2 Probable
3 Observed
9 Not assigned

Linkages The Linkages attribute indicates whether the ancillary file
00_RGI60_LINKS.CSV contains a link to mass-balance measurements in
the Fluctuations of Glaciers database. To date 232 linkages have been
identified.
Value Linkages
0 Not in FG
1 In FG
9 Not assigned

Name Name of the glacier, or the WGI or WGI-XF id code (modified after
Müller et al., 1978) if available. Many glaciers do not have names, and
coverage of those that do is incomplete. Of the 215,547 glaciers in RGI
6.0, 46,770 have information in their Name field, although for many the
content is actually an id code.

In version 5.0 the contents of the fields RGIFlag and GlacType were rearranged (see
below). In version 4.0 six topographic attributes were added to the main shapefile entry for each
glacier, with each glacier having a hypsometric list stored in a separate regional file. Each glacier
had 12 data attributes in version 3.2 and 10 in version 2.0.

57

Appendix C List of Attributes from WGI
WGI_GLACIER_ID
Description: A 12-character unique glacier identifier. The ID number is assigned to the glacier
as defined by the WGMS convention that forms the glacier ID number by combining the five
following elements:
2-character political unit
1-digit continent code
4-character drainage code
2-digit free position code
3-digit local glacier code
No Data Value: Mandatory field; WGMS has replaced any missing digit with zero.

POLITICAL_UNIT
Description: 2-character abbreviation for the name of the country or territory in which the
glacier is located. These codes are ISO3166 country codes from the ISO Maintenance Agency
for Country Codes. Table 1 contains the country codes used in the WGI.
No Data Value: Mandatory field

CONTINENT_CODE
Description: 1-digit code for the continent in which the glacier is located. The six continent
codes used in the database are listed in Table 2.
No Data Value: Mandatory field

DRAINAGE_CODE
Description: 4-character drainage basin code in which the glacier is located. "The study area
must then be divided and subdivided into drainage basins of first-order (A-Z), second-order (0-
9), third-order (0-9) and, if necessary, fourth-order (A-Z), see supplement Identification" (Müller
et al. 1977). According to WGMS 1989 the fourth-order digit of the drainage code may also be
(0-9).
No Data Value: Mandatory field; WGMS has replaced any missing digit with zero.

FREE_POSITION_CODE
Description: 2-digit identification numbers freely chosen by the investigator, usually used as
logical continuation of the DRAINAGE_CODE.
No Data Value: Mandatory field; WGMS has replaced any missing digit with zero.

LOCAL_GLACIER_CODE
Description: 3-digit local glacier code freely chosen by the investigator, usually used as logical
continuation of the DRAINAGE_CODE and FREE_POSITION_CODE
No Data Value: Mandatory field; WGMS has replaced any missing digit with zero.

GLACIER_NAME
Description: 30-character name of the glacier. "If a name is too long a meaningful abbreviation
of it should be entered. The spelling of the name must be in the Latin alphabet and may consist
only of the following characters: A B C D E F G H I J K L M N O P Q R S T U V W X Y Z"
58

(Müller et al. 1977). Note: If necessary, the name can be abbreviated; in which case the full name
is given in the REMARKS field.
No Data Value: Null

LAT
Description: The latitude of the glacier in decimal degrees North or South; up to 7 digits.
Positive values indicate the Northern Hemisphere and negative values indicate the Southern
Hemisphere. Latitude is given to a maximum precision of 4 decimal places."The point on the
glacier whose coordinates are given should be in the upper part of the ablation area, in the main
stream and sufficiently high so as not to be lost if the glacier retreats" (Müller et al. 1977).
No Data Value: Mandatory field

LON
Description: The longitude of the glacier in decimal degrees East or West; up to 7 digits.
Positive values indicate east of the zero meridian and negative values indicate west of the zero
meridian. Longitude is given to a maximum precision of 4 decimal places.
No Data Value: Mandatory field

EASTING
Description: Easting of local coordinate in UTM or other nationally determined format, up to 9
digits. Format is described in the COORDINATE_DESCRIPTION field.
No Data Value: Null

NORTHING
Description: Northing of local coordinates in UTM or other nationally determined format, up to
9 digits. Format is described in the COORDINATE_DESCRIPTION field.
No Data Value: Null

COORDINATE_DESCRIPTION
Description: Datum and projection or type of other formats can be given here (UTM zone, name
of coordinate system, etc.), up to 50 characters.
No Data Value: Null

NUM_BASINS
Description: The number of basins a glacier drains into, 1-digit integer. According to Müller et
al (1977), "An ice mass will often drain into several drainage basins (treated as separate units of
the identification code) but cannot be split into separate units. The total number of drainage
basins should be given in this field, e.g. 1 for one drainage basin. For identification purposes,
however, the ice mass should be assigned to the drainage basin which contains the largest portion
of the surface area."
No Data Value: Null

TOPO_YEAR
Description: The 4-digit year of the topographic map used for measurements of glacier
elevation. Note: If more than one topographic map was used, the most relevant year is recorded
in this field; and the others used are recorded in the REMARKS field.
59

No Data Value: Null

TOPO_SCALE
Description: The scale of the topographic map used for measurements of glacier parameters, up
to 7 digits. The values in this field are filled in as the reciprocal of the scale (1:25000 for the
example below). Note: If more than one topographic map was used, the most relevant scale is
recorded here; and the others used are recorded in the REMARKS field.
No Data Value: Null

PHOTO_YEAR
Description: The 4-digit year of the photograph used for measurements of glacier
parameters. Note: If more than one photograph were used, the most relevant year is recorded
here; and the others used are recorded in the REMARKS field. In general, the glaciers outlines;
and hence, the values for area and length; were determined from aerial photographs, so we
recommend using the PHOTO_YEAR for glacier area values. However, the elevation
information usually comes from topographic maps, so TOPO_YEAR should be used for
elevation values.
No Data Value: Null

MAX_ELEV
Description: Maximum elevation of the highest point of the glacier in meters above sea level, up
to 4 digits.
No Data Value: Null

MEAN_ELEV
Description: The mean elevation is the altitude of the contour line, in meters above sea level,
that halves the area of the glacier, up to 4 digits.
No Data Value: Null

MIN_ELEV
Description: The minimum elevation of the lowest point of the glacier in meters above sea level,
up to 4 digits.
No Data Value: Null

MIN_ELEV_EXP
Description: Minimum elevation exposed is the altitude of the lowest point of the total surface
area of the glacier, in meters above sea level, that is not covered with coarse stone material, up to
4 digits.
No Data Value: Null

MEAN_ELEV_ACC
Description: Mean elevation accumulation is the altitude of the contour line, in meters above sea
level, that halves the accumulation area of the glacier, up to 4 digits.
No Data Value: Null

MEAN_ELEV_ABL
60

Description: Mean elevation ablation is the altitude of the contour line, in meters above sea
level, that halves the ablation area of the glacier, up to 4 digits.
No Data Value: Null

PRIMARY_CLASS
Description: A 1-digit code that describes the primary classification of the glacier. The codes
are described in Table below.
No Data Value: Null
Code Name Description
0 Miscellaneous Any type not listed below.
1 Continental
Ice Sheet
Inundates areas of continental size.
2 Ice Field Ice masses of the sheet or blanket type with a thickness that is
insufficient to obscure the subsurface topography.
3 Ice Cap Dome-shaped ice masses with radial flow.
4 Outlet Glacier Drains an ice sheet, ice field, or ice cap, usually of valley glacier form;
the catchment area may not be easily defined.
5 Valley
Glacier
Flows down a valley; the catchment area is well defined.
6 Mountain
Glacier
Cirque, niche type, crater type, or hanging glacier; also includes ice
aprons and groups of small units.
7 Glacieret and
Snowfield
Small ice masses of indefinite shape in hollows, river beds, or on
protected slopes that have developed from snow drift, avalanches,
and/or particularly heavy accumulation in certain years. Usually no
marked flow pattern is visible; and it has been in existence for at least
two consecutive years.
8 Ice Shelf Floating ice sheet of considerable thickness attached to a coast
nourished by a glacier or glaciers; snow accumulation on its surface or
bottom freezing.
9 Rock Glacier Lava-stream-like debris mass containing ice in several possible forms
and moving slowly downslope.

FORM
Description: A 1-digit code that describes the form of the glacier. Table below describes the
glacier form codes.
No Data Value: Null
Code Name Description
0 Miscellaneous Any type not listed below.
61

1 Compound
Basins
Two or more individual valley glaciers issuing from tributary valleys
and coalescing (Fig. 1a).
2 Compound
Basin
Two or more individual accumulation basins feeding one glacier
system (Fig. 1b).
3 Simple Basin Single accumulation area (Fig. 1c).
4 Cirque Occupies a separate, rounded, steep-walled recess which has formed
on a mountain side (Fig. 1d).
5 Niche Small glacier in a V-shaped gully or depression on a mountain slope
(Fig. 1e); generally more common than genetically further-developed
cirque glacier.
6 Crater Occurring in extinct or dormant volcanic craters.
7 Ice Apron Irregular, usually thin ice mass which adheres to mountain slopes or
ridges.
8 Group A number of similar ice masses occurring in close proximity to one
another but are too small to be assessed individually.
9 Remnant Inactive, usually small ice masses left by a receding glacier.

FRONTAL_CHAR
Description: A 1-digit code that describes the frontal characteristics of the glacier. Table below
lists the frontal characteristic codes.
No Data Value: Null
Code Name Description
0 Miscellaneous Any type not listed below.
1 Piedmont Ice field formed on a lowland area by lateral expansion of one or
coalescence of several glaciers (Fig. 2a, 2b).
2 Expanded
Foot
Lobe or fan formed where the lower portion of the glacier leaves the
confining wall of a valley and extends on to a less restricted and more
level surface (Fig. 2c).
3 Lobed Part of an ice sheet or ice cap, disqualified as an outlet glacier (Fig. 2d).
4 Calving Terminus of a glacier sufficiently extending into sea or lake water to
produce icebergs; includes- for this inventory- dry land ice calving
which would be recognizable from the "lowest glacier elevation."
5 Confluent Coalescing, non-contributing (Fig. 2e).
6 Irregular, mainly clean ice (mountain or valley glaciers).
7 Irregular, mainly debris-covered (mountain or valley glaciers).
62

8 Single lobe, mainly clean ice (mountain or valley glaciers).
9 Single lobe, mainly debris-covered (mountain or valley glaciers).

LONGI_PROFILE
Description: A 1-digit code that describes the longitudinal profile of the glacier. Table below
describes the codes.
No Data Value: Null
Code Name Description
0 Miscellaneous Any type not listed below.
1 Even/Regular Includes the regular or slightly irregular and stepped longitudinal
profile.
2 Hanging Perched on a steep mountain side, or in some cases issuing from a steep
hanging valley.
3 Cascading Descending in a series of marked steps with some crevasses and
sÃ©racs.
4 Ice Fall A glacier with a considerable drop in the longitudinal profile at one
point causing heavily broken surface.
5 Interrupted Glacier that breaks off over a cliff and reconstitutes below.

SOURCE_NOURISH
Description: 1-digit code that describes the source of nourishment for the glacier. Table below
lists the source nourishment codes.
No Data Value: Null
Code Name
0 Unknown
1 Snow
2 Avalanches
3 Superimposed ice

TONGUE_ACTIVITY
Description: A 1-digit code that describes the activity of the tongue of the glacier. Table below
lists the tongue activity codes.
No Data Value: Null
Code Name
0 Uncertain
1 Marked retreat
63

2 Slight retreat
3 Stationary
4 Slight advance
5 Marked advance
6 Possible surge
7 Known surge
8 Oscillating

MORAINES1
Description: 1-digit code that refers to moraines in contact with the present-day glacier. Table
below describes the moraine codes.
No Data Value: Null
Code Name
0 No moraines
1 Terminal moraines
2 Lateral and/or medial moraine
3 Push moraine
4 Combination of 1 and 2
5 Combination of 1 and 3
6 Combination of 2 and 3
7 Combination of 1, 2, and 3
8 Debris, uncertain if morainic
9 Moraines, type uncertain or not listed

MORAINES2
Description: A 1-digit code that refers to moraines farther downstream of the glacier. Table
above describes the moraine codes.
No Data Value: Null

PERIOD_ACTIVITY_START
Description: 4-digit start year of the period for which the tongue activity was assessed. Note: If
the period for which the tongue activity was assessed is shorter than one year, this year will be
recorded in both fields PERIOD_ACTIVITY_START and PERIOD_ACTIVITY_END.
No Data Value: Null

PERIOD_ACTIVITY_END
64

Description: 4-digit end year of the period for which the tongue activity was assessed. Note: If
the period for which the tongue activity was assessed is shorter than one year, this year will be
recorded in both fields PERIOD_ACTIVITY_START and PERIOD_ACTIVITY_END.
No Data Value: Null

SNOW_LINE_ELEV
Description: Altitude of the snow line of the glacier in meters above sea level, up to 4-digits.
Note: The glacier data from the former Soviet Union often uses an estimation technique to
calculate the snowline. The type of technique used is recorded in the REMARKS field.
No Data Value: Null

SNOW_LINE_ACY
Description: 1-digit snow line accuracy rating. Table below lists the rating values.
No Data Value: Null
Rating Accuracy (meters)
1 0 - 25
2 25 - 50
3 50 - 100
4 100 - 200
5 > 200

SNOW_LINE_DATE
Description: 8-digit date of observation of the snowline of the form YYYYMMDD where
YYYY is the 4-digit year, MM is the 2-digit month, and DD is the 2-digit day of month. If part
or all of the date is missing, that missing parts are filled with 9's. Note: Snow line elevation is the
altitude of the transient snowline at the end of the ablation season or, in most cases and for
practical reasons, at the time the photograph was taken.
No Data Value: 99999999

MEAN_DEPTH
Description: The physical depth of the glacier in meters, up to 4 digits. Note I: Mean depth is
only be given if actually measured (for instance by drilling or radio-echo soundings). Note II:
Many of the values in this field were estimated using a thickness-area relation (Müller et al. 1997
and Müller et al. 1976).
No Data Value: Null

DEPTH_ACY
Description: 1-digit depth accuracy rating. Table below lists the depth accuracy ratings and their
values.
No Data Value: Null
Rating Accuracy (%)
1 0 - 5
65

2 5 - 10
3 10 - 20
4 20 - 30
5 > 30

TOTAL_AREA
Description: The total area of the glacier in a horizontal projection in square kilometers, up to 6
digits.
No Data Value: Null

AREA_ACY
Description: 1-digit area accuracy rating of the total area. Table below lists the area accuracy
ratings and their values.
No Data Value: Null
Rating Accuracy (%)
1 0 - 5
2 5 - 10
3 10 - 15
4 15 - 30
5 > 30

AREA_IN_STATE
Description: The total area of the glacier that resides in the political state concerned in a
horizontal projection in square kilometers, up to 6 digits.
No Data Value: Null

AREA_EXP
Description: The area of the exposed ice of the glacier in a horizontal projection in square
kilometers, up to 6 digits
No Data Value: Null

MEAN_WIDTH
Description: The mean width of the glacier in a horizontal projection in kilometers, up to 4
digits.
No Data Value: Null

MEAN_LENGTH
Description: Mean length of the glacier in a horizontal projection in kilometers, up to 4 digits.
No Data Value: Null

66

MAX_LENGTH
Description: Maximum length of the glacier in kilometers measured along the most important
flowline in a horizontal projection, up to 4 digits.
No Data Value: Null

MAX_LENGTH_EXP
Description: Maximum length, in kilometers, of the exposed ice of the glacier in a horizontal
projection, up to 4 digits.
No Data Value: Null

MAX_LENGTH_ABL
Description: Maximum length, in kilometers, of the ablation area of the glacier in a horizontal
projection, up to 4 digits.
No Data Value: Null

ORIENTATION_ACC
Description: The 1- to 2-character main orientation of the accumulation area using the 8 cardinal
points: N, NW, W, SW, S, SE, E, and NE.
No Data Value: Null

ORIENTATION_ABL
Description:The 1- to 2-character main orientation of the ablation area using the 8 cardinal
points: N, NW, W, SW, S, SE, E, and NE.
No Data Value: Null

DATA_CONTRIBUTOR
Description: The institution or persons who contributed the data to NSIDC, up to 255
characters. For full references see the Data Contributors Table.
No Data Value: Mandatory field

REMARKS
Description: Any important information or comments not included in the other fields above are
given here, up to 255 characters.
No Data Value: Null

67

Appendix D List of Attributes from NHD Waterbody
Field Name Definition Comments
Permanent Identifier Global ID and GUID data types store registry style
strings consisting of 36 characters enclosed in curly
brackets. These strings uniquely identify a feature
or table row within a geodatabase and across
geodatabases. This is how features are tracked in
one-way and two-way geodatabase replication.

FDate Date of last feature modification.
Resolution Source resolution. Currently NHD is available as
separate resolutions. Plans are to develop a single-
resolution database holding the highest resolution
data with tools to allow for generalization
Domain of values:
Local >1:12,000
High 1:24,000/12,000
Medium 1:100,000
GNIS_ID Unique identifier assigned by GNIS, length 10. GNIS_ID = “null” if
no name associated
with the feature
GNIS_Name Proper name, specific term, or expression by which
a particular geographic entity is known, length 65.
GNIS_Name = “null”
if no name associated
with the feature
AreaSqKm Area of areal feature based on Albers Equal Area,
length 8.
Computed
Elevation The vertical distance from a given datum. Stage of the water
elevation is encoded
in the FCode.
ReachCode Unique identifier composed of two parts. The first
eight digits is the subbasin code as defined by FIPS
103. The next six digits are randomly assigned,
sequential numbers that are unique within a
subbasin, length 14.
Required for all
NHDFlowlines.
NHDWaterbody and
NHDPoint feature
classes allow reach
codes but does not
require them. Ice
Mass and Playa do
not have reachcodes.
WBArea_Permanent_
Identifier
Global ID and GUID data types store registry style
strings consisting of 36 characters enclosed in curly
brackets. These strings uniquely identify a feature
or table row within a geodatabase and across
geodatabases. This is how features are tracked in
one-way and two-way geodatabase replication.

FType Three-digit integer value; unique identifier of a
feature type.

FCode Five-digit integer value; composed of the feature
type and combinations of characteristics and values.

68

Shape_Lenth Length in meters All features should
have a +integer value
Enabled Created when Geometric Network is built All features should be
set to True (From the
database).
New or modified
features will have a
Null value until
network is rebuilt.
69

Appendix E List of Attributes from Glaciers of the American West
Attribute Label Definition
FID Internal feature number.
Shape Feature geometry.
AREA Area calculated in square meters in the NAD_83_Albers_Equal_Area
projection
PERMIETER Perimeter calculated in meters in the NAD_83_Albers_Equal_Area
projection
RECNO Unique identifier generated by PSU
X_COORD Centroid X in Decimal Degrees
Y_COORD Centroid Y in Decimal Degrees
CLASSIFICA Description
SOURCE Agency or media of origination
SRC_SCALE Scale of feature's original mapping
GLACNAME Name of Glacier
70

Appendix F glaciers_variable Data Dictionary
Field Name Description SQL Data
Type
Null
able
glacier_id Glacier identification number character
varying(20)
Yes
submission_id Submission identification number integer No

glac_name Name of the glacier character
varying(28)
Yes
area Map-plane area of the glacier numeric Yes
db_area Map-plane area of the glacier, as calculated within
the GLIMS Glacier Database
numeric Yes
perimeter Perimeter of the glacier numeric Yes
min_elev Lowest elevation of the glacier numeric Yes
max_elev Maximum elevation of the glacier numeric Yes
mean_elev Mean elevation of the glacier numeric Yes
glacial_region Glacial region that the glacier lies within character
varying(20)
Yes
in_county California county that the glacier falls within text Yes
in_basin Watershed basin the glaciers lies within text Yes
num_basins Number of watershed basins the glacier is within smallint Yes
fcode Five-digit integer value; composed of the feature type
and combinations of characteristics and values
integer Yes
date The as-of date for the glacier. Usually the acquisition
date of the image used for analysis.
timestamp Yes
max_width Representative width of the glacier numeric Yes
max_length Representative length of the glacier numeric Yes
slope Mean slope of the glacier surface smallint Yes
aspect The aspect (orientation) of the glacier surface (deg) is
presented as an integer azimuth relative to 0° at due
north.
smallint Yes
connect See Description of Connect in Appendix B List of
Attributes from RGI
smallint Yes
surging See Description of Surging in Appendix B List of
Attributes from RGI
smallint Yes
linkages See Description of Linkages in Appendix B List of
Attributes from RGI
smallint Yes
form See Description of Form in Appendix B List of
Attributes from RGI
smallint Yes
term_type See Description of TermType in Appendix B List of
Attributes from RGI
smallint Yes
x_coord Centroid X in Decimal Degrees numeric Yes
y_coord Centroid Y in Decimal Degrees numeric Yes
loc_unc_x Local uncertainty of x smallint Yes
71

loc_unc_y Local uncertainty of y smallint Yes
glob_unc_x Global uncertainty of x smallint Yes
glob_unc_y Global uncertainty of y smallint Yes
prime_class Primary WGMS classification of the glacier. smallint Yes
frontal_char See Description of Frontal_Char in Appendix B List
of Attributes from RGI
smallint Yes
long_char See Description of Long_Char in Appendix B List of
Attributes from RGI
smallint Yes
tongue_act See Description of Tongue_Activity in Appendix B
List of Attributes from RGI
smallint Yes
moraines1 See Description of Moraines1 in Appendix B List of
Attributes from RGI
smallint Yes
moraines2 See Description of Moraines2 in Appendix B List of
Attributes from RGI
smallint Yes
source_nourish See Description of Source_Nourish in Appendix B
List of Attributes from RGI
smallint Yes
min_elev_exp See Description of Min_Elev_Exp in Appendix B
List of Attributes from RGI
smallint Yes
mean_elev_acy See Description of Mean_Elev_Acy in Appendix B
List of Attributes from RGI
smallint Yes
mean_elev_abl See Description of Mean_Elev_Abl in Appendix B
List of Attributes from RGI
smallint Yes
activity_start See Description of Activity_Start in Appendix B List
of Attributes from RGI
smallint Yes
activity_end See Description of Activity_End in Appendix B List
of Attributes from RGI
smallint Yes
snowline_acy See Description of Snowline_Acy in Appendix B List
of Attributes from RGI
smallint Yes
snowline_date See Description of Snowline_Date in Appendix B
List of Attributes from RGI
smallint Yes
mean_depth See Description of Mean_Depth in Appendix B List
of Attributes from RGI
smallint Yes
deapth_acy See Description of Depth_Acy in Appendix B List of
Attributes from RGI
smallint Yes
area_acy See Description of Area_Acy in Appendix B List of
Attributes from RGI
smallint Yes
area_exp See Description of Area_Exp in Appendix B List of
Attributes from RGI
smallint Yes
mean_width See Description of Mean_Width in Appendix B List
of Attributes from RGI
smallint Yes
mean_length See Description of Mean_Length in Appendix B List
of Attributes from RGI
smallint Yes
max_len_ex See Description of Max_Len_Ex in Appendix B List
of Attributes from RGI
smallint Yes
max_len_ab See Description of Max_Len_Ab in Appendix B List
of Attributes from RGI
smallint Yes
72

orient_acc See Description of Orient_Acc in Appendix B List of
Attributes from RGI
text Yes
orient_abl See Description of Orient_Abl in Appendix B List of
Attributes from RGI
text Yes
remarks Additional remarks or notes text Yes

73

Appendix G SQL Code
-- Database generated with pgModeler (PostgreSQL Database Modeler).
-- pgModeler version: 0.9.2_snapshot20190921
-- PostgreSQL version: 11.0
-- Project Site: pgmodeler.io
-- Model Author: ---

-- object: rds_iam | type: ROLE --
-- DROP ROLE IF EXISTS rds_iam;
CREATE ROLE rds_iam WITH
INHERIT
ENCRYPTED PASSWORD '********';
-- ddl-end --

-- object: rds_ad | type: ROLE --
-- DROP ROLE IF EXISTS rds_ad;
CREATE ROLE rds_ad WITH
INHERIT
ENCRYPTED PASSWORD '********';
-- ddl-end --

-- object: cvanschoonhoven | type: ROLE --
-- DROP ROLE IF EXISTS cvanschoonhoven;
CREATE ROLE cvanschoonhoven WITH
CREATEDB
CREATEROLE
INHERIT
LOGIN
ENCRYPTED PASSWORD '********';
-- ddl-end --

-- object: rdsrepladmin | type: ROLE --
-- DROP ROLE IF EXISTS rdsrepladmin;
CREATE ROLE rdsrepladmin WITH
REPLICATION
ENCRYPTED PASSWORD '********';
-- ddl-end --

-- -- object: rdsadmin | type: ROLE --
-- -- DROP ROLE IF EXISTS rdsadmin;
-- CREATE ROLE rdsadmin WITH
-- SUPERUSER
-- CREATEDB
-- CREATEROLE
-- INHERIT
74

-- LOGIN
-- REPLICATION
-- BYPASSRLS
-- ENCRYPTED PASSWORD '********';
-- -- ddl-end --
--
-- object: rds_superuser | type: ROLE --
-- DROP ROLE IF EXISTS rds_superuser;
CREATE ROLE rds_superuser WITH
INHERIT
ENCRYPTED PASSWORD '********'
ROLE cvanschoonhoven;
-- ddl-end --

-- object: rds_replication | type: ROLE --
-- DROP ROLE IF EXISTS rds_replication;
CREATE ROLE rds_replication WITH
INHERIT
ENCRYPTED PASSWORD '********'
ADMIN rds_superuser;
-- ddl-end --

-- object: rds_password | type: ROLE --
-- DROP ROLE IF EXISTS rds_password;
CREATE ROLE rds_password WITH
INHERIT
ENCRYPTED PASSWORD '********'
ADMIN rds_superuser;
-- ddl-end --

-- Database creation must be done outside a multicommand file.
-- These commands were put in this file only as a convenience.
-- -- object: california_glaciers | type: DATABASE --
-- -- DROP DATABASE IF EXISTS california_glaciers;
-- CREATE DATABASE california_glaciers
-- ENCODING = 'UTF8'
-- LC_COLLATE = 'en_US.UTF-8'
-- LC_CTYPE = 'en_US.UTF-8'
-- TABLESPACE = pg_default
-- OWNER = cvanschoonhoven;
-- -- ddl-end --
--

-- -- object: public.geometry | type: TYPE --
-- -- DROP TYPE IF EXISTS public.geometry CASCADE;
75

-- CREATE TYPE public.geometry;
-- -- ddl-end --
--
-- object: postgis | type: EXTENSION --
-- DROP EXTENSION IF EXISTS postgis CASCADE;
CREATE EXTENSION postgis
WITH SCHEMA public
VERSION '2.5.2';
-- ddl-end --
COMMENT ON EXTENSION postgis IS E'PostGIS geometry, geography, and raster spatial
types and functions';
-- ddl-end --

-- object: public.ca_counties_gid_seq | type: SEQUENCE --
-- DROP SEQUENCE IF EXISTS public.ca_counties_gid_seq CASCADE;
CREATE SEQUENCE public.ca_counties_gid_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
START WITH 1
CACHE 1
NO CYCLE
OWNED BY NONE;
-- ddl-end --
ALTER SEQUENCE public.ca_counties_gid_seq OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: public.ca_counties | type: TABLE --
-- DROP TABLE IF EXISTS public.ca_counties CASCADE;
CREATE TABLE public.ca_counties (
gid integer NOT NULL DEFAULT nextval('public.ca_counties_gid_seq'::regclass),
statefp character varying(2),
countyfp character varying(3),
countyns character varying(8),
geoid character varying(5),
name character varying(100),
namelsad character varying(100),
lsad character varying(2),
classfp character varying(2),
mtfcc character varying(5),
csafp character varying(3),
cbsafp character varying(5),
metdivfp character varying(5),
funcstat character varying(1),
aland double precision,
awater double precision,
76

intptlat character varying(11),
intptlon character varying(12),
geom public.geometry,
CONSTRAINT ca_counties_pkey PRIMARY KEY (gid),
CONSTRAINT name UNIQUE (name)

);
-- ddl-end --
ALTER TABLE public.ca_counties OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: ca_counties_geom_idx | type: INDEX --
-- DROP INDEX IF EXISTS public.ca_counties_geom_idx CASCADE;
CREATE INDEX ca_counties_geom_idx ON public.ca_counties
USING gist
(
geom
)
WITH (FILLFACTOR = 90);
-- ddl-end --

-- object: public.wbdhu6_gid_seq | type: SEQUENCE --
-- DROP SEQUENCE IF EXISTS public.wbdhu6_gid_seq CASCADE;
CREATE SEQUENCE public.wbdhu6_gid_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
START WITH 1
CACHE 1
NO CYCLE
OWNED BY NONE;
-- ddl-end --
ALTER SEQUENCE public.wbdhu6_gid_seq OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: public.wbdhu6 | type: TABLE --
-- DROP TABLE IF EXISTS public.wbdhu6 CASCADE;
CREATE TABLE public.wbdhu6 (
gid integer NOT NULL DEFAULT nextval('public.wbdhu6_gid_seq'::regclass),
objectid bigint,
tnmid character varying(40),
metasource character varying(40),
sourcedata character varying(100),
sourceorig character varying(130),
sourcefeat character varying(40),
loaddate date,
77

gnis_id bigint,
areaacres numeric,
areasqkm numeric,
states character varying(50),
huc6 character varying(6),
name character varying(120),
shape_leng numeric,
shape_area numeric,
geom public.geometry,
CONSTRAINT wbdhu6_pkey PRIMARY KEY (gid),
CONSTRAINT wbdhu6_name_key UNIQUE (name)

);
-- ddl-end --
ALTER TABLE public.wbdhu6 OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: wbdhu6_geom_idx | type: INDEX --
-- DROP INDEX IF EXISTS public.wbdhu6_geom_idx CASCADE;
CREATE INDEX wbdhu6_geom_idx ON public.wbdhu6
USING gist
(
geom
)
WITH (FILLFACTOR = 90);
-- ddl-end --

-- object: public.glac_regions_gid_seq | type: SEQUENCE --
-- DROP SEQUENCE IF EXISTS public.glac_regions_gid_seq CASCADE;
CREATE SEQUENCE public.glac_regions_gid_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
START WITH 1
CACHE 1
NO CYCLE
OWNED BY NONE;
-- ddl-end --
ALTER SEQUENCE public.glac_regions_gid_seq OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: public.glac_regions | type: TABLE --
-- DROP TABLE IF EXISTS public.glac_regions CASCADE;
CREATE TABLE public.glac_regions (
gid integer NOT NULL DEFAULT nextval('public.glac_regions_gid_seq'::regclass),
id integer,
78

mtnregion character varying(50),
geom public.geometry,
CONSTRAINT glac_regions_pkey PRIMARY KEY (gid),
CONSTRAINT mtnregion UNIQUE (mtnregion)

);
-- ddl-end --
ALTER TABLE public.glac_regions OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: glac_regions_geom_idx | type: INDEX --
-- DROP INDEX IF EXISTS public.glac_regions_geom_idx CASCADE;
CREATE INDEX glac_regions_geom_idx ON public.glac_regions
USING gist
(
geom
)
WITH (FILLFACTOR = 90);
-- ddl-end --

-- object: public.ca_places_gid_seq | type: SEQUENCE --
-- DROP SEQUENCE IF EXISTS public.ca_places_gid_seq CASCADE;
CREATE SEQUENCE public.ca_places_gid_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
START WITH 1
CACHE 1
NO CYCLE
OWNED BY NONE;
-- ddl-end --
ALTER SEQUENCE public.ca_places_gid_seq OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: public.ca_places | type: TABLE --
-- DROP TABLE IF EXISTS public.ca_places CASCADE;
CREATE TABLE public.ca_places (
gid integer NOT NULL DEFAULT nextval('public.ca_places_gid_seq'::regclass),
statefp character varying(2),
placefp character varying(5),
placens character varying(8),
geoid character varying(7),
name character varying(100),
namelsad character varying(100),
lsad character varying(2),
classfp character varying(2),
79

pcicbsa character varying(1),
pcinecta character varying(1),
mtfcc character varying(5),
funcstat character varying(1),
aland numeric,
awater numeric,
intptlat character varying(11),
intptlon character varying(12),
in_county character varying(254),
geom_lengt numeric,
geom_area numeric,
geom public.geometry,
CONSTRAINT ca_places_pkey PRIMARY KEY (gid)

);
-- ddl-end --
ALTER TABLE public.ca_places OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: ca_places_geom_idx | type: INDEX --
-- DROP INDEX IF EXISTS public.ca_places_geom_idx CASCADE;
CREATE INDEX ca_places_geom_idx ON public.ca_places
USING gist
(
geom
)
WITH (FILLFACTOR = 90);
-- ddl-end --

-- object: public.people | type: TABLE --
-- DROP TABLE IF EXISTS public.people CASCADE;
CREATE TABLE public.people (
contact_id integer NOT NULL,
surname text,
givennames text,
prof_title text,
affiliation text,
department text,
address1 text,
address2 text,
city text,
state_province text,
postal_code text,
country_code character(2),
office_loc text,
url_primary text,
80

url_secondary text,
workphone_primary text,
workphone_secondary text,
workfax text,
homephone text,
mobilephone text,
email_primary text,
email_secondary text,
comment text,
CONSTRAINT people_pk PRIMARY KEY (contact_id)

);
-- ddl-end --
ALTER TABLE public.people OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: public.regional_center | type: TABLE --
-- DROP TABLE IF EXISTS public.regional_center CASCADE;
CREATE TABLE public.regional_center (
rc_id integer NOT NULL,
parent_rc integer,
main_contact integer,
url_primary text,
url_secondary text,
rc_poly polygon,
CONSTRAINT "regional_Center_pk" PRIMARY KEY (rc_id),
CONSTRAINT rc_id UNIQUE (rc_id)

);
-- ddl-end --
ALTER TABLE public.regional_center OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: public.submission_info | type: TABLE --
-- DROP TABLE IF EXISTS public.submission_info CASCADE;
CREATE TABLE public.submission_info (
submission_id integer NOT NULL,
submitter_id integer,
rc_id integer,
glac_region integer,
file_name text,
submission_time date,
proc_desc text,
source_scale text,
coord_desc text,
gid_glac_regions integer,
81

CONSTRAINT data_source_gid_pk PRIMARY KEY (submission_id),
CONSTRAINT submission_id UNIQUE (submission_id)

);
-- ddl-end --
ALTER TABLE public.submission_info OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: public.glaciers_static | type: TABLE --
-- DROP TABLE IF EXISTS public.glaciers_static CASCADE;
CREATE TABLE public.glaciers_static (
glacier_id character varying(20) NOT NULL,
glacier_name text,
glims_id character varying(14),
wgi_id character varying(20),
rgi_id character varying(20),
nhd_id character varying(40),
gnis_id character varying(10),
recno character varying(3),
parent_icemass_id character varying(20),
glac_status smallint NOT NULL DEFAULT 0,
CONSTRAINT glaciers_static_pk PRIMARY KEY (glacier_id),
CONSTRAINT glaciers_static_glacier_id_key UNIQUE (glacier_id)

);
-- ddl-end --
ALTER TABLE public.glaciers_static OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: public.glaciers_variable | type: TABLE --
-- DROP TABLE IF EXISTS public.glaciers_variable CASCADE;
CREATE TABLE public.glaciers_variable (
glacier_id character varying(20),
submission_id integer NOT NULL,
glac_name character varying(28),
area numeric,
db_area numeric,
perimeter numeric,
min_elev numeric,
max_elev numeric,
mean_elev numeric,
glacial_region character varying(20),
in_county text,
in_basin text,
num_basins smallint,
fcode integer DEFAULT 37800,
82

date timestamp,
geom public.geometry,
max_width numeric,
max_length numeric,
slope smallint,
aspect smallint,
connect smallint,
surging smallint,
linkages smallint,
form smallint,
term_type smallint,
x_coord numeric,
y_coord numeric,
loc_unc_x smallint,
loc_unc_y smallint,
glob_unc_x smallint,
glob_unc_y smallint,
prime_class smallint,
frontal_char smallint,
long_char smallint,
tongue_act smallint,
moraines1 smallint,
moraines2 smallint,
source_nourish smallint,
min_elev_exp smallint,
mean_elev_acy smallint,
mean_elev_abl smallint,
activity_start smallint,
activity_end smallint,
snowline_acy smallint,
snowline_date smallint,
mean_depth smallint,
deapth_acy smallint,
area_acy smallint,
area_exp smallint,
mean_width smallint,
mean_length smallint,
max_len_ex smallint,
max_len_ab smallint,
orient_acc text,
orient_abl text,
remarks text,
CONSTRAINT glims_polygons_pkey PRIMARY KEY (submission_id)

);
-- ddl-end --
83

ALTER TABLE public.glaciers_variable OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: public.attachments | type: TABLE --
-- DROP TABLE IF EXISTS public.attachments CASCADE;
CREATE TABLE public.attachments (
attach_name text NOT NULL,
glacier_id character varying(20),
file_type character varying(4),
"URL" text,
adate date,
editor text,
author text,
description text,
submission_id_glaciers_variable integer,
CONSTRAINT attachments_pk PRIMARY KEY (attach_name)

);
-- ddl-end --
COMMENT ON COLUMN public.attachments.adate IS 'data of publication';
-- ddl-end --
ALTER TABLE public.attachments OWNER TO cvanschoonhoven;
-- ddl-end --

-- object: glaciers_variable_fk | type: CONSTRAINT --
-- ALTER TABLE public.attachments DROP CONSTRAINT IF EXISTS glaciers_variable_fk
CASCADE;
ALTER TABLE public.attachments ADD CONSTRAINT glaciers_variable_fk FOREIGN
KEY (submission_id_glaciers_variable)
REFERENCES public.glaciers_variable (submission_id) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE;
-- ddl-end --

-- object: glac_regions_fk | type: CONSTRAINT --
-- ALTER TABLE public.submission_info DROP CONSTRAINT IF EXISTS glac_regions_fk
CASCADE;
ALTER TABLE public.submission_info ADD CONSTRAINT glac_regions_fk FOREIGN
KEY (gid_glac_regions)
REFERENCES public.glac_regions (gid) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE;
-- ddl-end --

-- object: in_county | type: CONSTRAINT --
-- ALTER TABLE public.ca_places DROP CONSTRAINT IF EXISTS in_county CASCADE;
ALTER TABLE public.ca_places ADD CONSTRAINT in_county FOREIGN KEY
(in_county)
84

REFERENCES public.ca_counties (name) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --

-- object: parent_rc | type: CONSTRAINT --
-- ALTER TABLE public.regional_center DROP CONSTRAINT IF EXISTS parent_rc
CASCADE;
ALTER TABLE public.regional_center ADD CONSTRAINT parent_rc FOREIGN KEY
(parent_rc)
REFERENCES public.regional_center (rc_id) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --

-- object: rc_id | type: CONSTRAINT --
-- ALTER TABLE public.submission_info DROP CONSTRAINT IF EXISTS rc_id CASCADE;
ALTER TABLE public.submission_info ADD CONSTRAINT rc_id FOREIGN KEY (rc_id)
REFERENCES public.regional_center (rc_id) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --

-- object: submitter_id | type: CONSTRAINT --
-- ALTER TABLE public.submission_info DROP CONSTRAINT IF EXISTS submitter_id
CASCADE;
ALTER TABLE public.submission_info ADD CONSTRAINT submitter_id FOREIGN KEY
(submitter_id)
REFERENCES public.people (contact_id) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --

-- object: in_county | type: CONSTRAINT --
-- ALTER TABLE public.glaciers_variable DROP CONSTRAINT IF EXISTS in_county
CASCADE;
ALTER TABLE public.glaciers_variable ADD CONSTRAINT in_county FOREIGN KEY
(in_county)
REFERENCES public.ca_counties (name) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --

-- object: in_basin | type: CONSTRAINT --
-- ALTER TABLE public.glaciers_variable DROP CONSTRAINT IF EXISTS in_basin
CASCADE;
ALTER TABLE public.glaciers_variable ADD CONSTRAINT in_basin FOREIGN KEY
(in_basin)
REFERENCES public.wbdhu6 (name) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --
85

-- object: glacial_region | type: CONSTRAINT --
-- ALTER TABLE public.glaciers_variable DROP CONSTRAINT IF EXISTS glacial_region
CASCADE;
ALTER TABLE public.glaciers_variable ADD CONSTRAINT glacial_region FOREIGN
KEY (glacial_region)
REFERENCES public.glac_regions (mtnregion) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --

-- object: submission_id | type: CONSTRAINT --
-- ALTER TABLE public.glaciers_variable DROP CONSTRAINT IF EXISTS submission_id
CASCADE;
ALTER TABLE public.glaciers_variable ADD CONSTRAINT submission_id FOREIGN
KEY (submission_id)
REFERENCES public.submission_info (submission_id) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --

-- object: glaciers_variable_glacier_id_fkey | type: CONSTRAINT --
-- ALTER TABLE public.glaciers_variable DROP CONSTRAINT IF EXISTS
glaciers_variable_glacier_id_fkey CASCADE;
ALTER TABLE public.glaciers_variable ADD CONSTRAINT
glaciers_variable_glacier_id_fkey FOREIGN KEY (glacier_id)
REFERENCES public.glaciers_static (glacier_id) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --

-- -- object: public.geometry | type: TYPE --
-- -- DROP TYPE IF EXISTS public.geometry CASCADE;
-- CREATE TYPE public.geometry;
-- -- ddl-end --
--

Abstract (if available)

Abstract Glaciers are an important indicator of global and local climate change, an important factor in regional environmental health, and a possible natural hazard. The importance of glaciers has led to the creation of many glacial inventories ranging in scale. The datasets from these glacial inventories are often outdated or incomplete, especially for those in California. Additionally, none of the glacier inventories track change in the glaciers over time, but rather they are limited to the data at a single point in time in California. The objective of this project was to create a web-based relational geodatabase and web map to track spatiotemporal information of glaciers in California. The geodatabase was designed with three user groups in mind: geologists and glaciologists, policy makers, and the general public. The geodatabase was also designed to meet several goals. The first was to archive and store the large amounts of glacial data collected by other sources, add temporal attribute onto current glacial data, and plan for new types of data. This was done by developing the database in such a way that it meets current requirements and standards of existing global glacial databases. Additionally, another goal was to create a web map that allowed the data to be easily accessible and useful for various users with different goals. A web map was constructed to display the data within the geodatabase and allow the user-groups to interact with and download the data. The results of this project including the geodatabase, data, methods, images and documentation are published at mapping.cool/california-glaciers and on GitHub, along with the web map for public use. This geodatabase and web map can be used for different applications, ranging from the simple uses like as monitoring glaciers to detect change, to more advanced use of the data such as hazard detection and assessment.

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

A spatial narrative of alternative fueled vehicles in California: a GIS story map

PDF

A unified geodatabase design for sinkhole inventories in the United States

PDF

Designing an earthquake preparedness web mapping application for the older adult population of Los Angeles, California

PDF

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

PDF

Development of a Web GIS application to aid marathon runners in the race selection and planning process

PDF

Creating a geodatabase and web-GIS map to visualize drone legislation in the state of Maryland

PDF

Developing an archaeological specific geodatabase to chronicle historical perspectives at Bethsaida, Israel

PDF

A user study of GIS infused genealogy with dynamic thematic representation and spatiotemporal control

PDF

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

PDF

Creating a water quality geodatabase for the West Hawai‘i Island region

PDF

Cal ToxTrack: a full stack Web GIS for mapping pollution in California

PDF

Geodatabase for archaeogenetics: ancient peoples and family lines

PDF

A contributory Web-based application for documenting historic resources: French colonial era art deco architecture in Hanoi

PDF

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

PDF

Designing an early warning system web mapping application for the Atlanta Metropolitan Area before a flooding event

PDF

Urban areas and avian diversity: using citizen collected data to explore green spaces

PDF

Harnessing GIST-enabled resources in the classroom: developing a Story Map for use with secondary students

PDF

GIS data curation and Web map application for La Brea Tar Pits fossil occurrences in Los Angeles, California

PDF

Creating a Web GIS to support field operations and enhance data collection for the Animal and Plant Health Inspection Service (APHIS)

Asset Metadata

Creator VanSchoonhoven, Chase Juhl (author)

Core Title Web-based relational spatiotemporal geodatabase of glaciers in California

School College of Letters, Arts and Sciences

Degree Master of Science

Degree Program Geographic Information Science and Technology

Publication Date 07/14/2020

Defense Date 05/21/2020

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

Tag geodatabase,glacial,Glacier,OAI-PMH Harvest,temporal

Language English

Contributor Electronically uploaded by the author (provenance)

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

Creator Email cvanschoonhoven@gmail.com,vanschoo@usc.edu

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

Unique identifier UC11663528

Identifier etd-VanSchoonh-8679.pdf (filename),usctheses-c89-331735 (legacy record id)

Legacy Identifier etd-VanSchoonh-8679.pdf

Dmrecord 331735

Document Type Thesis

Rights VanSchoonhoven, Chase Juhl

Type texts

Source 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 a...

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