Appendix F: PostgreSQL/PostGIS Technical Merits

Introduction¶

This document outlines the technical rationale for selecting Azure Database for PostgreSQL with the PostGIS as the enterprise geodatabase solution. The decision is based on a comprehensive evaluation of technical features, performance characteristics and data management capabilities that support the objectives of the new eMap platform.

1. Advanced Spatial Functions and Data Types¶

Azure Database for PostgreSQL with PostGIS provides a significantly richer environment for sophisticated spatial operations and data modelling.

1.1 Extensive Library of Geometry Processing Functions¶

• Technical Feature: PostgreSQL with PostGIS offers an extensive library of approximately 380+ spatial functions (vs ~90 for SQL Server/Azure SQL). This comprehensive suite enables a wide range of geometric operations and analyses.
• Practical Application: This extensive function library facilitates complex spatial modelling and precise data manipulation.
  • Advanced Buffering: Capabilities include single-sided buffering and various endcap/join styles (e.g., using ST_Buffer). This allows for the creation of accurate buffer zones e.g., for strategic fuel breaks single-sided buffers can be created along a road that account for terrain.
  • Nuanced Overlay Operations: Functions such as ST_Difference and ST_SymDifference handle complex geometries. This supports intricate planning simulations such as calculating usable land after removing protected areas and existing infrastructure or performing precise geometric subtractions/unions of land parcels for planning.

1.2 Geometry Simplification¶

• Technical Feature: PostgreSQL/PostGIS includes specific geometry simplification algorithms that preserve topology, such as ST_SimplifyPreserveTopology.
• Practical Application: This feature allows creating cartographically sound, generalised geometries for display at different map scales. For instance, simplifying complex administrative boundaries for an overview map while ensuring that adjacent polygons remain perfectly connected without creating gaps or overlaps.

-- Simplify administrative boundaries while preserving topology
SELECT ST_SimplifyPreserveTopology(geom, 10) AS simplified_geom
FROM admin_boundaries
WHERE admin_level = 2;
-- The tolerance value (10) determines the degree of simplification
-- while ensuring no topological errors are introduced.

1.3 3D Object Support and Volumetric Analysis¶

• Technical Feature: PostgreSQL/PostGIS has mature support for true 3D geometries (e.g., PolyhedralSurface, TINs) and offers a rich set of functions to analyse them including 3D intersections (ST_3DIntersection), 3D area calculations (ST_3DArea), volume calculations (ST_3DVolume), and 3D buffering.
• Practical Application: Advanced 3D support allows:
  • Planning: Line-of-sight analysis for Fire Tower placement, shadow volume calculations cast by infrastructure.
  • Geology & Environmental: Modelling and calculating the volume of subsurface mineral bodies or aquifers, flood inundation modelling.
  • Engineering: Building Information Modeling (BIM) integration, interference checking between 3D models.

2. Raster Data Capabilities¶

2.1 Mature In-Database Raster Processing & Map Algebra¶

• Technical Feature: PostgreSQL/PostGIS provides powerful in-database raster analysis capabilities including map algebra operations (e.g., ST_MapAlgebra) directly on raster data.
• Practical Application: This allows for large-scale modeling directly within the database, eliminating data movement overhead. Examples include calculating slope and aspect from a Digital Elevation Model (DEM), performing land suitability analysis by combining multiple raster layers (e.g., land use, elevation, soil type) or conducting change detection from satellite imagery; without needing to export data to external processing tools.

2.2 Integrated Raster-Vector Operations¶

• Technical Feature: PostgreSQL/PostGIS offers functions for direct interaction and analysis between raster and vector data such as ST_Intersects(raster, geometry).
• Practical Application: Enables the execution of complex spatial queries that combine different data types. For example identifying all land parcels (vector data) that intersect with a high-risk fire zone defined by a raster layer, or summarising average rainfall (raster data) for each EM sector (vector data).

-- Calculate mean elevation for each land parcel
SELECT p.parcel_id, ST_SummaryStats(ST_Clip(r.rast, p.geom)) as elevation_stats
FROM parcels p, elevation_raster r
WHERE ST_Intersects(r.rast, p.geom);

3. Indexing and Performance¶

PostgreSQL/PostGIS offers several advanced indexing features that contribute to query performance and system availability.

3.1 Concurrent Spatial Index Creation and Maintenance¶

• Technical Feature: PostgreSQL/PostGIS allows for the creation and rebuilding of spatial indices without locking out write operations on the table using commands such as CREATE INDEX CONCURRENTLY geom_idx ON features USING GIST(geom);.
• Practical Application: This is important for minimising downtime and maintaining application availability, especially when dealing with very large spatial tables where index rebuilds can take significant time.

CREATE INDEX CONCURRENTLY geom_idx ON features USING GIST(geom);
-- Application continues to function during index creation

3.2 Indexes on Geometry Expressions¶

• Technical Feature: PostgreSQL/PostGIS provides support for creating indexes on expressions or functions involving geometry columns (e.g., an index on ST_Transform(geom, 4326) or ST_Centroid(geom)).
• Practical Application: Can dramatically speed up queries that filter or join based on the result of a spatial function. This is beneficial e.g., for web mapping applications requiring on-the-fly coordinate transformation or for improving searches based on feature centroids as it eliminates the need to compute these values at query time.

3.3 Spatial Indexing (GiST/SP-GiST)¶

• Technical Feature: PostgreSQL/PostGIS offers highly optimised and flexible spatial indexing methods such as GiST (Generalised Search Tree) and SP-GiST (Space-Partitioned GiST), known for their adaptability in handling various data distributions and query types. PostGIS also allows for customisable parameters in spatial indexes.
• Practical Application: These advanced indexing options can lead to faster execution of a broader range of spatial queries, especially complex ones or those on large, distributed, or variably dense datasets (e.g., linear referencing systems).

3.4 Efficient Spatial Calculations¶

• Technical Feature: PostgreSQL/PostGIS ensures spatial predicates (e.g., ST_Contains in a WHERE clause) are evaluated at the storage engine level, filtering data as early as possible in the query execution plan.
• Practical Application: This results in faster query performance especially on large tables, by reducing the amount of data that needs to be processed by higher layers of the database engine. This speeds up all applications performing spatial filtering such as displaying features within the current map view or identifying features intersecting an analysis area.

4. Concurrency and Data Management¶

4.1 Multi Version Concurrency Control (MVCC)¶

• Technical Feature: PostgreSQL's Multi-Version Concurrency Control (MVCC) is a core characteristic where readers do not block writers, and writers do not block readers.
• Practical Application: MVCC results in faster concurrent operations, higher throughput, and fewer lock contention issues. This is particularly beneficial when many users are performing both read-heavy queries (e.g., map display) and write-intensive editing tasks. For ArcGIS versioned editing, this architecture translates to more efficient row versioning and better interaction with autovacuum processes.

4.2 Transaction-Aware Vacuuming¶

• Technical Feature: PostgreSQL/PostGIS provides targeted table and spatial index maintenance capabilities (e.g., VACUUM (INDEX_CLEANUP ON, PROCESS_MAIN OFF) features;) and granular control over autovacuum processes.
• Practical Application: This allows for targeted and less disruptive maintenance of spatial tables and their indexes. This is beneficial for maintaining optimal query performance in versioned geodatabases with minimal impact on ongoing operations.

5. Extensibility¶

5.1 Rich Ecosystem of Geospatial Extensions¶

• Technical Feature: PostgreSQL ecosystem provides powerful extensions beyond PostGIS. Examples include pgRouting for network analysis and pgPointcloud for LiDAR data management.
• Practical Application: Allows using a vast and evolving set of specialised spatial tools directly within the database.
  • pgRouting: Can be used for in-database analysis of optimal paths for emergency services or logistics planning. Network calculations can be combined with other spatial queries.
  • pgPointCloud: Enables the management, querying, and analysis of massive LiDAR datasets for detailed 3D terrain modelling, forestry analysis, or infrastructure inspection.

5.2 Geocoding and Reverse Geocoding¶

• Technical Feature: postgis_tiger_geocoder provides robust in-database geocoding functionality.
• Practical Application: This facilitates batch geocoding of large address lists, real-time address validation for data entry forms and location-aware applications where addresses need to be converted to coordinates (or vice-versa).

5.3 Topological Data Models¶

• Technical Feature: PostGIS's postgis_topology allows creation and management of true topological data models within the database. This enables the definition and enforcement of spatial relationships such as shared boundaries (edges) and adjacency between features.
• Practical Application: This is essential for managing authoritative datasets where ensuring data integrity is paramount.

6. Advanced Processing & Scalability¶

6.1 Distributed Spatial Clustering Algorithms¶

• Technical Feature: PostgreSQL/PostGIS provides in-database functions for spatial clustering such as ST_ClusterDBSCAN(geom, eps, minpoints) OVER(), which can leverage PostgreSQL's parallel execution capabilities for improved performance.
• Practical Application: This allows for identifying spatial patterns in large datasets directly within the database, such as finding hotspots for resource allocation; without requiring external processing tools.

6.2 GPU-Accelerated Processing¶

• Technical Feature: PostgreSQL offers the potential to use GPU acceleration for certain computationally intensive spatial operations through specialised functions designed for GPU offloading.
• Practical Application: This can significantly speed up computationally intensive spatial operations such as complex geometric calculations or near real-time raster analysis and simulations.

6.3 Advanced Partitioning and Tiered Data Storage¶

• Technical Feature: PostgreSQL provides native table partitioning that can be integrated with external storage, such as Azure Blob Storage, via foreign data wrappers (e.g., postgres_fdw). This allows for strategies such as time-aware sharding or attribute-based partitioning.
• Practical Application: This is beneficial for efficiently managing and querying very large, growing datasets such as as large-scale archives. Older or less frequently accessed data can reside on cheaper storage tiers but remain queryable.

6.4 Efficient Geometry Processing¶

• Technical Feature: PostgreSQL allows direct control over parallelism for geometry processing using parameters such as max_parallel_workers at the database or query level.
• Practical Application: This enables database administrators to fine-tune performance for complex, CPU-intensive spatial queries or geoprocessing tasks by explicitly allocating CPU resources for the parallel execution of spatial functions.

6.5 Optimised XML & JSON Column Handling¶

• Technical Feature: PostgreSQL offers efficient storage mechanisms for XML columns.
• Practical Application: This leads to more efficient storage and faster retrieval of ArcGIS geodatabase metadata which extensively uses XML.