Graphql-engine: Return the geography distance in query response

[sawankumarbundelkhandi]

How can I return the distance from the geography(location) I gave in query variable to the city (cities table) geography (Basically, the distance of the city from my location). I need to show the city as nearest to farthest.

Below is my table schema

CREATE TABLE cities (
  id SERIAL PRIMARY KEY,
  name TEXT,
  location GEOGRAPHY(Point)
);

Below is the query I am executing to get the cities near my location. I am passing my current location and a distance

query cities_near($point: geography!, $distance: Float!) {
  cities(
    where: {location: {
      _cast: {geography: {
        _st_d_within: {from: $point, distance: $distance}
      }}
    }}
  ) {
    name
  }
}

Below is the query variables

{
  "point": {
    "type": "Point",
    "coordinates": [1, 50]
  },
  "distance": 1000000
}

[0x777]

You'll need to write a custom SQL function for this: https://docs.hasura.io/1.0/graphql/manual/queries/custom-functions.html

[marionschleifer]

I'm closing this. @sawankumarbundelkhandi feel free to re-open if you'd like to add something 🙂

[webdeb]

Since _st_d_within is a built-in type, it makes sense to also return the relative distance to the geographical object, which you pass into the query. This would allow to order the items by the returned distance. And in combination with limit could result in more efficient queries.

@0x777 really not a simple way to implement it for the _st_d_within type? I would like to take action, if needed, just give me some advises which parts are necessary to tweak, since I do not have any haskell experience yet :D

[sawankumarbundelkhandi]

@webdeb @0x777 We are trying to use the computed fields for the same thing. I will update here if I get it to work.

https://docs.hasura.io/1.0/graphql/manual/schema/computed-fields.html

[BayCo]

@sawankumarbundelkhandi i have the same issue, how did you solve this problem, what function did you used in computed field?

[webdeb]

We've created a custom view id, distance and a function which accepts lat, lon, kms to return a set of those view. Based on this we have a relationship to the original table so the query looks something like this:

 {
   locals_by_distance(args: {lat: 12, lon: 5, kms: 100}) {
      distance
      local {
         ...other fields
      }
   }
}

The SQL query is also not difficult, basically based on this blog post:
https://hasura.io/blog/graphql-and-geo-location-on-postgres-using-hasura-562e7bd47a2f/

[BayCo]

@webdeb then computed field wasn't the right approach??? because now i am trying to solve this issue by computed field

[Edjevw12]

@BayCo Did you manage to solve it?

[sawankumarbundelkhandi]

@Edjevw12 for now I work around it. I have followed @webdeb answer above.

I created a small table with Id and distance column and added a foreign key reference of the Id column to the original table with all the data.
Then I created a function which takes the user lat, long and calculates the distance between the lat long passed to the function and all the location in the main table. The function returns a set of the small table with distance calculated and the Id of the row for which the distance is calculated.