Understanding Projections

One of the most fundamental concepts in spatial databases is understanding how the three-dimensional Earth is represented on two-dimensional maps and in computer systems. Projections, coordinate reference systems, and spatial transformations form the foundation of accurate spatial analysis in PostGIS. Mastering these concepts is essential for anyone working with spatial data.

What Are Projections?

Maps flatten the Earth—which is a 3D sphere—onto a 2D plane. To accomplish this transformation accurately, we use mathematical projections. Each projection transforms geographic coordinates (latitude/longitude) into flat coordinates (X/Y), but this transformation always involves some distortion.

The choice of projection affects how distances, areas, angles, and shapes are represented. No single projection can preserve all these properties simultaneously, so different projections are optimized for different purposes.

Understanding Coordinate Reference Systems (CRS)

A Coordinate Reference System (CRS) defines how coordinates relate to real locations on Earth. In PostGIS, every geometry is tagged with a Spatial Reference ID (SRID) that identifies which coordinate system the geometry uses.

Common SRIDs and Their Uses

Checking and Setting SRIDs

-- Check the SRID of existing geometries
SELECT 
  name,
  ST_SRID(geom) AS current_srid
FROM cities;

-- Set SRID for geometries without one
UPDATE cities 
SET geom = ST_SetSRID(geom, 4326) 
WHERE ST_SRID(geom) = 0;

-- Create geometry with specific SRID
SELECT ST_GeomFromText('POINT(77.59 12.97)', 4326) AS point_wgs84;

The Spatial Reference System Catalog

PostGIS maintains a comprehensive catalog of coordinate reference systems in the spatial_ref_sys table:

-- View available coordinate systems
SELECT 
  srid,
  auth_name,
  auth_srid,
  srtext
FROM spatial_ref_sys 
WHERE srid IN (4326, 3857, 32643)
ORDER BY srid;

-- Search for coordinate systems by name
SELECT srid, srtext
FROM spatial_ref_sys
WHERE srtext ILIKE '%UTM%India%'
LIMIT 5;

-- Find coordinate systems for a specific region
SELECT srid, srtext
FROM spatial_ref_sys
WHERE srtext ILIKE '%India%' OR srtext ILIKE '%Indian%'
ORDER BY srid;

Understanding Projection Parameters

Coordinate Transformation with ST_Transform

The ST_Transform function converts geometries from one coordinate system to another:

-- Transform from WGS84 to Web Mercator
SELECT 
  name,
  geom AS original_wgs84,
  ST_Transform(geom, 3857) AS web_mercator
FROM cities
WHERE ST_SRID(geom) = 4326;

-- Transform to local UTM for accurate measurements
SELECT 
  name,
  ST_Area(ST_Transform(geom, 32643)) AS area_square_meters
FROM districts
WHERE ST_SRID(geom) = 4326;

-- Batch transformation
UPDATE cities 
SET geom_utm = ST_Transform(geom, 32643)
WHERE ST_SRID(geom) = 4326;

Transformation Validation

-- Ensure geometries have correct SRID before transforming
SELECT 
  COUNT(*) AS total_records,
  COUNT(CASE WHEN ST_SRID(geom) = 0 THEN 1 END) AS missing_srid,
  COUNT(CASE WHEN ST_SRID(geom) = 4326 THEN 1 END) AS wgs84_records
FROM spatial_data;

-- Fix missing SRIDs before transformation
UPDATE spatial_data 
SET geom = ST_SetSRID(geom, 4326) 
WHERE ST_SRID(geom) = 0 
  AND longitude BETWEEN -180 AND 180 
  AND latitude BETWEEN -90 AND 90;

Choosing the Right Projection

Geographic vs Projected Coordinates

Projection Selection Guidelines

-- For global web mapping (use Web Mercator)
SELECT ST_Transform(geom, 3857) FROM global_data;

-- For accurate area calculations (use appropriate UTM zone)
SELECT 
  name,
  ST_Area(ST_Transform(geom, 32643)) / 10000 AS area_hectares
FROM land_parcels
WHERE country = 'India';

-- For distance calculations (use local projected system)
SELECT 
  ST_Distance(
    ST_Transform(point1, 32643),
    ST_Transform(point2, 32643)
  ) AS distance_meters
FROM location_pairs;

UTM Zones and Regional Projections

Understanding UTM Zones

Universal Transverse Mercator (UTM) divides the world into 60 zones, each 6 degrees wide. Each zone has its own SRID for northern and southern hemispheres.

-- Function to determine UTM zone from longitude
CREATE OR REPLACE FUNCTION get_utm_srid(longitude FLOAT, latitude FLOAT)
RETURNS INTEGER AS $$
DECLARE
  zone INTEGER;
  srid INTEGER;
BEGIN
  zone := FLOOR((longitude + 180) / 6) + 1;
  
  IF latitude >= 0 THEN
    srid := 32600 + zone;  -- Northern hemisphere
  ELSE
    srid := 32700 + zone;  -- Southern hemisphere
  END IF;
  
  RETURN srid;
END;
$$ LANGUAGE plpgsql;

-- Use the function to get appropriate UTM SRID
SELECT 
  name,
  longitude,
  latitude,
  get_utm_srid(longitude, latitude) AS utm_srid
FROM cities;

Common UTM Zones by Region

Real-World Applications

Urban Planning Analysis

-- Calculate building densities using appropriate projection
WITH building_stats AS (
  SELECT 
    district_name,
    ST_Area(ST_Transform(district_boundary, 32643)) / 10000 AS area_hectares,
    COUNT(buildings.id) AS building_count
  FROM districts
  LEFT JOIN buildings ON ST_Within(
    ST_Transform(buildings.geom, 32643),
    ST_Transform(districts.district_boundary, 32643)
  )
  GROUP BY district_name, district_boundary
)
SELECT 
  district_name,
  area_hectares,
  building_count,
  ROUND(building_count / area_hectares, 2) AS buildings_per_hectare
FROM building_stats
ORDER BY buildings_per_hectare DESC;

Environmental Analysis

-- Buffer analysis using appropriate projection
SELECT 
  facility_name,
  ST_Transform(
    ST_Buffer(
      ST_Transform(facility_location, 32643),
      1000  -- 1km buffer in meters
    ),
    4326
  ) AS buffer_zone_wgs84
FROM industrial_facilities;

-- Calculate watershed areas
SELECT 
  watershed_name,
  ST_Area(ST_Transform(geom, 32643)) / 1000000 AS area_sq_km
FROM watersheds
ORDER BY area_sq_km DESC;

Common Pitfalls and Solutions

Projection-Related Errors

Debugging Projection Issues

-- Check for mixed SRIDs in a table
SELECT 
  ST_SRID(geom) AS srid,
  COUNT(*) AS count
FROM spatial_table
GROUP BY ST_SRID(geom)
ORDER BY count DESC;

-- Validate transformation results
SELECT 
  name,
  ST_X(geom) AS original_x,
  ST_Y(geom) AS original_y,
  ST_X(ST_Transform(geom, 3857)) AS transformed_x,
  ST_Y(ST_Transform(geom, 3857)) AS transformed_y
FROM test_points
WHERE ST_SRID(geom) = 4326;

-- Check for geometries outside valid coordinate ranges
SELECT name
FROM locations
WHERE ST_SRID(geom) = 4326
  AND (ST_X(geom) < -180 OR ST_X(geom) > 180 
       OR ST_Y(geom) < -90 OR ST_Y(geom) > 90);

Performance Considerations

Transformation Optimization

-- Create indexes on transformed geometries for frequent queries
CREATE INDEX idx_cities_utm ON cities USING GIST(ST_Transform(geom, 32643));

-- Store pre-transformed geometries for better performance
ALTER TABLE cities ADD COLUMN geom_utm GEOMETRY(Point, 32643);
UPDATE cities SET geom_utm = ST_Transform(geom, 32643) WHERE ST_SRID(geom) = 4326;
CREATE INDEX idx_cities_utm_stored ON cities USING GIST(geom_utm);

Best Practices

1. Always specify SRID: Never leave geometries without a spatial reference system
2. Choose appropriate projections: Match the projection to your analysis needs
3. Validate before transforming: Ensure source SRID is correct
4. Use local projections for measurements: UTM zones for accurate distance/area calculations
5. Document your choices: Record which projections you use and why
6. Test transformation results: Verify that transformed coordinates make sense
7. Consider performance: Pre-transform frequently used geometries

Projection Decision Matrix