Geospatial Data Types¶

GEOGRAPHY Data Type¶

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.

GEOMETRY Data Type¶

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 spatial reference system identifier (SRID) number. Unless the SRID is provided when creating the GEOMETRY object or by calling ST_SETSRID, the SRID is 0.

Snowflake provides a set of geospatial functions that operate on the GEOMETRY data type. For these functions:

• All functions assume planar coordinates, even if the geometry uses a non-planar SRS.

• The measurement functions (e.g. ST_LENGTH) use the same units as the coordinate system.

• For functions that accept multiple GEOMETRY expressions as arguments (e.g. ST_DISTANCE), the input expressions must be defined in the same SRS.

Supported Standard Input and Output Formats¶

The GEOGRAPHY and GEOMETRY data types support the following standard industry formats for input and output:

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

Supported Geospatial Object Types¶

The GEOGRAPHY and GEOMETRY data types 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

Specifying the Output Format for Result Sets¶

The session parameters GEOGRAPHY_OUTPUT_FORMAT and GEOMETRY_OUTPUT_FORMAT control the rendering of GEOGRAPHY- and GEOMETRY-typed columns in result sets (respectively).

The GEOGRAPHY_OUTPUT_FORMAT and GEOMETRY parameters can have one of the following values:

Parameter Value	Description
GeoJSON (default)	The GEOGRAPHY / GEOMETRY result is rendered as an OBJECT in GeoJSON format.
WKT	The GEOGRAPHY / GEOMETRY result is rendered as a VARCHAR in WKT format.
WKB	The GEOGRAPHY / GEOMETRY result is rendered as a BINARY in WKB format.
EWKT	The GEOGRAPHY / GEOMETRY result is rendered as a VARCHAR in EWKT format.
EWKB	The GEOGRAPHY / GEOMETRY 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' or GEOMETRY_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', or if: GEOMETRY_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', or if GEOMETRY_OUTPUT_FORMAT='WKB' or 'EWKB':

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

  • ResultSetMetaData.getColumnClassName(i) returns "[B" (array of byte).

Examples of Inserting and Querying GEOGRAPHY Data¶

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 (id INTEGER, g GEOGRAPHY);
insert into geospatial_table values
    (1, 'POINT(-122.35 37.55)'), (2, 'LINESTRING(-124.20 42.00, -120.01 41.99)');

Copy

alter session set GEOGRAPHY_OUTPUT_FORMAT='GeoJSON';

Copy

select g
    from geospatial_table
    order by id;
+------------------------+
| G                      |
|------------------------|
| {                      |
|   "coordinates": [     |
|     -122.35,           |
|     37.55              |
|   ],                   |
|   "type": "Point"      |
| }                      |
| {                      |
|   "coordinates": [     |
|     [                  |
|       -124.2,          |
|       42               |
|     ],                 |
|     [                  |
|       -120.01,         |
|       41.99            |
|     ]                  |
|   ],                   |
|   "type": "LineString" |
| }                      |
+------------------------+

Copy

alter session set GEOGRAPHY_OUTPUT_FORMAT='WKT';

Copy

select g
    from geospatial_table
    order by id;
+-------------------------------------+
| G                                   |
|-------------------------------------|
| POINT(-122.35 37.55)                |
| LINESTRING(-124.2 42,-120.01 41.99) |
+-------------------------------------+

Copy

alter session set GEOGRAPHY_OUTPUT_FORMAT='WKB';

Copy

select g
    from geospatial_table
    order by id;
+------------------------------------------------------------------------------------+
| G                                                                                  |
|------------------------------------------------------------------------------------|
| 01010000006666666666965EC06666666666C64240                                         |
| 010200000002000000CDCCCCCCCC0C5FC00000000000004540713D0AD7A3005EC01F85EB51B8FE4440 |
+------------------------------------------------------------------------------------+

Copy

alter session set GEOGRAPHY_OUTPUT_FORMAT='EWKT';

Copy

select g
    from geospatial_table
    order by id;
+-----------------------------------------------+
| G                                             |
|-----------------------------------------------|
| SRID=4326;POINT(-122.35 37.55)                |
| SRID=4326;LINESTRING(-124.2 42,-120.01 41.99) |
+-----------------------------------------------+

Copy

alter session set GEOGRAPHY_OUTPUT_FORMAT='EWKB';

Copy

select g
    from geospatial_table
    order by id;
+--------------------------------------------------------------------------------------------+
| G                                                                                          |
|--------------------------------------------------------------------------------------------|
| 0101000020E61000006666666666965EC06666666666C64240                                         |
| 0102000020E610000002000000CDCCCCCCCC0C5FC00000000000004540713D0AD7A3005EC01F85EB51B8FE4440 |
+--------------------------------------------------------------------------------------------+

Copy

Understanding the Effects of Using Different SRIDs with GEOMETRY¶

In a GEOMETRY column, you can insert objects that have different SRIDs. If the column contains more than one SRID, some of the important performance optimizations are not applied. This can result in slower queries, in particular when joining on a geospatial predicate.

Performing DML Operations on GEOGRAPHY and GEOMETRY Columns¶

When a GEOGRAPHY or GEOMETRY 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 or GEOMETRY : An expression of type GEOGRAPHY or GEOMETRY is usually the result of a parsing function, a constructor function, or an existing GEOGRAPHY or GEOMETRY column. For a complete list of supported functions and categories of functions, see Geodatenfunktionen.

• 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) and

• TO_GEOMETRY(BINARY)).

• VARIANT: interpreted as a GeoJSON object (see TO_GEOGRAPHY(VARIANT) and TO_GEOMETRY(VARIANT)).

Loading Geospatial Data from 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 for GEOGRAPHY Values.

Loading data from other file formats (Parquet, ORC, etc.) is possible through a COPY transform.

Using Geospatial Data with Java UDFs¶

Java UDFs allow the GEOGRAPHY type as an argument and as a return value. See Zuordnung von Datentypen zwischen SQL und Java and Übergeben eines GEOGRAPHY-Werts an eine Inline-Java-UDF for details.

Using Geospatial Data with 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
$$;

Understanding the Differences Between GEOGRAPHY and GEOMETRY¶

Although both the GEOGRAPHY and GEOMETRY data types define geospatial features, the types use different models. The following table summarizes the differences.

Examples Comparing the GEOGRAPHY and GEOMETRY Data Types¶

The following examples compare the output of the geospatial functions when using the GEOGRAPHY and GEOMETRY data types as input.

SELECT ST_DISTANCE(
         ST_POINT(13.4814, 52.5015),
         ST_POINT(-121.8212, 36.8252))
       AS distance_in_meters;

+--------------------+
| DISTANCE_IN_METERS |
|--------------------|
|   9182410.99227821 |
+--------------------+

SELECT ST_DISTANCE(
         ST_GEOM_POINT(13.4814, 52.5015),
         ST_GEOM_POINT(-121.8212, 36.8252))
       AS distance_in_degrees;

+---------------------+
| DISTANCE_IN_DEGREES |
|---------------------|
|       136.207708844 |
+---------------------+

SELECT ST_AREA(border) AS area_in_sq_meters
  FROM world_countries
  WHERE name = 'Germany';

+-------------------+
| AREA_IN_SQ_METERS |
|-------------------|
|  356379183635.591 |
+-------------------+

SELECT ST_AREA(border) as area_in_sq_degrees
  FROM world_countries_geom
  WHERE name = 'Germany';

+--------------------+
| AREA_IN_SQ_DEGREES |
|--------------------|
|       45.930026848 |
+--------------------+

Example 3: Querying the Names of Countries Overlapping the Line from Berlin to San Francisco¶

The following table compares the output of ST_INTERSECTS for GEOGRAPHY types and GEOMETRY types:

ST_INTERSECTS Using . GEOGRAPHY Input	ST_INTERSECTS Using . GEOMETRY Input
SELECT name FROM world_countries WHERE ST_INTERSECTS(border, TO_GEOGRAPHY( 'LINESTRING(13.4814 52.5015, -121.8212 36.8252)' )); Copy +--------------------------+ | NAME | |--------------------------| | Germany | | Denmark | | Iceland | | Greenland | | Canada | | United States of America | +--------------------------+ Copy	SELECT name FROM world_countries_geom WHERE ST_INTERSECTS(border, TO_GEOMETRY( 'LINESTRING(13.4814 52.5015, -121.8212 36.8252)' )); Copy +--------------------------+ | NAME | |--------------------------| | Germany | | Belgium | | Netherlands | | United Kingdom | | United States of America | +--------------------------+ Copy

Understanding the Differences in Input Data Validation¶

To create a GEOMETRY or GEOGRAPHY object for an input shape, you must use a shape that is well-formed and valid, according to the OGC rules for Simple Features. The next sections explain how the validity of input data differs between GEOMETRY and GEOGRAPHY.

Understanding the Effects of Invalid Shapes on Geospatial Functions¶

Different geospatial functions have different effects when you pass in a GEOGRAPHY or GEOMETRY object for an invalid shape.

Working with Invalid Shapes¶

The next sections explain how to allow functions to create invalid shapes and how to determine if a GEOGRAPHY or GEOMETRY object represents an invalid or repaired shape.

TO_GEOGRAPHY( <input> [, <allowInvalid> ] )

ST_GEOGFROMWKB( <input> [, <allowInvalid> ] )

ST_GEOGFROMWKT( <input> [, <allowInvalid> ] )

TO_GEOMETRY( <input> [, <allowInvalid> ] )

ST_GEOMFROMWKB( <input> [, <allowInvalid> ] )

ST_GEOMFROMWKT( <input> [, <allowInvalid> ] )

SELECT TO_GEOMETRY('LINESTRING(100 102,100 102)', TRUE);

SELECT TO_GEOMETRY('LINESTRING(100 102,100 102)', TRUE) AS g, ST_ISVALID(g);