Geospatial Data Types¶
Snowflake offers native support for geospatial features such as points, lines, and polygons on the Earth’s surface.
In this Topic:
GEOGRAPHY Data Type¶
Snowflake provides the GEOGRAPHY data type, which models Earth as though it were a perfect sphere.
The GEOGRAPHY data type follows the WGS 84 standard (spatial reference ID 4326; for details, see https://spatialreference.org/ref/epsg/wgs-84/).
Points on the earth are represented as degrees of longitude (from -180 degrees to +180 degrees) and latitude (-90 to +90).
Altitude is currently not supported.
Line segments are interpreted as geodesic arcs on the Earth’s surface.
Snowflake also provides geospatial functions that operate on the GEOGRAPHY data type.
If you have geospatial data (e.g. longitude and latitude data, WKT, WKB, GeoJSON, etc.), you should convert and store this data in GEOGRAPHY columns, rather than keeping the data in their original formats in VARCHAR, VARIANT or NUMBER columns. Storing your data in GEOGRAPHY columns can significantly improve the performance of queries that use geospatial functionality.
Geospatial Input and Output Formats¶
The GEOGRAPHY data type’s input and output formats, as well as the geospatial function names and semantics, follow industry standards. The supported input and output formats are:
Well-Known Text (“WKT”)
Well-Known Binary (“WKB”)
Extended WKT and WKB (EWKT and EWKB) (see the note on EWKT and EWKB handling)
IETF GeoJSON (see the note on GeoJSON handling)
You might also find the following references helpful:
Open Geospatial Consortium’s Simple Feature Access / Common Architecture and SQL Option:
Any departure from these standards is noted explicitly in the Snowflake documentation.
Note on GeoJSON Handling¶
The WKT and WKB standards specify a format only; the semantics of WKT/WKB objects depend on the reference system - for example, a plane or a sphere.
The GeoJSON standard, on the other hand, specifies both a format and its semantics: GeoJSON points are explicitly WGS 84 coordinates, and GeoJSON line segments are supposed to be planar edges (straight lines).
Contrary to that, the Snowflake GEOGRAPHY data type interprets all line segments - including those input from or output to GeoJSON format - as geodesic arcs. In essence, Snowflake treats GeoJSON as JSON-formatted WKT with spherical semantics.
Note on EWKT and EWKB handling¶
EWKT and EWKB are non-standard formats introduced by PostGIS. They enhance the WKT and WKB formats by including a Spatial Reference System Identifier (SRID), which specifies the coordinate reference system to use with the data. Snowflake currently supports only WGS84, which maps to SRID=4326.
By default, Snowflake issues an error if an EWKB or EWKT input value contains an SRID other than 4326. Conversely, all EWKB and EWKT output values have SRID=4326.
Geospatial Object Types¶
The GEOGRAPHY data type can store the following types of geospatial objects:
WKT / WKB / EWKT / EWKB / GeoJSON geospatial objects:
Point
MultiPoint
LineString
MultiLineString
Polygon
MultiPolygon
GeometryCollection
These GeoJSON-specific geospatial objects:
Feature
FeatureCollection
Input and Output¶
This section explains geospatial data input and output in more detail.
DML Operations on GEOGRAPHY Columns¶
When a GEOGRAPHY column is the target of a DML operation (INSERT, COPY, UPDATE, MERGE, or CREATE TABLE AS…), the column’s source expression can be any of the following types:
GEOGRAPHY: An expression of type GEOGRAPHY is usually the result of a parsing function, a constructor function, or an existing GEOGRAPHY column. For a complete list of supported functions and categories of functions, see Geospatial Functions.
VARCHAR: interpreted as a WKT, WKB (in hex format), EWKT, EWKB (in hex format), or GeoJSON formatted string (see TO_GEOGRAPHY(VARCHAR)).
BINARY: interpreted as a WKB binary (see TO_GEOGRAPHY(BINARY)).
VARIANT: interpreted as a GeoJSON object (see TO_GEOGRAPHY(VARIANT)).
GEOGRAPHY_OUTPUT_FORMAT: GEOGRAPHY in result sets¶
The session parameter GEOGRAPHY_OUTPUT_FORMAT controls the rendering of GEOGRAPHY-typed columns in result sets.
The GEOGRAPHY_OUTPUT_FORMAT parameter can have one of the following values:
Parameter Value
Description
GeoJSON (default)
The GEOGRAPHY result is rendered as an OBJECT in GeoJSON format.
WKT
The GEOGRAPHY result is rendered as a VARCHAR in WKT format.
WKB
The GEOGRAPHY result is rendered as a BINARY in WKB format.
EWKT
The GEOGRAPHY result is rendered as a VARCHAR in EWKT format.
EWKB
The GEOGRAPHY result is rendered as a BINARY in EWKB format.
For EWKT and EWKB, the SRID is always 4326 in the output. See the note on EWKT and EWKB handling.
This parameter affects all clients, including the Snowflake UI and the SnowSQL command-line client, as well as the JDBC, ODBC, node.js, python, etc. drivers and connectors.
For example: the JDBC Driver returns the following metadata for a GEOGRAPHY-typed result column (column i
in this
example):
If
GEOGRAPHY_OUTPUT_FORMAT='GeoJSON'
:ResultSetMetaData.getColumnType(i)
returnsjava.sql.Types.VARCHAR
.ResultSetMetaData.getColumnClassName(i)
returns"java.lang.String"
.
If
GEOGRAPHY_OUTPUT_FORMAT='WKT'
or'EWKT'
:ResultSetMetaData.getColumnType(i)
returnsjava.sql.Types.VARCHAR
.ResultSetMetaData.getColumnClassName(i)
returns"java.lang.String"
.
If
GEOGRAPHY_OUTPUT_FORMAT='WKB'
or'EWKB'
:ResultSetMetaData.getColumnType(i)
returnsjava.sql.Types.BINARY
.ResultSetMetaData.getColumnClassName(i)
returns"[B"
(array of byte).
Note
APIs for retrieving database-specific type names (getColumnTypeName
in JDBC and the
SQL_DESC_TYPE_NAME
descriptor in ODBC) always return GEOGRAPHY
for the type name, regardless of the value of the
GEOGRAPHY_OUTPUT_FORMAT
parameter. For details, see:
Snowflake-specific Behavior in the JDBC Driver documentation.
Retrieving Results and Information About Results in the ODBC Driver documentation.
Support for Stages¶
Data from CSV or JSON / AVRO files in a stage can be loaded directly (i.e. without copy transforms) into a GEOGRAPHY column.
CSV: String values from the corresponding CSV column are parsed as GeoJSON, WKT, EWKT, WKB, or EWKB (see TO_GEOGRAPHY(VARCHAR)).
JSON / AVRO: The JSON values in the file are interpreted as GeoJSON (see TO_GEOGRAPHY(VARIANT)).
See also: Note on GeoJSON Handling.
Loading data from other file formats (Parquet, ORC, etc.) is possible through a COPY transform.
Support for JavaScript UDFs¶
JavaScript UDFs allow the GEOGRAPHY type as an argument and as a return value.
If a JavaScript UDF has an argument of type GEOGRAPHY, that argument will be visible as a JSON object in GeoJSON format inside the UDF body.
If a JavaScript UDF returns GEOGRAPHY, the UDF body is expected to return a JSON object in GeoJSON format.
For example, these two JavaScript UDFs are roughly equivalent to the built-in functions ST_X and ST_MAKEPOINT:
create or replace function my_st_x(G geography) returns real
language javascript
as
$$
if (G["type"] != "Point")
{
throw "Not a point"
}
return G["coordinates"][0]
$$;
create or replace function my_st_makepoint(LNG real, LAT real) returns geography
language javascript
as
$$
g = {}
g["type"] = "Point"
g["coordinates"] = [ LNG, LAT ]
return g
$$;
Examples¶
The code below shows sample input and output for the GEOGRAPHY data type. Note the following:
For the coordinates in WKT, EWKT, and GeoJSON, longitude appears before latitude (e.g.
POINT(lon lat)
).For the WKB and EWKB output, it is assumed that the BINARY_OUTPUT_FORMAT parameter is set to
HEX
(the default value for the parameter).
The example creates a table with a GEOGRAPHY column, inserts data in WKT format, and returns the data in different output formats.
create table geospatial_table (g GEOGRAPHY); insert into geospatial_table values ('POINT(-122.35 37.55)'), ('LINESTRING(-124.20 42.00, -120.01 41.99)');alter session set GEOGRAPHY_OUTPUT_FORMAT='GeoJSON';select g from geospatial_table; +------------------------+ | G | |------------------------| | { | | "coordinates": [ | | -122.35, | | 37.55 | | ], | | "type": "Point" | | } | | { | | "coordinates": [ | | [ | | -124.2, | | 42 | | ], | | [ | | -120.01, | | 41.99 | | ] | | ], | | "type": "LineString" | | } | +------------------------+alter session set GEOGRAPHY_OUTPUT_FORMAT='WKT';select g from geospatial_table; +-------------------------------------+ | G | |-------------------------------------| | POINT(-122.35 37.55) | | LINESTRING(-124.2 42,-120.01 41.99) | +-------------------------------------+alter session set GEOGRAPHY_OUTPUT_FORMAT='WKB';select g from geospatial_table; +------------------------------------------------------------------------------------+ | G | |------------------------------------------------------------------------------------| | 01010000006666666666965EC06666666666C64240 | | 010200000002000000CDCCCCCCCC0C5FC00000000000004540713D0AD7A3005EC01F85EB51B8FE4440 | +------------------------------------------------------------------------------------+alter session set GEOGRAPHY_OUTPUT_FORMAT='EWKT';select g from geospatial_table; +-----------------------------------------------+ | G | |-----------------------------------------------| | SRID=4326;POINT(-122.35 37.55) | | SRID=4326;LINESTRING(-124.2 42,-120.01 41.99) | +-----------------------------------------------+alter session set GEOGRAPHY_OUTPUT_FORMAT='EWKB';select g from geospatial_table; +--------------------------------------------------------------------------------------------+ | G | |--------------------------------------------------------------------------------------------| | 0101000020E61000006666666666965EC06666666666C64240 | | 0102000020E610000002000000CDCCCCCCCC0C5FC00000000000004540713D0AD7A3005EC01F85EB51B8FE4440 | +--------------------------------------------------------------------------------------------+