地理空间数据类型¶

GEOGRAPHY 数据类型¶

GEOGRAPHY 数据类型遵循 WGS 84 标准（空间参考 ID 4326；有关详细信息，请参阅 https://spatialreference.org/ref/epsg/wgs-84/ (https://spatialreference.org/ref/epsg/wgs-84/)）。

地球上的点表示为经度（从 -180 度到 +180 度）和纬度（从 -90 到 +90）。Snowflake 使用 14 位小数来存储 GEOGRAPHY 坐标。当数据包含的小数位数超过此限制时，坐标将被四舍五入，以确保符合指定的长度约束条件。

目前不支持海拔高度。

线段解释为地球表面的测地线弧。

Snowflake 还提供 地理空间函数，对 GEOGRAPHY 数据类型执行操作。

如果您有地理空间数据（例如经纬度数据、WKT、WKB、GeoJSON 等），您应该在 GEOGRAPHY 列中转换和存储此类数据，而不是在 VARCHAR、NUMBER 或 VARIANT 列中以原始格式存储数据。在 GEOGRAPHY 列中存储数据可以显著提高使用地理空间功能的查询的性能。

GEOMETRY 数据类型¶

GEOMETRY 数据类型表示平面（欧几里得、笛卡尔）坐标系中的特征。

坐标表示为实数对 (x, y)。目前，仅支持 2D 坐标。

X 和 Y 的单位由与 GEOMETRY 对象关联的 空间参考系 (SRS) (link removed) 确定。空间参考系由 空间参考系标识符 (SRID) (link removed) 编号标识。除非在创建 GEOMETRY 对象时或通过调用 ST_SETSRID 提供 SRID，否则 SRID 为 0。

Snowflake 使用 14 位小数来存储 GEOMETRY 坐标。当数据包含的小数位数超过此限制时，坐标将被四舍五入，以确保符合指定的长度约束条件。

Snowflake 提供一组 地理空间函数，对 GEOMETRY 数据类型 执行操作。对于这些函数：

• 所有函数都假定平面坐标，即使几何图形使用非平面 SRS。

• 测量函数（例如 ST_LENGTH）使用与坐标系相同的单位。

• 对于接受多个 GEOMETRY 表达式作为实参的函数（例如 ST_DISTANCE），输入表达式必须在相同的 SRS 中定义。

支持的标准输入和输出格式¶

GEOGRAPHY 和 GEOMETRY 数据类型支持以下标准输入和输出行业格式：

• 知名文本 (link removed) （“WKT”）

• 知名二进制 (link removed) （“WKB”）

• `扩展 WKT 和 WKB （EWKT 和 EWKB）<https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry#Format_variations>`_ （请参阅 :ref:` EWKT 和 EWKB 处理说明 <label-a_note_on_ewkt_ewkb_handling>`）

• IETF GeoJSON (https://tools.ietf.org/html/rfc7946) （请参阅 :ref:` GeoJSON 处理说明 <label-a_note_on_geojson_handling>`）

您可能还会发现以下参考很有帮助：

• Open Geospatial Consortium 的简单要素访问规范/通用架构和 SQL 选项：

  • https://www.opengeospatial.org/standards/sfa (https://www.opengeospatial.org/standards/sfa)

  • https://www.opengeospatial.org/standards/sfs (https://www.opengeospatial.org/standards/sfs)

Snowflake 文档中明确指出了所有偏离这些标准的情况。

支持的地理空间对象类型¶

GEOGRAPHY 和 GEOMETRY 数据类型可以存储以下类型的地理空间对象：

• WKT / WKB / EWKT / EWKB / GeoJSON 地理空间对象：

  • 点

  • MultiPoint

  • LineString

  • MultiLineString

  • 多边形

  • MultiPolygon

  • GeometryCollection

• 这些 GeoJSON 特定的地理空间对象：

  • 功能

  • FeatureCollection

指定结果集的输出格式¶

会话参数 GEOGRAPHY_OUTPUT_FORMAT 和 GEOMETRY_OUTPUT_FORMAT （分别）控制结果集中 GEOGRAPHY 类型和 GEOMETRY 类型列的呈现。

GEOGRAPHY_OUTPUT_FORMAT 和 GEOMETRY 参数可以具有下列任一值：

参数值	描述
GeoJSON （默认）	GEOGRAPHY / GEOMETRY 结果以 GeoJSON 格式呈现为 OBJECT。
WKT	GEOGRAPHY / GEOMETRY 结果以 WKT 格式呈现为 VARCHAR。
WKB	GEOGRAPHY / GEOMETRY 结果以 WKB 格式呈现为 BINARY。
EWKT	GEOGRAPHY / GEOMETRY 结果以 EWKT 格式呈现为 VARCHAR。
EWKB	GEOGRAPHY / GEOMETRY 结果以 EWKB 格式呈现为 BINARY。

对于 EWKT 和 EWKB，SRID 在输出中始终为 4326。请参阅 :ref:` EWKT 和 EWKB 处理备注 <label-a_note_on_ewkt_ewkb_handling>`。

此参数影响所有客户端（包括 Snowflake UI 和 SnowSQL 命令行客户端），以及 JDBC、ODBC、node.js、python 等驱动程序和连接器。

例如，JDBC 驱动程序为 GEOGRAPHY 类型的结果列（本示例中为列 i）返回以下元数据：

• 如果 GEOGRAPHY_OUTPUT_FORMAT='GeoJSON' 或 GEOMETRY_OUTPUT_FORMAT='GeoJSON'：

  • ResultSetMetaData.getColumnType(i) 返回 java.sql.Types.VARCHAR。

  • ResultSetMetaData.getColumnClassName(i) 返回 "java.lang.String"。

• 如果 GEOGRAPHY_OUTPUT_FORMAT='WKT' 或 'EWKT'，或者如果 GEOMETRY_OUTPUT_FORMAT='WKT' 或 'EWKT'：

  • ResultSetMetaData.getColumnType(i) 返回 java.sql.Types.VARCHAR。

  • ResultSetMetaData.getColumnClassName(i) 返回 "java.lang.String"。

• 如果 GEOGRAPHY_OUTPUT_FORMAT='WKB' 或 'EWKB'，或者如果 GEOMETRY_OUTPUT_FORMAT='WKB' 或 'EWKB'：

  • ResultSetMetaData.getColumnType(i) 返回 java.sql.Types.BINARY。

  • ResultSetMetaData.getColumnClassName(i) 返回 "[B" （字节数组）。

插入和查询 GEOGRAPHY 数据的示例¶

下面的代码显示了 GEOGRAPHY 数据类型的示例输入和输出。请注意以下事项：

• 对于 WKT、EWKT 和 GeoJSON 中的坐标，经度在纬度之前显示（例如 POINT(lon lat)）。

• 对于 WKB 和 EWKB 输出，假设 BINARY_OUTPUT_FORMAT 参数设置为 HEX （参数的默认值）。

该示例创建包含 GEOGRAPHY 列的表，以 WKT 格式插入数据，并以不同的输出格式返回数据。

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

了解为 GEOMETRY 使用不同 SRIDs 的效果¶

在 GEOMETRY 列中，您可以插入具有不同 SRIDs (link removed) 的对象。如果列包含多个 SRID，则不会应用某些重要的性能优化。这就可能导致查询速度变慢，尤其是基于地理空间谓词联接时。

更改 GEOMETRY 对象的空间参考系 (SRS) 和 SRID¶

要更改现有 GEOMETRY 对象的 SRS (link removed) 和 SRID (link removed)，请调用 ST_TRANSFORM 函数，传入新的 SRID。该函数返回具有新 SRID 的新 GEOMETRY 对象，坐标转换为使用 SRS。例如，要为 geometry_expression 返回 GEOMETRY 对象，且该对象使用 SRID 32633 的 SRS，请执行以下语句：

SELECT ST_TRANSFORM(geometry_expression, 32633);

Copy

如果在现有 GEOMETRY 对象中未正确设置原始 SRID，请将原始 SRID 指定为附加实参。例如，如果 geometry_expression 是使用 SRID 4326 的 GEOMETRY 对象，并且您希望将其转换为使用 SRID 28992，请执行以下语句：

SELECT ST_TRANSFORM(geometry_expression, 4326, 28992);

Copy

请注意，如果 GEOMETRY 对象为 SRS 使用正确的坐标，但具有错误的 SRID，您可以通过调用 ST_SETSRID 函数来修正 SRID。例如，以下语句将 geometry_expression 的 SRID 设置为 4326，同时保持坐标不变：

SELECT ST_SETSRID(geometry_expression, 4326);

Copy

对 GEOGRAPHY 列和 GEOMETRY 列执行 DML 操作¶

当 GEOGRAPHY 或 GEOMETRY 列是 DML 操作（INSERT、COPY、UPDATE、MERGE 或 CREATE TABLE AS...）的目标时，该列的源表达式可以是以下任一类型：

• GEOGRAPHY 或 GEOMETRY：类型 GEOGRAPHY 或 GEOMETRY 的表达式通常是解析函数、构造函数或现有 GEOGRAPHY 或 GEOMETRY 列的结果。有关支持的函数和函数类别的完整列表，请参阅 地理空间函数。

• VARCHAR：解析为 WKT、WKB （十六进制格式）、EWKT、EWKB （十六进制格式）或 GeoJSON 格式化字符串（请参阅 TO_GEOGRAPHY(VARCHAR)）。

• BINARY：解析为 WKB 二进制（请参阅 TO_GEOGRAPHY(BINARY) 和

• TO_GEOMETRY(BINARY)）。

• VARIANT：解析为 GeoJSON 对象（请参阅 TO_GEOGRAPHY(VARIANT) 和 TO_GEOMETRY(VARIANT)）。

从暂存区加载地理空间数据¶

来自暂存区中 CSV 或 JSON / AVRO 文件的数据可以直接加载（没有复制转换）到 GEOGRAPHY 列。

• CSV：来自相应 CSV 列的字符串值解析为 GeoJSON、WKT、EWKT、WKB 或 EWKB （请参阅 TO_GEOGRAPHY(VARCHAR)）。

• JSON / AVRO：文件中的 JSON 值解析为 GeoJSON （请参阅 TO_GEOGRAPHY(VARIANT)）。

  另请参阅：针对 GEOGRAPHY 值的 GeoJSON 处理说明。

通过 COPY 转换，可以从其他文件格式（Parquet、ORC 等）加载数据。

将地理空间数据与 Java UDFs 结合使用¶

Java UDFs 允许将 GEOGRAPHY 类型作为实参和返回值。有关详细信息，请参阅 SQL-Java 数据类型映射 和 将 GEOGRAPHY 值传递给内联 Java UDF。

将地理空间数据与 JavaScript UDFs 结合使用¶

JavaScript UDFs 允许将 GEOGRAPHY 或 GEOMETRY 类型作为实参和返回值。

如果 JavaScript UDF 具有类型 GEOGRAPHY 或 GEOMETRY 的实参，该实参将在 UDF 主体内以 GeoJSON 格式显示为 JSON 对象。

如果 JavaScript UDF 返回 GEOGRAPHY 或 GEOMETRY，UDF 主体预计会以 GeoJSON 格式返回 JSON 对象。

例如，以下两种 JavaScript UDFs 大致等同于内置函数 ST_X 和 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
$$;

将地理空间数据与 Python UDFs 结合使用¶

Python UDFs 允许将 GEOGRAPHY 和 GEOMETRY 类型作为实参和返回值。

如果 Python UDF 有一个 GEOGRAPHY 或 GEOMETRY 类型的实参，该实参将被表示为 GeoJSON 对象，并在 UDF 主体中被转换为一个 Python dict 对象。

如果 Python UDF 返回 GEOGRAPHY 或 GEOMETRY，则 UDF 主体将返回一个符合 GeoJSON 结构的 Python dict 对象。

例如，该 Python UDF 返回构成复合 GEOGRAPHY 类型的不同几何图形的数量：

CREATE OR REPLACE FUNCTION py_numgeographys(geo GEOGRAPHY)
RETURNS INTEGER
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
PACKAGES = ('shapely')
HANDLER = 'udf'
AS $$
from shapely.geometry import shape, mapping
def udf(geo):
    if geo['type'] not in ('MultiPoint', 'MultiLineString', 'MultiPolygon', 'GeometryCollection'):
        raise ValueError('Must be a composite geometry type')
    else:
        g1 = shape(geo)
        return len(g1.geoms)
$$;

请查看 Snowflake Labs (https://github.com/Snowflake-Labs/sf-samples/tree/main/samples/geospatial/Python%20UDFs)，了解更多 Python UDFs 示例。其中一些示例可实现复杂的空间操作或简化数据引入。例如，该 UDF (https://github.com/Snowflake-Labs/sf-samples/blob/main/samples/geospatial/Python%20UDFs/PY_LOAD_GEOFILES.sql) 允许读取原生不支持的格式，如 Shapefiles (.SHP)、TAB、KML、GPKG 以及其他格式。

将 GEOGRAPHY 对象与 H3 结合使用¶

H3 (https://h3geo.org/docs/) 是一种 分层地理空间索引 (https://h3geo.org/docs/highlights/indexing)，它将世界划分为 离散的全局网格系统 (link removed) 中的六边形单元格。

Snowflake 提供 SQL 函数，使您能够将 H3 与 GEOGRAPHY 对象结合使用。您可以使用这些函数执行以下操作：

• 获取表示点的 GEOGRAPHY 对象的 H3 单元格 ID （索引 (https://h3geo.org/docs/core-library/h3Indexing)），反之亦然。

• 获取覆盖 GEOGRAPHY 对象的最小 H3 单元格集的 IDs。

• 获取在表示多边形的 GEOGRAPHY 对象中具有质心的 H3 单元格的 IDs。

• 获取表示 H3 单元格边界的 GEOGRAPHY 对象。

• 获取给定 H3 单元格的父级和子级。

• 获取 H3 单元格质心的经度和纬度（反之亦然）。

• 获取 H3 单元格的 分辨率 (https://h3geo.org/docs/core-library/restable)。

• 获取 H3 单元格 ID 的十六进制表示形式（反之亦然）。

有关这些函数的更多信息，请参阅 地理空间函数。

了解 GEOGRAPHY 和 GEOMETRY 之间的差异¶

尽管 GEOGRAPHY 和 GEOMETRY 数据类型都定义地理空间要素，但这些类型使用不同的模型。下表总结了这些差异。

比较 GEOGRAPHY 和 GEOMETRY 数据类型的示例¶

以下示例比较了在使用 GEOGRAPHY 和 GEOMETRY 数据类型作为输入时地理空间函数的输出。

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

示例 3：查询从柏林到旧金山所经过的国家/地区的名称¶

下表比较了 GEOGRAPHY 类型和 GEOMETRY 类型的 ST_INTERSECTS 输出：

ST_INTERSECTS，使用 . GEOGRAPHY 输入	ST_INTERSECTS，使用 . GEOMETRY 输入
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

了解输入数据验证中的差异¶

要为输入形状创建 GEOMETRY 或 GEOGRAPHY 对象，您必须根据 OGC 简单要素规则 (https://www.ogc.org/standards/sfa)，使用格式正确且有效的形状。接下来的部分介绍 GEOMETRY 和 GEOGRAPHY 之间的输入数据有效性有何不同。

了解地理空间函数上无效形状的效果¶

当您传入无效形状的 GEOGRAPHY 或 GEOMETRY 对象时，不同的 地理空间函数 会产生不同的影响。

处理无效形状¶

接下来的部分介绍如何允许函数创建无效的形状，以及如何确定 GEOGRAPHY 或 GEOMETRY 对象表示无效的形状，还是已修复的形状。

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