PostGIS

Complete guide to geospatial data analysis in Tacnode using PostGIS extension for spatial queries, geometric operations, and location-based analytics.

PostGIS adds geospatial capabilities to Tacnode, letting you store, index, and analyze geographic data. This guide covers spatial data types, common operations, and real-world use cases for location-based analytics.

Quick Reference

Setup and Installation

Enable PostGIS Extension

-- Enable PostGIS for spatial operations
CREATE EXTENSION IF NOT EXISTS postgis;

-- Verify installation and version
SELECT PostGIS_full_version();

-- Check available spatial reference systems
SELECT srid, auth_name, auth_srid, srtext 
FROM spatial_ref_sys 
WHERE srid IN (4326, 3857)  -- WGS84 and Web Mercator
LIMIT 2;

Spatial Data Types

-- Common spatial data types
CREATE TABLE locations_demo (
    id SERIAL PRIMARY KEY,
    name TEXT,
    
    -- Point (0D) - stores lat/lng coordinates
    point_location GEOMETRY(POINT, 4326),
    
    -- LineString (1D) - stores routes, paths
    route GEOMETRY(LINESTRING, 4326),
    
    -- Polygon (2D) - stores areas, boundaries  
    boundary GEOMETRY(POLYGON, 4326),
    
    -- Geography type - uses ellipsoidal calculations
    geo_location GEOGRAPHY(POINT, 4326),
    
    created_at TIMESTAMP DEFAULT NOW()
) USING COLUMNAR;

Fundamental Spatial Operations

Creating and Inserting Spatial Data

-- Create sample datasets
CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name TEXT,
    country TEXT,
    population INTEGER,
    location GEOMETRY(POINT, 4326)
) USING COLUMNAR;

CREATE TABLE countries (
    id SERIAL PRIMARY KEY,
    name TEXT,
    iso_code CHAR(2),
    boundary GEOMETRY(POLYGON, 4326)
) USING COLUMNAR;

-- Insert city data with coordinates
INSERT INTO cities (name, country, population, location) VALUES
('New York', 'US', 8400000, ST_GeomFromText('POINT(-74.006 40.7128)', 4326)),
('London', 'UK', 9000000, ST_GeomFromText('POINT(-0.1276 51.5074)', 4326)),
('Tokyo', 'JP', 14000000, ST_GeomFromText('POINT(139.6917 35.6895)', 4326)),
('Paris', 'FR', 2200000, ST_GeomFromText('POINT(2.3522 48.8566)', 4326)),
('Sydney', 'AU', 5300000, ST_GeomFromText('POINT(151.2093 -33.8688)', 4326));

-- Insert country boundaries (simplified rectangles for demo)
INSERT INTO countries (name, iso_code, boundary) VALUES
('United States', 'US', ST_GeomFromText('POLYGON((-125 25, -66 25, -66 49, -125 49, -125 25))', 4326)),
('United Kingdom', 'UK', ST_GeomFromText('POLYGON((-8 50, 2 50, 2 60, -8 60, -8 50))', 4326)),
('France', 'FR', ST_GeomFromText('POLYGON((-5 42, 8 42, 8 51, -5 51, -5 42))', 4326));

Distance Calculations

-- Calculate distances between cities
SELECT 
    c1.name AS city1,
    c2.name AS city2,
    ROUND(ST_Distance(c1.location::geography, c2.location::geography) / 1000, 2) AS distance_km
FROM cities c1, cities c2
WHERE c1.id < c2.id
ORDER BY distance_km
LIMIT 5;

-- Find cities within 1000km of Paris
SELECT 
    name,
    country,
    ROUND(ST_Distance(
        location::geography,
        (SELECT location::geography FROM cities WHERE name = 'Paris')
    ) / 1000, 2) AS distance_km
FROM cities
WHERE ST_DWithin(
    location::geography,
    (SELECT location::geography FROM cities WHERE name = 'Paris'),
    1000000  -- 1000km in meters
)
AND name != 'Paris'
ORDER BY distance_km;

Spatial Relationships

-- Find which cities are in their respective countries
SELECT 
    c.name AS city,
    co.name AS country,
    CASE 
        WHEN ST_Contains(co.boundary, c.location) THEN 'Inside'
        ELSE 'Outside'
    END AS relationship
FROM cities c
LEFT JOIN countries co ON c.country = co.iso_code;

-- Find cities not contained in any defined country boundary
SELECT c.name, c.country
FROM cities c
WHERE NOT EXISTS (
    SELECT 1 FROM countries co 
    WHERE ST_Contains(co.boundary, c.location)
);

Real-World Use Cases

Store Locator System

-- Create stores and customers tables
CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name TEXT,
    address TEXT,
    store_type TEXT,
    location GEOMETRY(POINT, 4326)
) USING COLUMNAR;

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    home_location GEOMETRY(POINT, 4326)
) USING COLUMNAR;

-- Sample data
INSERT INTO stores (name, address, store_type, location) VALUES
('Downtown Store', '123 Main St', 'flagship', ST_GeomFromText('POINT(-74.005 40.7127)', 4326)),
('Mall Location', '456 Shopping Center', 'standard', ST_GeomFromText('POINT(-74.010 40.7200)', 4326)),
('Express Shop', '789 Quick Ave', 'express', ST_GeomFromText('POINT(-73.995 40.7050)', 4326));

INSERT INTO customers (name, home_location) VALUES
('Alice Johnson', ST_GeomFromText('POINT(-74.007 40.7140)', 4326)),
('Bob Smith', ST_GeomFromText('POINT(-74.000 40.7080)', 4326));

-- Find nearest stores for each customer
WITH customer_distances AS (
    SELECT 
        c.id AS customer_id,
        c.name AS customer_name,
        s.id AS store_id,
        s.name AS store_name,
        s.store_type,
        ROUND(ST_Distance(c.home_location::geography, s.location::geography), 0) AS distance_meters,
        ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY ST_Distance(c.home_location::geography, s.location::geography)) AS rank
    FROM customers c
    CROSS JOIN stores s
)
SELECT 
    customer_name,
    store_name,
    store_type,
    distance_meters || 'm' AS distance
FROM customer_distances
WHERE rank <= 2  -- Top 2 nearest stores per customer
ORDER BY customer_name, rank;

Delivery Zone Management

-- Create delivery zones
CREATE TABLE delivery_zones (
    id SERIAL PRIMARY KEY,
    zone_name TEXT,
    delivery_fee DECIMAL(5,2),
    boundary GEOMETRY(POLYGON, 4326)
) USING COLUMNAR;

-- Define circular delivery zones using buffers
INSERT INTO delivery_zones (zone_name, delivery_fee, boundary)
SELECT 
    'Zone ' || s.id || ' - ' || s.name,
    CASE 
        WHEN s.store_type = 'express' THEN 5.99
        WHEN s.store_type = 'standard' THEN 3.99
        ELSE 0.00
    END,
    ST_Buffer(s.location::geography, 2000)::geometry  -- 2km radius
FROM stores s;

-- Check which delivery zone a customer address falls into
SELECT 
    c.name AS customer,
    dz.zone_name,
    dz.delivery_fee,
    ST_Distance(c.home_location::geography, 
               (SELECT location::geography FROM stores WHERE id = 1)) AS distance_to_store
FROM customers c
JOIN delivery_zones dz ON ST_Contains(dz.boundary, c.home_location)
ORDER BY c.name;

Service Area Analysis

-- Create service areas and locations
CREATE TABLE hospitals (
    id SERIAL PRIMARY KEY,
    name TEXT,
    capacity INTEGER,
    location GEOMETRY(POINT, 4326)
) USING COLUMNAR;

CREATE TABLE neighborhoods (
    id SERIAL PRIMARY KEY,
    name TEXT,
    population INTEGER,
    area GEOMETRY(POLYGON, 4326)
) USING COLUMNAR;

-- Sample hospital data
INSERT INTO hospitals (name, capacity, location) VALUES
('General Hospital', 500, ST_GeomFromText('POINT(-74.003 40.7150)', 4326)),
('City Medical Center', 300, ST_GeomFromText('POINT(-74.012 40.7080)', 4326));

-- Sample neighborhood data (simplified squares)
INSERT INTO neighborhoods (name, population, area) VALUES
('Downtown', 15000, ST_GeomFromText('POLYGON((-74.010 40.710, -74.000 40.710, -74.000 40.720, -74.010 40.720, -74.010 40.710))', 4326)),
('Midtown', 25000, ST_GeomFromText('POLYGON((-74.015 40.705, -74.005 40.705, -74.005 40.715, -74.015 40.715, -74.015 40.705))', 4326));

-- Calculate hospital service coverage
SELECT 
    h.name AS hospital,
    n.name AS neighborhood,
    n.population,
    ROUND(ST_Distance(
        ST_Centroid(n.area)::geography, 
        h.location::geography
    ) / 1000, 2) AS distance_km,
    CASE 
        WHEN ST_Distance(ST_Centroid(n.area)::geography, h.location::geography) <= 5000 
        THEN 'Adequate Coverage'
        ELSE 'Underserved'
    END AS service_level
FROM hospitals h
CROSS JOIN neighborhoods n
ORDER BY h.name, distance_km;

Route and Transportation Analysis

-- Create transportation infrastructure
CREATE TABLE roads (
    id SERIAL PRIMARY KEY,
    name TEXT,
    road_type TEXT,  -- highway, arterial, local
    speed_limit INTEGER,
    path GEOMETRY(LINESTRING, 4326)
) USING COLUMNAR;

CREATE TABLE bus_stops (
    id SERIAL PRIMARY KEY,
    stop_name TEXT,
    routes TEXT[],
    location GEOMETRY(POINT, 4326)
) USING COLUMNAR;

-- Sample road data
INSERT INTO roads (name, road_type, speed_limit, path) VALUES
('Highway 101', 'highway', 65, ST_GeomFromText('LINESTRING(-74.020 40.700, -74.010 40.710, -74.000 40.720)', 4326)),
('Main Street', 'arterial', 35, ST_GeomFromText('LINESTRING(-74.015 40.705, -74.005 40.715, -73.995 40.725)', 4326)),
('Oak Avenue', 'local', 25, ST_GeomFromText('LINESTRING(-74.008 40.708, -74.008 40.718)', 4326));

-- Sample bus stop data
INSERT INTO bus_stops (stop_name, routes, location) VALUES
('Central Station', ARRAY['Route 1', 'Route 3'], ST_GeomFromText('POINT(-74.006 40.7128)', 4326)),
('Mall Stop', ARRAY['Route 2'], ST_GeomFromText('POINT(-74.010 40.7200)', 4326));

-- Find roads that intersect with bus routes (simplified analysis)
SELECT 
    r.name AS road_name,
    r.road_type,
    COUNT(bs.id) AS nearby_bus_stops,
    STRING_AGG(bs.stop_name, ', ') AS stop_names
FROM roads r
LEFT JOIN bus_stops bs ON ST_DWithin(r.path::geography, bs.location::geography, 200)  -- 200m buffer
GROUP BY r.id, r.name, r.road_type
ORDER BY nearby_bus_stops DESC;

Advanced Spatial Operations

Spatial Aggregation and Analysis

-- Calculate city density by country
SELECT 
    co.name AS country,
    COUNT(c.id) AS city_count,
    ROUND(ST_Area(co.boundary::geography) / 1000000, 2) AS area_sq_km,
    ROUND(COUNT(c.id)::float / (ST_Area(co.boundary::geography) / 1000000), 4) AS cities_per_sq_km
FROM countries co
LEFT JOIN cities c ON ST_Contains(co.boundary, c.location)
GROUP BY co.id, co.name, co.boundary
ORDER BY cities_per_sq_km DESC;

-- Find geographic center of cities by country
SELECT 
    co.name AS country,
    COUNT(c.id) AS city_count,
    ST_AsText(ST_Centroid(ST_Collect(c.location))) AS geographic_center
FROM countries co
JOIN cities c ON ST_Contains(co.boundary, c.location)
GROUP BY co.id, co.name
HAVING COUNT(c.id) > 0;

Proximity and Buffer Analysis

-- Create environmental monitoring points
CREATE TABLE pollution_sensors (
    id SERIAL PRIMARY KEY,
    sensor_type TEXT,
    reading_value DECIMAL(8,2),
    location GEOMETRY(POINT, 4326)
) USING COLUMNAR;

INSERT INTO pollution_sensors (sensor_type, reading_value, location) VALUES
('air_quality', 45.2, ST_GeomFromText('POINT(-74.005 40.7128)', 4326)),
('noise_level', 68.5, ST_GeomFromText('POINT(-74.010 40.7200)', 4326)),
('air_quality', 52.1, ST_GeomFromText('POINT(-73.995 40.7050)', 4326));

-- Find areas within high pollution zones
WITH pollution_zones AS (
    SELECT 
        sensor_type,
        reading_value,
        ST_Buffer(location::geography, 500)::geometry AS impact_zone  -- 500m radius
    FROM pollution_sensors
    WHERE reading_value > 50  -- Above threshold
)
SELECT 
    c.name AS affected_city,
    pz.sensor_type,
    pz.reading_value,
    'Within impact zone' AS status
FROM cities c
JOIN pollution_zones pz ON ST_Intersects(c.location, pz.impact_zone);

Common Patterns and Best Practices

Data Validation

-- Validate spatial data integrity
SELECT 
    name,
    ST_IsValid(location) AS point_valid,
    CASE 
        WHEN ST_SRID(location) = 4326 THEN 'Correct SRID'
        ELSE 'Wrong SRID: ' || ST_SRID(location)
    END AS srid_check
FROM cities;

-- Fix invalid geometries
UPDATE spatial_table 
SET geom = ST_MakeValid(geom) 
WHERE NOT ST_IsValid(geom);

Coordinate System Management

-- Transform between coordinate systems
SELECT 
    name,
    ST_AsText(location) AS wgs84,
    ST_AsText(ST_Transform(location, 3857)) AS web_mercator
FROM cities
LIMIT 3;

-- Set SRID if missing
UPDATE table_name 
SET geom = ST_SetSRID(geom, 4326) 
WHERE ST_SRID(geom) = 0;

Common Gotchas to Avoid

1. Mixed Coordinate Systems: Always verify SRID consistency
2. Geography vs Geometry: Use geography for accurate distance calculations
3. Performance: Always create spatial indexes on geometry columns
4. Precision: Consider precision requirements for your use case
5. Validation: Check geometry validity before complex operations

PostGIS Reference

Geometry and Geography Types

PostGIS provides two primary spatial data types:

Geometry Type

• Uses planar (Cartesian) coordinate system
• Faster for calculations within small areas
• Measurements are in the units of the coordinate system (typically degrees for SRID 4326)

Geography Type

• Uses ellipsoidal (spherical) coordinate system
• More accurate for long-distance calculations
• Measurements are always in meters

-- Creating columns with specific geometry types
CREATE TABLE spatial_data (
    id SERIAL PRIMARY KEY,
    point_geom GEOMETRY(POINT, 4326),           -- 2D point
    point_geog GEOGRAPHY(POINT, 4326),          -- Geographic point
    line_geom GEOMETRY(LINESTRING, 4326),       -- 2D line
    polygon_geom GEOMETRY(POLYGON, 4326),       -- 2D polygon
    multipoint_geom GEOMETRY(MULTIPOINT, 4326), -- Multiple points
    geom_collection GEOMETRY(GEOMETRYCOLLECTION, 4326)  -- Mixed geometries
);

Spatial Reference Systems (SRID)

Common spatial reference systems:

-- Query available spatial reference systems
SELECT srid, auth_name, auth_srid, srtext
FROM spatial_ref_sys
WHERE auth_name = 'EPSG' AND srid IN (4326, 3857, 2163);

-- Transform between coordinate systems
SELECT ST_Transform(geom, 3857) FROM my_table WHERE ST_SRID(geom) = 4326;

Geometry Constructors

Functions to create geometry objects:

-- Various ways to create points
SELECT ST_GeomFromText('POINT(-74.006 40.7128)', 4326);
SELECT ST_SetSRID(ST_MakePoint(-74.006, 40.7128), 4326);
SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-74.006, 40.7128]}');

-- Create a polygon
SELECT ST_GeomFromText('POLYGON((-74.1 40.7, -74.0 40.7, -74.0 40.8, -74.1 40.8, -74.1 40.7))', 4326);
SELECT ST_MakeEnvelope(-74.1, 40.7, -74.0, 40.8, 4326);

Spatial Relationships

Functions to test relationships between geometries:

Spatial Measurements

Functions for measuring geometries:

-- Distance in meters (using geography type)
SELECT ST_Distance(
    'SRID=4326;POINT(-74.006 40.7128)'::geography,
    'SRID=4326;POINT(-0.1276 51.5074)'::geography
) AS distance_meters;

-- Area in square meters
SELECT ST_Area(
    ST_GeomFromText('POLYGON((-74.1 40.7, -74.0 40.7, -74.0 40.8, -74.1 40.8, -74.1 40.7))', 4326)::geography
) AS area_sq_meters;

Spatial Indexes

Create spatial indexes to improve query performance:

-- Create a GiST index on a geometry column
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

-- Create a GiST index on a geography column
CREATE INDEX idx_locations_geog ON locations USING GIST (geog);

-- Query using the spatial index
SELECT * FROM locations
WHERE ST_DWithin(geom, ST_GeomFromText('POINT(-74.006 40.7128)', 4326), 1000);

Output Functions

Functions to export geometry data:

SELECT
    ST_AsText(location) AS wkt,
    ST_AsGeoJSON(location) AS geojson,
    ST_X(location) AS longitude,
    ST_Y(location) AS latitude
FROM cities
LIMIT 1;

For more detailed information on PostGIS functions, see the PostGIS Reference.