Better PostGIS support with Citus

[saicitus]

PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL.

Comes up very frequently in various prospect engagements. PostGIS currently works with Citus but just created this issue for re-evaluating/reviewing the specifics of what works and what doesn't, based on which coverage could be improved.

(The above is just a high-level summary of the issue, in future, this could be made elaborate with specific requirements).

[marcocitus]

The main unsupported features is the lack of custom aggregates in Citus (#120). Implementing this would allow us to provide distributed versions of the PostGIS aggregates, which are quite commonly used.

In general, the main shortcoming is that PostGIS applications generally require more advanced SQL support than what Citus offers. Since there is no natural sharding dimension (only 1 contiguous earth), router executor generally cannot be used.

[hackermd]

I just stumbled upon this limitation. Is there a workaround for implementing a Postgis aggregate function?

SELECT ST_ClusterWithin(geom, 20.0) FROM my_table;
ERROR:  unsupported aggregate function st_clusterwithin

I am using Citus 6.1 and Postgis 2.3.2.

[tsykes]

I also just ran into an aggregate problem with st_extent.
ERROR: unsupported aggregate function st_extent

It would be great if this worked.

[tsykes]

I have an implementation that I'm testing that's distributed by a geohash. I was hoping that some of the postgis functions would work but st_intersects against a reference table yields "unsupported clause type"

[mccarthyryanc]

@marcocitus or @tsykes, any updates on using PostGIS with Citus? For my particular use case I would only need basic PostGIS features: creating indexes, ST_Within, ST_Intersects, and/or ST_Contains. Is spatial indexing even necessary assuming sharding is done properly?

[houmamw]

It looks the issue still exists even in Citus 8.1 with PostGIS 2.4.3. PostGIS aggregate functions do not work with distributed queries.

For example, the following query does not work since the table parking is distributed:

SELECT vehicle_id, ST_Collect(location) AS circle
FROM parking
WHERE parking_time > '2019-11-11'
GROUP BY vehicle_id

The generated error is:

SQL Error [XX000]: ERROR: unsupported aggregate function st_collect

In the other hand, the query works fine as follows:

WITH L AS (
    SELECT vehicle_id, location 
    FROM parking
    WHERE parking_time > '2019-11-11'
)
SELECT vehicle_id, ST_Collect(location) AS circle
FROM L
GROUP BY vehicle_id

[hek23]

Was said by @DimCitus on Slack that this should be available early 2020 :'(

[serprex]

With Citus 9.1 we support push down of ST_Extent, ST_3DExtent, ST_MemCollect, & ST_MemUnion