如何修改Supabase PostGIS函数以筛选指定距离内的记录？

作为不熟悉SQL与PostGIS的前端工程师，基于Supabase搭建后端API时，参考官方文档编写了nearby_flies函数，但该函数会返回所有记录。希望添加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;
$$;

表结构代码：

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);

已确认表中能正常存储POINT类型数据，需改写函数实现按指定距离筛选记录。

只需两步修改即可实现需求：

• 给函数新增max_distance_meters参数，用于指定筛选的最大距离（单位：米）
• 使用PostGIS的ST_DWithin函数添加距离过滤条件，同时保留原有的近远排序逻辑

修改后的函数代码：

create or replace function nearby_flies(lat float, long float, max_distance_meters 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
  -- 筛选指定距离内的记录，利用已有的GIST索引提升性能
  where st_dwithin(location, st_point(long, lat)::geography, max_distance_meters)
  order by location <-> st_point(long, lat)::geography;
$$;

关键说明 #

• ST_DWithin函数会检查location点是否在以传入坐标为中心、max_distance_meters为半径的范围内，由于你已创建location字段的GIST索引，这个过滤会走索引，性能表现优异
• 保留原有的<->运算符排序，确保结果按距离从近到远返回
• 调用函数时需传入三个参数：纬度、经度、最大距离（示例：select * from nearby_flies(39.9042, 116.4074, 5000)表示筛选北京天安门5公里内的记录）

内容的提问来源于stack exchange，提问作者Phil Lucks