Tipos de dados geoespaciais¶

Tipo de dados GEOGRAPHY¶

O tipo de dados GEOGRAPHY segue o padrão WGS 84 (ID de referência espacial 4326; para detalhes, consulte https://epsg.io/4326).

Os pontos na Terra são representados como graus de longitude (de -180 graus a +180 graus) e latitude (-90 a +90). Snowflake usa 14 casas decimais para armazenar coordenadas GEOGRAPHY. Quando os dados incluem casas decimais que excedem este limite, as coordenadas são arredondadas para garantir a conformidade com a restrição de comprimento especificada.

Atualmente, a altitude não é compatível.

Os segmentos de linha são interpretados como arcos de grande círculo na superfície da Terra.

O Snowflake também fornece funções geoespaciais que operam com o tipo de dados GEOGRAPHY.

Se você tiver dados geoespaciais (por exemplo, dados de longitude e latitude, WKT, WKB, GeoJSON etc.), sugerimos convertê-los e armazená-los nas colunas GEOGRAPHY, em vez de mantê-los em seus formatos originais nas colunas VARCHAR, VARIANT ou NUMBER. O armazenamento de seus dados em colunas GEOGRAPHY pode melhorar significativamente o desempenho das consultas que utilizam a funcionalidade geoespacial.

Quando a entrada para uma função geoespacial para o tipo de dados GEOGRAPHY representa um polígono, o ponto inicial e o final do polígono devem ser os mesmos. Caso contrário, a função poderá retornar erros.

Tipo de dados GEOMETRY¶

O tipo de dados GEOMETRY representa recursos em um sistema de coordenadas planar (euclidiano, cartesiano).

As coordenadas são representadas como pares de números reais (x, y). Atualmente, apenas coordenadas em 2D são suportadas.

As unidades do X e Y são determinadas pelo sistema de referência espacial (SRS) associado ao objeto GEOMETRY. O sistema de referência espacial é identificado pelo número do identificador do sistema de referência espacial (SRID). A menos que o SRID seja fornecido ao criar o objeto GEOMETRY ou ao chamar ST_SETSRID, o SRID é 0.

Snowflake usa 14 casas decimais para armazenar coordenadas GEOMETRY. Quando os dados incluem casas decimais que excedem este limite, as coordenadas são arredondadas para garantir a conformidade com a restrição de comprimento especificada.

O Snowflake fornece um conjunto de funções geoespaciais que operam no tipo de dados GEOMETRY. Para essas funções:

• Todas as funções assumem coordenadas planares, mesmo que a geometria utilize um SRS não planar.

• As funções de medição (por exemplo, ST_LENGTH) usam as mesmas unidades que o sistema de coordenadas.

• Para funções que aceitam várias expressões GEOMETRY como argumentos (por exemplo, ST_DISTANCE), as expressões de entrada devem ser definidas no mesmo SRS.

Formatos de entrada e saída padrão compatíveis¶

Os tipos de dados GEOGRAPHY e GEOMETRY oferecem suporte aos seguintes formatos padrão da indústria para entrada e saída:

• Texto bem conhecido (WKT)

• Binário bem conhecido (WKB)

• WKT e WKB estendidos (EWKT e EWKB) (consulte a observação sobre tratamento de EWKT e EWKB)

• IETF GeoJSON (consulte a observação sobre tratamento de GeoJSON)

Você também pode achar úteis as seguintes referências do Simple Feature Access do Open Geospatial Consortium:

• Arquitetura comum

• SQL Opção

Qualquer desvio em relação a essas normas é observado explicitamente na documentação do Snowflake.

Tipos de objetos geoespaciais compatíveis¶

Os tipos de dados GEOGRAPHY e GEOMETRY podem armazenar os seguintes tipos de objetos geoespaciais:

• Objetos geoespaciais WKT / WKB / EWKT / EWKB / GeoJSON:

  • Point

  • MultiPoint

  • LineString

  • MultiLineString

  • Polygon

  • MultiPolygon

  • GeometryCollection

• Estes objetos geoespaciais específicos de GeoJSON:

  • Recurso

  • FeatureCollection

Especificação do formato de saída para conjuntos de resultados¶

Os parâmetros de sessão GEOGRAPHY_OUTPUT_FORMAT e GEOMETRY_OUTPUT_FORMAT controlam a renderização das colunas GEOGRAPHY e GEOMETRY nos conjuntos de resultados (respectivamente).

Esses parâmetros podem ter um dos seguintes valores:

Para EWKT e EWKB, o SRID é sempre 4326 na saída. Consulte Tratamento de EWKT e EWKB para valores GEOGRAPHY.

Esse parâmetro afeta todos os clientes, inclusive a Snowflake UI e o cliente de linha de comando SnowSQL, bem como os drivers e conectores JDBC, ODBC, Node.js, Python etc.

Por exemplo: o driver JDBC retorna os seguintes metadados para uma coluna de resultados do tipo GEOGRAPHY (coluna i neste exemplo):

• Se GEOGRAPHY_OUTPUT_FORMAT='GeoJSON' ou GEOMETRY_OUTPUT_FORMAT='GeoJSON':

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

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

• Se GEOGRAPHY_OUTPUT_FORMAT='WKT' ou 'EWKT', ou se GEOMETRY_OUTPUT_FORMAT='WKT' ou 'EWKT':

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

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

• Se GEOGRAPHY_OUTPUT_FORMAT='WKB' ou 'EWKB', ou se GEOMETRY_OUTPUT_FORMAT='WKB' ou 'EWKB':

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

  • ResultSetMetaData.getColumnClassName(i) retorna "[B" (matriz de bytes).

Exemplos de inserção e consulta de dados GEOGRAPHY¶

O código abaixo mostra exemplos de entrada e saída para o tipo de dados GEOGRAPHY. Observe o seguinte:

• Para as coordenadas em WKT, EWKT e GeoJSON, a longitude aparece antes da latitude (por exemplo, POINT(lon lat)).

• Para a saída WKB e EWKB, presume-se que o parâmetro BINARY_OUTPUT_FORMAT está definido como HEX (o valor padrão para o parâmetro).

O exemplo a seguir cria uma tabela com uma coluna GEOGRAPHY, insere dados no formato WKT e retorna os dados em diferentes formatos de saída.

CREATE OR REPLACE 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)');

ALTER SESSION SET GEOGRAPHY_OUTPUT_FORMAT='GeoJSON';

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

ALTER SESSION SET GEOGRAPHY_OUTPUT_FORMAT='WKT';

SELECT g
  FROM geospatial_table
  ORDER BY id;

+-------------------------------------+
| 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
  ORDER BY id;

+------------------------------------------------------------------------------------+
| G                                                                                  |
|------------------------------------------------------------------------------------|
| 01010000006666666666965EC06666666666C64240                                         |
| 010200000002000000CDCCCCCCCC0C5FC00000000000004540713D0AD7A3005EC01F85EB51B8FE4440 |
+------------------------------------------------------------------------------------+

ALTER SESSION SET GEOGRAPHY_OUTPUT_FORMAT='EWKT';

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

ALTER SESSION SET GEOGRAPHY_OUTPUT_FORMAT='EWKB';

SELECT g
  FROM geospatial_table
  ORDER BY id;

+--------------------------------------------------------------------------------------------+
| G                                                                                          |
|--------------------------------------------------------------------------------------------|
| 0101000020E61000006666666666965EC06666666666C64240                                         |
| 0102000020E610000002000000CDCCCCCCCC0C5FC00000000000004540713D0AD7A3005EC01F85EB51B8FE4440 |
+--------------------------------------------------------------------------------------------+

Efeitos da utilização de SRIDs diferentes com GEOMETRY¶

Em uma coluna GEOMETRY, você pode inserir objetos que tenham SRIDs diferentes. Se a coluna contiver mais de um SRID, algumas das otimizações de desempenho importantes não serão aplicadas. Isso pode resultar em consultas mais lentas, em particular ao realizar uma junção em um predicado geoespacial.

Alteração do sistema de referência espacial (SRS) e SRID de um objeto GEOMETRY¶

Para alterar SRS e SRID de um objeto GEOMETRY existente, chame a função ST_TRANSFORM, passando o novo SRID. A função retorna um novo objeto GEOMETRY com o novo SRID e as coordenadas convertidas para usar o SRS. Por exemplo, para retornar um objeto GEOMETRY para geometry_expression que usa SRS para SRID 32633, execute a seguinte instrução:

SELECT ST_TRANSFORM(geometry_expression, 32633);

Se o SRID original não estiver definido corretamente no objeto GEOMETRY existente, especifique o SRID original como um argumento adicional. Por exemplo, se geometry_expression for um objeto GEOMETRY que usa SRID 4326 e você quiser transformá-lo para usar SRID 28992, execute a seguinte instrução:

SELECT ST_TRANSFORM(geometry_expression, 4326, 28992);

Se um objeto GEOMETRY usar as coordenadas corretas para um SRS, mas tiver o SRID errado, é possível corrigir o SRID chamando a função ST_SETSRID. Por exemplo, a instrução a seguir define o SRID para geometry_expression como 4326, deixando as coordenadas inalteradas:

SELECT ST_SETSRID(geometry_expression, 4326);

Como executar operações DML em colunas GEOGRAPHY e GEOMETRY¶

Quando uma coluna GEOGRAPHY ou GEOMETRY é o alvo de uma operação DML (INSERT, COPY, UPDATE, MERGE, ou CREATE TABLE AS…), a expressão de origem da coluna pode ser de qualquer um dos seguintes tipos:

• GEOGRAPHY ou GEOMETRY: uma expressão do tipo GEOGRAPHY ou GEOMETRY é geralmente o resultado de uma função de análise, uma função de construção ou uma coluna GEOGRAPHY ou GEOMETRY existente. Para uma lista completa das funções e categorias de funções aceitas, consulte Funções geospaciais.

• VARCHAR: interpretado como uma cadeia de caracteres com formato WKT, WKB (em formato hexadecimal), EWKT, EWKB (em formato hexadecimal) ou GeoJSON (consulte TO_GEOGRAPHY(VARCHAR)).

• BINARY: interpretado como um binário WKB (consulte TO_GEOGRAPHY(BINARY) e TO_GEOMETRY(BINARY)).

• VARIANT: interpretado como um objeto GeoJSON (consulte TO_GEOGRAPHY(VARIANT) e TO_GEOMETRY(VARIANT)).

Como carregar dados geoespaciais de estágios¶

É possível carregar dados de arquivos CSV ou JSON/AVRO em um estágio diretamente (ou seja, sem transformações de cópia) em uma coluna GEOGRAPHY.

• CSV: os valores das cadeias de caracteres da coluna CSV correspondente são analisados como GeoJSON, WKT, EWKT, WKB, ou EWKB (consulte TO_GEOGRAPHY(VARCHAR)).

• JSON/AVRO: os valores JSON no arquivo são interpretados como GeoJSON (consulte TO_GEOGRAPHY(VARIANT)).

  Consulte também Tratamento GeoJSON dos valores GEOGRAPHY.

O carregamento de dados de outros formatos de arquivo (Parquet, ORC etc.) é possível por meio de uma transformação COPY.

Utilização de dados geoespaciais com UDFs Java¶

UDFs de Java permitem o tipo GEOGRAPHY como argumento e como valor de retorno. Consulte Mapeamentos de tipos de dados SQL-Java e Como passar um valor GEOGRAPHY para uma UDF Java em linha para obter mais detalhes.

Utilização de dados geoespaciais com UDFs JavaScript¶

UDFs JavaScript permitem o tipo GEOGRAPHY ou GEOMETRY como argumento e como valor de retorno.

Se uma UDF JavaScript tiver um argumento do tipo GEOGRAPHY ou GEOMETRY, esse argumento será visível como um objeto JSON no formato GeoJSON dentro do corpo da UDF.

Se uma UDF JavaScript retorna GEOGRAPHY ou GEOMETRY, espera-se que o corpo da UDF retorne um objeto JSON no formato GeoJSON.

Por exemplo, essas duas UDFs JavaScript são aproximadamente equivalentes às funções internas ST_X e 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
$$;

Utilização de dados geoespaciais com UDFs Python¶

As UDFs Python permitem os tipos GEOGRAPHY e GEOMETRY como argumentos e valores de retorno.

Se uma UDF Python tiver um argumento do tipo GEOGRAPHY ou GEOMETRY, esse argumento será representado como um objeto GeoJSON, que é convertido em um objeto Python dict dentro do corpo da UDF.

Se um UDF de Python retornar GEOGRAPHY ou GEOMETRY, espera-se que o corpo da UDF retorne um objeto Python dict que esteja em conformidade com a estrutura de GeoJSON.

Por exemplo, esta UDF de Python retorna o número de geometrias distintas que constituem um tipo GEOGRAPHY composto:

CREATE OR REPLACE FUNCTION py_numgeographys(geo GEOGRAPHY)
RETURNS INTEGER
LANGUAGE PYTHON
RUNTIME_VERSION = 3.10
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)
$$;

Verifique o Snowflake Labs para obter mais exemplos de UDFs de Python. Alguns deles permitem manipulações espaciais complexas ou simplificam a ingestão de dados. Por exemplo, esta UDF permite a leitura de formatos que não sejam compatíveis nativamente, como Shapefiles (.SHP), TAB, KML, GPKG, entre outros.

Utilização de objetos GEOGRAPHY com H3¶

H3 é um índice geoespacial hierárquico que particiona o mundo em células hexagonais em um sistema de indexação de grade global.

Snowflake fornece funções SQL que permitem usar H3 com objetos GEOGRAPHY. Você pode usar essas funções para:

• Obter o ID de célula H3 (índice) para um objeto GEOGRAPHY que representa um ponto (e vice-versa).

• Obter os IDs do conjunto mínimo de células H3 que cobrem um objeto GEOGRAPHY.

• Obter os IDs das células H3 que possuem centroides dentro de um objeto GEOGRAPHY que representa um polígono.

• Obter o objeto GEOGRAPHY que representa o limite de uma célula H3.

• Obter os pais e filhos de uma determinada célula H3.

• Obter a longitude e a latitude do centroide de uma célula H3 (e vice-versa).

• Obter a resolução de uma célula H3.

• Obter a representação hexadecimal de um ID de célula H3 (e vice-versa).

Para obter mais informações sobre essas funções, consulte Funções geospaciais.

Diferenças entre GEOGRAPHY e GEOMETRY¶

Embora ambos os tipos de dados GEOGRAPHY e GEOMETRY definam características geoespaciais, os tipos usam modelos diferentes. A tabela a seguir resume as diferenças.

Exemplos comparando os tipos de dados GEOGRAPHY e GEOMETRY¶

Os exemplos a seguir comparam a saída das funções geoespaciais ao usar os tipos de dados GEOGRAPHY e GEOMETRY como entrada.

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_GEOMPOINT(13.4814, 52.5015),
    ST_GEOMPOINT(-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 |
+--------------------+

Exemplo 3: Consulta dos nomes dos países que sobrepõem a linha de Berlim a São Francisco¶

A tabela a seguir compara a saída de ST_INTERSECTS para tipos GEOGRAPHY e GEOMETRY:

ST_INTERSECTS usando uma entrada . GEOGRAPHY	ST_INTERSECTS usando uma entrada . 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 | +--------------------------+	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 | +--------------------------+

Diferenças na validação de dados de entrada¶

Para criar um objeto GEOMETRY ou GEOGRAPHY para uma forma de entrada, você deve usar uma forma bem formada e válida, de acordo com as regras da OGC para recursos simples. As próximas seções explicam como a validade dos dados de entrada difere entre GEOMETRY e GEOGRAPHY.

Uma forma pode ser um GEOGRAPHY válido mas um GEOMETRY inválido¶

Uma determinada forma pode ser um objeto GEOGRAPHY válido, mas um objeto GEOMETRY inválido, e vice-versa.

Por exemplo, polígonos autointersectantes são proibidos pelas regras da OGC. Um determinado conjunto de pontos pode definir bordas que se cruzam no domínio cartesiano, mas não em uma esfera. Considere o seguinte polígono:

POLYGON((0 50, 25 50, 50 50, 0 50))

Copy

No domínio cartesiano, esse polígono é degradado a uma linha e, como resultado, é inválido.

Entretanto, em uma esfera, esse mesmo polígono não se intercepta e é válido:

GeoJoin¶

GeoJoin é um recurso de otimização de consulta do Snowflake para junções geoespaciais. Trata-se de uma otimização de reescrita de junção especializada que melhora o desempenho ao unir tabelas com base em predicados que chamam funções geoespaciais, como ST_INTERSECTS, ST_CONTAINS, ST_DWITHIN e assim por diante. Por exemplo, é possível usar GeoJoin para encontrar todas as lojas em regiões geográficas específicas.

O GeoJoin tem as seguintes características:

• Otimiza automaticamente as consultas que unem tabelas que usam funções geoespaciais.

• Faz uma análise de sobreposição espacial entre conjuntos de dados geográficos.

A otimização de GeoJoin é acionada automaticamente pelo otimizador de consulta do Snowflake quando ele detecta padrões de junção geoespacial apropriados em suas consultas SQL. Nenhuma configuração adicional é necessária para se beneficiar da melhoria de desempenho.

Remoção geoespacial em predicados GEOMETRY¶

O Snowflake pode melhorar o desempenho de algumas consultas que filtram com base na coluna GEOMETRY removendo as micropartições que não podem conter linhas correspondentes. Essa otimização usa metadados de caixa delimitadora armazenados com valores GEOMETRY para evitar a verificação de dados que têm a garantia de não satisfazer o predicado. Ou seja, o Snowflake ignora as micropartições com metadados de caixa delimitadora armazenados que não fazem interseção com a caixa delimitadora de uma geometria constante no filtro.

O Snowflake realiza essa otimização automaticamente, e nenhuma configuração adicional é necessária para se beneficiar da melhoria de desempenho.

Como funciona a remoção geoespacial¶

Um valor GEOMETRY é armazenado com metadados que incluem os seguintes itens:

• Uma caixa delimitadora (xmin, ymin, xmax, ymax)

• Um valor SRID

O Snowflake usa metadados de caixa delimitadora no nível do arquivo como o principal sinal para remoção.

O Snowflake pré-verifica se há interseções de caixa delimitadora nas micropartições antes de avaliar o predicado exato da geometria. Se os metadados indicarem que não é possível uma sobreposição de caixas delimitadoras, as micropartições podem ser ignoradas.

A ilustração a seguir mostra uma linha de caixa delimitadora com duas formas de interseção e duas formas que não são de interseção:

A ilustração mostra uma consulta geoespacial semelhante ao exemplo a seguir:

SELECT *
  FROM <table>
  WHERE ST_CONTAINS(<geo_column>, <constant_geometry>);

Copy

Para uma caixa delimitadora especificada da constante geoespacial (mostrada em azul), somente as micropartições que correspondem às caixas delimitadoras que se sobrepõem à linha da caixa delimitadora (mostrada em verde) são verificadas. As micropartições que correspondem às caixas delimitadores que não se sobrepõem (mostradas em vermelho) são removidas.

ALTER TABLE <table_name>
  CLUSTER BY (H3_POINT_TO_CELL(<geo_column>, <h3_resolution>));

ALTER TABLE <table_name>
  CLUSTER BY (H3_POINT_TO_CELL(ST_CENTROID(<geo_column>), <h3_resolution>));

Efeitos das formas inválidas sobre as funções geoespaciais¶

Diferentes funções geospaciais têm efeitos diferentes quando você passa um objeto GEOGRAPHY ou GEOMETRY para uma forma inválida.

Como trabalhar com formas inválidas¶

As próximas seções explicam como permitir que as funções criem formas inválidas e como determinar se um objeto GEOGRAPHY ou GEOMETRY representa uma forma inválida ou reparada.

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