Using PostGIS for Location-Based Features in PostgreSQL

Introduction

PostGIS extends PostgreSQL with geospatial capabilities for storing and querying geographic data. This guide covers spatial data types, indexing, and location queries.

Prerequisites

• PostgreSQL >=12
• PostGIS extension

Step 1: Enable PostGIS Extension

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

Step 2: Create Spatial Tables

CREATE TABLE locations (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  geom GEOMETRY(Point, 4326) NOT NULL -- WGS84 coordinate system
);

-- Create spatial index for performance
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

Step 3: Insert Location Data

INSERT INTO locations (name, geom) VALUES
('New York City', ST_SetSRID(ST_MakePoint(-74.006, 40.7128), 4326)),
('Los Angeles', ST_SetSRID(ST_MakePoint(-118.2437, 34.0522), 4326)),
('Chicago', ST_SetSRID(ST_MakePoint(-87.6298, 41.8781), 4326));

Step 4: Proximity Queries

Find locations within 1000 km of a point:

SELECT name, ST_Distance(
  geom::geography,
  ST_SetSRID(ST_MakePoint(-74.006, 40.7128), 4326)::geography
) / 1000 AS distance_km
FROM locations
WHERE ST_DWithin(
  geom::geography,
  ST_SetSRID(ST_MakePoint(-74.006, 40.7128), 4326)::geography,
  1000000 -- 1000 km in meters
)
ORDER BY distance_km;

Step 5: Node.js Integration

npm install pg @types/pg

import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export async function findNearbyLocations(
  longitude: number,
  latitude: number,
  radiusKm: number = 10
) {
  const query = `
    SELECT id, name, 
           ST_Distance(geom::geography, ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography) / 1000 AS distance_km
    FROM locations
    WHERE ST_DWithin(
      geom::geography,
      ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography,
      $3 * 1000
    )
    ORDER BY distance_km
  `;
  
  const result = await pool.query(query, [longitude, latitude, radiusKm]);
  return result.rows;
}

export async function createLocation(name: string, longitude: number, latitude: number) {
  const query = `
    INSERT INTO locations (name, geom)
    VALUES ($1, ST_SetSRID(ST_MakePoint($2, $3), 4326))
    RETURNING id, name
  `;
  
  const result = await pool.query(query, [name, longitude, latitude]);
  return result.rows[0];
}

Step 6: Advanced Spatial Queries

Area calculations and polygon queries:

-- Create polygon table
CREATE TABLE zones (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  boundary GEOMETRY(Polygon, 4326)
);

-- Insert a rectangular zone
INSERT INTO zones (name, boundary) VALUES (
  'Downtown Area',
  ST_SetSRID(ST_MakePolygon(ST_GeomFromText('LINESTRING(
    -74.02 40.70, -73.99 40.70, -73.99 40.72, -74.02 40.72, -74.02 40.70
  )')), 4326)
);

-- Find points within polygon
SELECT l.name
FROM locations l
JOIN zones z ON ST_Within(l.geom, z.boundary)
WHERE z.name = 'Downtown Area';

Summary

PostGIS enables powerful location-based features in PostgreSQL with spatial indexing, proximity searches, and geometric operations for building location-aware applications.