Open mdconaway opened 2 weeks ago
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
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!)
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;
Summary
The postgres postgis extension introduces
geometry
andgeography
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 ongeometry
orgeography
must be introduced into the available pg_graphql filter types.Rationale
Without additional filter support for
geometry
andgeography
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.
json
andjsonb
.geography
andgeometry
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
, ortext
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.