Databases in GIS

Databases are an essential tool in modern GIS, especially for projects involving large datasets, complex queries, or multi-user environments. While file-based systems remain useful for smaller-scale tasks, the ability of spatial databases to handle advanced spatial operations, ensure data integrity, and integrate with other systems makes them indispensable for enterprise-level GIS applications. Understanding the strengths and trade-offs of databases empowers GIS professionals to choose the right tool for their needs.

Databases are structured systems for storing, managing, and retrieving data efficiently. In the context of GIS, databases play a critical role in handling spatial data, offering powerful tools for querying, analysing, and visualising geographic information. They provide a robust alternative to file-based storage systems, particularly as the volume and complexity of spatial data grow.

While files like Shapefiles or GeoJSONs are common for storing spatial data, databases offer several advantages. Files are straightforward and portable, making them ideal for small projects or data sharing. However, they can become cumbersome as datasets increase in size, complexity or number. Databases, on the other hand, centralise data storage, enabling efficient management, secure multi-user access, and support for complex queries and analysis. Spatial databases, such as PostGIS or SpatiaLite, extend traditional database functionality to handle geographic data like points, lines, polygons and raster data.

Spatial databases extend the capabilities of traditional databases to store and analyse spatial data. While non-spatial databases manage alphanumeric data with columns and rows, spatial databases introduce specialised data types (e.g. geometry and topology rules) and indexing systems (e.g. R-trees) for efficient spatial querying. They enable operations such as finding intersections, calculating distances or running spatial joins directly in the database. Tools like PostGIS, Oracle Spatial and Microsoft SQL Server Spatial are examples of spatially enabled databases.

Pros and Cons of Using Databases for Spatial Data

Pros:

1. Centralised Storage: Databases can store and manage vast amounts of data in one place, eliminating redundancy.
2. Scalability: They handle large datasets efficiently and support concurrent access by multiple users.
3. Advanced Querying: Databases enable complex spatial and attribute queries using languages like SQL, which is much more powerful than file-based tools.
4. Data Integrity and Security: Databases ensure data consistency and provide access control mechanisms to secure sensitive information.
5. Integration: Databases can integrate spatial and non-spatial data, facilitating advanced analyses.

Cons:

1. Setup Complexity: Databases require setup, configuration and maintenance, which might be overkill for small-scale projects.
2. Resource-Intensive: Databases require more computational resources and expertise compared to simple file management.
3. Less Portable: Sharing database content often involves exporting or providing remote access, which can be less straightforward than sharing a file.

Database Management Systems for Geodata

Some common Database Management Systems (DBMS) frequently used for geospatial data:

• SQLite with SpatiaLite. SpatiaLite is an extension to SQLite that enables the storage and management of spatial data within a lightweight, file-based database. The file extension is .sqlite or .db, and it supports both vector and raster data, along with spatial indexing and geospatial queries. SpatiaLite is often used for standalone GIS applications and is valued for its portability and ability to handle spatial data without the need for a separate server.
• PostgreSQL with PostGIS. PostgreSQL is an open-source relational database management system that, with the PostGIS extension, supports the storage and querying of spatial data. It is used to store large-scale vector and raster datasets with advanced spatial indexing and geospatial operations. The format does not have a specific file extension, as it is a server-based system, and data is managed through SQL queries, making it ideal for enterprise-level GIS applications and multi-user environments.
• MySQL with Spatial Extensions. MySQL, one of the most widely used open-source relational database management systems, includes support for spatial data through its spatial extensions. These extensions allow MySQL to store, query, and manipulate geometric and geographic data types, but it lacks support for 3D geometries and advanced spatial functions like raster processing or topological relationships.
• Microsoft SQL Server with Spatial Features. MS SQL Server is a relational database management system that, with the Spatial Data extension, supports the storage and querying of spatial data. It can store both vector and raster data, offering advanced spatial indexing, geospatial functions, and the ability to perform spatial queries. The format does not have a specific file extension, as it is a server-based system, and spatial data is managed within the database using SQL Server’s spatial data types and queries.
• Oracle Spatial and Graph. Oracle Spatial is an extension of the Oracle Database that enables the storage, management, and analysis of spatial data. It supports both vector and raster data, offering powerful spatial indexing, geospatial functions, and advanced querying capabilities. As a server-based system, it does not have a specific file extension, with spatial data managed through Oracle's spatial data types and SQL queries, making it ideal for enterprise-level GIS applications.
• SAP HANA with Spatial Features. SAP HANA is an in-memory, column-oriented relational database management system that supports spatial data through its SAP HANA Spatial extension. It allows for the storage and analysis of both vector and raster data, offering advanced spatial indexing and geospatial queries. As a server-based system, it does not have a specific file extension, with spatial data stored and managed within the database using SAP HANA’s spatial data types and SQL queries, making it suitable for enterprise-level GIS and real-time analytics applications.
• Google BigQuery GIS. BigQuery GIS is a cloud-based platform for storing, querying, and analyzing geospatial data within Google's BigQuery, a fully managed data warehouse designed for large-scale analytics. With its GIS capabilities, BigQuery enables users to integrate and analyze spatial data alongside traditional datasets using SQL. Spatial and non-spatial data can be combined seamlessly for multidimensional analysis. For instance, businesses can analyze sales data by region or compare demographic information with geospatial boundaries.Built on BigQuery’s scalable architecture, it can handle massive geospatial datasets with fast processing speeds, making it ideal for enterprise-level and real-time analytics.

Connecting to a Spatial Database from QGIS

QGIS offers a wide variety of options to connect to a spatial database. As of version QGIS 3.40, these options include: GeoPackage, SpatialLite, PostgreSQL, MS SQL Server, Oracle and SAP HANA database management systems.

Various database connection options in QGIS’s Open Source Data Manager.

In order to connect to a spatial database, a new connection must be defined first.

Creating a new database connection in QGIS’s Open Source Data Manager.

At least the following information must be provided when connecting to a PosgreSQL database: host, port, database name and connection name. Oracle, SAP HANA and MS SQL Server require similar options.

Defining a new connection to a PosgreSQL database “test” running on localhost:5432.

On the other hand, GeoPackage and SpatiaLite are so-called file databases, which are defined by their file and location. After selecting a table from the database, the geodata can be added to the project by clicking the “Add” button.

Connected GeoPackage with one table “kraje_cr_20210310” containing multi-polygons in a column named “geom”.

Connecting to a spatial database from WebGIS

HSLayers-based WebGIS is well integrated with the layer management system Layman. Layman is a geodata publishing system, responsible for data saving, loading, updating and access regulation. Layman stores the data in a PostGIS database. Two major types of data can be stored in Layman:

• individual map layers and
• map compositions.

Alongside that, Layman also stores information about the layer’s styles and about users and their access privilege.

In order to connect to the Layman database, a connection link must be set-up in the HsConfig object in HSLayers-NG. E.g.:

    
datasources: [
        {
          title: 'Layman Catalogue',
          url: 'https://vetfarm.org/layman-proxy',
          type: 'layman',
        },
      ]
    

If the correct link is provided and if the Layman system is running properly at the given location, it is then possible to find layers from Layman in the “Data catalogue” panel in the side-menu and map compositions from Layman in the “Compositions catalogue” panel in the side-menu.

Compositions catalogue (A) and Datasource catalogue (B) menu items in the HSLayers-based WebGIS as a mean to work with data from spatial database.