Supabase POSTGIS SQL function to return records within given distance from user

Question

I am not familiar with SQL or PostGIS as a front end engineer, but I have to build out this backend API with Supabase. I followed their docs which is almost what I need, except that this will return ALL records.

I want to pass a 3rd argument distance:

create or replace function nearby_flies(lat float, long float)
returns table (pattern public.submitted_images.pattern%TYPE, dist_meters float)
language sql
as $$
  select pattern, st_distance(location, st_point(long, lat)::geography) as dist_meters
  from public.submitted_images
  order by location <-> st_point(long, lat)::geography;
$$;

My table is written as:

create table
  public.submitted_images (
    user_id uuid not null,
    created_at timestamp with time zone not null default now(),
    bucket_id text null,
    pattern text null,
    location geography (point) not null,
    constraint submitted_images_pkey primary key (created_at),
    constraint submitted_images_pattern_fkey foreign key (pattern) references pattern (label),
    constraint submitted_images_user_id_fkey foreign key (user_id) references auth.users (id)
  ) tablespace pg_default;

create index submitted_images_geo_index
  on public.submitted_images
  using GIST (location);

and I have confirmed that the table is recording the POINT record.

How would this query be re-written to take into account only records that are within X distance as an additional argument?

Answer 1

As suggested by @JGH, that's exactly what st_dwithin() is for: finding things within a certain distance. Demo:

create or replace function nearby_flies(lat float, long float, dist numeric)
returns table (pattern public.submitted_images.pattern%TYPE, dist_meters float)
language sql
as $$
  select pattern, st_distance(location, st_point(long,lat)::geography) as dist_meters
  from public.submitted_images
  where st_dwithin(location,st_point(long,lat)::geography,dist)
  order by location<->st_point(long,lat)::geography;
$$;

Using st_dwithin() directly instead of measuring the distance and comparing to your desired limit, can result in a plan with an index condition added - it'll first check this range quickly at bounding box level before proceeding to apply the actual distance filter. In the demo you can observe a way simpler and faster plan, compared to the alternative.