geo querys and sorting by distance

[Crey443]

Hi,
I am currently using firestore, but I need to be able to get my data via a geopoint that is stored in the documents.
Now I am wondering if supabase supports Geo queries and sorting by distance to a given point?

Thanks for any help.

[wiesson]

If your data is available as longitude and latitude (floats), then you could make use of haversine formula.

[Crey443]

when using this formula, is it possible to sort the data before getting it?
because I want to paginate it for an infinite list view that is sorted by distance

[wiesson]

I'm not sure, but I guess yes - I have used it once with postgres (raw query), but not yet with supabase. So if you write your SQL query, check if you can add your sort parameters. Once it works, define a function and call with via RPC (others may know better how it works 😅 ) - https://supabase.com/docs/guides/database/functions and https://supabase.com/docs/reference/javascript/rpc

[GaryAustin1]

You can also use the postgres PostGIS extension with a point data type and then make queries (using the rpc functions mentioned above) for simple to complex retrieval of data. It is reasonably complicated to get things going though, and you can't use the table UI to add the column. But should be fast as built in and let you do more advanced things like find in a shape versus just distance if ever needed.

[oripka]

I found this project https://www.madewithsupabase.com/p/supabase-geodb, maybe that's helpful

[dshukertjr]

Yes, Postgres supports geo queries much better than Firestore!

In order to query data and order it by distance, you would first need to enable postgis extension in your Supabase project.

Your table could look something like this:

create table if not exists public.videos (
    id uuid not null primary key DEFAULT uuid_generate_v4 (),
    created_at timestamp with time zone default timezone('utc' :: text, now()) not null,
    description varchar(320) not null,
    location geography(POINT) not null
);

The key here is the location column with geography(POINT) type which represents a geographic location on earth.

Then you can create a Postgres function like this:

create or replace function test_nearby(lat float, long float)
returns setof videos
as $$
    select *
    from videos
    order by location <-> st_point(long, lat)::geography;
$$
language sql;

The above function can be called like this:

 { data, error } = await supabase.rpc('nearby_videos', 
  {location: 'POINT(${longitude} ${latitude})'}
)

I actually utilize a lot of these geo queries in my open source project Spot, so it might help out to check it out.

[phillipmohr]

@dshukertjr hey Tyler, Unfornuately it turned out that it doesn't work like I expected. This puts premium studios first, yes, but it also gives me premium studios from another city which is far away. I only want to get premium studios that are nearby. How could I do that?

Edit: Tyler helped me out somewhere else, this is the solution if anyone should stumble over this. This sorts by location and then puts premium studios first:

create or replace function nearby_studios(location text)
returns setof studios
language sql
as
$$
  with nearest_50_studios as (select *
    from studios
    order by
      location <-> st_geogfromtext(nearby_studios.location) limit 50
  )
  select * from nearest_50_studios order by is_premium is true desc, location <-> st_geogfromtext(nearby_studios.location);
$$;

[mijorus]

I edited it slightly to make it more cleaner to use, IMO

create or replace function get_nearby_shops(lat float, long float)
returns setof shops
language sql
as

$$
  select *
  from shops
  order by location <-> st_geogfromtext(
    concat('POINT(', get_nearby_shops.long, ' ', get_nearby_shops.lat, ')')
);
$$;

const {data, err} = await client.rpc('get_nearby_shops', {lat: 30, long: 20}).limit(10)

I have also found another solution:

SELECT uuid, name, lat, long, (point(long, lat) <@> point(get_nearby_shops.long, get_nearby_shops.lat)) AS distance
FROM
 shops

ORDER BY
 distance;

although some claim it is slower on bigger datasets

[organicnz]

Hi @dshukertjr,

Apologies for asking, mate. Just wondering, Is it possible to convert geography to geopoint data type? I already switched from Firebase to Supabase where I used lat/long geopoint data type in my FlutterFlow application, but now I can't set it up unfortunately :)

Thank you :)

[dshukertjr]

@organicnz
No need to apologize to ask questions 😁

The FlutterFlow Supabase integration is build and maintained by the FlutterFlow team, and we don't have access to any of their source codes. These data types seem FlutterFlow specific, so you would have to ask the FlutterFlow team how to convert the Supabase geography type to data types that you can use within FlutterFlow.

[incrediblezayed]

Hey @diamondalltheway @dshukertjr @phillipmohr @mijorus Can you guys help me to filter by distance, for example I want to fetch the stores within 50 Kms/Miles of the given lat long, how can I achieve this?

[iamevaristus]

I don't really know how, but I want to build something like uber where the driver's location will be inserted to the live_location table so that the users can fetch that when they want to request for a ride. I have been able to insert the driver's location to the table, but how can I make the table realtime such that it reads the changes of the driver's location and provide the new latlng. Like when the driver moves, I need the table to be updated with the new latlng.

I am new to supabase and I need help with that. I dont want to be adding new data row to my live_location table, i want to update a certain row based on the ID of that driver such that a driver will only have one row of data which will be inserted once and then be updating itself once the location changes. And when the driver goes offline, I want to delete that record from the live_location table.