问题

在Supabase JavaScript客户端中，是否可以进行距离筛选？如果可能的话，我想避免实现服务器端逻辑。

// 定义距离阈值，以米为单位（50公里 = 50000米）
const distanceThreshold = 50000
const targetLatitude = 10.724577
const targetLongitude = 5.525625

const supabaseClient = createClient(supabaseUrl, supabaseKey)

supabaseClient
  .from('my_table')
  .select('*')
  .filter(
    `ST_Distance_Sphere(
      ST_MakePoint(longitude, latitude),
      ST_MakePoint(${targetLongitude}, ${targetLatitude})
    )`, 'lt', distanceThreshold
  )
  .then(console.log)

我想要获取表格"my_table"中距离目标位置最多50公里的记录，是否可以像这样实现？

// Define the distance threshold in meters (50km = 50000m)
const distanceThreshold = 50000
const targetLatitude = 10.724577
const targetLongitude = 5.525625

const supabaseClient = createClient(supabaseUrl, supabaseKey)

supabaseClient
  .from('my_table')
  .select('*')
  .filter(
    `ST_Distance_Sphere(
      ST_MakePoint(longitude, latitude),
      ST_MakePoint(${targetLongitude}, ${targetLatitude})
    )`, 'lt', distanceThreshold
  )
  .then(console.log)

答案1

得分: 1

将其直接封装在RPC调用中以实现最佳方法：

``` sql
CREATE OR REPLACE FUNCTION get_entries_within_distance(tlongitude float, tlatitude float, distance numeric)
RETURNS SETOF my_table AS
$$
  BEGIN
    RETURN QUERY SELECT * FROM my_table 
    WHERE ST_DistanceSphere(ST_MakePoint(longitude, latitude), ST_MakePoint(tlongitude, tlatitude)) < distance::float;
  END;
$$LANGUAGE plpgsql VOLATILE;

从SQL调用：

select * from get_records_within_distance(10.724577,5.525625, 900000);

从JavaScript客户端库调用：

const { data: ret, error } = await supabase
  .rpc('get_entries_within_distance', 
  { tlongitude: 5.525625,  tlatitude: 10.724577, distance: 900000});
console.log(JSON.stringify(ret));

例如返回：

> [{"id":1,"created_at":"2023-02-24T15:03:00.965401+00:00","latitude":10.724578,"longitude":5.525624},{"id":2,"created_at":"2023-02-24T15:03:21.587739+00:00","latitude":10.72457,"longitude":5.52561}]

注意：

默认情况下，Postgres函数/RPC对所有用户都可用。您也可以通过运行以下命令将其限制为经过身份验证的用户：

REVOKE EXECUTE 
ON FUNCTION public.get_entries_within_distance 
FROM anon;

<details>
<summary>英文:</summary>

The best way to achieve this would be to encapsulate it directly in an RPC call:

``` sql
CREATE OR REPLACE FUNCTION get_entries_within_distance(tlongitude float, tlatitude float, distance numeric)
RETURNS SETOF my_table AS
$$
  BEGIN
    RETURN QUERY SELECT * FROM my_table 
    WHERE ST_DistanceSphere(ST_MakePoint(longitude, latitude), ST_MakePoint(tlongitude, tlatitude)) &lt; distance::float;
  END;
$$LANGUAGE plpgsql VOLATILE;

Calling from SQL:

select * from get_records_within_distance(10.724577,5.525625, 900000);

Calling from Javascript Client library:

const { data: ret, error } = await supabase
  .rpc('get_entries_within_distance', 
  { tlongitude: 5.525625,  tlatitude: 10.724577, distance: 900000});
console.log(JSON.stringify(ret));

e.g return:

> [{"id":1,"created_at":"2023-02-24T15:03:00.965401+00:00","latitude":10.724578,"longitude":5.525624},{"id":2,"created_at":"2023-02-24T15:03:21.587739+00:00","latitude":10.72457,"longitude":5.52561}]

Note:

By default Postgres functions/RPC are available to all users. You can also restrict them to authenticated users by running the following:

REVOKE EXECUTE 
ON FUNCTION public.get_entries_within_distance 
FROM anon;