Spatial Databases
What are spatial databases in GIS?
Spatial databases provide a strong foundation to accessing, storing and managing your spatial data empire.
A database is a collection of related information that permits the entry, storage, input, output and organization of data. A database management system (DBMS) serves as an interface between users and their database.
A spatial database includes location. It has geometry as points, lines and polygons.
GIS combines spatial data from many sources with many different people. Databases connect users to the GIS database.
For example, a city might have the waste water division, land records, transportation and fire departments connected and using datasets from common spatial databases.
Let’s take a closer look at spatial databases and how they are used in GIS:
What is an attribute table in GIS?
First, let’s look at the basics:
By default, spatial vector features are always associated with non-spatial attribute tables in a GIS. Spatial features store where objects are located on a map. Non-spatial attribute tables explain what the objects on the map represent.
Attribute Table Example
Attribute tables are similar to spreadsheets. Columns are fields that gives structure to tables. Fields must be constrained to different types such as text (strings), integers (whole numbers) and dates (YYYY/MM/DD). Field name are the title of the column names. A field name should be descriptive of the information being entered in the column. For example, the average population may have a field name of AVG_POP.
Rows in an attribute table represents a spatial feature in the data set or an associated record of that data set.
Rows in an attribute can have relationships with spatial features. There are three types of relationships that exist in a GIS: one-to-one relationship, one-to-many relationship and many-to-many relationship.
One-to-one relationships tie one table with one table
What are one-to-one relationships in GIS?
Spatial data on a map is always linked to a row in a table. When one feature is linked to one entry in a table, this is called a one-to-one relationship (1-1). For example, here are the geographic locations of five cities in the United States:
Attribute tables are similar to spreadsheets. Columns are fields that gives structure to tables. Fields must be constrained to different types such as text (strings), integers (whole numbers) and dates (YYYY/MM/DD). Field name are the title of the column names. A field name should be descriptive of the information being entered in the column. For example, the average population may have a field name of AVG_POP.
Rows in an attribute table represents a spatial feature in the data set or an associated record of that data set.
Rows in an attribute can have relationships with spatial features. There are three types of relationships that exist in a GIS: one-to-one relationship, one-to-many relationship and many-to-many relationship.
One-to-one relationships tie one table with one table
What are one-to-one relationships in GIS?
Spatial data on a map is always linked to a row in a table. When one feature is linked to one entry in a table, this is called a one-to-one relationship (1-1). For example, here are the geographic locations of five cities in the United States:
United States Locations
Here is the associated attribute table with these five locations:
Here is the associated attribute table with these five locations:
Attribute table
As you can see, we can get some extra information about these five cities. We learn their latitude and longitude.
Not only that, but we see their pop max (which is for the metropolitan areas) and pop_min(which is for the incorporated city of the same name).
One-to-many relationships ties multiple records from a table together
What are one-to-many relationships?
When one spatial feature is associated with multiple records in an attribute table this is called aone-to-many relationship (1-M).
In this case there is a unique identifier in the spatial table that is used to uniquely identify each row in a table. These are called keys and they exists in both the spatial and non-spatial attribute table.
This unique ID (primary key) is the primary linkage between geographic data and attribute table. This key must exist in the related table as a foreign key.
1-M relationships are commonly set up in spatial databases. Relationship classes set up the type of relationship and which features are being connected by a unique ID.
Here is an example where surveys were done at two mountain locations over a period of 3 years. The coordinates of the mountain surveys remained the same over the years:
As you can see, we can get some extra information about these five cities. We learn their latitude and longitude.
Not only that, but we see their pop max (which is for the metropolitan areas) and pop_min(which is for the incorporated city of the same name).
One-to-many relationships ties multiple records from a table together
What are one-to-many relationships?
When one spatial feature is associated with multiple records in an attribute table this is called aone-to-many relationship (1-M).
In this case there is a unique identifier in the spatial table that is used to uniquely identify each row in a table. These are called keys and they exists in both the spatial and non-spatial attribute table.
This unique ID (primary key) is the primary linkage between geographic data and attribute table. This key must exist in the related table as a foreign key.
1-M relationships are commonly set up in spatial databases. Relationship classes set up the type of relationship and which features are being connected by a unique ID.
Here is an example where surveys were done at two mountain locations over a period of 3 years. The coordinates of the mountain surveys remained the same over the years:
Mountain Survey Sites
These two mountains have unique IDs of A101 and A102 as shown in their attribute tables:
These two mountains have unique IDs of A101 and A102 as shown in their attribute tables:
Survey Site Attribute Table
Over a period of three years, there have been multiple observations of different animals. The two sites of these mountains have the same IDs – A101 and A102.
The observations at each site can be found within the OBS field. The YEAR field identifies which year the animal was observed. The two fields that link up these attribute tables are
Over a period of three years, there have been multiple observations of different animals. The two sites of these mountains have the same IDs – A101 and A102.
The observations at each site can be found within the OBS field. The YEAR field identifies which year the animal was observed. The two fields that link up these attribute tables are
the primary keys ID and SURV_ID.
Multiple Observations per Survey
You can see how we are reducing redundancy. We are no longer storing the latitude and longitude in each observation. This is because each latitude and longitude is only being stored once in the survey points table.
Many-to-many relationships in GIS are much less common in GIS. This type of relationship exists when there are many entries in related attribute tables and vice versa. An intersection table is usually generated to associate many records together.
You can see how we are reducing redundancy. We are no longer storing the latitude and longitude in each observation. This is because each latitude and longitude is only being stored once in the survey points table.
Many-to-many relationships in GIS are much less common in GIS. This type of relationship exists when there are many entries in related attribute tables and vice versa. An intersection table is usually generated to associate many records together.
