Supabase Spatial Data with PostGIS

Why Supabase + PostGIS Matters

Supabase is an open-source Firebase alternative built on PostgreSQL. PostGIS extends PostgreSQL with spatial capabilities. Together they provide a complete backend for location-based applications — user authentication, spatial data storage, real-time subscriptions, and geo-spatial queries.

Why this matters for your career:

• Supabase is the most popular open-source BaaS (Backend as a Service)
• PostGIS is the industry standard for open-source spatial databases
• Location-based features are required in many modern applications
• Supabase's real-time capabilities enable live map updates

What Is PostGIS?

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

Supported Geometry Types

| Type | Description | Example | |------|-------------|--------| | POINT | A single coordinate | Campsite location | | LINESTRING | A line through coordinates | Hiking trail, road | | POLYGON | An enclosed area | Campground boundary, lake | | MULTIPOINT | Multiple points | Cluster of campsites | | MULTILINESTRING | Multiple lines | Trail network | | MULTIPOLYGON | Multiple polygons | National park boundaries | | GEOMETRYCOLLECTION | Mixed types | Complex geographic features |

Setting Up Supabase

Create a Supabase Project

1. Go to supabase.com and sign up
2. Click "New project"
3. Enter project name (e.g., "camping-map")
4. Set a secure database password
5. Choose a region close to your users (Singapore for Asia)
6. Click "Create new project"

Enable PostGIS

-- Go to SQL Editor in Supabase Dashboard and run:
CREATE EXTENSION IF NOT EXISTS postgis;

-- Verify the installation
SELECT postgis_version();
-- Expected: "3.4 USE_GEOGRAPHY"

-- List all available PostGIS functions
SELECT DISTINCT proname
FROM pg_proc
WHERE proname LIKE 'ST_%'
ORDER BY proname;
-- There are 500+ functions!

Creating Spatial Tables

Campsites Table

CREATE TABLE campsites (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  description TEXT,
  location GEOGRAPHY(POINT, 4326) NOT NULL,
  elevation INTEGER,
  amenities TEXT[],
  price_level INTEGER CHECK (price_level BETWEEN 1 AND 5),
  image_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create spatial index -- THIS IS CRITICAL for performance
CREATE INDEX idx_campsites_location
ON campsites
USING GIST (location);

Hiking Trails Table

CREATE TABLE trails (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  path GEOGRAPHY(LINESTRING, 4326) NOT NULL,
  difficulty TEXT CHECK (difficulty IN ('easy', 'moderate', 'hard')),
  distance_km NUMERIC,
  elevation_gain INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_trails_path ON trails USING GIST (path);

National Park Boundaries

CREATE TABLE parks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  boundary GEOGRAPHY(POLYGON, 4326) NOT NULL,
  established_year INTEGER,
  area_sqkm NUMERIC,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_parks_boundary ON parks USING GIST (boundary);

Inserting Spatial Data

Insert a Single Point

INSERT INTO campsites (name, description, location, price_level, amenities)
VALUES (
  'Sunset Ridge Campground',
  'Beautiful mountain ridge campsite with panoramic sunrise views.',
  ST_SetSRID(ST_MakePoint(121.564, 25.033), 4326),
  3,
  ARRAY['water', 'toilet', 'fire_pit']
);

Insert Multiple Points

INSERT INTO campsites (name, description, location, price_level) VALUES
  ('Forest Creek Camp', 'Next to a crystal clear mountain creek.', ST_SetSRID(ST_MakePoint(121.568, 25.041), 4326), 2),
  ('High Peak Campsite', 'At 2200m elevation. Stunning sunrise views.', ST_SetSRID(ST_MakePoint(121.550, 25.020), 4326), 4),
  ('Lakeside Haven', 'Peaceful lakeside camping with fishing.', ST_SetSRID(ST_MakePoint(121.580, 25.010), 4326), 3);

Insert a Trail (LineString)

INSERT INTO trails (name, path, difficulty, distance_km, elevation_gain)
VALUES (
  'Sunset Ridge Trail',
  ST_SetSRID(ST_MakeLine(ARRAY[
    ST_MakePoint(121.564, 25.033),
    ST_MakePoint(121.570, 25.040),
    ST_MakePoint(121.580, 25.045),
    ST_MakePoint(121.590, 25.050)
  ]), 4326),
  'moderate',
  3.2,
  450
);

Spatial Queries

Find Nearby Campsites (ST_DWithin)

-- Find campsites within 10km of Taipei 101 (25.033, 121.564)
SELECT
  id,
  name,
  ST_Distance(
    location,
    ST_SetSRID(ST_MakePoint(121.564, 25.033), 4326)
  ) AS distance_meters,
  price_level,
  amenities
FROM campsites
WHERE ST_DWithin(
  location,
  ST_SetSRID(ST_MakePoint(121.564, 25.033), 4326),
  10000  -- 10 kilometers in meters
)
ORDER BY distance_meters
LIMIT 20;

Find Nearest N Points

-- Find the 10 closest campsites using <-> operator (uses index!)
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 enables KNN (K-Nearest Neighbor) queries using the spatial index. This is much faster than sorting by ST_Distance.

Check If a Point Is Inside a Polygon

-- Check if a location is inside a national park
SELECT p.name AS park_name
FROM parks p
WHERE ST_Within(
  ST_SetSRID(ST_MakePoint(121.564, 25.033), 4326),
  p.boundary
);

Find All Trails Within a Bounding Box

SELECT t.name, t.difficulty, t.distance_km
FROM trails t
WHERE t.path && ST_MakeEnvelope(121.0, 24.5, 122.0, 25.5, 4326)
ORDER BY t.distance_km DESC;

The && operator checks bounding box intersection using the index.

Creating a Supabase RPC Function

Expose spatial queries as callable functions:

CREATE OR REPLACE FUNCTION get_nearby_campsites(
  ref_lat DOUBLE PRECISION,
  ref_lng DOUBLE PRECISION,
  radius_meters DOUBLE PRECISION DEFAULT 10000,
  max_results INTEGER DEFAULT 50
)
RETURNS TABLE(
  id UUID,
  name TEXT,
  description TEXT,
  distance_meters DOUBLE PRECISION,
  latitude DOUBLE PRECISION,
  longitude DOUBLE PRECISION,
  price_level INTEGER,
  amenities TEXT[]
)
LANGUAGE SQL
STABLE
AS $$
  SELECT
    c.id,
    c.name,
    c.description,
    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,
    c.price_level,
    c.amenities
  FROM campsites c
  WHERE ST_DWithin(
    c.location,
    ST_SetSRID(ST_MakePoint(ref_lng, ref_lat), 4326),
    radius_meters
  )
  ORDER BY distance_meters
  LIMIT max_results;
$$;

Calling RPC from the Client

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

const supabase = createClient(
  process.env.PUBLIC_SUPABASE_URL,
  process.env.PUBLIC_SUPABASE_ANON_KEY
);

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

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

  return data.map(site => ({
    ...site,
    // Leaflet uses [lat, lng] order
    latlng: [site.latitude, site.longitude]
  }));
}

Real-Time Subscriptions

Supabase supports real-time subscriptions for live map updates:

// Subscribe to new campsite insertions
const subscription = supabase
  .channel('campsites-changes')
  .on('postgres_changes',
    { event: 'INSERT', schema: 'public', table: 'campsites' },
    (payload) => {
      const newSite = payload.new;
      // Add new marker to the map
      const marker = L.marker([newSite.latitude, newSite.longitude])
        .bindPopup(`<b>${newSite.name}</b>`);
      mcg.addLayer(marker);
    }
  )
  .subscribe();

// Clean up on unmount
subscription.unsubscribe();

Row Level Security (RLS)

Enable RLS to control who can read/write spatial data:

-- Enable RLS
ALTER TABLE campsites ENABLE ROW LEVEL SECURITY;

-- Allow public read access (anyone can view campsites)
CREATE POLICY "Campsites are publicly readable"
ON campsites FOR SELECT
USING (true);

-- Only authenticated users can insert
CREATE POLICY "Users can add campsites"
ON campsites FOR INSERT
WITH CHECK (auth.role() = 'authenticated');
-- Only the owner can update
CREATE POLICY "Owners can update their campsites"
ON campsites FOR UPDATE
USING (auth.uid() = user_id);

Best Practices

| Practice | Reason | |----------|--------| | Always use GEOGRAPHY(Point, 4326) for GPS coordinates | Handles Earth curvature automatically | | Always create GIST indexes on spatial columns | Without index, spatial queries are O(n) full table scans | | Use ST_DWithin for radius queries (not ST_Distance in WHERE) | ST_DWithin uses the spatial index | | Use <-> for nearest-neighbor sorting | Enables KNN search with the index | | Expose complex queries as RPC functions | Clean API, hides SQL complexity from the client | | Use && for bounding box queries | Fast index-assisted bounding box intersection | | Enable RLS for data security | Control who can read, insert, update spatial data | | Add real-time subscriptions for live updates | Users see new campsites without refreshing | | Convert geography to geometry for ST_X/ST_Y | ST_X() works on geometry, not geography directly | | Limit results with pagination | Don't return thousands of rows at once |

Summary

Supabase with PostGIS provides a complete backend for your camping map. Create spatial tables with geography columns and GIST indexes, insert data with ST_MakePoint, query with ST_DWithin and <->, and expose results via RPC functions. Add RLS for security and real-time subscriptions for live updates.

Key takeaways:

• Enable PostGIS extension in Supabase SQL Editor
• Use GEOGRAPHY(POINT, 4326) for point locations
• Create GIST indexes for spatial query performance
• ST_DWithin(field, point, radius) for radius queries (uses index)
• <-> operator for nearest-neighbor search
• && operator for bounding box intersection
• Expose queries as RPC functions via supabase.rpc()
• Enable RLS for read/write access control
• Use real-time subscriptions for live map updates
• Convert to geometry for coordinate extraction (ST_X, ST_Y)

What's Next: Custom Markers & Popups

The next chapter covers custom markers, themed icons, and rich popup content for your camping map.