Foundations of Geographic Information Systems Final Project
usaggwp
Week4-DatabaseManagementQueriesFA19GIS5103.pptxFoundations of Geographic Information Science
GIS5103 – Fall 2019
Week 4 Database Management
Outline
Differentiate between relational database management systems (RDBMS), databases, and data files.
Explain why we select the database approach for GIS
Define what a relational database is
Describe how databases are created
Describe GIS database applications
Database Management System (DBMS)
What is a Database Management System (DBMS)?
A DBMS is a system [or software] designed to manage a database and allow user-
selected operations to be performed on the data.
1.
2.
3.
4.
It is a logical construct for the storage and retrieval of information. It puts the database into a geographic coordinate system.
Attribute data models are needed for the DBMS.
It contains data definition language; a data-entry module; a data update module; a report generator; and query language.
What is the difference between and database and a database management system?
A database is a simply a collection of related data. This can include non-computerized data such as those found in a ‘phone or address book. It can also include information contained in spreadsheets or also in relational databases or databases comprised of multiple tables that are connected or related to each other using common fields or
“keys” found in each of the tables.
Data in a computer database, however, are managed and accessed through a database management system (DBMS). The database management system is used to manage, access the database
The Database is distinct from the DBMS
In order for there to be a DBMS, there must be a database. In order for there to be a database there must be data files
Bolstad’s Figure 8-4. It shows the database approach to data handling data.
First comes the Data File, then the database and then the DBMS
So, what is the difference between a data file and a database? Heywood discusses this on p.110. For simplicity, below is a comparison in tabular form.
The image above from Screencast illustrated the differences between a flat file (like a spreadsheet) and the relational database model structure (RDBMS).
Creating the Database, 337-339
To design a database, the user must:
Identify the features (i.e. entities) to be represented (e.g., forests, trails, and recreational features. These are all nouns);
Identify the appropriate tables for a relational database. What is the ID# that could allow a relational join for e.g. (Forest-ID)?
Identify the feature’s attributes
(e.g., Trail Difficulty)
Identify relationships between the features (e.g., what activities, trails, can be explored at each of the forests? What trails are in each forest and how difficult area they?);
Figure 8-6: Forest data in a relational database structure.
Creating the Database
Keys or Key ID’s are extremely important in a relational database. They allow us to combine and display data from other tables and also allow us to store, query, and edit data more efficiently. There are two types of keys, primary and foreign.
A primary key, also called a primary keyword, is a key in a relational database that is unique for each record. It is a unique identifier, such as a driver license number, telephone number (including area code), or vehicle identification number (VIN). In the example to the left, the Forest-ID would be the primary key. A relational database must always have one and only one primary key.
A foreign key is a set of one or more columns in a table that refers to the primary key in another table. There isn’t any special code, configurations, or table definitions you need to place to officially “designate” a foreign key. For example, trail name.
Cardinality
In the context of databases, cardinality refers to the uniqueness of data values contained in a column.
High cardinality columns are those with very unique or uncommon data values. For example, in a
database table that stores bank account numbers, the “Account Number” column should have very high
cardinality – by definition, every item of data in this column should be totally unique.
Normal cardinality columns are those with a somewhat unique percentage of data values. For instance, if a table holds customer information, the “Last Name” column would have normal cardinality. Not every last name will be unique (for example, there will likely be several occurrences of “Smith”) but on the whole, the data is fairly non-repetitive.
Low cardinality columns are those with very few unique values. In a customer table, a low cardinality column would be the “Gender” column. This column will likely only have “M” and “F” as the range of values to choose from, and all the thousands or millions of records in the table can only pick one of these two values for this column.
Cardinality relationships between tables can take the form of one-to-one, one-to-many (whose reversal is many-to-one) or many-to-many. These terms simply refer to the relationships of data between the tables. For example, the relationship between the “Customers” table and the “Bank Accounts” table is one-to- many, that is, one customer can have several accounts, but one account cannot belong to more than one customer. That is, of course, assuming this bank has never heard of joint accounts!
Cardinality
Cardinality is commonly expressed in the following ways:
Databases
In many tabular databases (e.g., Dbase, SQL Server, ORACLE, Excel etc.), it is possible to list data, create queries, and sort the results.
Linking a tabular database to a GIS, however, provides additional “spatial”
capabilities that enhance the value of the data to provide useful information.
The “geographic search” is the secret to GIS data retrieval, in addition to the
ability to get back on demand data that were previously stored.
Let’s take a look some examples of GIS software and the “spatial” database
facilities they provide.
Multitier Architecture
.
Figure 8-5: Data are stored and accessed from the databases on the bottom tier and requests to view the data (queries) are made from the clients on the top tier consisting of desktop and web based products. Application servers act as the middle-men, processing transactions from the clients to the databases on the bottom tier. In well designed systems there are rules or policies setup that control how the data is accessed and by whom. We will look at this in more detail layer in the lecture.
Spatial Database facilities in ArcGIS
ESRI’s ArcGIS software is packaged with tutorials for ArcCatalog, ArcMap, ArcToolbox, and any extensions that you purchase. ESRI maintains on-line resources for teaching GIS in higher education, which includes links to lab exercises. The software you are using in the analysis course is, of course, ESRI software.
GIS and image processing software developed at Clark Labs - an educational and research institution at Clark University in Worcester, Massachusetts - is called Idrisi. It is easy to use while providing professional-level GIS, image processing, and spatial statistics analysis on Windows computers. It originally focused on handling raster data.
Database facilities in Idrisi http://www.clarklabs.org/
The historical approach to data handling is that a different version of the visitor’s details may be stored in separate databases. Additionally, data may be duplicated, errors may occur during transcription, and data storage mechanisms are often inflexible etc.
Why Choose a Database Approach?
Card index record from ski school manual database
It is also very difficult to update files using the traditional approach, as
illustrated in this example for a hardcopy index card held for an individual.
We are striving for the database approach for data handling
Key Points for databases:
They are collections of related material shared by different users;
Data is organized for easy reference with no duplication (e.g., think of a phone book);
Computer databases are superior to hard- copy ones because they are easier to update and data can be adapted to different uses (e.g., unlike the phonebook).
Relational Databases
Example of a relational attribute table data for hotels in Happy Valley, including the
terminology
/ ID number
Earlier this term you were introduced to flat file and hierarchical attribute tables. Relational attribute files are different to flat files and hierarchical files (see next slide for comparison). As we can see here, by organizing the data into 2D tables that contain records for each entity / feature, it is possible to “query” these data using a GIS, to find out which hotels are the cheapest or which is 5 star etc.
Examples will be given in later slides.
New attribute tables can be created using the relational ID number.
This is 1 example of the flexibility of computer databases over hardcopy forms, in that they can be adapted to different purposes to illustrate different relationships.
You will be doing these types of exercises during your analysis course.
Creating New Attribute Tables from Existing Ones
Retrieval Operations
Retrieval operations may be applied to either spatial or attribute data. For example, if searches are made to attribute data then the user would find or browse an entity.
As we saw in the last slide, and as you have experienced in your analysis course, this can be used for data reorganization by selecting, renumbering, and sorting the data.
Similarly, new attributes can be calculated by computation of selected values.
Spatial Retrieval Operations
In a spatial (map) database, records are features or themes.
The spatial equivalent of an “attribute find” is locate. The GIS then
highlights the result.
Spatial equivalents of the DBMS queries result in locating sets of features or building new GIS layers.
Queries
Functions, query language and applying algorithms could be difficult requiring a
command line approach. For example:
find in states where state_name = ‘California’
<1 record in result>
use states
calculate in states population_density = population / area
<50 records in result>
restrict in states where population_density > 1000
<20 records selected in result>
So you are coming into this field at a good time because it recent years it has become A LOT more user friendly.
Querying via SQL
At the highest level, a query in SQL is comprised of three key components or operations: SELECT, FROM, and WHERE. Intuitively, these operations used together tell your DBMS/GIS to SELECT some subset or fields of data FROM some data set WHERE some such condition is met.
Retrieval User Interface
Now the Retrieval User Interface simplifies the process. Most GIS packages use the GUI (Graphical User Interface) of the computer’s operating system to support both a menu-type query interface and a macro or programming language.
SQL (Standard Query Language) is a standard interface that works with relational databases and is supported by many GIS software packages.
So, let us examine a few things we can do operations with the GUI and SQL in the next slides. You will also look at this in more detail in week 8 (chapter 9) and you will be applying these techniques extensively in the analysis course.
I will start with spatial retrieval operations. Then go on to illustrate an application from my own work that uses complex retrieval operations. Finally, I will finish this week’s lecture component by discussing GIS applications using the web interface.
Examples of Attribute Searches and Queries
Identify– The image below shows a commonly used tool in GIS. The user clicks on the “identify” tool (circled in red) and then clicks on a feature in the map which will then display all of the attributes associated with that feature. This tool provides a quick
“snapshot” of attributes associated with a feature and does not allow the user to save or
work with the popup.
Examples of Attribute Searches and Queries
Select by Attributes – The “select by attributes” tool allows the user to build a SQL query and then view the results of the query on the map display. The user can also see the results in the layer’s attribute table, further refine the query, or export the data into a new table. The example below
shows features from the 2013 EPA_Temperature_2013 layer greater than January 2nd 2013 and less than or equal to April 1st 2013. Another advantage of using this tool is that more complex queries can be saved an re-used at a later date.
Examples of Spatial Searches and Queries
– Select by Location
In GIS there will be instances where you will be required to find features that are near or that intersect other features. While the select by attributes tool we saw on the previous slide is a powerful tool it is limited in that it can only use SQL language to select features. The Select by Location is an example of a tool that can be used to select features from one layer that intersect, touch, are within a distance of, etc. a feature or features in another layer. The example to the right retrieves all of the 2009 seismic events that are within a mile of the selected countries. Once the data is selected in can be exported to another feature layer or viewed as an attribute table which can be exported.
