I am experimenting with Hasura for the first time and wanted to toy around with a location-based app where a user could see a grid of locations from their current location.
Naturally, I referenced the blog posts https://blog.hasura.io/graphql-and-geo-location-on-postgres-using-hasura-562e7bd47a2f/ and https://blog.hasura.io/native-support-for-postgis-topology-operators-now-in-graphql-engine/ to get me started, and have kept things relatively the same as what the blog post outlined.
-- SETOF table
CREATE TABLE user_venues (
user_id INTEGER,
location GEOGRAPHY(Point),
nearby_venues JSON
);
-- function returns a list of venues near a user based on the
-- input arguments distance_kms and userid
CREATE FUNCTION search_venues_near_user(userid integer, distance_kms integer)
RETURNS SETOF user_venues AS $$
SELECT A.user_id, A.location,
(SELECT json_agg(row_to_json(B)) FROM venue B
WHERE (
ST_Distance(
ST_Transform(B.location::Geometry, 3857),
ST_Transform(A.location::Geometry, 3857)
) /1000) < distance_kms
) AS nearby_venues
FROM user_location A where A.user_id = userid
$$ LANGUAGE sql STABLE;
Where it differs, is in the custom function — I am returning GeoJSON as mentioned in the comments of the original blog post, but also wanted to extend it further by calculating the distance
relative to the current user and returning that in the response data as well.
CREATE
OR REPLACE FUNCTION public.search_venues_near_user(userid integer, distance_kms integer) RETURNS SETOF user_venues LANGUAGE sql STABLE AS $ function $
SELECT
A.user_id,
A.location,
(
SELECT
json_agg(
json_build_object(
'name',
B.name,
'type',
B.type,
'geometry',
ST_AsGeoJSON(B.location) :: json,
'distance',
ST_Distance(
ST_Transform(B.location :: Geometry, 3857),
ST_Transform(A.location :: Geometry, 3857)
) / 1000
)
)
FROM
venue B
WHERE
(
ST_Distance(
ST_Transform(B.location :: Geometry, 3857),
ST_Transform(A.location :: Geometry, 3857)
) / 1000
) < distance_kms
) AS nearby_venues
FROM
user_location A
where
A.user_id = userid $ function $
The issue I am running into now is trying to use ORDER BY
correctly so that I could return the results in ASC
order, but no matter where I try to place the ORDER BY
, I get errors when trying to save the function.
Any ideas/recommendations on how to solve that problem as well as DRY-ing up the function code would be greatly appreciated!
Sorry I couldn't have the reserved keywords in blue and the fancy stuff.
CREATE OR REPLACE FUNCTION public.search_venues_near_user(userid integer, distance_kms integer)
RETURNS SETOF user_venues LANGUAGE sql STABLE AS $$
SELECT
A.user_id,
A.location,
(
SELECT
json_agg(
json_build_object(
'name',
B.name,
'type',
B.type,
'geometry',
ST_AsGeoJSON(B.location) :: json,
'distance',
ST_Distance(
ST_Transform(B.location :: Geometry, 3857),
ST_Transform(A.location :: Geometry, 3857)
) / 1000
)
)
FROM
venue B
WHERE
(
ST_Distance(
ST_Transform(B.location :: Geometry, 3857),
ST_Transform(A.location :: Geometry, 3857)
) / 1000
) < distance_kms
) AS nearby_venues
FROM
user_location A
where
A.user_id = userid order by 1 $$