Supabase client (JS): filtering by distance

Question

In supabase Javascript client is it possible to do a distance filter? If possible, I'd like to avoid implementing server-side logic.

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

I want to get the records from the table "my_table" at a maximum distance of 50km from the target position position, is it possible to do something like this?

Answer 1

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

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;

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;