GraphQL and geolocation on Postgres (PostGIS using Hasura)
Tags: graphql, postgres, tutorial, database
Published: 2019-12-19 08:01:29

---
title: GraphQL and geolocation on Postgres (PostGIS using Hasura)
published: true
date: 2018-05-02 00:00:00 UTC
tags: graphql, postgres, tutorial, database
canonical_url: https://blog.hasura.io/graphql-and-geo-location-on-postgres-using-hasura-562e7bd47a2f/
---

![](https://blog.hasura.io/content/images/2019/01/image-1.png)

A primer to building backends for location-aware applications using postgres and GraphQL

In this post, we are going to go over how you can use Postgresql and GraphQL to build location aware applications. We are going to go over a simple use case where we are building a GraphQL backend for an application which shows a list of landmarks around you.

We are going to use the [Hasura GraphQL engine](https://hasura.io/) for this as it provides out of the box GraphQL APIs over postgres.

## Getting started

We will use the Hasura GraphQL engine for instantly getting GraphQL APIs over Postgres. Click on the button below to deploy the GraphQL engine to Heroku’s free tier.

[![Hasura on Heroku](https://www.herokucdn.com/deploy/button.svg)](https://heroku.com/deploy?template=https://github.com/hasura/graphql-engine-heroku)

This will deploy the `graphql-engine` to Heroku. You might need to create a Heroku account if you don’t have one. The `graphql-engine` will be running at `https://your-app.herokuapp.com` (replace `your-app` with your heroku app name).

## API Console

Every Hasura cluster comes with an `API Console` that you can use to build the backend for your application.

The API Console is available at [`https://your-app.herokuapp.com`](https://your-app.herokuapp.com)`/console`

## PostGIS

To store and compute on geo-location data, we are going to use `PostGIS` which is a spatial database extender for `Postgres`. It adds support for geographical objects on Postgres and also comes with utility functions such as distance, area, union, and intersection.

### Enabling PostGIS

Before we begin, we need to enable the PostGIS extension on Postgres. (You’ll need a Postgres instance with PostGIS installed. Heroku Postgres comes with the installation, you just need to create the extension. If you’re using Docker based setup, checkout [this docker-compose](https://github.com/hasura/graphql-engine/tree/master/install-manifests/docker-compose-postgis) file)

Head to `Data` tab in the `Api Console` and click on `SQL` in nav bar on the left. And execute the following SQL commands:

{% gist https://gist.github.com/dsandip/a153dbd3907a174c9949c77a2f31e290.js %}

_Please ensure that you select the_ `Track this` _checkbox before running the query so that this change is tracked as a migration._

## Creating tables

Say we need 2 tables, for user and landmark location data, with the following definitions:

{% gist https://gist.github.com/dsandip/885f6fac9f86667d7b81e9193817189c.js %}

Run the above SQL in the same `SQL` section of the console as shown above, and don't forget to check the `Track this` checkbox (_this is because we want GraphQL Engine to track this change in the db schema_).

We are storing the location of the landmark and the user in a column named location of type `GEOGRAPHY(Point)` for each table.

## Inserting Data using GraphQL

You can populate the tables we just created with test data using the following mutations and variables:

{% gist https://gist.github.com/dsandip/deae2a7c3d8f7407dcd27c6cd4746281.js %}

## Fetching list of landmarks around a user

In this example, we want to fetch a list of landmarks that are near a given user, along with the user’s details in the same query. PostGIS’ built-in function, `ST_Distance` can be used to implement this use case.

Since our use-case requires an output that isn’t a “_subset_” of any of the existing tables i.e. the `SETOF` table doesn’t exist, let’s first create this table and then create our location search function using the following definitions (_once again, we’ll use_ _the_ `Data` **-\>** `SQL` _section of the Hasura console):_

{% gist https://gist.github.com/dsandip/8f85439eb59adc9c659bafe9d0106c00.js %}

This function fetches user information (_for the given input_ `userid`) and a list of landmarks which are less than `distance_kms` kilometres away from the user’s location as a JSON field. We can now refer to this function in our GraphQL API and get the corresponding response as shown below:

**query:**

{% gist https://gist.github.com/dsandip/c93904d6e6908fb6a84ccacf1e10e782.js %}

**example response:**

{% gist https://gist.github.com/dsandip/e0aef9a2911bae62127b59ead3c1a83f.js %}

## Conclusion

PostGIS also comes with a variety of other utility functions which you can use. You can find that list [here](https://postgis.net/docs/manual-1.5/ch08.html).