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
- Geospatial Input and Output Formats
  - Note on GeoJSON Handling
  - Note on EWKT and EWKB handling
- Geospatial Object Types
Input and Output
Examples

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.

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:
- https://www.opengeospatial.org/standards/sfa
- https://www.opengeospatial.org/standards/sfs

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: in the JDBC client, the metadata of a GEOGRAPHY-typed result column (say, column i) look similar to the following:

If GEOGRAPHY_OUTPUT_FORMAT=’GeoJSON’:
- ResultSetMetaData.getColumnTypeName(i) returns “OBJECT”.
- ResultSetMetaData.getColumnClassName(i) returns “java.lang.String”.
If GEOGRAPHY_OUTPUT_FORMAT=’WKT’ or ‘EWKT’:
- ResultSetMetaData.getColumnTypeName(i) returns “VARCHAR”.
- ResultSetMetaData.getColumnClassName(i) returns “java.lang.String”.
If GEOGRAPHY_OUTPUT_FORMAT=’WKB’ or ‘EWKB’:
- ResultSetMetaData.getColumnTypeName(i) returns “BINARY”.
- ResultSetMetaData.getColumnClassName(i) returns “[B” (array of byte).

Similar information is returned for other clients, such as the ODBC driver.

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. 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).

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 |
+--------------------------------------------------------------------------------------------+