GEOGRAPHY

Data Type and usages

Description

A collection of points, linestrings, and polygons, which is represented as a point set, or a subset of the surface of the Earth. For more information please refer to BigQuery GEOGRAPHY data type.

Supported data type in Snowflake.

Sample Source Patterns

GEOGRAPHY output format

The default output format for geography data types in BigQuery is WKT (Well-Known Text) and in Snowflake WKB (Well-Known Binary). For this reason, when geography columns are selected, the ST_ASWKT function is automatically added. In addition, when all the columns of a table are selected and it contains a Geography column, the GEOGRAPHY_OUTPUT_FORMAT is set to WKT. This is in order to keep the default BigQuery format.

BigQuery

Query

CREATE OR REPLACE TABLE test.geographyType
(
  COL1 GEOGRAPHY
);

INSERT INTO test.geographyType VALUES
    (ST_GEOGFROMTEXT('POINT(-122.35 37.55)')), (ST_GEOGFROMTEXT('LINESTRING(-124.20 42.00, -120.01 41.99)'));

SELECT COL1 FROM test.geographyType;
SELECT * FROM test.geographyType;	

Output

POINT(-122.35 37.55)	
LINESTRING(-124.2 42, -120.01 41.99)

POINT(-122.35 37.55)	
LINESTRING(-124.2 42, -120.01 41.99)

Snowflake

Query

CREATE OR REPLACE TABLE test.geographyType
(
  COL1 GEOGRAPHY
);

INSERT INTO test.geographyType
VALUES
    (
     --** MSC-WARNING - MSC-BQ0010 - THE FUNCTION 'ST_GEOGFROMTEXT' IS NOT REQUIRED IN SNOWFLAKE. **
     'POINT(-122.35 37.55)'), (
     --** MSC-WARNING - MSC-BQ0010 - THE FUNCTION 'ST_GEOGFROMTEXT' IS NOT REQUIRED IN SNOWFLAKE. **
     'LINESTRING(-124.20 42.00, -120.01 41.99)');
     
SELECT ST_ASWKT( COL1) FROM test.geographyType;
            
ALTER SESSION SET GEOGRAPHY_OUTPUT_FORMAT = 'WKT';
SELECT * FROM test.geographyType;

Output

POINT(-122.35 37.55)
LINESTRING(-124.2 42,-120.01 41.99)

POINT(-122.35 37.55)
LINESTRING(-124.2 42,-120.01 41.99)

In case it is not added automatically and you want to see the data in WKT format, you can use the ST_ASWKT function or set the GEOGRAPHY_OUTPUT_FORMAT format.

Insert GEOGRAPHY data

To insert data in geography type columns, no function is needed, because Snowflake automatically detects that the data follows the WGS 84 standard.

Sample Source

BigQuery

CREATE OR REPLACE TABLE test.geographyType
(
  COL1 GEOGRAPHY
);

INSERT INTO test.geographyType VALUES
    (ST_GEOGFROMTEXT('POINT(-122.35 37.55)')), 
    (ST_GEOGFROMTEXT('LINESTRING(-124.20 42.00, -120.01 41.99)'));

Snowflake

CREATE OR REPLACE TABLE test.geographyType
(
  COL1 GEOGRAPHY
);

INSERT INTO test.geographyType
VALUES
    (
     --** MSC-WARNING - MSC-BQ0010 - THE FUNCTION 'ST_GEOGFROMTEXT' IS NOT REQUIRED IN SNOWFLAKE. **
     'POINT(-122.35 37.55)'), (
     --** MSC-WARNING - MSC-BQ0010 - THE FUNCTION 'ST_GEOGFROMTEXT' IS NOT REQUIRED IN SNOWFLAKE. **
     'LINESTRING(-124.20 42.00, -120.01 41.99)');

Observations

• Please keep in mind that the default output format for geography data types is WKT (Well-Known Text) and in Snowflake WKB (Well-Known Binary). You can use the ST_ASWKT function or set the GEOGRAPHY_OUTPUT_FORMAT format if you want to view the data in WKT format.

• Geography functions used to insert data into a values clause are not needed in Snowflake. Please refer to Geography Functions documentation to learn more about this.

Related EWI's

• MSC-BQ0010: Geography function is not required in Snowflake.