In previous blogs, we’ve covered off what Coordinate Reference Systems (CRS) are, its scope and uses.
In this blog, we’ll cover how to set these on the Snowflake platform for geospatial referencing and analysis.
In Snowflake, you can define the Coordinate Reference System (CRS) by specifying a spatial reference identifier (SRID), which is a unique code that tells you which map or coordinate system you’re using, including its tolerance and resolution (or in other words, how precise and accurate it is).
TL;DR
If using the GEOGRAPHY data type in Snowflake, you won’t need to set the CRS, as it will be automatically set as WGS 84.
To set the CRS of a GEOMETRY data type, determine its SRID, then use the ST_SETSRID() function.
If not explicitly set, the SRID of a GEOMETRY column will be 0.
To convert from one CRS to another CRS on a GEOMETRY column, use the ST_TRANSFORM() function.
Overview
Coordinate Reference System
A Coordinate Reference System (CRS) defines how the two-dimensional, projected map in your GIS relates to real places on the earth. It encompasses:
- Datum: Defines the position of the spheroid relative to the centre of the earth.
- Projection: Converts the 3D surface of the earth to a 2D map.
- Coordinate system: Defines how the coordinates relate to positions in the real world.
Spatial Reference System Identifier (SRID)
An SRID is a unique identifier associated with a CRS. It is a numeric value that references a specific CRS definition in a spatial database or standard, like the EPSG (European Petroleum Survey Group) codes.
Key Points
CRS is the comprehensive system that includes all the information needed to translate between coordinate systems and real-world positions.
SRID is an identifier for a specific CRS.
Example:
EPSG:4326 is a common SRID, where 4326 is the SRID that corresponds to the WGS 84 CRS (used by GPS).
What are the benefits of setting SRID in Snowflake?
Setting up an SRID in Snowflake ensures data consistency by aligning all spatial data to the same coordinate system, enhancing accuracy with precise tolerance and resolution information.
It facilitates interoperability between systems, enables advanced geospatial analysis and maintains data integrity by providing a defined coordinate framework.
This allows users to perform complex spatial queries efficiently in Snowflake.
How to set CRS and SRID in Snowflake
Snowflake provides the following data types for geospatial data:
- The GEOGRAPHY data type, which models Earth as though it were a perfect sphere.
- The GEOMETRY data type, which represents features in a planar (Euclidean, Cartesian) coordinate system.
The GEOGRAPHY data type follows the WGS 84 standard (spatial reference ID 4326).
The GEOMETRY data type represents features in a planar (Euclidean, Cartesian) coordinate system.
The coordinates are represented as pairs of real numbers (x, y). Currently, only 2D coordinates are supported.
The units of the X and Y are determined by the spatial reference system (SRS) associated with the GEOMETRY object. The spatial reference system is identified by the SRID number.
Unless the SRID is provided when creating the GEOMETRY object or by calling ST_SETSRID, the SRID is 0.
ST_SETSRID()
Returns a GEOMETRY object that has its SRID set to the specified value.
Use this function to change the SRID without affecting the coordinates of the object. If you also need to change the coordinates to match the new SRS, use ST_TRANSFORM instead.
ST_TRANSFORM()
Converts a GEOMETRY object from one spatial reference system SRS to another.
Use this function to change the SRID and the coordinates of the object to match the new SRS (spatial reference system).
If you just need to change the SRID without changing the coordinates (e.g. if the SRID was incorrect), use ST_SETSRID instead.
Syntax
ST_SETSRID( <geometry_expression> , <srid> )
Examples
The following example creates and returns a GEOMETRY object that uses the SRID 4326:
ALTER SESSION SET GEOMETRY_OUTPUT_FORMAT='EWKT';
SELECT ST_SETSRID(TO_GEOMETRY('POINT(13 51)'), 4326);
Syntax
ST_TRANSFORM( <geometry_expression> [ , <from_srid> ] , <to_srid> );
Examples
The following example transforms a POINT GEOMETRY object from EPSG:32633 (WGS 84 / UTM zone 33N) to EPSG:3857 (Web Mercator).
-- Set the output format to EWKT
ALTER SESSION SET GEOMETRY_OUTPUT_FORMAT='EWKT';
SELECT
ST_TRANSFORM(
ST_GEOMFROMWKT('POINT(389866.35 5819003.03)', 32633),
3857
) AS transformed_geom;
After setting the SRID on a GEOMETRY object, you can check if it has been applied correctly using the ST_SRID() function.
ST_SRID()
Returns the SRID (spatial reference system identifier) of a GEOGRAPHY or GEOMETRY object.
Currently, for any value of the GEOGRAPHY type, only SRID 4326 is supported and is returned.
Syntax
ST_SRID( <geography_or_geometry_expression> )
Examples
This shows a simple use of the ST_SRID function:
SELECT ST_SRID(ST_MAKEPOINT(37.5, 45.5));
+-----------------------------------+
| ST_SRID(ST_MAKEPOINT(37.5, 45.5)) |
|-----------------------------------|
| 4326 |
+———————————–+
Subscribe to our newsletter
Subscribe to receive the latest blogs and data listings direct to your inbox.