Querying PostGIS columns with Supabase and TypeScript

Supabase's type generator is very helpful when using supabase-js with TypeScript. But when your query selects a PostGIS geometry type column, its generated type will be unknown. We have a few options:

Type overrides

supabase-js queries Supabase's PostgREST API, which actually returns geometry columns in a usable form (column name in this example is location):

{
  "location": {
    "type": "Point",
    "crs":{
      "type": "name",
      "properties": {
        "name": "EPSG:4326"
      }
    },
    "coordinates": [103.80138721340607, 1.449137453350455]
  }
}
 

So we can just override the generated types in the client, similar to how it's done in the official docs. E.g. assuming you know the value is a Point and you're just interested in the location:

import type { MergeDeep } from 'type-fest'
import type { Database as DatabaseGenerated } from './database.types'

export type Database = MergeDeep<DatabaseGenerated, {
    public: {
        Tables: {
            your_table: {
                Row: {
                    location: {
                        type: "Point",
                        coordinates: number[],
                    },
                },
            },
        },
    },
}>;

However the response including the unnecessary type and crs fields is a bit long, which might make a difference if your query is returning thousands of points.

A view to unpack the geometry

Postgres views are amazingly useful to simplify your queries, even more so with Supabase and PostgREST. We can create a view using PostGIS functions to unpack the geometry into built-in types. E.g. in this simple example using a Point, we can use ST_X and ST_Y to get its coordinate components:

create view your_table_with_coordinates
with
  (security_invoker = true) as
select
  *,
  st_x (location) as location_lon,
  st_y (location) as location_lat
from
  your_table;

grant all on table your_table_with_coordinates to authenticated, anon;

When using views, always consider using security_invoker = true so that your view applies the same row level security rules as the underlying table (PostgreSQL's default is that the view will use the creator's permissions!). Also review to which roles to grant permissions.

Returning coordinates from a function

If you need to do some more transformations or filtering anyway, instead of a view, you can also use a function to return the unpacked coordinates. Those are callable via PostgREST as well. Note how we also create a custom object type to allow providing latitude and longitude as an argument.

do $$
  begin
    create type coordinates as (lon numeric, lat numeric);
    exception when duplicate_object then null;
  end
$$;

create or replace function get_points (
  within_1km_radius_from coordinates default null
) returns table (
  location_lon float,
  location_lat float
)
language plpgsql security invoker
set search_path to '' as $$
  begin
    return query
    select
      extensions.st_x(location::extensions.geometry) as lon,
      extensions.st_y(location::extensions.geometry) as lat
    from public.points p
    where
      case when within_1km_radius_from is not null then
        extensions.st_dwithin(p.location::extensions.geography, extensions.st_geomfromtext('POINT(' || within_1km_radius_from.lon || ' ' || within_1km_radius_from.lat || ')', 4326)::extensions.geography, '1000')
      else
        true 
      end;
  end;
$$;