Finding Nearby Items and Sorting by Distance: A Guide with Hasura and PostGIS

I found some tutorials on how to find all the items that are within a certain distance from LAT/LON (latitude/longitude) but didn’t find how to see the actual distance so I can display it and also sort the items for example by ascending distance in meters.

I did it for my cool customer Woofi where I needed it so that if there’s a new item in service_requests table (where is also geo_location field that has type geography) then I can query all the items from service_providers table that are within 50km radius and sort them by nearest first:

query NearbyProviders($lat: float8, $lon: float8, $max_distance: Int) {
  get_nearby_service_providers(
    args: { lon: $lon, lat: $lat, max_distance: $max_distance }
    order_by: { distance: asc }
  ) {
    id
    travel_distance
    distance
  }
}

And then get a response like this:

OK, so how did I do it?

Creating a View

This view includes all the attributes of service providers and adds an additional attribute called distance, which will later be used to store the geographical distance between a specific point and each service provider.

CREATE OR REPLACE VIEW "public"."service_providers_with_distance" AS
SELECT
  service_providers.id,
  <---- ALL THE OTHER FIELDS I HAVE IN THE TABLE ---->
  service_providers.geo_location,
  (0) :: double precision AS distance
FROM
  service_providers;

You’ll need to add all the fields into the view that you want to query later.

Defining a Custom Function

This function will be responsible for computing the distances of the service providers from a specific location (defined by longitude and latitude) and for limiting the results to service providers within a maximum distance.

The function uses the PostGIS functions ST_SetSRID to assign a spatial reference identifier (SRID) to the geographic points, ST_MakePoint to create a point from the provided longitude and latitude, ST_DWithin to filter the service providers within the maximum distance, and ST_Distance to compute the distances:

CREATE OR REPLACE FUNCTION public.get_nearby_service_providers(
  lon double precision,
  lat double precision,
  max_distance integer
) RETURNS SETOF public.service_providers_with_distance LANGUAGE plpgsql STABLE AS $$
BEGIN
RETURN QUERY
SELECT
  sp.id,
 <---- ALL THE FIELDS YOU ADDED ALSO TO THE VIEW IN THE SAME ORDER --->
  sp.geo_location,
  ST_Distance(
    ST_SetSRID(sp.geo_location, 4326)::geography,
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography
  ) as distance
FROM
  public.service_providers sp
WHERE
  ST_DWithin(
    ST_SetSRID(sp.geo_location, 4326)::geography,
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography,
    max_distance
  )
ORDER BY
  distance;
END;
$$;

Make sure to tick the “track this” checkbox when you run these SQLs so you don’t need to track them later.

All the code: https://github.com/crewnew-git/hasura-postgis-distance (star please). There you have also a query to add an item with the type “geography” field and how to drop the function or view when stuff goes wrong before running a new SQL.

That’s it!

Go and test it out. Need help? WhatsApp me +3725288846.

With these two steps completed, we now have a view and a function that, when used together, can identify all service providers within a specific distance from a point and sort them from nearest to farthest.