PostgreSQL Geospatial Data: An Introduction to PostGIS

Table of Contents

• What is PostGIS?
• Installing PostGIS
• Key PostGIS Concepts
  • Spatial Data Types: Geometry vs. Geography
  • Spatial Reference Systems (SRS)
  • Core Spatial Types
• Basic Spatial Operations
  • Creating Spatial Data
  • Querying Spatial Relationships
• Advanced Spatial Features
  • Spatial Indexing
  • Spatial Joins
  • Coordinate Transformations
• Real-World Use Cases
• Performance Considerations
• Conclusion
• References

What is PostGIS?

PostGIS is an open-source extension for PostgreSQL that adds geospatial capabilities to the database. Released in 2001, it is maintained by a global community and adheres to the Open Geospatial Consortium (OGC) standards, ensuring compatibility with tools like QGIS, ArcGIS, and GeoPandas.

At its core, PostGIS extends PostgreSQL with:

• Spatial data types (e.g., Point, LineString, Polygon).
• Spatial functions for analysis (e.g., distance calculations, containment checks).
• Spatial indexes (e.g., GIST indexes) for fast querying.
• Support for spatial reference systems (SRS) (e.g., WGS84, UTM).

Installing PostGIS

PostGIS is easy to install on most operating systems. Below are simplified steps for common platforms.

Prerequisites

• A running PostgreSQL instance (version 12+ recommended; PostGIS 3.x requires PostgreSQL 12+).

1. Linux (Ubuntu/Debian)

Use apt to install the PostGIS package:

# Update package lists  
sudo apt update  

# Install PostGIS (replace "postgresql-16" with your PostgreSQL version)  
sudo apt install postgresql-16-postgis-3  

2. macOS

Use Homebrew:

# Install PostgreSQL (if not already installed)  
brew install postgresql  

# Install PostGIS  
brew install postgis  

3. Windows

• Download PostgreSQL from the official website.
• During installation, select the “PostGIS” component in the “Stack Builder” tool.

Verifying Installation

Once installed, enable PostGIS in a PostgreSQL database:

-- Create a new database (or use an existing one)  
CREATE DATABASE my_geodb;  

-- Connect to the database  
\c my_geodb  

-- Enable PostGIS extension  
CREATE EXTENSION postgis;  

-- Verify installation (should return PostGIS version info)  
SELECT PostGIS_full_version();  

Sample output:

postgis_full_version                                                                 
--------------------------------------------------------------------------------------
POSTGIS="3.4.2 3.4.2" [EXTENSION] PGSQL="160" GEOS="3.11.2-CAPI-1.17.2" PROJ="9.2.1"…

Key PostGIS Concepts

To work effectively with PostGIS, you need to understand its core spatial concepts.

Spatial Data Types: Geometry vs. Geography

PostGIS offers two primary spatial data types: geometry and geography.

Spatial Reference Systems (SRS)

Spatial data is meaningless without a spatial reference system (SRS)—a framework that defines how coordinates map to the Earth’s surface. PostGIS uses EPSG codes to identify SRS.

• EPSG:4326 (WGS84): The most common SRS for global data (latitude/longitude in degrees). Used by GPS and most mapping apps.
• EPSG:32633 (UTM Zone 33N): A local SRS for northern Europe (meters). Ideal for high-precision local analyses.

Always specify the SRS when creating spatial columns:

-- Create a table with a geometry column (Point in WGS84)  
CREATE TABLE cities (  
  id SERIAL PRIMARY KEY,  
  name TEXT,  
  location GEOMETRY(Point, 4326)  -- SRS=4326 (WGS84)  
);  

Core Spatial Types

PostGIS supports all OGC-standard spatial types. Here are the most common:

Basic Spatial Operations

PostGIS provides hundreds of spatial functions for creating, querying, and analyzing data. Let’s explore the most useful ones with examples.

Creating Spatial Data

Use PostGIS functions to convert text or numeric coordinates into geometry/geography objects.

Example 1: Insert a Point

Insert a city’s location (latitude/longitude) into the cities table:

-- Insert New York City (EPSG:4326: longitude, latitude)  
INSERT INTO cities (name, location)  
VALUES ('New York City', ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326));  

-- Insert London  
INSERT INTO cities (name, location)  
VALUES ('London', ST_SetSRID(ST_MakePoint(-0.1278, 51.5074), 4326));  

• ST_MakePoint(x, y): Creates a 2D point from x/y coordinates.
• ST_SetSRID(geom, srid): Assigns an SRS (e.g., 4326) to the geometry.

Example 2: Create a Polygon

Define a polygon representing a park boundary:

-- Create a parks table  
CREATE TABLE parks (  
  id SERIAL PRIMARY KEY,  
  name TEXT,  
  boundary GEOMETRY(Polygon, 4326)  
);  

-- Insert Central Park (simplified coordinates)  
INSERT INTO parks (name, boundary)  
VALUES (  
  'Central Park',  
  ST_SetSRID(  
    ST_GeomFromText('POLYGON((-73.9819 40.7644, -73.9492 40.7644, -73.9492 40.7831, -73.9819 40.7831, -73.9819 40.7644))'),  
    4326  
  )  
);  

• ST_GeomFromText(wkt): Creates a geometry from Well-Known Text (WKT), a human-readable format for spatial data.

Querying Spatial Relationships

PostGIS shines at querying spatial relationships (e.g., “Is this point inside that polygon?”). Here are common functions:

1. ST_Contains(geom1, geom2)

Returns true if geom1 fully contains geom2.

Example: Find cities inside Central Park (spoiler: none, but let’s test!):

SELECT c.name  
FROM cities c, parks p  
WHERE p.name = 'Central Park'  
  AND ST_Contains(p.boundary, c.location);  

2. ST_DWithin(geom1, geom2, distance)

Returns true if geom1 and geom2 are within distance units of each other.

Example: Find cities within 500 km of London (using geography for spherical accuracy):

-- Add a geography column to cities (convert geometry to geography)  
ALTER TABLE cities ADD COLUMN location_geo GEOGRAPHY(Point, 4326);  
UPDATE cities SET location_geo = ST_GeographyFromText(ST_AsText(location));  

-- Query: Cities within 500 km of London  
SELECT c.name, ST_Distance(c.location_geo, london.location_geo)/1000 AS distance_km  
FROM cities c, (SELECT location_geo FROM cities WHERE name = 'London') AS london  
WHERE ST_DWithin(c.location_geo, london.location_geo, 500000); -- 500,000 meters = 500 km  

Output:

name       | distance_km  
-----------|-------------  
London     | 0.0  
Paris      | 343.7  -- (if Paris were in the table)  

3. ST_Distance(geom1, geom2)

Calculates the distance between two geometries (units depend on SRS: meters for geography, planar units for geometry).

Example: Distance between New York and London (in km):

SELECT ST_Distance(  
  (SELECT location_geo FROM cities WHERE name = 'New York City'),  
  (SELECT location_geo FROM cities WHERE name = 'London')  
)/1000 AS distance_km;  

Output: ~5580 km (accurate for geography type).

Advanced Spatial Features

PostGIS offers advanced tools for scaling and optimizing geospatial workflows.

Spatial Indexing

Without indexes, spatial queries (e.g., “find all points in a polygon”) can be slow on large datasets. PostGIS uses GIST (Generalized Search Tree) indexes to speed up these queries.

Create a GIST index on a geometry or geography column:

-- Index the cities' location column  
CREATE INDEX idx_cities_location ON cities USING GIST (location);  

-- Index the parks' boundary column  
CREATE INDEX idx_parks_boundary ON parks USING GIST (boundary);  

When to use GIST indexes: Always index columns used in WHERE clauses with spatial functions (e.g., ST_Contains, ST_DWithin).

Coordinate Transformations

Data often comes in different SRS (e.g., WGS84 for GPS, UTM for local surveys). Use ST_Transform to convert between them.

Example: Convert a WGS84 point (EPSG:4326) to UTM Zone 33N (EPSG:32633):

SELECT ST_AsText(  
  ST_Transform(  
    ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326),  -- WGS84 point  
    32633  -- UTM Zone 33N (meters)  
  )  
);  

Output: POINT(583774.6 4507522.3) (UTM coordinates in meters).

Spatial Joins

Spatial joins combine rows from two tables based on spatial relationships (e.g., “join cities to the parks that contain them”).

Example: Find all cities contained within any park:

SELECT c.name AS city_name, p.name AS park_name  
FROM cities c  
JOIN parks p ON ST_Contains(p.boundary, c.location);  

Real-World Use Cases

PostGIS powers applications across industries. Here are a few examples:

1. Logistics & Transportation

• Track delivery vehicles in real time using Point data.
• Use ST_DWithin to find the nearest warehouse to a customer.

2. Agriculture

• Map crop fields with Polygon data to monitor growth.
• Analyze soil moisture by joining Raster (satellite imagery) with Vector (field boundaries).

3. Urban Planning

• Use ST_Intersection to identify overlapping zoning districts.
• Calculate population density with ST_Area (area of a polygon) and census data.

4. Environmental Science

• Model wildlife habitats with MultiPolygon data.
• Track deforestation by comparing Polygon datasets over time.

Performance Considerations

To keep PostGIS queries fast, follow these best practices:

• Use geography for global data: For large areas (e.g., country-wide analysis), geography ensures accurate distance calculations.
• Simplify geometries: Use ST_Simplify(geom, tolerance) to reduce vertex count in complex polygons (e.g., coastlines).
• Analyze tables: Run ANALYZE after creating indexes to update PostgreSQL’s statistics:

  ANALYZE cities;  

• Avoid SELECT *: Only fetch columns needed, especially for large spatial datasets.

Conclusion

PostGIS transforms PostgreSQL into a powerful geospatial database, enabling you to store, query, and analyze location data with SQL. Whether you’re building a mapping app or conducting scientific research, PostGIS simplifies complex spatial workflows.

Ready to dive deeper? Explore the PostGIS documentation or experiment with sample datasets (e.g., Natural Earth).

References

• PostGIS Official Documentation
• PostgreSQL Spatial Data Types
• Open Geospatial Consortium (OGC) Standards
• Natural Earth Data (free geospatial datasets)
• PostGIS in Action (2nd Edition) by Regina Obe & Leo Hsu (Apress)