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

.

Related Topics

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:

You might also find the following references helpful:

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) returns java.sql.Types.VARCHAR.

    • ResultSetMetaData.getColumnClassName(i) returns "java.lang.String".

  • If GEOGRAPHY_OUTPUT_FORMAT='WKT' or 'EWKT':

    • ResultSetMetaData.getColumnType(i) returns java.sql.Types.VARCHAR.

    • ResultSetMetaData.getColumnClassName(i) returns "java.lang.String".

  • If GEOGRAPHY_OUTPUT_FORMAT='WKB' or 'EWKB':

    • ResultSetMetaData.getColumnType(i) returns java.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:

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.

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