Supabase Spatial Data — Working with PostGIS

Why Spatial Data Matters

A camping map is fundamentally about locations. Every campsite, viewpoint, and trailhead has coordinates. PostGIS extends PostgreSQL with spatial capabilities — storing points, lines, polygons, and running geo-spatial queries like "find all campsites within 10km."

Why this matters for your career:

• Spatial data is used in maps, logistics, real estate, and urban planning
• PostGIS is the gold standard for open-source spatial databases
• Supabase provides PostGIS out of the box — no extra setup needed
• Geo-spatial queries are essential for location-based applications

What Is PostGIS?

PostGIS is a spatial database extender for PostgreSQL. It adds support for geographic objects, spatial indexes, and spatial functions.

Core Features

| Feature | Description | |---------|-------------| | Geometry types | Point, LineString, Polygon, MultiPolygon, GeometryCollection | | Spatial indexes | GIST indexes for fast spatial queries | | Spatial functions | Distance, containment, intersection, buffering | | Coordinate systems | Support for 5000+ SRIDs (WGS84, UTM, etc.) | | Geography vs. Geometry | Geography for Earth coordinates, Geometry for projected |

Setting Up PostGIS in Supabase

-- Enable PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- Verify installation
SELECT postgis_version();
-- Returns: 3.4 USE_GEOGRAPHY

Supabase projects have PostGIS pre-installed. You just need to enable it.

Creating a Spatial Table

CREATE TABLE campsites (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  description TEXT,
  location GEOGRAPHY(Point, 4326),  -- WGS84 longitude/latitude
  altitude NUMERIC,
  amenities TEXT[],
  price_level INTEGER CHECK (price_level BETWEEN 1 AND 5),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create spatial index for fast geo-queries
CREATE INDEX idx_campsites_location ON campsites USING GIST (location);

Inserting Spatial Data

-- Insert a campsite with coordinates (longitude, latitude)
INSERT INTO campsites (name, description, location, price_level)
VALUES (
  'Sunset Ridge Campground',
  'Beautiful mountain views with hiking trails nearby.',
  ST_SetSRID(ST_MakePoint(121.564, 25.033), 4326),
  3
);

-- Insert multiple campsites
INSERT INTO campsites (name, description, location, price_level) VALUES
  ('Forest Creek Camp', 'Next to a crystal clear creek.', ST_SetSRID(ST_MakePoint(121.568, 25.041), 4326), 2),
  ('High Peak Site', 'At 2200m elevation, stunning sunrise.', ST_SetSRID(ST_MakePoint(121.550, 25.020), 4326), 4);

Spatial Queries

Find Campsites Within Distance

-- Find campsites within 10km of a specific location
SELECT
  id,
  name,
  description,
  ST_Distance(location, ST_SetSRID(ST_MakePoint(121.564, 25.033), 4326)) AS distance_meters
FROM campsites
WHERE ST_DWithin(
  location,
  ST_SetSRID(ST_MakePoint(121.564, 25.033), 4326),
  10000  -- 10 kilometers in meters
)
ORDER BY distance_meters;

Find Nearest Campsites

-- Find the 10 closest campsites
SELECT
  id,
  name,
  ST_Distance(location, ST_SetSRID(ST_MakePoint(121.564, 25.033), 4326)) AS distance_meters
FROM campsites
ORDER BY location <-> ST_SetSRID(ST_MakePoint(121.564, 25.033), 4326)
LIMIT 10;

The <-> operator uses the spatial index for efficient nearest-neighbor search.

Spatial Aggregation

-- Count campsites by region (10km grid cells)
SELECT
  ST_SnapToGrid(location::geometry, 0.1) AS grid_cell,
  COUNT(*) AS campsite_count
FROM campsites
GROUP BY grid_cell
ORDER BY campsite_count DESC;

Supabase JavaScript Client Example

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_ANON_KEY
);

async function findNearbyCampsites(lat, lng, radiusMeters = 10000) {
  const { data, error } = await supabase.rpc('find_campsites_nearby', {
    ref_lat: lat,
    ref_lng: lng,
    radius_meters: radiusMeters
  });

  if (error) {
    console.error('Error finding campsites:', error);
    return [];
  }

  return data;
}

PostgreSQL Function for RPC

CREATE OR REPLACE FUNCTION find_campsites_nearby(
  ref_lat DOUBLE PRECISION,
  ref_lng DOUBLE PRECISION,
  radius_meters DOUBLE PRECISION
)
RETURNS TABLE(
  id UUID,
  name TEXT,
  distance_meters DOUBLE PRECISION,
  latitude DOUBLE PRECISION,
  longitude DOUBLE PRECISION
)
LANGUAGE SQL
STABLE
AS $$
  SELECT
    c.id,
    c.name,
    ST_Distance(
      c.location,
      ST_SetSRID(ST_MakePoint(ref_lng, ref_lat), 4326)
    ) AS distance_meters,
    ST_Y(c.location::geometry) AS latitude,
    ST_X(c.location::geometry) AS longitude
  FROM campsites c
  WHERE ST_DWithin(
    c.location,
    ST_SetSRID(ST_MakePoint(ref_lng, ref_lat), 4326),
    radius_meters
  )
  ORDER BY distance_meters;
$$;

Using in Leaflet

async function loadCampsitesOnMap(map, centerLat, centerLng) {
  const campsites = await findNearbyCampsites(centerLat, centerLng);

  campsites.forEach(site => {
    const marker = L.circleMarker([site.latitude, site.longitude], {
      radius: 8,
      fillColor: '#22c55e',
      color: '#166534',
      weight: 2,
      opacity: 1,
      fillOpacity: 0.8
    });

    marker.bindPopup(`
      <b>${site.name}</b><br>
      Distance: ${(site.distance_meters / 1000).toFixed(2)} km
    `);

    marker.addTo(map);
  });
}

// Load campsites when map pans or zooms
map.on('moveend', function() {
  const center = map.getCenter();
  loadCampsitesOnMap(map, center.lat, center.lng);
});

Common Spatial Functions

| Function | Purpose | |----------|---------| | ST_DWithin(geom1, geom2, distance) | Check if geometries are within a distance | | ST_Distance(geom1, geom2) | Calculate distance between geometries | | ST_Within(geom1, geom2) | Check if one geometry is inside another | | ST_Intersects(geom1, geom2) | Check if geometries intersect | | ST_Buffer(geom, radius) | Create a buffer zone around a geometry | | ST_Area(geom) | Calculate area of a polygon | | ST_Length(geom) | Calculate length of a line | | ST_AsGeoJSON(geom) | Convert to GeoJSON format | | ST_X(geom) | Get X coordinate (longitude) | | ST_Y(geom) | Get Y coordinate (latitude) |

Best Practices

| Practice | Reason | |----------|--------| | Always use GEOGRAPHY type for Earth coordinates | Handles Earth curvature automatically | | Create GIST indexes on spatial columns | Without index, spatial queries scan all rows | | Use ST_DWithin instead of ST_Distance for radius queries | ST_DWithin uses the spatial index | | Limit results with pagination | Don't return thousands of markers at once | | Use ST_AsGeoJSON for API responses | Standard format compatible with mapping libraries | | Filter by bounding box first, then refine | Load only markers visible in the current map view | | Set SRID 4326 for GPS coordinates | Standard WGS84 coordinate system | | Use functions (RPC) for complex queries | Keep logic on the server, call from client |

Summary

Supabase with PostGIS provides powerful spatial data capabilities. Store campground coordinates as GEOGRAPHY points, create GIST indexes for fast queries, and use ST_DWithin for efficient radius searches. Combine with Leaflet to display results on an interactive map.

Key takeaways:

• Enable PostGIS extension in Supabase
• Use GEOGRAPHY(Point, 4326) for GPS coordinates
• Create GIST index on spatial columns for performance
• ST_DWithin finds points within a radius (uses index)
• <-> operator finds nearest neighbors using index
• Expose spatial queries as Supabase RPC functions
• Convert to GeoJSON with ST_AsGeoJSON for API responses
• Load markers dynamically based on the current map bounds

What's Next: Map Bounds

The next chapter covers map bounds — managing the visible area, dynamic data loading based on viewport, and optimizing marker rendering.