Spatial Databases – Build Your Spatial Data Empire

How To Manage Your GIS Data with a Relational Database Management System

Spatial Databases

What are spatial databases in GIS?

Spatial databases provide a strong foundation for 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 databases.

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 wastewater 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 tables are similar to spreadsheets.

Attribute Table Example
Attribute Table Example

FIELDS have 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:

  • One-to-one relationship
  • One-to-many relationship
  • 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 Attribute Table

Here is the associated attribute table with these five locations:

Latitude and longitude 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 a one-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 exist 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:

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

Relational database management system (RDBMS)

database example

What is a relational database management system (RDBMS)?

As explained above, the two fields that link up these attribute tables are the primary keys ID and SURV_ID. What ties these tables together is user setting up a 1-M relationship.

This is an example of an RDBMS.

RDBMS is a relational storage concept for data. A relational database contains tables that hold records. Each record holds fields containing data for that record.

It uses normalization to separate tables and link tables together. Normalization doesn’t waste space. It breaks out information into discrete components. RDBMS reduces repetitive information. In the example above, we take out duplicate information into multiple tables. There’s a foreign key on one table and we can gain access to that table through a relationship.

RDBMS can get very complex such as the example below:

Complex Relational Database

What is Boolean Algebra?

Boolean algebra consists of conditions used to select features with a set of algebraic conditions. Conditions include AND, OR, or NOT.

When you filter the displayed features in the map by setting up a definition query using Boolean algebra. For example: CITY = “LARGE” AND COUNTRY = “NORTH AMERICA” would filter out all the records that meet this criteria

In GIS, Boolean algebra are conditions used to select features with a set of algebraic conditions. Conditions include AND, OR, or NOT. Venn diagrams are often used to represent Boolean operations. The name Boolean algebra originated by founder George Boole in 1847.

Structured Query Language (SQL) is how users can interact with the database using Boolean algebra.

Venn Diagram (Boolean Algebra)

Spatial Database Examples

Vector GIS File Format

  • Proprietary Esri File Geodatabases stores vectors, rasters, tables, topology and relationships. Schemas can be set up for data integrity. File geodatabases offer structural, performance and data management advantages.
  • Open source PostGIS adds spatial objects to the cross-platform PostgreSQL database. The three features that PostGIS delivers to PostgreSQL DBMS are spatial types, indexes and functions. With support for different geometry types, the PostGIS spatial database allows querying and managing information about locations and mapping.
  • Other database examples include SQL Server (where geometry is just another data type, like char and int) and Microsoft Access (known as a personal geodatabase in ArcGIS).

Spatial databases provide a mechanism for multiple users to simultaneously access shared spatial data – similar to a DBMS.

READ MORE: What is a Geodatabase? Personal vs File Geodatabase

The Final Word on Spatial Databases

A database management system (DBMS) allows users to store, insert, delete, and update information in a database.

RDBMS takes it a step further. It reduces redundancy through normalization. It links tables together through primary and foreign keys.

Spatial databases go a step even further because it records data with geographic coordinates.

From geodatabases to PostGIS, spatial databases have quickly become the primary method of managing spatial data.

Start your spatial data empire using spatial databases.

Be the first to comment

Leave a Reply

Your email address will not be published.