# Geo data with PostGIS

> Store locations in a SupaHost project with PostGIS, insert points from supabase-js, index them with GiST, and run nearby and within-area queries through RPC functions.

Source: https://supahost.app/docs/database/postgis · Updated 2026-11-11

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:

```sql
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?

| Type | Units | Use it when |
|---|---|---|
| `geography` | Metres, on a round earth | Points anywhere in the world: stores, users, venues |
| `geometry` | Units of the coordinate system | Local maps, complex shapes, heavy spatial analysis |

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:

```sql
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**.

```ts
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)`:

```sql
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()`:

```sql
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;
$$;
```

```ts
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:

```sql
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:

```sql
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](/docs/realtime/postgres-changes).

## Related

- [Postgres extensions](/docs/database/extensions)
- [Indexes](/docs/database/indexes)
- [Calling database functions (RPC)](/docs/api/rpc)
- PostGIS reference: [postgis.net/docs](https://postgis.net/docs/)