Using PostGIS with postgraphql

[amaury1093]

Is this library compatible with PostGIS?

Running postgraphql@next, I can generate the graphql schema of a pg database with PostGIS installed.

However, I cannot manage to find how to get for example the result of this query via GraphQL:

SELECT ST_AsGeoJSON(polygon) from territories;

assuming I have a table called territories with a column named polygon.

[benjie]

We don't have explicit support for PostGIS, but it might work implicitly.

First port of call would be to implement it as a computed column:

https://www.graphile.org/postgraphile/computed-columns/

If that works but you want it to work everywhere without having to add loads of computed columns, this is where I would use a plugin to add an extra field for every column of type polygon (or whatever the rule is); see:

https://www.graphile.org/postgraphile/extending/

If you could develop the plugin in the open then others can help give you feedback and the entire community can benefit 👍

(The documentation is still just a first version - feel free to raise pull requests improving it: https://github.com/graphile/graphile.github.io/ )

[amaury1093]

Thanks for the explanations. I would go for the plugin way.

Three questions:

1. First idea is to add an extra field as you said, and it's done in this gist as the polygonAs field. However, even after reading the docs and the code of some of the other plugins, I still didn't figure out how to resolve by calling my custom SQL query. See line 31 of the gist.

2. My second idea was to replace the default resolver when we have a polygon field, using a hook on GraphQLObjectType:fields:field. See this gist for the beginning of the implementation. Line 36, I wish to modify the SQL query sent to resolve, but again, no idea how.

3. Still in the second gist, line 10: instead of filtering on the fieldname like I do right now, would it be possible to filter on the Postgres type of the field? In my case it's a GEOMETRY(POLYGON) type, and the Graphql type is a GraphQLString. I inspected the field and Context objects, but didn't find anything.

[benjie]

Hey; great start! So in v4 you wouldn't implement it with a resolve method because that would require an entire query to the DB just to do that one field (though you could do it that way and it would work it would be very inefficient). Instead we have a QueryBuilder that builds our queries for us, so we register the field with the look-ahead feature, and register a pgQuery callback like in the PgColumnsPlugin that tells the QueryBuilder to also select an additional field (and alias it with the name requested by the GraphQL query):

https://github.com/graphile/graphile-build/blob/master/packages/graphile-build-pg/src/plugins/PgColumnsPlugin.js#L61-L70

The data generator will probably be something like this (where attr is a reference to the column found in pgIntrospectionResultsByKind.attribute):

addDataGenerator(parsedResolveInfoFragment => {
  const { alias } = parsedResolveInfoFragment;
  return {
    pgQuery: queryBuilder => {
      queryBuilder.select(
        sql.fragment`ST_AsGeoJSON(${sql.identifier(queryBuilder.getTableAlias(), attr.name)})`,
        alias
      );
    },
  };
});

and then you want a simple resolver that just returns the alias property on the parent:

resolve(data, _args, _context, resolveInfo) {
  const alias = getAliasFromResolveInfo(resolveInfo);
  return data[alias];
}

Hope this helps, feel free to ask for further help. If you fancy turning this into a tutorial once you've figured it out that would be awesome 🤘

[brambow]

In a previous version of postgraphql, I experimented with working with postgis and wound up creating a view (or used materialized views for complex polygons) that added a geojson field.

Something to think about is whether you really just want a column of geojson geometry or you want to return a full GeoJSON FeatureCollection from a query. I think there are valid cases for either option, depending on the ultimate use.

If you want a FeatureCollection instead of individual geometries and don't want to write it client-side, you can use something like this to build it in PostGIS (I think it requires v9.5+ for some of the json operations):

https://gist.github.com/brambow/889aca48831e189a62eec5a70067bf8e

In my past case, I put the query in that gist inside of a query to either create a view or a 'materialized view' (create table + trigger). Another way to do it would be to put the query inside a custom function that returns a set of json.

I just came across this and am interested in digging into this idea of plugins for postgraphql. Any thoughts on writing geojson geometry to a computed column vs. writing and returning a FeatureCollection?

[benjie]

I don't know enough about PostGIS to pass comment on those parts of your message, however you could add a plugin that adds a pgTweaksByTypeId for the relevant PostGIS types that automatically converts it somehow whenever it's referenced (rather than requiring a computed column). That's assuming the raw value converted via to_json() isn't particularly useful to your API.

https://github.com/graphile/graphile-build/blob/a383a35dd7aba9453768363e02f559ec738e09f0/packages/graphile-build-pg/src/plugins/PgTypesPlugin.js#L253-L255

e.g. something like (pseudocode):

module.exports = function AddPostGISTypes(builder) {
  builder.hook(
    "init",
    (_, { pgRegisterGqlTypeByTypeId, pgRegisterGqlInputTypeByTypeId, pgTweaksByTypeId }) => {
      const JSON = getTypeByName("JSON"); // This won't work in the current release but will in the next one when I release it
      pgRegisterGqlTypeByTypeId(POSTGIS_TYPE_ID, set => set(JSON));
      pgRegisterGqlInputTypeByTypeId(POSTGIS_TYPE_ID, set => set(JSON));
      pgTweaksByTypeId[POSTGIS_TYPE_ID] = fragment => sql.fragment`ST_AsGeoJSON(${fragment})`;
      return _;
    }
  );
};

⚠️ untested, just for inspiration.

[brambow]

Ignoring my previous comment about returning the geometry vs a full feature collection, let's say the initial goal is to build a plugin that creates a computed column called 'geojson' that could be applied to any table that has a 'geometry' type column and would return the geometry in geojson format.

It would be nice to filter on the field types to only get postgis geometry column types, but naming the geometry column of a table something like "geom" is a common practice so doing it by column name would also work. It should also be noted that ST_AsGeoJSON returns a "text" type, not a "json" type (although you could cast the result: ST_AsGeoJSON(geom)::json).

I'm not quite wrapping my head around building a plugin yet, so I'll keep reading. For example, I don't really understand the choices that were made on lines 4-6 here (the build and scope objects):

Admittedly, my main exposure to GraphQL has been through this project, so there are probably some things I'm not grasping. Also, what's the recommended way for testing/debugging while trying to build a plugin? Do I just need to be appending it to the plugins in a project that's using postgraphile, or is there some other workflow you recommend?

This is what I'm working with right now:

https://gist.github.com/brambow/60a4eb916cda18b80e297408eb88030f

[benjie]

You kind of need a hybrid between those two solutions. The former matches the relevant table and adds a new field, but does not register the "data generator" required to request the data (and it doesn't really have a resolve method). The latter adds the data generator and the resolve method but it hooks the root Query type rather than the relevant table.

For debugging I hook it up to an existing system and use breakpoints and chrome://inspect; I've also added a lot of optional verbosity via the debug module that you can enable with DEBUG="graphile*" postgraphile ...

[singingwolfboy]

I'd love to have a PostGIS plugin for PostGraphQL, and I'm not afraid to dive in and start building it myself if no one else has done so yet! However, it would help a LOT of people could send me whatever code they've written that's related to this effort -- I just found this project a few days ago, so I'm still quite new to it. Having some code samples to start from would make it much easier to write a generic plugin that can handle all different sorts of PostGIS data-types.

Does anyone have any code they're willing to share?

[brambow]

I never made much progress on creating a postgraphile plugin for PostGIS compatibility, but here's how I use this library with postgis to return geojson from a table:

CREATE OR REPLACE FUNCTION {function_name}({table name})
  RETURNS "pg_catalog"."json" AS
    $BODY$
      SELECT st_asgeojson({table name}.geom)::json as geojson;
    $BODY$
LANGUAGE sql STABLE

Creating this stored procedure returns a property called 'geojson' with the regular postgraphile query you'd use to query all records in a table. This syntax assumes that the geometry field in the postgis table is called 'geom', and you do have to create this procedure for each table you want to return geojson for. That's where a plugin would be nice.

I'd love to dig in further and actually work on a postgraphile plugin for this. There's a lot more to postgis than returning geojson and postgis is a major use-case for postgresql databases, so there's a lot of opportunity.

[singingwolfboy]

I got started on writing a generic PostGIS plugin for graphile-build! It's not working yet, but I've made a GitHub repository here: https://github.com/singingwolfboy/graphile-build-postgis

Please take a look at what I've done so far, try it out yourself, and maybe help me extend it! I included the notes I made while trying to understand the graphile-build codebase -- maybe reading those, and the comments I left in the code, will help you figure out how to improve this project and make it work. If so, please make a pull request! Even if you don't have any code to add, and you just want to add to the notes document, that would be really helpful.

Let's figure this out together, and build it together!

[benjie]

Awesome 👏

If you need to expand the introspection query, please submit a PR 👍

If you need extra data in the plugin interface/etc please raise an issue against graphile-build; I had to add more yesterday that i thought were already there. Do not assume that the stuff you need is definitely there 😉

[barakd]

Loved @singingwolfboy initiative!

In the meanwhile, this has worked for me out of the box: In order to update geometry column:

mutation{
  createAddress(input: {
    address: {
      geom: "SRID=4326;POINT(25800 256000)",
    }
  })`

And I created this computed column called coordinates, to retrieve the geom, as a readable JSON, otherwise, it returns it as a hex value.

create function schema.adderss_coordinates(a schema.address)
returns jsonb as $$
  select ST_AsGeoJSON(a.geom)::jsonb
$$ language sql stable;

[sfkeller]

Just FYI: "Creating a GeoJSON FeatureCollection Type for GraphQL - Using a custom Scalar type to return feature geometry" by Bryan Grill, Jan 30 2018, https://medium.com/@brygrill/creating-a-geojson-featurecollection-type-for-graphql-352591451b4a

[chrisbull]

So I too am stuck on this Postgris problem. And I took a look at that plugin, but it doesn't work (either branch).

So @brambow has a great recommendation, but super confusing for me with the example. So after hours of research and trial and error, I stumbled on the documentation and got the computed column to work. Here is my version of the example:

create function public.segments_geojson(u public.segments)
returns json as $$
  select ST_AsGeoJSON(u.geometry)::json;
$$ language sql stable;

and the documentation example: https://www.graphile.org/postgraphile/computed-columns/

in which "public" is your schema name (typically it's "public") then the "segments_geojson"... does something interesting where it makes a graphql field called "geojson" available in the "Segments" type. And returns the value that is returned in the above computed column.

Thus:

Segment(id: 500) {
  geojson
}

returns

{
  geojson: "{\"type\":\"MultiLineString\",\"coordinates\":[[[-79123456,40.123456],[-79.123456,40.123456]]}"
}

...which is exactly what I needed, and way easier than writing an entire plugin.

I hope this helps someone else.

* UPDATE To update and help for anyone who arrives here looking for help, here is a fix and more simplified version of the query I had above:

--- segments is my table name
--- geojson is the column i'm generating via the function and attatching to the table
CREATE FUNCTION geojson(u segments)
  RETURNS json AS
$func$
    SELECT ST_AsGeoJSON(u.geometry)::json AS geojson;
$func$ LANGUAGE SQL STABLE;

--- here is a sample select query to test that the column was created 
SELECT a.geojson from public."segments" a;

[benjie]

I recommend you enable --dynamic-json so you don't have to parse the JSON on the client side.

---

BTW the wip branch to @singingwolfboy's plugin does some work on this but it's definitely work in progress and needs documentation and tests. I'm hoping to get back to it at some point soon and at least explain how it works!

https://github.com/singingwolfboy/graphile-build-postgis/pull/1

[benjie]

[semi-automated message] We try and keep the open issues to actual issues (bugs, etc); this seems like more of a discussion right now, so I'm closing it but please feel free to keep discussing it below 👍

[sfkeller]

Since we are still in the discussion phase ;-) it's interesting to see how others implemented it: See https://github.com/hasura/graphql-engine/search?q=geometry .

Can anyone comment how Hasura compares to PostGraphile and if the Hasura approach could help implementing geometry/geography in PostGraphile?

[benjie]

I'd certainly be interested to hear how their PostGIS support compares to that in the wip branch of graphile-build-postgis. 👍

[sfkeller]

From what I understood, they don't change the json output (no GeoJSON) and use views (to be exposed to GraphQL) which contain functions from PostGIS and Timescale. There's a video "Dashboard for 10million rides with geo-location (PostGIS, Timescale)" https://youtu.be/tsY573yyGWA?t=56s on https://github.com/hasura/graphql-engine .

[benjie]

We make the geojson available; and we also add lat/lng to point types; and it's pluggable so you can add additional attributes (by extracting from the geojson) available also. I just need to find some time to finish it up - mostly just adding comments to the source and some tests is what's required.

[sfkeller]

@benjie I'm currently running a database seminar at our university and wanted give https://github.com/benjie/graphile-build-postgis a try, being aware that the README there say's "a comprehensive solution is a long way off.". I mainly was encouraged by your comment above, saying "I just need to find some time to finish it up - mostly just adding comments...". What's the status of this plugin between "long way off" and "just adding comments" :) ?

Should I just add this https://github.com/benjie/graphile-build-postgis/pull/1 to my local code to get s'thing working?

[benjie]

Use the “wip” branch; the master branch is not my work. And by all means add it to your own code and/or even send a PR :)

[sfkeller]

Ok, I'll try. Given the boundary polygon of Switzerland, I'd like to get all zoos within, like this:

  query zoos($ch_boundary: geometry){
    zoos(where: {geom: {_st_within: $ch_boundary}}){
      id
      name
      geom
    }
  }

And I'd expect a GeoJSON response like this

  {
    "type": "FeatureCollection",
    "features": [
      {
        "type": "Feature",
        "properties": {
          "id": 1,
          "name": "Kinderzoo"
        },
        "geometry": {
          "type": "Point",
          "coordinates": [8.822570, 47.223437]
        }
      },
      {
        "type": "Feature",
        "properties": {
          "id": 2,
          "name": "Züri Zoo"
        },
        "geometry": {
          "type": "Point",
          "coordinates": [8.574421, 47.385239]
        }
      }
    ]
  }

[sfkeller]

The focus of the seminar is to evaluate PostgreSQL-based GraphQL backends (including extensions like PostGIS) and to make simple benchmarks against these. So there's no time left to make patches to server side libraries. Unfortuately we had no success with the above mentioned current wip branch of the PostGIS plugin.

P.S. That's what we expected and got with Hasura: A query containing PostGIS filter fn. st_within() with a GeoJSON response:

[benjie]

Sorry we couldn't meet your deadline. Nonetheless for anyone else following, progress is being made:

[benjie]

(Also note that you an implement any PostGIS features you need using PostgreSQL functions.)

[sfkeller]

Thx @benjie for your answers.

Sorry we couldn't meet your deadline. Nonetheless for anyone else following, progress is being made:

It's never too late :) We're still having another decision later about a university project (delivering processed OpenStreetMap data as GraphQL).

(Also note that you an implement any PostGIS features you need using PostgreSQL functions.)

Noted. And that's what we will do in oder to test how to map Postgres data types to GraphQL.

[benjie]

@graphile/postgis now available on npm; adds support for reading and writing various geometry and geography types. Does not add any filtering capabilities yet.

[benjie]

@mattbretl is working on PostGIS filters for the filter plugin if anyone wants to help out (or help testing): https://github.com/mattbretl/postgraphile-plugin-connection-filter-postgis

[brambow]

@mattbretl what kind of contributions are you looking for?

[mattbretl]

@brambow Mostly for people to dive in and test it, and file issues (bugs, feature requests) as needed.

If you're keen to work on something concrete, I just opened this issue: https://github.com/mattbretl/postgraphile-plugin-connection-filter-postgis/issues/2

[Murthy10]

@benjie which types are supported?

I've got a column of the type "geography(Point,4326)" resulting in an error:

"Field \"geom\" must not have a selection since type \"String\"

Do you have any suggestions for improvements?

[benjie]

@Murthy10 Are you using the postgis plugin?

https://github.com/graphile/postgis

If so, could you share more about what you're doing to get that error — what's the GraphQL query you're executing?

[Murthy10]

@benjie yes, I'm using the postgis plugin.

I've got a table that looks like following:

Column	Type
id	bigint
name	text
geom	geography(Point,4326)
tags	hstore

And I would like to perform a query like:

{
  allOsmPoints {
    nodes {
      geom {
        ... on 
      }
    }
  }
}

I'm not sure about the "postgis plugin syntax" and graphiql doesn't provide any suggestions.

[benjie]

If geom is coming through as a String (you should be able to see what type it is by hovering over it in GraphiQL), it seems that the postgis plugin has not loaded. How are you attempting to load it?

[Murthy10]

First I installed it from the npm regestry and afterwards executed postgraphile like the following:

postgraphile --connection postgres://<user>:<password>@<host>:<port>/<db> --schema public --watch --enhance-graphiql --append-plugins postgraphile-plugin-connection-filter,@graphile/postgis,@graphile-contrib/pg-order-by-related

I'm not sure about how to handle multiple plugins. Do I have to separate them by a comma or whitespace?

[benjie]

That looks correct. You should be able to query

{
  allOsmPoints {
    nodes {
      geom { geojson }
    }
  }
}

?

[Murthy10]

Unfortunately no success even if the only plugin I use is the @graphile/postgis. Other plugins like postgraphile-plugin-connection-filter and @graphile-contrib/pg-order-by-related work.

[benjie]

Are you able to create a small repo that reproduces the issue and share it with us? I wonder if it’s a search_path issue or something.

[Murthy10]

@benjie I'm astonished about your "lightning-fast" responses. Thank you very much. Probably the following repository (it's still wip, so might some things don't work like a charm) can help to reproduce the issue. https://github.com/Murthy10/DBaaS

[benjie]

I'm confused, sakila.sql doesn't seem to mention "postgis", "geometry" or "geography". I'm trying to determine where the "postgis" extension is installed into the database? (I didn't pull down the other dump, do I need to?)

[Murthy10]

You're right, only the eosm_ch database has geographies. I should have point that out in the last commit, but it's mentioned in the Readme of the repo. And yes the other dump is the spatial one.

[benjie]

I was confused because the README mentioned Sakila DB (Geospatial data) so I thought that postgis would be in that. Can you give a minimal reproduction?

[Murthy10]

You are right. I update the readme. I have to work on a small example for reproduction but that will take me some time (and I have to work on an other task right now). Thanks.

[sfkeller]

_Following infos in the meantime: Could it be docker making (searchpath) issues? (Then just FYI: the geospatial OSM data is coming from https://wiki.openstreetmap.org/wiki/Osm2pgsql tool).

[Murthy10]

So, last but not least I found time to prepare a little setup to reproduce the problem. https://github.com/Murthy10/littleSetup

[benjie]

Before I try spinning this up; what version of PostGraphile is your local docker running? I fear Docker may have cached an older version on your machine.

This plugin requires PostGraphile v4.4.0-beta.3 or higher to function correctly.

[Murthy10]

Yes, you are right.

/ # postgraphile --version
4.3.2

Thus, I will test it with v4.4.0-beta.3 or higher.

[Murthy10]

With PostGraphile v4.4.1-alpha.4 it works now. Thank you.

[benjie]

Excellent! I am relieved.