Geo data with PostGIS

PostGIS adds geographic types and functions to Postgres: points, lines and polygons, distances in metres, "what's within 2 km of me" and "which delivery zone is this address in". It ships with the Supabase Postgres image, so you enable it per project with one statement.

Enable the extension

Install it in the extensions schema to keep public tidy:

create extension if not exists postgis with schema extensions;

Check the installed version with select extensions.postgis_full_version();. The extensions schema is on the Data API's search path, so types like geography resolve without a prefix in most places. Inside functions with set search_path = '', write them in full, as in the examples below.

geography or geometry?

For most apps, geography(Point, 4326) is the right choice. 4326 is the standard GPS coordinate system (WGS 84), and distance functions return metres without extra conversion.

Store locations

A bookings app that lists venues:

create table public.venues (
  id bigint generated always as identity primary key,
  name text not null,
  location extensions.geography(Point, 4326) not null
);

create index venues_location_idx on public.venues using gist (location);

alter table public.venues enable row level security;

create policy "Anyone can read venues"
  on public.venues for select
  to anon, authenticated
  using (true);

The GiST index is what makes distance and area queries fast. Without it, every nearby search computes the distance to every row.

Insert points from supabase-js

PostGIS accepts points as text in WKT form. Note the order: longitude first, then latitude.

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

const supabase = createClient(
  'https://app-one.acme.supahost.space',
  process.env.SUPABASE_ANON_KEY!
)

// Leeds city centre: lat 53.7997, lng -1.5492
const { error } = await supabase
  .from('venues')
  .insert({ name: 'Riverside Hall', location: 'SRID=4326;POINT(-1.5492 53.7997)' })

Inserting needs an insert policy for the user, or run it from a server with the service_role key. In SQL the same insert uses extensions.st_point(lng, lat):

insert into public.venues (name, location)
values ('The Glasshouse', extensions.st_setsrid(extensions.st_point(-2.5879, 51.4545), 4326)::extensions.geography);

When you select a geography column through the Data API, it comes back as a hex-encoded binary string, which isn't handy in an app. Return latitude and longitude explicitly from a function or view instead, as below.

Find venues near a point

Distance queries need SQL that supabase-js filters can't express, so wrap them in a function and call it with rpc():

create or replace function public.venues_nearby(lat float8, lng float8, radius_m float8 default 5000)
returns table (id bigint, name text, venue_lat float8, venue_lng float8, distance_m float8)
language sql
stable
security invoker
set search_path = ''
as $$
  select
    v.id,
    v.name,
    extensions.st_y(v.location::extensions.geometry) as venue_lat,
    extensions.st_x(v.location::extensions.geometry) as venue_lng,
    extensions.st_distance(v.location, here.point) as distance_m
  from public.venues v,
       lateral (
         select extensions.st_setsrid(extensions.st_point(lng, lat), 4326)::extensions.geography as point
       ) here
  where extensions.st_dwithin(v.location, here.point, radius_m)
  order by v.location operator(extensions.<->) here.point
  limit 50;
$$;

const { data, error } = await supabase.rpc('venues_nearby', {
  lat: 53.8008,
  lng: -1.5491,
  radius_m: 3000,
})
// [{ id: 1, name: 'Riverside Hall', venue_lat: 53.7997, venue_lng: -1.5492, distance_m: 122.4 }, ...]

st_dwithin uses the GiST index to discard far-away rows quickly, and <-> orders by distance using the same index. The function is security invoker, so your RLS policies still decide which venues each caller sees.

Find which area a point is in

Store delivery zones, districts or geofences as polygons and test whether a point falls inside:

create table public.delivery_zones (
  id bigint generated always as identity primary key,
  name text not null,
  area extensions.geography(Polygon, 4326) not null
);

create index delivery_zones_area_idx on public.delivery_zones using gist (area);

create or replace function public.zone_for_point(lat float8, lng float8)
returns setof public.delivery_zones
language sql
stable
security invoker
set search_path = ''
as $$
  select z.*
  from public.delivery_zones z
  where extensions.st_intersects(
    z.area,
    extensions.st_setsrid(extensions.st_point(lng, lat), 4326)::extensions.geography
  );
$$;

You can insert a polygon from GeoJSON with extensions.st_geomfromgeojson('{"type":"Polygon","coordinates":[...]}')::extensions.geography, which is handy if your map library draws shapes as GeoJSON.

Return GeoJSON for maps

Map libraries such as Leaflet and MapLibre read GeoJSON. Build a feature collection in SQL so the app can pass it straight to the map:

create or replace function public.venues_geojson()
returns jsonb
language sql
stable
security invoker
set search_path = ''
as $$
  select jsonb_build_object(
    'type', 'FeatureCollection',
    'features', coalesce(jsonb_agg(jsonb_build_object(
      'type', 'Feature',
      'geometry', extensions.st_asgeojson(v.location)::jsonb,
      'properties', jsonb_build_object('id', v.id, 'name', v.name)
    )), '[]'::jsonb)
  )
  from public.venues v;
$$;

For large datasets, add a bounding-box argument so the map only loads what's on screen.

Realtime and PostGIS

Postgres Changes sends new rows to subscribers, but geography values arrive in their binary form. If clients need coordinates in real time, add plain lat and lng columns kept in sync by a trigger, or broadcast the coordinates yourself. See Postgres Changes.

Related

• Postgres extensions
• Indexes
• Calling database functions (RPC)
• PostGIS reference: postgis.net/docs