-- Install PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;
-- Create table with geometry column
CREATE TABLE locations (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
location GEOMETRY(Point, 4326), -- 4326 = WGS 84 (GPS coordinates)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create spatial index
CREATE INDEX idx_locations_geom ON locations USING GIST(location);
-- Insert point (longitude, latitude)
INSERT INTO locations (name, location) VALUES
('Statue of Liberty', ST_SetSRID(ST_MakePoint(-74.0445, 40.6892), 4326)),
('Empire State Building', ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)),
('Central Park', ST_SetSRID(ST_MakePoint(-73.9654, 40.7829), 4326));
-- Insert from lat/lon columns
INSERT INTO locations (name, location)
SELECT
name,
ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
FROM raw_locations;
-- Find distance between two points (meters)
SELECT
l1.name AS from_location,
l2.name AS to_location,
ST_Distance(
l1.location::geography,
l2.location::geography
) AS distance_meters
FROM locations l1
CROSS JOIN locations l2
WHERE l1.id != l2.id;
-- Find locations within radius (5km)
SELECT
name,
ST_Distance(
location::geography,
ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326)::geography
) AS distance_meters
FROM locations
WHERE ST_DWithin(
location::geography,
ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326)::geography,
5000 -- 5000 meters = 5km
)
ORDER BY distance_meters;
-- Nearest neighbors (5 closest locations)
SELECT
name,
ST_Distance(
location::geography,
ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326)::geography
) AS distance_meters
FROM locations
ORDER BY location <-> ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326)
LIMIT 5;
-- Extract coordinates
SELECT
name,
ST_X(location) AS longitude,
ST_Y(location) AS latitude
FROM locations;
-- Polygon (service area, delivery zone)
CREATE TABLE delivery_zones (
id SERIAL PRIMARY KEY,
zone_name VARCHAR(100),
boundary GEOMETRY(Polygon, 4326)
);
-- Create polygon
INSERT INTO delivery_zones (zone_name, boundary) VALUES (
'Downtown',
ST_SetSRID(
ST_MakePolygon(
ST_GeomFromText('LINESTRING(
-74.02 40.70,
-74.00 40.70,
-74.00 40.72,
-74.02 40.72,
-74.02 40.70
)')
),
4326
)
);
-- Check if point is within polygon
SELECT
l.name,
dz.zone_name
FROM locations l
JOIN delivery_zones dz ON ST_Within(l.location, dz.boundary);
-- Find zone for a specific point
SELECT zone_name
FROM delivery_zones
WHERE ST_Contains(
boundary,
ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326)
);
-- Bounding box query (faster preliminary filter)
SELECT name
FROM locations
WHERE location && ST_MakeEnvelope(
-74.05, 40.70, -- min lon, min lat
-73.95, 40.80, -- max lon, max lat
4326
);
-- LineString (routes, paths)
CREATE TABLE routes (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
path GEOMETRY(LineString, 4326)
);
INSERT INTO routes (name, path) VALUES (
'Broadway',
ST_SetSRID(
ST_MakeLine(ARRAY[
ST_MakePoint(-73.9857, 40.7484),
ST_MakePoint(-73.9851, 40.7580),
ST_MakePoint(-73.9845, 40.7676)
]),
4326
)
);
-- Length of path
SELECT
name,
ST_Length(path::geography) AS length_meters
FROM routes;
-- Intersection detection
SELECT
r1.name AS route1,
r2.name AS route2
FROM routes r1
JOIN routes r2 ON r1.id < r2.id
WHERE ST_Intersects(r1.path, r2.path);
-- Buffer zone around point (500m radius)
SELECT
name,
ST_Buffer(location::geography, 500)::geometry AS buffer_zone
FROM locations;
-- Centroid of polygon
SELECT
zone_name,
ST_Centroid(boundary) AS center_point,
ST_X(ST_Centroid(boundary)) AS center_lon,
ST_Y(ST_Centroid(boundary)) AS center_lat
FROM delivery_zones;
-- Area of polygon (square meters)
SELECT
zone_name,
ST_Area(boundary::geography) AS area_sqm,
ST_Area(boundary::geography) / 1000000 AS area_sqkm
FROM delivery_zones;
-- Convex hull (smallest polygon containing points)
SELECT
ST_ConvexHull(
ST_Collect(location)
) AS hull
FROM locations;
-- Closest point on line to a point
SELECT
name,
ST_ClosestPoint(
path,
ST_SetSRID(ST_MakePoint(-73.9850, 40.7500), 4326)
) AS closest_point_on_route
FROM routes;
-- Distance from point to line
SELECT
name,
ST_Distance(
path::geography,
ST_SetSRID(ST_MakePoint(-73.9850, 40.7500), 4326)::geography
) AS distance_to_route_meters
FROM routes;
-- Simplify geometry (reduce points for display)
SELECT
ST_Simplify(path, 0.001) AS simplified_path
FROM routes;
-- GeoJSON export
SELECT
jsonb_build_object(
'type', 'FeatureCollection',
'features', jsonb_agg(
jsonb_build_object(
'type', 'Feature',
'geometry', ST_AsGeoJSON(location)::jsonb,
'properties', jsonb_build_object(
'name', name,
'id', id
)
)
)
) AS geojson
FROM locations;
-- Spatial join (all locations in each zone)
SELECT
dz.zone_name,
COUNT(l.id) AS location_count,
STRING_AGG(l.name, ', ') AS locations
FROM delivery_zones dz
LEFT JOIN locations l ON ST_Within(l.location, dz.boundary)
GROUP BY dz.zone_name;
-- Heatmap data (count points in grid cells)
WITH grid AS (
SELECT
i,
j,
ST_MakeEnvelope(
-74.05 + (i * 0.01),
40.70 + (j * 0.01),
-74.05 + ((i + 1) * 0.01),
40.70 + ((j + 1) * 0.01),
4326
) AS cell
FROM generate_series(0, 9) AS i
CROSS JOIN generate_series(0, 9) AS j
)
SELECT
i,
j,
COUNT(l.id) AS point_count,
ST_AsText(cell) AS cell_bounds
FROM grid
LEFT JOIN locations l ON ST_Within(l.location, cell)
GROUP BY i, j, cell;
-- Spatial clustering (group nearby points)
SELECT
ST_ClusterKMeans(location::geometry, 3) OVER () AS cluster_id,
name,
location
FROM locations;
-- Transform between coordinate systems
-- Convert from WGS84 (4326) to Web Mercator (3857)
SELECT
name,
ST_Transform(location, 3857) AS web_mercator_location
FROM locations;
-- Validate geometry
SELECT
name,
ST_IsValid(boundary) AS is_valid,
ST_IsValidReason(boundary) AS validation_message
FROM delivery_zones;
