Spatial Relationships

Understanding spatial relationships is fundamental to GIS analysis. PostGIS provides a comprehensive set of functions to determine how geometries interact with each other - whether they intersect, contain one another, touch at boundaries, or overlap partially. These relationship functions form the backbone of spatial queries, enabling complex location-based analysis and decision-making.

Introduction to Spatial Relationships

Spatial relationship functions allow you to compare geometries and determine their spatial interactions. These functions are essential for:

• Finding points within polygons (e.g., buildings within districts)
• Detecting spatial conflicts (e.g., overlapping land claims)
• Identifying adjacent features (e.g., neighboring properties)
• Performing spatial joins between datasets
• Implementing spatial business rules and constraints

Setting Up Sample Data

Let's create realistic sample data representing parcels and buildings with real-world coordinates:

-- Create parcels table
CREATE TABLE parcels (
  id SERIAL PRIMARY KEY,
  name TEXT,
  district TEXT,
  area_hectares NUMERIC,
  geom GEOMETRY(POLYGON, 4326)
);

-- Create buildings table
CREATE TABLE buildings (
  id SERIAL PRIMARY KEY,
  name TEXT,
  building_type TEXT,
  floors INTEGER,
  geom GEOMETRY(POINT, 4326)
);

-- Create roads table
CREATE TABLE roads (
  id SERIAL PRIMARY KEY,
  name TEXT,
  road_type TEXT,
  geom GEOMETRY(LINESTRING, 4326)
);

-- Insert sample parcel geometries (Delhi area)
INSERT INTO parcels (name, district, area_hectares, geom) VALUES
('Parcel A', 'Central Delhi', 5.2, ST_GeomFromText('POLYGON((77.20 28.60, 77.25 28.60, 77.25 28.65, 77.20 28.65, 77.20 28.60))', 4326)),
('Parcel B', 'Central Delhi', 3.8, ST_GeomFromText('POLYGON((77.25 28.60, 77.30 28.60, 77.30 28.65, 77.25 28.65, 77.25 28.60))', 4326)),
('Parcel C', 'South Delhi', 4.1, ST_GeomFromText('POLYGON((77.22 28.55, 77.27 28.55, 77.27 28.60, 77.22 28.60, 77.22 28.55))', 4326)),
('Parcel D', 'North Delhi', 6.3, ST_GeomFromText('POLYGON((77.15 28.65, 77.22 28.65, 77.22 28.72, 77.15 28.72, 77.15 28.65))', 4326));

-- Insert sample building locations
INSERT INTO buildings (name, building_type, floors, geom) VALUES
('Building 1', 'Office', 12, ST_Point(77.22, 28.62)),
('Building 2', 'Residential', 8, ST_Point(77.28, 28.62)),
('Building 3', 'Commercial', 15, ST_Point(77.24, 28.57)),
('Building 4', 'Industrial', 4, ST_Point(77.18, 28.68)),
('Building 5', 'Residential', 6, ST Point(77.35, 28.66)),
('Building 6', 'Office', 10, ST_Point(77.26, 28.63));

-- Insert sample roads
INSERT INTO roads (name, road_type, geom) VALUES
('Main Road', 'Highway', ST_GeomFromText('LINESTRING(77.18 28.58, 77.32 28.64)', 4326)),
('Side Street', 'Local', ST_GeomFromText('LINESTRING(77.21 28.60, 77.21 28.70)', 4326)),
('Ring Road', 'Arterial', ST_GeomFromText('LINESTRING(77.15 28.65, 77.30 28.65)', 4326));

-- Create spatial indexes
CREATE INDEX idx_parcels_geom ON parcels USING GIST (geom);
CREATE INDEX idx_buildings_geom ON buildings USING GIST (geom);
CREATE INDEX idx_roads_geom ON roads USING GIST (geom);

ST_Intersects - General Spatial Intersection

ST_Intersects is the most commonly used spatial relationship function. It returns true if geometries share any space, including touching at boundaries.

Basic Intersection Queries

-- Find buildings that intersect with parcels
SELECT 
  b.name AS building_name, 
  b.building_type,
  p.name AS parcel_name,
  p.district
FROM buildings b
JOIN parcels p ON ST_Intersects(b.geom, p.geom)
ORDER BY p.district, b.name;

-- Find roads that intersect with parcels
SELECT 
  r.name AS road_name,
  r.road_type,
  p.name AS parcel_name,
  ST_Length(ST_Intersection(r.geom, p.geom)) AS intersection_length
FROM roads r
JOIN parcels p ON ST_Intersects(r.geom, p.geom)
ORDER BY intersection_length DESC;

Intersection with Distance Buffer

-- Find buildings within 500 meters of roads
SELECT 
  b.name AS building_name,
  r.name AS road_name,
  ST_Distance(b.geom, r.geom) * 111000 AS distance_meters
FROM buildings b
CROSS JOIN roads r
WHERE ST_Intersects(b.geom, ST_Buffer(r.geom, 0.005)) -- ~500m buffer
ORDER BY distance_meters;

-- Count buildings by district using intersection
SELECT 
  p.district,
  COUNT(b.id) AS building_count,
  array_agg(b.building_type) AS building_types
FROM parcels p
LEFT JOIN buildings b ON ST_Intersects(b.geom, p.geom)
GROUP BY p.district
ORDER BY building_count DESC;

ST_Within - Complete Containment

ST_Within returns true if the first geometry is completely inside the second geometry.

-- Find buildings completely within parcels
SELECT 
  b.name AS building_name,
  b.building_type,
  p.name AS parcel_name,
  p.district,
  p.area_hectares
FROM buildings b
JOIN parcels p ON ST_Within(b.geom, p.geom)
ORDER BY p.area_hectares DESC;

-- Buildings within specific districts
SELECT 
  p.district,
  COUNT(b.id) AS buildings_within,
  array_agg(DISTINCT b.building_type) AS building_types,
  AVG(b.floors) AS avg_floors
FROM parcels p
LEFT JOIN buildings b ON ST_Within(b.geom, p.geom)
GROUP BY p.district
ORDER BY buildings_within DESC;

Nested Within Queries

-- Find buildings within parcels of a specific district
SELECT 
  b.name,
  b.building_type,
  b.floors
FROM buildings b
WHERE ST_Within(b.geom, (
  SELECT ST_Union(geom) 
  FROM parcels 
  WHERE district = 'Central Delhi'
))
ORDER BY b.floors DESC;

-- Multi-level containment analysis
SELECT 
  p.district,
  p.name AS parcel_name,
  COUNT(b.id) AS building_count,
  SUM(b.floors) AS total_floors,
  AVG(b.floors) AS avg_floors,
  MAX(b.floors) AS max_floors
FROM parcels p
LEFT JOIN buildings b ON ST_Within(b.geom, p.geom)
GROUP BY p.district, p.name, p.id
ORDER BY total_floors DESC NULLS LAST;

ST_Contains - Reverse Containment

ST_Contains is the inverse of ST_Within. It checks if the first geometry completely contains the second.

-- Find parcels that contain buildings
SELECT 
  p.name AS parcel_name,
  p.district,
  p.area_hectares,
  COUNT(b.id) AS buildings_contained,
  array_agg(b.name ORDER BY b.floors DESC) AS building_names,
  SUM(b.floors) AS total_floors
FROM parcels p
LEFT JOIN buildings b ON ST_Contains(p.geom, b.geom)
GROUP BY p.id, p.name, p.district, p.area_hectares
ORDER BY buildings_contained DESC, total_floors DESC;

-- Parcels containing specific building types
SELECT 
  p.name AS parcel_name,
  p.district,
  b.building_type,
  COUNT(b.id) AS count_of_type
FROM parcels p
JOIN buildings b ON ST_Contains(p.geom, b.geom)
WHERE b.building_type IN ('Office', 'Commercial')
GROUP BY p.name, p.district, b.building_type
ORDER BY p.district, count_of_type DESC;

ST_Touches - Boundary Relationships

ST_Touches returns true when geometries share a boundary but don't overlap in their interiors.

-- Find adjacent parcels
SELECT 
  p1.name AS parcel1,
  p2.name AS parcel2,
  p1.district AS district1,
  p2.district AS district2,
  ST_Length(ST_Intersection(p1.geom, p2.geom)) AS shared_boundary_length
FROM parcels p1
JOIN parcels p2 ON p1.id < p2.id AND ST_Touches(p1.geom, p2.geom)
ORDER BY shared_boundary_length DESC;

-- Buildings touching parcel boundaries
SELECT 
  b.name AS building_name,
  p.name AS parcel_name,
  'Boundary Location' AS relationship_type
FROM buildings b
JOIN parcels p ON ST_Touches(b.geom, p.geom)
ORDER BY p.name;

-- Roads touching parcel boundaries
SELECT 
  r.name AS road_name,
  p.name AS parcel_name,
  ST_Length(ST_Intersection(r.geom, ST_Boundary(p.geom))) AS boundary_contact_length
FROM roads r
JOIN parcels p ON ST_Touches(r.geom, p.geom)
ORDER BY boundary_contact_length DESC;

ST_Overlaps - Partial Overlapping

ST_Overlaps returns true when geometries of the same dimension share some but not all of their area.

-- Create overlapping parcels for demonstration
INSERT INTO parcels (name, district, area_hectares, geom) VALUES
('Overlapping Parcel', 'Central Delhi', 2.1, 
 ST_GeomFromText('POLYGON((77.23 28.61, 77.28 28.61, 77.28 28.64, 77.23 28.64, 77.23 28.61))', 4326));

-- Find overlapping parcels
SELECT 
  p1.name AS parcel1,
  p2.name AS parcel2,
  ST_Area(ST_Intersection(p1.geom, p2.geom)) AS overlap_area,
  ST_Area(ST_Intersection(p1.geom, p2.geom)) / ST_Area(p1.geom) * 100 AS overlap_percent_p1,
  ST_Area(ST_Intersection(p1.geom, p2.geom)) / ST_Area(p2.geom) * 100 AS overlap_percent_p2
FROM parcels p1
JOIN parcels p2 ON p1.id < p2.id AND ST_Overlaps(p1.geom, p2.geom)
ORDER BY overlap_area DESC;

-- Detect potential land use conflicts
SELECT 
  p1.name AS parcel1,
  p1.district AS district1,
  p2.name AS parcel2,
  p2.district AS district2,
  'Potential Conflict' AS issue_type,
  ST_Area(ST_Intersection(p1.geom, p2.geom)) * 111000 * 111000 AS overlap_area_m2
FROM parcels p1
JOIN parcels p2 ON p1.id != p2.id AND ST_Overlaps(p1.geom, p2.geom)
ORDER BY overlap_area_m2 DESC;

ST_Disjoint - No Spatial Relationship

ST_Disjoint returns true when geometries have no spatial relationship whatsoever.

-- Find buildings not associated with any parcel
SELECT 
  b.name AS isolated_building,
  b.building_type,
  b.floors,
  ST_X(b.geom) AS longitude,
  ST_Y(b.geom) AS latitude
FROM buildings b
WHERE NOT EXISTS (
  SELECT 1 FROM parcels p 
  WHERE ST_Intersects(b.geom, p.geom)
)
ORDER BY b.name;

-- Parcels with no buildings
SELECT 
  p.name AS empty_parcel,
  p.district,
  p.area_hectares
FROM parcels p
WHERE ST_Disjoint(p.geom, (SELECT ST_Union(geom) FROM buildings))
ORDER BY p.area_hectares DESC;

Advanced Spatial Relationship Analysis

Distance-Based Relationships

-- Find nearest parcel for each building
WITH building_parcel_distances AS (
  SELECT 
    b.id AS building_id,
    b.name AS building_name,
    p.id AS parcel_id,
    p.name AS parcel_name,
    ST_Distance(b.geom, p.geom) AS distance,
    ROW_NUMBER() OVER (PARTITION BY b.id ORDER BY ST_Distance(b.geom, p.geom)) AS rn
  FROM buildings b
  CROSS JOIN parcels p
)
SELECT 
  building_name,
  parcel_name,
  ROUND(distance * 111000, 2) AS distance_meters,
  CASE 
    WHEN distance = 0 THEN 'Within'
    WHEN distance < 0.001 THEN 'Very Close'
    WHEN distance < 0.005 THEN 'Close'
    ELSE 'Distant'
  END AS proximity_category
FROM building_parcel_distances
WHERE rn = 1
ORDER BY distance;

Complex Spatial Queries

-- Multi-criteria spatial analysis
SELECT 
  b.name AS building_name,
  b.building_type,
  b.floors,
  p.name AS parcel_name,
  p.district,
  CASE 
    WHEN ST_Within(b.geom, p.geom) THEN 'Inside Parcel'
    WHEN ST_Touches(b.geom, p.geom) THEN 'On Boundary'
    WHEN ST_DWithin(b.geom, p.geom, 0.001) THEN 'Very Close'
    ELSE 'Distant'
  END AS spatial_relationship,
  ST_Distance(b.geom, p.geom) * 111000 AS distance_meters
FROM buildings b
LEFT JOIN parcels p ON ST_Intersects(b.geom, ST_Buffer(p.geom, 0.001))
ORDER BY b.name, distance_meters;

-- Comprehensive relationship matrix
SELECT 
  'Buildings vs Parcels' AS relationship_type,
  COUNT(CASE WHEN ST_Within(b.geom, p.geom) THEN 1 END) AS within_count,
  COUNT(CASE WHEN ST_Touches(b.geom, p.geom) THEN 1 END) AS touches_count,
  COUNT(CASE WHEN ST_Intersects(b.geom, p.geom) AND NOT ST_Within(b.geom, p.geom) THEN 1 END) AS intersects_only,
  COUNT(CASE WHEN ST_Disjoint(b.geom, p.geom) THEN 1 END) AS disjoint_count
FROM buildings b
CROSS JOIN parcels p;

Combining Spatial Relationships with Attribute Filters

-- Find office buildings within commercial districts
SELECT 
  b.name AS building_name,
  b.floors,
  p.name AS parcel_name,
  p.district
FROM buildings b
JOIN parcels p ON ST_Within(b.geom, p.geom)
WHERE b.building_type = 'Office' 
  AND p.district LIKE '%Central%'
ORDER BY b.floors DESC;

-- High-rise buildings near major roads
SELECT 
  b.name AS building_name,
  b.building_type,
  b.floors,
  r.name AS nearby_road,
  ST_Distance(b.geom, r.geom) * 111000 AS distance_meters
FROM buildings b
CROSS JOIN roads r
WHERE b.floors >= 10
  AND ST_DWithin(b.geom, r.geom, 0.002) -- ~200m
  AND r.road_type IN ('Highway', 'Arterial')
ORDER BY b.floors DESC, distance_meters;

-- District-wise building analysis with spatial constraints
SELECT 
  p.district,
  COUNT(DISTINCT p.id) AS total_parcels,
  COUNT(b.id) AS total_buildings,
  COUNT(CASE WHEN b.building_type = 'Residential' THEN 1 END) AS residential_buildings,
  COUNT(CASE WHEN b.building_type = 'Office' THEN 1 END) AS office_buildings,
  COUNT(CASE WHEN b.building_type = 'Commercial' THEN 1 END) AS commercial_buildings,
  AVG(b.floors) AS avg_building_height,
  SUM(p.area_hectares) AS total_area_hectares
FROM parcels p
LEFT JOIN buildings b ON ST_Within(b.geom, p.geom)
GROUP BY p.district
ORDER BY total_buildings DESC;

Performance Optimization

Using Spatial Indexes Effectively

-- Check index usage
EXPLAIN ANALYZE
SELECT b.name, p.name
FROM buildings b
JOIN parcels p ON ST_Within(b.geom, p.geom);

-- Optimize with bounding box pre-filter
EXPLAIN ANALYZE
SELECT b.name, p.name
FROM buildings b
JOIN parcels p ON b.geom && p.geom AND ST_Within(b.geom, p.geom);

Performance Best Practices

Real-World Applications

Urban Planning

-- Zoning compliance check
SELECT 
  b.name AS building_name,
  b.building_type,
  b.floors,
  p.district,
  CASE 
    WHEN b.building_type = 'Industrial' AND p.district LIKE '%Central%' THEN 'Zoning Violation'
    WHEN b.floors > 15 AND p.district = 'Residential' THEN 'Height Violation'
    ELSE 'Compliant'
  END AS compliance_status
FROM buildings b
JOIN parcels p ON ST_Within(b.geom, p.geom)
WHERE ST_Within(b.geom, p.geom)
ORDER BY compliance_status, b.floors DESC;

Emergency Services Planning

-- Find buildings within emergency response zones
WITH emergency_zones AS (
  SELECT 
    name,
    ST_Buffer(geom, 0.01) AS response_zone -- ~1km radius
  FROM buildings 
  WHERE building_type = 'Emergency Services'
)
SELECT 
  b.name AS building_name,
  b.building_type,
  ez.name AS nearest_emergency_service,
  'Within Response Zone' AS status
FROM buildings b
JOIN emergency_zones ez ON ST_Within(b.geom, ez.response_zone)
WHERE b.building_type != 'Emergency Services'
ORDER BY b.building_type, b.name;

Common Pitfalls and Solutions

Testing and Validation

-- Validate spatial relationships
SELECT 
  'Relationship Validation' AS test_type,
  COUNT(*) AS total_tests,
  COUNT(CASE WHEN ST_Within(b.geom, p.geom) THEN 1 END) AS within_relationships,
  COUNT(CASE WHEN ST_Intersects(b.geom, p.geom) AND NOT ST_Within(b.geom, p.geom) THEN 1 END) AS intersect_only,
  COUNT(CASE WHEN NOT ST_Intersects(b.geom, p.geom) THEN 1 END) AS no_relationship
FROM buildings b
CROSS JOIN parcels p;

-- Geometry validation
SELECT 
  'Buildings' AS table_name,
  COUNT(*) AS total_geometries,
  COUNT(CASE WHEN ST_IsValid(geom) THEN 1 END) AS valid_geometries,
  COUNT(CASE WHEN NOT ST_IsValid(geom) THEN 1 END) AS invalid_geometries
FROM buildings
UNION ALL
SELECT 
  'Parcels',
  COUNT(*),
  COUNT(CASE WHEN ST_IsValid(geom) THEN 1 END),
  COUNT(CASE WHEN NOT ST_IsValid(geom) THEN 1 END)
FROM parcels;