Snowflake - spectrum_spatial - Latest

Spectrum Spatial Guide
Product type
Software
Portfolio
Locate
Product family
Spectrum™ software
Product
Spectrum™ software > Spatial > Spectrum Spatial
Version
Latest
ft:locale
en-US
Product name
Spectrum Technology Platform
ft:title
Spectrum Spatial Guide
Copyright
2025
First publish date
2007
ft:lastEdition
2025-09-26
ft:lastPublication
2025-09-26T10:03:58.880000
Changes in this topic Description
Introduced in version 24.1 Snowflake Datasource

Snowflake is a relational database system that supports the storage of spatial objects. Spatial treats a Snowflake data source as a read-write data source.

Supported Data Types

The Snowflake data source provider follows the same rules as the JDBC data source provider when mapping a non-spatial JDBC data type to Spectrum Spatial’s type system.

The Snowflake geometry and geography data types are the spatial data equivalent to Spectrum Spatial's supported data types: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.

Determining the spatial metadata is done in several steps. The first step looks at the MAPINFO_MAPCATALOG table in the MAPINFO schema and if an entry exists identifies the geometry column in the Snowflake table and the style column. If the table is identified as being an X/Y table then the two columns identified as the X and Y columns will be “fused” together into a geometry column in the spatial table with the coordinate system specified in the MAPINFO_MAPCATALOG. If the MAPINFO_MAPCATALOG does not exists or the table to be opened does not have an entry then the GEOMETRY_COLUMNS table is consulted for the name of the spatial column and its coordinate system.

Upcasting and Downcasting Geometries

When inserting or updating geometries for Snowflake, upcasting and downcasting of geometries are supported.

For example:

  • Polygon geometries can be inserted into tables that are configured to accept geometries of a MultiPolygon type.
  • MultiPolygon geometries can be inserted into tables that are configured to accept geometries of a Polygon type, if the MultiPolygon contains a single Polygon.

Primary Key

Spectrum Spatial follows the same rules as the JDBC data source provider for determining the primary key.

Read-Only Columns

Spectrum Spatial follows the same rules as the JDBC data source provider in determining whether a column is read-only. It marks auto-increment columns as read-only as well.

MI SQL Optimizations

Snowflake supports the same functions and operators as the JDBC data source provider data source provider.

Snowflake supports the following spatial operators:

For more information, see Delegation to Data Source Providers.

Note: For best performance we recommend using Geography as a column type and enabling Snowflake's search optimization on that column. This will increase performance when using queries with spatial predicates such as intersects and contains. For more information, see Geospatial Queries.

Performance Considerations

  1. Metadata Query Optimization: Querying metadata in Snowflake can be costly. To optimize performance, consider the following recommendations:
    • Restrict Metadata Queries: Use context-specific filtering. For more information, see CONNECTION_CTX Parameter and Configure the JDBC driver.
    • Add entries to MAPINFO_MAPCATALOG: Ensure to include details such as geometry columns and NUMBER_ROWS.
    • Adjust Volatility Settings: Turn off Volatility if possible to enhance efficiency.
    • Use a Larger Warehouse Size: This can improve query performance.
  2. Geography Type Optimization: For more information, see Search Optimization Service.

Volatility

For Snowflake volatility is any change to the table's schema, such as adding or dropping a column or changing an index on a column. This includes a bounds change to the table via a direct database change. If it has changed, Spectrum Spatial will flush the metadata about the table from the cache and reload it before proceeding with the data access operation. For more information, see Data Source Volatility.

XY Table Support

Snowflake tables can be accessed as XY tables. See XY Table Support.

GeoIndexed Table Support

Named tables that are geoindexed (that is, they have a geohash column or attribute) are supported in Snowflake. For more information, see Adding a Geohash Index to a Snowflake Table in Support for Spatial Aggregation.