https://supabase.com/ logo
Join Discord
Powered by
Has anyone set up geo location, distance from loca...
# off-topic
j
Has anyone set up geo location, distance from location, with supabase query?
s
It depends what you're trying to achieve. If you just want to know the distance from a location, there's libs like 
geolib
(for JS). If you've got a set of data in Postgres and want to search for e.g. nearest points from the user location, there's a few things to setup. I've done this for things like bus stops, since users will usually want to know where bus stops are within an area as opposed to remembering a street name. 1. Ensure that each row has a 
latitude
and 
longitude
property and that all rows have valid data for these columns. 2. Ensure that you've enabled (or installed, if self-hosting) the 
postgis
extensions 3. Update your table so that it has a 
geometry
column of type 
extensions.geometry
4. Update every row in the table to create the geometry data (in this example, the table is called `some_table`:
Copy code
sql
UPDATE public.some_table SET geometry = extensions.ST_SetSRID(extensions.ST_MakePoint(longitude, latitude), 4326);
5. Create a function in Postgres with the following content:
Copy code
sql
CREATE OR REPLACE FUNCTION public.search_locations_by_distance(
    in_latitude real,
    in_longitude real,
    max_distance_in_meters integer,
    result_limit integer DEFAULT 20)
    RETURNS TABLE(
        atco_code text,
        naptan text,
        common_name text,
        short_common_name text,
        landmark text,
        street text,
        crossing text,
        bearing text,
        longitude real,
        latitude real,
        geographic_area text,
        distance double precision
    ) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000
AS $BODY$
BEGIN RETURN QUERY
SELECT *
FROM public.some_table as stops,
    extensions.ST_Distancesphere(
        stops.geometry,
        extensions.ST_MakePoint(in_longitude, in_latitude)
    ) as distance
WHERE extensions.ST_Distancesphere(
        stops.geometry,
        extensions.ST_MakePoint(in_longitude, in_latitude)
    ) < LEAST (max_distance_in_meters, 5000)
ORDER BY distance ASC
LIMIT LEAST (result_limit, 100);
END;
$BODY$;
In that example, it allows me to specify a latitude, a longitude, a maximum distance, and a result limit. It also enforces a maximum distance of 5000 meters and 100 results using the 
LEAST
function. After you've gone through that setup, you're able to call it with the JS library like this:
Copy code
js
SupabaseClient.rpc("search_locations_by_distance", {
  in_latitude: <user latitude>,
  in_longitude: <user longitude>,
  max_distance_in_meters: 800,
})
result_limit
will default to 20 if not specified.
In terms of performance, with ~37 million rows which have indexes and partitions setup, querying data takes around 1400ms on a self-hosted instance (with 4GB RAM and 2 VCPU cores). It's certainly not the fastest query out there, but I deem it to be acceptable considering the size of the dataset.
j
Hi Scott, Thank you so much for this explanation. The more involved set up is what I'm looking for. I appreciate the detail. And this is exciting. We ought to set up a Medium for official supabase tutorials, this being one. Or some sort of curated list of tutorials by the Supabase team. Question, what do you think the query performance would be on a Supabase hosted instance?
s
I'm honestly not sure what the performance would be like on a hosted instance - my dataset actually exceeded what was available on the $25 plan so I couldn't test anything of this scale. However, I'd expect the performance to be very similar. Without indexes and partitions, I found the queries to take anywhere between 3-5x longer to complete.
j
Awesome! Thank you for the insight!
@User can you share your postgres geolocation function? I figured I'd try and do this.
b
Sure, I'll post here!
Copy code
WHERE
    earth_box(ll_to_earth(${lat}, ${lng}), ${radius}) @> ll_to_earth("Latitude", "Longitude") 

    AND earth_distance(ll_to_earth(${lat}, ${lng}), 
              ll_to_earth("Latitude", "Longitude")) < ${radius} 

ORDER BY distance LIMIT 200
You pass it: 
lat
and 
lng
, the fields in the database are named 
Latitude
and 
Longitude
, and you also pass it 
radius
which is in meters, which would be the max search radius.
Be sure to turn on the 
EARTHDISTANCE
extension!
PreviousNext
Powered by