PostGIS

Spatial and Geographic Objects for PostgreSQL

The PostGIS extension adds support for geographic objects to PostgreSQL, allowing you to store, query, and manipulate spatial data. It effectively turns PostgreSQL into a spatial database. Your Nile database arrives with the PostGIS extension already enabled.

Quick Start

Let’s walk through some common PostGIS operations using a simple example of storing and querying location data.

Creating a Spatial Table

-- Create a table for storing points of interest
CREATE TABLE points_of_interest (
    tenant_id uuid,
    id INTEGER,
    name VARCHAR(100),
    type VARCHAR(50),
    -- POINT geometry in WGS84 (latitude/longitude)
    location geometry(POINT, 4326),
    PRIMARY KEY (tenant_id, id)
);

-- Create a spatial index
CREATE INDEX points_of_interest_gist ON points_of_interest USING GIST(location);

Inserting Data

-- Create a tenant first
INSERT INTO tenants (id, name) VALUES
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 'Tenant 1');

-- Insert some points of interest
INSERT INTO points_of_interest (tenant_id, id, name, type, location) VALUES
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 1, 'Central Park', 'park',
   ST_SetSRID(ST_MakePoint(-73.965355, 40.782865), 4326)),
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 2, 'Empire State', 'building',
   ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326)),
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 3, 'Statue of Liberty', 'monument',
   ST_SetSRID(ST_MakePoint(-74.044502, 40.689247), 4326));

Basic Spatial Queries

Find all points within 5km of a location:

SELECT name, 
       ST_Distance(
         location::geography,
         ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326)::geography
       ) as distance_meters
FROM points_of_interest
WHERE ST_DWithin(
        location::geography,
        ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326)::geography,
        5000  -- 5km in meters
      )
ORDER BY distance_meters;

Calculate distance between two points:

-- Distance calculations default to meters, you can multiple by 0.000621371 to get miles
SELECT ST_Distance(
    (SELECT location::geography FROM points_of_interest WHERE name = 'Central Park'),
    (SELECT location::geography FROM points_of_interest WHERE name = 'Empire State')
) as distance_meters;

Working with Areas

Create and query polygons:

-- Create a table for areas
CREATE TABLE areas (
    tenant_id uuid,
    id INTEGER,
    name VARCHAR(100),
    boundary geometry(POLYGON, 4326),
    PRIMARY KEY (tenant_id, id)
);

-- Insert multiple polygons (simplified boundaries)
INSERT INTO areas (tenant_id, id, name, boundary) VALUES
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 1, 'Central Park',
   ST_SetSRID(ST_MakePolygon(ST_GeomFromText('LINESTRING(
     -73.968285 40.785091,
     -73.961675 40.785091,
     -73.961675 40.780467,
     -73.968285 40.780467,
     -73.968285 40.785091
   )')), 4326)),
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 2, 'Area1',
   ST_SetSRID(ST_MakePolygon(ST_GeomFromText('LINESTRING(
     -73.965 40.783,
     -73.960 40.783,
     -73.960 40.779,
     -73.965 40.779,
     -73.965 40.783
   )')), 4326)),
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 3, 'Area2',
   ST_SetSRID(ST_MakePolygon(ST_GeomFromText('LINESTRING(
     -73.963 40.784,
     -73.958 40.784,
     -73.958 40.780,
     -73.963 40.780,
     -73.963 40.784
   )')), 4326));

-- Find points within the area
SELECT p.name
FROM points_of_interest p
JOIN areas a ON ST_Contains(a.boundary, p.location)
WHERE a.name = 'Central Park';

Common Operations

Coordinate Transformations

Convert between coordinate systems:

-- Convert from WGS84 (EPSG:4326) to Web Mercator (EPSG:3857)
SELECT ST_Transform(
    ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326),
    3857
);

Distance Calculations

-- Calculate distance in meters
SELECT ST_Distance(
    ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326)::geography,
    ST_SetSRID(ST_MakePoint(-73.968285, 40.785091), 4326)::geography
);

Spatial Relationships

-- Check if point is within polygon
SELECT ST_Contains(
    (SELECT boundary FROM areas WHERE name = 'Central Park'),
    (SELECT location FROM points_of_interest WHERE name = 'Empire State')
);

-- Find intersection of two polygons
SELECT ST_Intersection(a.boundary, b.boundary)
FROM areas a, areas b
WHERE a.name = 'Area1' AND b.name = 'Area2';

Geometry Creation

-- Create a point
SELECT ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326);

-- Create a line
SELECT ST_MakeLine(
    ST_MakePoint(-73.985428, 40.748817),
    ST_MakePoint(-73.968285, 40.785091)
);

-- Create a polygon
SELECT ST_MakePolygon(ST_GeomFromText('LINESTRING(
    0 0, 1 0, 1 1, 0 1, 0 0
)'));

Best Practices

1. Indexing:

   • Always create spatial indexes (GiST) on geometry columns
   • Use appropriate coordinate systems for your use case

2. Performance:

   • Use ST_DWithin instead of ST_Distance for radius searches
   • Cast to geography type for accurate earth-distance calculations
   • Consider clustering on spatial indexes for large datasets

3. Data Quality:

   • Validate geometries using ST_IsValid
   • Use appropriate SRID for your data
   • Clean up invalid geometries using ST_MakeValid

Common Use Cases

• Location-based services
• Geofencing
• Territory management
• Asset tracking
• Spatial analysis
• Map visualization
• Route planning
• Environmental analysis

Additional Resources

• PostGIS Documentation
• PostGIS Introduction
• Coordinate Systems Guide
• PostGIS in Action Book

Assistant

Responses are generated using AI and may contain mistakes.