Geospatial data with PostGIS

-- 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;