TutorialsCourses

How to Use Hasura with PostGIS

AuthorJason Brown

When working with location data things can become very complicated, especially when needing to query for locations with in regions, or by distance. PostGIS makes this a very easy process as it is an extension that with one command can add GIS abilities to your database.

Generally many ORMs do not integrate well with PostGIS, thankfully Hasura has first class support for integrating with PostGIS. We'll walk through getting setup locally with Hasura, Postgres, and PostGIS.

The first step is our docker-compose.yaml file. Rather than use the regular postgres docker image we'll use the postgis/postgis docker image which has the extension already enabled.

So take the code below, save it off as docker-compose.yaml and then run docker-compose up -d.

version: "3.6"

services:
  db:
    image: postgis/postgis
    restart: always
    environment:
      POSTGRES_USER: postgres
      POSTGRES_HOST_AUTH_METHOD: trust
    volumes:
      - db_data:/var/lib/postgresql/data
  graphql-engine:
    image: hasura/graphql-engine:v1.3.4-beta.2
    ports:
      - "8080:8080"
    depends_on:
      - "db"
    restart: always
    environment:
      HASURA_GRAPHQL_DATABASE_URL: postgres://postgres:@db:5432/postgres
      HASURA_GRAPHQL_ENABLE_CONSOLE: "true"
      HASURA_GRAPHQL_DEV_MODE: "true"
      HASURA_GRAPHQL_ADMIN_SECRET: secret
volumes:
  db_data:

If you don't already have a hasura folder running make sure you install the hasura CLI and then run hasura init to create your hasura project.

Open up the hasura console with hasura console and head over to the Data > SQL area to apply a migration. To enable the PostGIS extension we need to run this. We'll save it off as a migration so that when we go to production it will be applied. Places like AWS RDS have PostGIS available to be enabled.

CREATE EXTENSION IF NOT EXISTS postgis;

Now we need to add a column. In this case lets say we have a table full of wineries and we want to then search for those wineries and display them on a map. All we need is a singular point aka latitude/longitude so we'll create a GEOGRAPHY(Point) column type.

ALTER TABLE winery ADD location GEOGRAPHY(Point);

Many people may have existing content that that has latitude and longitude stored in separate text columns. To transition over to our new column we can run this query. Lets break it apart.

  • We get the winery.longitude and winery.latitude column values.
  • We create a point out of it with a function provided by PostGIS called ST_MakePoint. Notice the order. We supply the longitude in the first slot and latitude in the second slot.
  • Then we set the SRID (Spatial Reference Identifiers). It describes the type of coordinate system to use. In this case we're saying use geographic coordinates.
UPDATE winery SET location = ST_SetSRID(ST_MakePoint(winery.longitude, winery.latitude), 4326);

Now that we have a table full of locations lets look at the query that we need to the Hasura GraphQL query we need to construct to actually query for locations.

If we are displaying wineries on a map we want to query for wineries with in a distance. Using https://postgis.net/docs/ST_DWithin.html we can supply a central point and then query for any wineries by supplying a specific diameter in meters.

{
  _st_d_within: {
    distance: 32186.9, // 20 miles in meters
    from: {
      type: "Point",
      coordinates: [-118.39, 45.93]
    }
  }
}

A GraphQL query we could build we can specify the geography scalar type. So we can query by distance and then pass back the from from above. Again the longitude comes first, and the latitude comes second in the array.

query FindWineries($distance: Float!, $from: geography!) {
  winery_winery(
    where: { location: { _st_d_within: { distance: $distance, from: $from } } }
  ) {
    name
    location
  }
}

If we want to do an insert with Hasura we supply the location as a type point, and then longitude and latitude. This will insert a point correctly and now be queryable with the graphql query above.

 {
	location: {
		type: "Point",
		coordinates: [values.longitude, values.latitude],
	},
}
Home
Tutorials
Courses