Supascale
Sign Up

Setting Up PostGIS for Self-Hosted Supabase: Build Location Apps

Complete guide to enabling PostGIS on self-hosted Supabase for geospatial queries, with fixes for common issues and performance tips.

Cover Image for Setting Up PostGIS for Self-Hosted Supabase: Build Location Apps

If you're building a location-based application with self-hosted Supabase, PostGIS is the extension you need. It transforms PostgreSQL into a powerful geographic database capable of storing coordinates, calculating distances, finding nearby points, and handling complex geospatial queries. But setting it up on a self-hosted instance comes with some gotchas that the official documentation glosses over.

This guide walks you through deploying PostGIS on your self-hosted Supabase instance, including the workarounds for a known issue that trips up many developers.

What PostGIS Brings to Your Supabase Stack

PostGIS is the gold standard for geospatial data in PostgreSQL. Unlike storing latitude and longitude as simple decimal columns (which doesn't scale), PostGIS provides:

  • Native geographic types: Point, Polygon, LineString, and more
  • Spatial indexing: GiST indexes that make geospatial queries fast even on millions of rows
  • Built-in functions: Distance calculations, containment checks, nearest-neighbor searches
  • SRID support: Proper coordinate system handling for accurate real-world measurements

For applications like delivery route optimization, ride-sharing, store locators, or real estate platforms, PostGIS is essential. And combined with Supabase's real-time features, you can build live location-tracking apps entirely on PostgreSQL.

Enabling PostGIS on Self-Hosted Supabase

The setup process differs slightly from Supabase Cloud because you have direct control over your PostgreSQL instance.

Option 1: Via SQL (Recommended for Self-Hosted)

Connect to your database and run:

-- Enable PostGIS in the public schema
CREATE EXTENSION IF NOT EXISTS postgis;

-- Verify installation
SELECT PostGIS_version();

For self-hosted instances, enabling PostGIS in the public schema is often smoother than using a separate extensions schema. More on why this matters below.

Option 2: Via Supabase Studio

If you've set up Supabase Studio with your self-hosted instance:

  1. Navigate to Database → Extensions
  2. Search for postgis
  3. Click to enable

When prompted for a schema, choose public rather than creating a new schema. This avoids a common issue with self-hosted deployments.

Verifying PostGIS is Working

Run this query to confirm the extension is properly installed:

SELECT 
  name, 
  default_version, 
  installed_version 
FROM pg_available_extensions 
WHERE name = 'postgis';

You should see the installed version populated.

The Self-Hosted PostGIS Bug (And How to Fix It)

Here's where things get interesting. A known issue affects PostGIS on self-hosted Supabase: calling PostGIS functions via the API returns "type 'geometry' does not exist" even though the same queries work in the SQL editor.

The root cause is the PostgREST search path. When you call a function through the Supabase API, PostgREST looks for types in specific schemas. If PostGIS is installed in a separate schema that isn't in the search path, the geometry types become invisible to API calls.

Fix 1: Install PostGIS in the Public Schema

The simplest solution is installing PostGIS in public:

-- If PostGIS is already in extensions schema, you'll need to reinstall
-- WARNING: This drops existing geospatial data - backup first!
DROP EXTENSION IF EXISTS postgis CASCADE;
CREATE EXTENSION postgis;

Fix 2: Expand the PostgREST Search Path

If you need PostGIS in a separate schema, update your docker-compose.yml for the supabase-rest container:

rest:
  environment:
    PGRST_DB_EXTRA_SEARCH_PATH: public,extensions

Then restart your Supabase stack:

docker-compose down && docker-compose up -d

Fix 3: Use Schema-Qualified Function Calls

As a workaround without changing configuration, you can call PostGIS functions with explicit schema qualification in your database functions:

CREATE OR REPLACE FUNCTION find_nearby_locations(
  lat double precision,
  lng double precision,
  radius_meters integer DEFAULT 5000
)
RETURNS SETOF locations AS $$
  SELECT *
  FROM locations
  WHERE extensions.ST_DWithin(
    location::extensions.geography,
    extensions.ST_MakePoint(lng, lat)::extensions.geography,
    radius_meters
  )
  ORDER BY location <-> extensions.ST_MakePoint(lng, lat)::extensions.geometry;
$$ LANGUAGE sql;

Creating Tables with Geospatial Data

With PostGIS enabled, create tables that store geographic data:

-- Restaurants with geographic locations
CREATE TABLE restaurants (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  location GEOGRAPHY(POINT, 4326) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Add a spatial index for fast queries
CREATE INDEX restaurants_location_idx 
ON restaurants 
USING GIST (location);

The GEOGRAPHY(POINT, 4326) type stores latitude/longitude coordinates using the standard WGS84 coordinate system (SRID 4326) that GPS devices and mapping services use.

Insert data with PostGIS functions:

INSERT INTO restaurants (name, location) VALUES
  ('Pizza Palace', ST_MakePoint(-73.935242, 40.730610)),
  ('Burger Barn', ST_MakePoint(-73.985428, 40.748817)),
  ('Sushi Spot', ST_MakePoint(-73.968285, 40.785091));

Note: ST_MakePoint takes longitude first, then latitude. This trips up many developers.

Common Geospatial Queries

Find Locations Within a Radius

-- Find restaurants within 2km of a point
SELECT 
  name,
  ST_Distance(
    location::geography,
    ST_MakePoint(-73.950000, 40.750000)::geography
  ) AS distance_meters
FROM restaurants
WHERE ST_DWithin(
  location::geography,
  ST_MakePoint(-73.950000, 40.750000)::geography,
  2000 -- 2km in meters
)
ORDER BY distance_meters;

-- Find 10 closest restaurants to a point
SELECT 
  name,
  location <-> ST_MakePoint(-73.950000, 40.750000)::geometry AS distance
FROM restaurants
ORDER BY location <-> ST_MakePoint(-73.950000, 40.750000)::geometry
LIMIT 10;

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

Check if Point is Within a Polygon

-- Define a delivery zone polygon
WITH delivery_zone AS (
  SELECT ST_MakePolygon(ST_MakeLine(ARRAY[
    ST_MakePoint(-74.0, 40.7),
    ST_MakePoint(-73.9, 40.7),
    ST_MakePoint(-73.9, 40.8),
    ST_MakePoint(-74.0, 40.8),
    ST_MakePoint(-74.0, 40.7)
  ])) AS zone
)
SELECT name
FROM restaurants, delivery_zone
WHERE ST_Within(location::geometry, zone);

Exposing PostGIS Functions via the Supabase API

Create database functions to expose geospatial queries through the Supabase client:

-- Function to find nearby locations
CREATE OR REPLACE FUNCTION nearby_restaurants(
  user_lat double precision,
  user_lng double precision,
  max_distance integer DEFAULT 5000
)
RETURNS TABLE (
  id integer,
  name text,
  distance_meters double precision
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    r.id,
    r.name,
    ST_Distance(
      r.location::geography,
      ST_MakePoint(user_lng, user_lat)::geography
    ) AS distance_meters
  FROM restaurants r
  WHERE ST_DWithin(
    r.location::geography,
    ST_MakePoint(user_lng, user_lat)::geography,
    max_distance
  )
  ORDER BY distance_meters;
END;
$$ LANGUAGE plpgsql;

Call it from your application:

const { data, error } = await supabase
  .rpc('nearby_restaurants', {
    user_lat: 40.750000,
    user_lng: -73.950000,
    max_distance: 2000
  });

Performance Tips for Geospatial Data

Always Create Spatial Indexes

Without a GiST index, geospatial queries scan every row:

CREATE INDEX idx_locations_geo 
ON your_table 
USING GIST (location);

Use Geography vs Geometry Appropriately

  • Geography: Uses spherical earth calculations. Accurate for real-world distances but slower.
  • Geometry: Uses flat plane calculations. Faster but less accurate for large distances.

For most location-based apps where accuracy matters, use GEOGRAPHY. For games or applications where you need speed and can tolerate some inaccuracy, use GEOMETRY.

Consider BRIN Indexes for Large Datasets

For tables with millions of rows where data is physically sorted by location:

CREATE INDEX idx_locations_brin 
ON your_table 
USING BRIN (location);

BRIN indexes are smaller but only effective when data has natural ordering.

Monitor Query Performance

Enable query logging in your PostgreSQL configuration to identify slow geospatial queries:

-- Check if spatial index is being used
EXPLAIN ANALYZE
SELECT * FROM restaurants
WHERE ST_DWithin(location::geography, ST_MakePoint(-73.95, 40.75)::geography, 1000);

Look for "Index Scan using restaurants_location_idx" in the output.

Combining PostGIS with Supabase Realtime

PostGIS pairs well with Supabase Realtime for live location tracking. Set up a table for tracking positions:

CREATE TABLE live_positions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  location GEOGRAPHY(POINT, 4326) NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE live_positions ENABLE ROW LEVEL SECURITY;

-- Users can only update their own position
CREATE POLICY "Users manage own position" ON live_positions
  FOR ALL USING (auth.uid() = user_id);

Subscribe to updates in your application:

supabase
  .channel('live-positions')
  .on('postgres_changes', { 
    event: '*', 
    schema: 'public', 
    table: 'live_positions' 
  }, (payload) => {
    // Update map markers in real-time
    updateMapMarker(payload.new);
  })
  .subscribe();

Managing PostGIS on Self-Hosted Supabase with Supascale

Setting up PostGIS is just one piece of running a location-based application. You also need reliable backups that capture your geospatial data, proper environment configuration, and the ability to scale as your user base grows.

Supascale simplifies self-hosted Supabase management with:

  • Automated backups to S3-compatible storage, ensuring your PostGIS data is protected
  • One-click restore when you need to recover
  • Custom domains with free SSL for your location APIs
  • Selective service deployment - run only the Supabase services you need

The one-time $39.99 purchase covers unlimited projects, making it economical for teams building multiple location-based applications.

Conclusion

PostGIS transforms self-hosted Supabase into a powerful platform for location-based applications. The setup requires attention to schema configuration—particularly the PostgREST search path issue—but once running, you get enterprise-grade geospatial capabilities without vendor lock-in.

The combination of PostGIS for spatial queries, Supabase's auto-generated API, and real-time subscriptions creates a compelling stack for delivery apps, social platforms with location features, or any application where geography matters.

For teams already self-hosting Supabase, adding PostGIS is straightforward. For those considering self-hosting, tools like Supascale reduce the operational overhead while giving you full control over your infrastructure.

Further Reading