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://spatialreference.org/ref/epsg/wgs-84/).

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.

No momento, não há suporte para altitude.

Os segmentos de linha são interpretados como arcos geodésicos 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.), você deve converter e armazenar esses dados em colunas GEOGRAPHY, em vez de manter os dados em seus formatos originais em 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.

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) utilizam 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 suportados¶

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 as seguintes referências úteis:

• Open Geospatial Consortium’s Simple Feature Access / Common Feature Architecture e opção SQL:

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

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

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

Tipos de objetos geoespaciais suportados¶

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 de colunas do tipo GEOGRAPHY e GEOMETRY em conjuntos de resultados (respectivamente).

Os parâmetros GEOGRAPHY_OUTPUT_FORMAT e GEOMETRY podem ter um dos seguintes valores:

Valor do parâmetro	Descrição
GeoJSON (padrão)	O resultado GEOGRAPHY/GEOMETRY é apresentado como um OBJECT no formato GeoJSON.
WKT	O resultado GEOGRAPHY/GEOMETRY é renderizado como um VARCHAR no formato WKT.
WKB	O resultado GEOGRAPHY/GEOMETRY é renderizado como um BINARY no formato WKB.
EWKT	O resultado GEOGRAPHY/GEOMETRY é renderizado como um VARCHAR no formato EWKT.
EWKB	O resultado GEOGRAPHY/GEOMETRY é renderizado como um BINARY no formato EWKB.

Para EWKT e EWKB, o SRID é sempre 4326 na saída. Consulte a nota sobre como lidar com EWKT e EWKB.

Este parâmetro afeta todos os clientes, incluindo a UI do Snowflake e o cliente SnowSQL da linha de comando, assim 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 de 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 cria uma tabela com uma coluna GEOGRAPHY, insere os dados no formato WKT e retorna os dados em diferentes formatos de saída.

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

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

Copy

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

Copy

Observe que se um objeto GEOMETRY usar as coordenadas corretas para um SRS, mas tiver o SRID errado, você poderá corrigir o SRID chamando a função ST_SETSRID. Por exemplo, a instrução a seguir define SRID para geometry_expression como 4326, deixando as coordenadas inalteradas:

SELECT ST_SETSRID(geometry_expression, 4326);

Copy

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

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

• BINARY: interpretado como um WKB binário (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¶

Dados de arquivos CSV ou JSON / AVRO em um estágio podem ser carregados diretamente (isto é, 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: Nota sobre o tratamento de GeoJSON para valores GEOGRAPHY.

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

Utilização de dados geoespaciais com UDFs de 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 de Java inline para obter mais detalhes.

Utilização de dados geoespaciais com UDFs de JavaScript¶

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

Se uma UDF JavaScript tem 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¶

UDFs Python permitem o tipo GEOGRAPHY ou GEOMETRY como argumento e como valor 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.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)
$$;

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 são suportados nativamente, como Shapefiles (.SHP), TAB, KML, GPKG e outros.

Uso 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).

As funções SQL para H3 estão listadas abaixo:

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

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

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.

Compreensão dos 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);