Considerations when using spatial data with Amazon Redshift - Amazon Redshift

Considerations when using spatial data with Amazon Redshift

The following are considerations when using spatial data with Amazon Redshift:

  • The maximum size of a GEOMETRY or GEOGRAPHY object is 1,048,447 bytes.

  • Amazon Redshift Spectrum doesn't natively support spatial data. Therefore, you can't create or alter an external table with a GEOMETRY or GEOGRAPHY column.

  • Data types for Python user-defined functions (UDFs) don't support the GEOMETRY or GEOGRAPHY data type.

  • You can't use a GEOMETRY or GEOGRAPHY column as a sort key or a distribution key for an Amazon Redshift table.

  • You can't use GEOMETRY or GEOGRAPHY columns in SQL ORDER BY, GROUP BY, or DISTINCT clauses.

  • You can't use GEOMETRY or GEOGRAPHY columns in many SQL functions.

  • You can't perform an UNLOAD operation on GEOMETRY or GEOGRAPHY columns into every format. You can UNLOAD GEOMETRY or GEOGRAPHY columns to text or comma-separated value (CSV) files. Doing this writes GEOMETRY or GEOGRAPHY data in hexadecimal EWKB format. If the size of the EWKB data is more than 4 MB, then a warning occurs because the data can't later be loaded into a table.

  • The supported compression encoding of GEOMETRY or GEOGRAPHY data is RAW.

  • When using JDBC or ODBC drivers, use customized type mappings. In this case, the client application must have information on which parameters of a ResultSet object are GEOMETRY or GEOGRAPHY objects. The ResultSetMetadata operation returns type VARCHAR.

  • To copy geographic date from a SHAPEFILE, first ingest into a GEOMETRY column, and then cast the objects to GEOGRAPHY objects. .

The following nonspatial functions can accept an input of type GEOMETRY or GEOGRAPHY, or columns of type GEOMETRY or GEOGRAPHY:

  • The aggregate function COUNT

  • The conditional expressions COALESCE and NVL

  • CASE expressions

  • The default encoding for GEOMETRY and GEOGRAPHY is RAW. For more information, see Compression encodings.