Imagine trying to keep track of thousands of land parcels, each with its own set of characteristics, owners, and transaction history. With the right database tools, GIS makes this not just possible—but efficient, reliable, and scalable.
The first commercial relational database system launched in 1979, long before GIS was widely used. Modern geospatial databases now handle billions of spatial records every day.
A database management system (DBMS) is an essential tool for organizing, storing, and retrieving large datasets. Although traditional DBMS platforms were initially developed for nonspatial data, they have evolved to seamlessly integrate with GIS. Modern GIS software uses a relational database (RDBMS, explained in the next section) and a geospatial database, which is a database specifically designed to store and manage spatial data, to store spatial features (points, lines, and polygons) along with their descriptive attributes. Integrating GIS software and these databases ensures that your data stays organized and ready for smooth updates and analysis.
Today, geospatial databases, such as PostGIS, SQLite/GeoPackage, and Esri’s geodatabase, offer enhanced performance and advanced features, like network analysis and spatial queries. (I tell you more about spatial queries later in this chapter, and about network analysis in Chapter 14).
A software system that allows users to define, create, maintain, and control access to databases—essential for efficient data management in GIS.
A specialized database designed to store spatial features (like points, lines, polygons) and their associated attributes for GIS applications.
Structuring relational data
At the heart of GIS databases are relational tables, which organize data into rows and columns. Each row (or record) represents an individual feature such as a parcel, and each column (attribute) stores a specific type of information, such as land use category or owner, as shown in Figure 6-1.
A relational database gets its name from how it links data between tables using primary keys (unique identifiers) and foreign keys (references to primary keys in related tables). These keys create relationships between tables, allowing them to share information without duplicating data. This sharing of information is why you often see primary and foreign keys referred to as “shared keys.”
Why do you think it’s important to avoid duplicating data across multiple tables in a GIS database?
Primary keys are often single-column values, like a parcel identification number (such as Parcel_ID in Figure 6-1). However, you can create composite keys by combining multiple columns to ensure uniqueness, like combining an owner’s name with a street address. Databases automatically index primary keys, making searches faster by directing queries to the right data. The primary key’s index acts like a book’s table of contents, helping the database quickly find the matching row instead of scanning the entire table. This capability is especially helpful when you have a large dataset.
Take a look at Figure 6-2. The Land Use table lists parcels with attributes like Parcel_ID, Status, and Acres. Each row represents a different parcel, and the Parcel_ID ensures that each entry is unique.
Below the Land Use table is a Transactions table that logs sales and leases using a matching Parcel_ID as a foreign key. Because properties can be bought and sold multiple times, the foreign key is not required to be unique. Instead, it links each transaction to the relevant parcel.
When setting up your main table, every row should represent a unique feature. Use primary keys to ensure uniqueness and foreign keys to link related data between tables.
A good rule of thumb for building a database is to make your tables simple and focused on a specific theme, like land ownership or zoning. Plan ahead by asking yourself, “What questions do I want this table to answer?”
Want to go deeper? The science behind database indexing
Database indexes are special lookup tables that the database search engine can use to speed up data retrieval. Instead of scanning every row, the system uses the index (like an address book) to jump straight to a record. In large GIS datasets, this can mean the difference between instant results and multi-minute waits.
Consider a scenario where two parcels have the same owner name but different locations. How would composite keys help ensure data integrity in your GIS database?
Local governments use relational GIS databases to unify zoning, tax assessment, and transaction records—empowering city planners to visualize and analyze land use changes over time.
- You can explain the purpose of primary and foreign keys in relational tables.
- You understand why indexing improves query performance in large GIS datasets.
Practice designing a relational table for a GIS scenario.
- List 5 imaginary parcels, each with a unique Parcel_ID, Status, and Zoning type.
- Design a second table to log transactions (sales/leases), using Parcel_ID as a foreign key.
- Draw arrows to show how the tables relate.
Practitioners often spend considerable time planning their database structure before entering any data—getting the relationships and keys right from the start saves countless hours down the road.
How might linking tables with foreign keys make updating or analyzing spatial data more efficient in your GIS projects?
Relational database management systems (RDBMS) and well-structured tables are the backbone of modern GIS, enabling efficient data storage, linking, and analysis through primary and foreign keys.
Integrating GIS software and these databases ensures that your data stays organized and ready for smooth updates and analysis.
What is a primary key in a relational database?
Tap to revealA unique identifier for each row in a table, ensuring that each record is distinct.
Define foreign key in the context of GIS databases.
Tap to revealAn attribute in one table that links to the primary key in another table, creating a relationship between datasets.
Why use relational tables in a GIS?
Tap to revealTo efficiently organize, link, and manage spatial and attribute data without unnecessary duplication.
Each table in a GIS database must contain every detail about each feature, even if it means repeating data.
Relational databases are designed to minimize duplication by linking tables with keys, allowing details to be stored once and referenced as needed.
Think about a project where you needed to organize a lot of information (not necessarily geographic). How might using primary and foreign keys have improved your process? What challenges do you anticipate when designing a relational database for GIS?
Using primary and foreign keys to link tables keeps GIS data organized, eliminates redundancy, and enables powerful, flexible analysis.