PostGIS Geometry / Geography Filter Support

[mdconaway]

Summary

The postgres postgis extension introduces geometry and geography column types with large set of new query operators. Generally, these column types serialize and deserialize successfully into geojson as per my experimental branch here, where I was exploring the use of postgis in conjunction with pg_graphql. To truly make these column types useful with pg_graphql, however, the various query operators capable of filtering rows based on geometry or geography must be introduced into the available pg_graphql filter types.

Rationale

Without additional filter support for geometry and geography types, pg_graphql is only minimally useful for any database that needs to store geographic or geometric information.

Design

I believe one of two design approaches would suffice to introduce better filters for postgis.

1. Allow pg_graphql app developers to introduce their own filter types somehow, via some combination of plpgsql functions, postgres types, and / or postgres domains. This would solve the filtering problem for the postgis extension, and also allow app developers to introduce their own custom filters for column types like json and jsonb.
2. Add direct support for the postgis extension, and the included geography and geometry types to pg_graphql. This would allow other pg_graphql app developers to activate the postgis extension with minimal effort.

Examples

Postgis supports all of the following sql operators: https://postgis.net/docs/manual-1.5/ch08.html

For filtering, I have found the most common operators to be (from geoalchemy2):
INTERSECTS ("&&")
INTERSECTS_ND ("&&&")
OVERLAPS_OR_TO_LEFT ("&<")
OVERLAPS_OR_TO_RIGHT ("&>")
OVERLAPS_OR_BELOW ("&<|")
TO_LEFT ("<<")
BELOW ("<<|")
TO_RIGHT (">>")
CONTAINED ("@")
OVERLAPS_OR_ABOVE ("|&>")
ABOVE ("|>>")
CONTAINS ("~")
SAME ("~=")
DISTANCE_CENTROID ("<->")
DISTANCE_BOX ("<#>")

Where the item on either side of the comparator can be a geometry, geography, or text value that casts to geometry. (One side being the column of the table being filtered, and the other side being the value passed into graphql as the filter value)

Drawbacks

Unknown.

Alternatives

None available.

Unresolved Questions

TBD.

[olirice]

We would like to add first party support for PostGIS and pgvector as they're the most popular extensions on the platform.

pgvector will probably go first because its a lot smaller in scope and that should give us an idea of how best to implement PostGIS

Going to leave this open

[mdconaway]

Awesome, glad to hear that!

Probably more relevant for a separate issue, but what are the current plans (if any) for implementing jsonb filters? I could see some kind of abstraction that allows app developers to define their own filters as being useful there. jsonb has some cool query capabilities beyond what can be achieved with the flatter array type - like contained by.

(Also, thanks for all the hard work that went into making this super cool postgres extension!)

[mdconaway]

To help with any future development that may go into supporting PostGIS, I added these lines of SQL to enable sending geojson TO the server, and also force the server to always respond with geojson when a column type is set to geography.

-- json(geography) does all the implicit casting to RENDER geometry columns in Queries
create or replace function json(geography) returns json as $$
  select ($1::geometry)::json;
$$ language sql immutable;
create cast (geography AS json) with function json(geography) as implicit;

-- geography(json) WOULD implicitly convert incoming json values to geography types IF pg_graphql ever allows objects as input for JSON scalars
create or replace function geography(json) returns geography as $$
  select ST_GeomFromGeoJSON($1::json)::geography;
$$ language sql immutable;
create cast (json AS geography) with function geography(json) as implicit;

-- geography(text) currently converts incoming stringified json representations of a geography object during an incoming Mutation
create or replace function geography(text) returns geography as $$
  select ST_GeomFromGeoJSON($1::json)::geography;
$$ language sql immutable;
create cast (text AS geography) with function geography(text) as implicit;

[jmealo]

@olirice: I'm very interested in this. Is this on the official road map yet? I'm curious if it's beyond reasonable complexity for a first-time contributor.

[olirice]

It depends on how complex the type you want to implement is
So long as the type is representable using the existing base types it would be pretty straightforward

Here's an example of how citext was special cased so that it would be preresented as String instead of Opaque
https://github.com/supabase/pg_graphql/pull/418/files

you could imagine doing the same thing for pgvector's vector and/or halfvec types by representing them as [Float]

Types with nested data could be more complex