A Comparison of Spatial Functions: PostGIS, Athena, PrestoDB, BigQuery vs RedShift

A brief comparison of spatial functions supported by PostGIS, Athena, PrestoDB, BigQuery and Redshift

Photo by NASA on Unsplash

We are moving our geospatial analysis to the cloud. As we are evaluating the various offerings by AWS and Google Cloud Platform, we wonder to what extent these cloud database products support spatial functions in comparison to PostGIS.

Being the most widely used extended PostgreSQL object-relational database system, PostGIS allows spatial objects to be stored in the database and offers a wide range of functions for their analysis and processing.

AWS Athena, PrestoDB, Google BigQuery, and AWS Redshift are included in our considerations. Direct links to the respective documentation of currently supported spatial functions are listed in the References section at the end of this post.

Here is a summary of the comparison.

Geometry/Geography/Box Data Types

Table Management Functions

Geometry Constructors

Geometry Accessors

Geometry Editors

Geometry Validation

Spatial Reference System Functions

Geometry Input

Well-Known Text (WKT)

Well-Known Binary (WKB)

Other Formats

Geometry Output

Well-Known Text (WKT)

Well-Known Binary (WKB)

Other Formats


Bounding Box Operators

Distance Operators

Spatial Relationships

Topological Relationships

Distance Relationship

Measurement Functions

Geometry Processing

Affine Transformations

Clustering Functions

Bounding Box Functions

Linear Referencing

Trajectory Functions

SFCGAL Functions

Long Transaction Support


MS Big Tiles

Although AWS and Google Cloud Platform have started to support spatial functions in their product offerings, there are still quite some spatial functions missing as the time this post is written. Hopefully more of these currently missing functions will be available soon.

References: - [PostGIS Reference](https://postgis.net/docs/reference.html) - [AWS Athena Spatial Functions](https://docs.aws.amazon.com/athena/latest/ug/geospatial-functions-list.html) - [Presto DB Geospatial Functions](https://prestodb.io/docs/current/functions/geospatial.html) - [Google BigQuery Geography Functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions) - [AWS Redshift Spatial Functions](https://docs.aws.amazon.com/redshift/latest/dg/geospatial-functions.html)

Yoong Shin Chow
Yoong Shin Chow
Research Assistant