GeoJSON - ClickHouse Documentation

Entrada	Saída	Alias
✔	✔

Descrição

Os dados GeoJSON são representados como um único documento FeatureCollection, que o ClickHouse mapeia para três colunas — id, geometry e properties — um conjunto para cada Feature. A leitura de um documento produz uma linha por feature; a escrita produz uma feature por linha.

Leitura de dados

Ler uma FeatureCollection produz uma linha por feature com o seguinte esquema fixo:

Coluna	Tipo	Descrição
`id`	`Nullable(String)`	O membro `id` da feature (uma string ou número JSON), armazenado como texto; `NULL` se o `id` estiver ausente ou for `null`, enquanto um `id` explicitamente vazio é mantido como `''`.
`geometry`	`Geometry`	A geometria da feature, armazenada como um tipo variante `Geometry`.
`properties`	`Nullable(JSON)`	O objeto `properties` da feature, armazenado como uma coluna `JSON` semiestruturada. Um `"properties": null` explícito é preservado como `NULL`.

Cada geometria é armazenada no tipo Geometry do ClickHouse (um Variant). Os tipos de geometria GeoJSON compatíveis são Point, LineString, MultiLineString, Polygon e MultiPolygon. Os outros dois tipos de geometria GeoJSON, GeometryCollection e MultiPoint, não podem ser representados pelo tipo Geometry; ler um deles na coluna geometry gera uma exceção por padrão, mas isso pode ser alterado para inserir NULL em vez disso — veja Como lidar com tipos de geometria não compatíveis abaixo. Por padrão, a coluna geometry é NULL apenas quando a geometria de uma feature é um null JSON explícito; com input_format_geojson_unsupported_geometry_handling = 'null', ela também é NULL para um tipo de geometria não compatível. A estrutura do documento é validada: o type de nível superior deve ser FeatureCollection e todo elemento de features deve ter type Feature. Por padrão, as coordenadas devem satisfazer os invariantes de forma do GeoJSON — um LineString (e cada linha de um MultiLineString) deve ter pelo menos dois pontos, e um anel de Polygon (e cada anel de um MultiPolygon) deve ser fechado e ter pelo menos quatro pontos (veja Validação de geometria). Documentos malformados são rejeitados em vez de serem carregados silenciosamente. A ordem das chaves é flexível: o type de nível superior pode aparecer antes ou depois do array features, e, dentro de um objeto de geometria, coordinates pode aparecer antes ou depois de type. A inferência de esquema retorna o esquema fixo acima, então DESCRIBE e SELECT ... FROM format(...) funcionam sem uma definição de tabela. Considere o seguinte arquivo GeoJSON london.geojson, com uma combinação de tipos de geometria:

{
    "type": "FeatureCollection",
    "features": [
        {
            "type": "Feature",
            "id": "1",
            "geometry": {"type": "Point", "coordinates": [-0.0761, 51.5081]},
            "properties": {"name": "Tower of London", "feature_type": "landmark", "year_built": 1078}
        },
        {
            "type": "Feature",
            "id": "2",
            "geometry": {
                "type": "LineString",
                "coordinates": [[-0.2500, 51.4700], [-0.1800, 51.4900], [-0.1200, 51.5060], [-0.0700, 51.5050], [0.0000, 51.5100]]
            },
            "properties": {"name": "River Thames", "feature_type": "river", "length_km": 346}
        },
        {
            "type": "Feature",
            "id": "3",
            "geometry": {
                "type": "Polygon",
                "coordinates": [[[-0.1880, 51.5074], [-0.1533, 51.5074], [-0.1533, 51.5153], [-0.1880, 51.5153], [-0.1880, 51.5074]]]
            },
            "properties": {"name": "Hyde Park", "feature_type": "park", "area_km2": 1.42}
        }
    ]
}

Podemos consultar o arquivo e inspecionar os tipos geométricos:

Query

SELECT id, properties.name AS name, variantType(geometry) AS geo_type
FROM file('london.geojson', GeoJSON);

Response

┌─id─┬─name────────────┬─geo_type───┐
│ 1  │ Tower of London │ Point      │
│ 2  │ River Thames    │ LineString │
│ 3  │ Hyde Park       │ Polygon    │
└────┴─────────────────┴────────────┘

A extensão .geojson é detectada automaticamente, portanto o argumento format pode ser omitido:

Query

SELECT id, properties.name AS name, variantType(geometry) AS geo_type
FROM file('london.geojson');

Podemos usar variantType para verificar o tipo subjacente de cada objeto Geometry:

Query

SELECT properties.name AS name, geometry, variantType(geometry)
FROM file('london.geojson', GeoJSON);

Response

Row 1:
──────
name:                  Tower of London
geometry:              (-0.0761,51.5081)
variantType(geometry): Point

Row 2:
──────
name:                  River Thames
geometry:              [(-0.25,51.47),(-0.18,51.49),(-0.12,51.506),(-0.07,51.505),(0,51.51)]
variantType(geometry): LineString

Row 3:
──────
name:                  Hyde Park
geometry:              [[(-0.188,51.5074),(-0.1533,51.5074),(-0.1533,51.5153),(-0.188,51.5153),(-0.188,51.5074)]]
variantType(geometry): Polygon

E podemos extrair os dados subjacentes assim:

Query

SELECT properties.name AS name, variantType(geometry), geometry.Point, geometry.LineString, geometry.Polygon
FROM file('london.geojson', GeoJSON);

Response

Row 1:
──────
name:                  Tower of London
variantType(geometry): Point
geometry.Point:        (-0.0761,51.5081)
geometry.LineString:   []
geometry.Polygon:      []

Row 2:
──────
name:                  River Thames
variantType(geometry): LineString
geometry.Point:        (0,0)
geometry.LineString:   [(-0.25,51.47),(-0.18,51.49),(-0.12,51.506),(-0.07,51.505),(0,51.51)]
geometry.Polygon:      []

Row 3:
──────
name:                  Hyde Park
variantType(geometry): Polygon
geometry.Point:        (0,0)
geometry.LineString:   []
geometry.Polygon:      [[(-0.188,51.5074),(-0.1533,51.5074),(-0.1533,51.5153),(-0.188,51.5153),(-0.188,51.5074)]]

Ao acessar uma subcoluna Geometry, o valor é retornado quando a linha contém esse tipo; caso contrário, retorna o valor padrão do tipo — (0,0) para Point e [] para os tipos baseados em Array — portanto, use variantType(geometry) para identificar qual deles está definido. Também podemos fazer a ingestão de dados GeoJSON em uma tabela:

Query

CREATE TABLE london
(
    id           String,
    geometry     Geometry,
    properties   Nullable(JSON),
    name         String MATERIALIZED properties.name,
    feature_type String MATERIALIZED properties.feature_type
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO london
SELECT id, geometry, properties
FROM file('london.geojson', GeoJSON);

Em seguida, faça a consulta por tipo de feature:

Query

SELECT name, feature_type, variantType(geometry) AS geo_type
FROM london
ORDER BY id;

Response

┌─name────────────┬─feature_type─┬─geo_type───┐
│ Tower of London │ landmark     │ Point      │
│ River Thames    │ river        │ LineString │
│ Hyde Park       │ park         │ Polygon    │
└─────────────────┴──────────────┴────────────┘

Também podemos inferir o esquema dos dados GeoJSON sem uma definição de tabela:

Query

DESCRIBE format(GeoJSON, '{"type":"FeatureCollection","features":[]}');

Response

┌─name───────┬─type─────────────┐
│ id         │ Nullable(String) │
│ geometry   │ Geometry         │
│ properties │ Nullable(JSON)   │
└────────────┴──────────────────┘

Tratamento de tipos de geometria não compatíveis

Alguns tipos de geometria GeoJSON válidos — como GeometryCollection e MultiPoint — não podem ser representados pelo tipo Geometry do ClickHouse. Você pode controlar o que acontece quando uma dessas geometrias precisa ser armazenada na coluna geometry usando a configuração input_format_geojson_unsupported_geometry_handling. Os valores possíveis são:

'throw' — lançar uma exceção (padrão)
'null' — inserir um valor NULL na coluna geometry e continuar a análise

Esse tratamento se aplica apenas quando a coluna geometry é lida. Quando geometry não é uma coluna de saída solicitada (por exemplo, SELECT id FROM ...), uma geometria não compatível ainda é validada quanto à sua integridade estrutural, mas não aciona esse tratamento — ela não lança exceção nem insere NULL, porque nenhum valor de geometria é materializado.

Limitações

A leitura reflete apenas o que se encaixa no esquema fixo, portanto algumas informações do GeoJSON não são preservadas:

Apenas id, geometry e properties são gerados; as demais estruturas do documento não são expostas como colunas.
A terceira coordenada (elevação) de uma posição, e quaisquer coordenadas além dela, são descartadas — as posições passam a ser [longitude, latitude].
bbox e membros externos (como um name ou crs de nível superior, ou membros extras dentro de uma Feature) são ignorados.
Um id numérico é armazenado como texto, portanto a distinção entre string e número se perde; um id ausente ou null se torna NULL.
GeometryCollection e MultiPoint não podem ser representados — consulte Tratamento de tipos de geometria não compatíveis.

Gravação de dados

Gravar um conjunto de resultados produz um único FeatureCollection GeoJSON, com um Feature por linha. As colunas do resultado são mapeadas para cada Feature da seguinte forma:

Membro de `Feature`	Construído a partir de	Observações
`type`	—	Sempre `"Feature"`.
`geometry`	a única coluna do tipo geometria	É necessária exatamente uma coluna do tipo geometria; caso contrário, a consulta é rejeitada. Uma geometria `NULL` é gravada como `null`.
`id`	uma coluna chamada `id`	Omitido quando o valor é `NULL`. Uma coluna `String` é gravada como uma string JSON, e uma coluna numérica como um número JSON.
`properties`	todas as colunas restantes	Uma única coluna chamada `properties` cujo tipo tem estrutura de objeto (`JSON`, `Map` ou um `Tuple` nomeado) é gravada diretamente como o objeto `properties`, em vez de ficar aninhada sob uma chave `properties`. Caso contrário, cada coluna restante se torna uma propriedade identificada por seu nome (um objeto vazio quando não houver nenhuma).

A coluna do tipo geometria pode ser a variante Geometry ou um tipo geo específico; cada um é mapeado para um tipo de geometria GeoJSON:

Tipo do ClickHouse	GeoJSON `"type"`
`Point`	`Point`
`LineString`	`LineString`
`MultiLineString`	`MultiLineString`
`Polygon`	`Polygon`
`MultiPolygon`	`MultiPolygon`
`Ring`	`Polygon` (um único anel)
`Geometry`	o tipo da variante ativa (ou `null`)

Ring não é um tipo de geometria GeoJSON — um anel linear é um componente de um Polygon — portanto, um valor Ring é gravado como um Polygon de anel único.

Exemplos

Continuando com a tabela london criada acima, a exportação de colunas de atributos simples transforma todas as colunas, exceto id e geometry, em uma propriedade:

Query

SELECT id, geometry, name, feature_type
FROM london
ORDER BY id
FORMAT GeoJSON;

Response

{"type":"FeatureCollection","features":[{"type":"Feature","id":"1","geometry":{"type":"Point","coordinates":[-0.0761,51.5081]},"properties":{"name":"Tower of London","feature_type":"landmark"}},{"type":"Feature","id":"2","geometry":{"type":"LineString","coordinates":[[-0.25,51.47],[-0.18,51.49],[-0.12,51.506],[-0.07,51.505],[0,51.51]]},"properties":{"name":"River Thames","feature_type":"river"}},{"type":"Feature","id":"3","geometry":{"type":"Polygon","coordinates":[[[-0.188,51.5074],[-0.1533,51.5074],[-0.1533,51.5153],[-0.188,51.5153],[-0.188,51.5074]]]},"properties":{"name":"Hyde Park","feature_type":"park"}}]}

Como uma única coluna do tipo objeto chamada properties é gravada diretamente, ler um arquivo GeoJSON e gravá-lo de volta reproduz o documento (as colunas id, geometry e properties são as inferidas para o arquivo):

Query

SELECT * FROM file('london.geojson', GeoJSON) FORMAT GeoJSON;

Response

{"type":"FeatureCollection","features":[{"type":"Feature","id":"1","geometry":{"type":"Point","coordinates":[-0.0761,51.5081]},"properties":{"feature_type":"landmark","name":"Tower of London","year_built":1078}},{"type":"Feature","id":"2","geometry":{"type":"LineString","coordinates":[[-0.25,51.47],[-0.18,51.49],[-0.12,51.506],[-0.07,51.505],[0,51.51]]},"properties":{"feature_type":"river","length_km":346,"name":"River Thames"}},{"type":"Feature","id":"3","geometry":{"type":"Polygon","coordinates":[[[-0.188,51.5074],[-0.1533,51.5074],[-0.1533,51.5153],[-0.188,51.5153],[-0.188,51.5074]]]},"properties":{"area_km2":1.42,"feature_type":"park","name":"Hyde Park"}}]}

Uma coluna id numérica é representada como um número JSON (um id Nullable que é NULL é omitido completamente):

Query

SELECT 42 AS id, (-0.1276, 51.5072)::Point AS geometry FORMAT GeoJSON;

Response

{"type":"FeatureCollection","features":[{"type":"Feature","id":42,"geometry":{"type":"Point","coordinates":[-0.1276,51.5072]},"properties":{}}]}

Um Ring é representado como um Polygon de um único anel:

Query

SELECT [(0., 0.), (10., 0.), (10., 10.), (0., 0.)]::Ring AS geometry FORMAT GeoJSON;

Response

{"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[0,0],[10,0],[10,10],[0,0]]]},"properties":{}}]}

Gravando em um arquivo

Use INTO OUTFILE para gravar um arquivo GeoJSON no cliente:

Query

SELECT id, geometry, properties
FROM london
ORDER BY id
INTO OUTFILE 'london_export.geojson'
FORMAT GeoJSON;

O servidor pode gravar o arquivo diretamente com a função de tabela file (a extensão .geojson seleciona o formato automaticamente):

Query

INSERT INTO FUNCTION file('london_export.geojson', GeoJSON)
SELECT id, geometry, properties FROM london;

Limitações

Os tipos geo do ClickHouse não carregam um sistema de referência de coordenadas, portanto a saída pressupõe que as coordenadas já estejam em longitude/latitude WGS84 na ordem [longitude, latitude], como exige a RFC 7946. Nenhuma reprojeção nem troca de eixos é realizada, portanto coordenadas projetadas — ou dados armazenados como (latitude, longitude) — produzem GeoJSON estruturalmente válido, mas fora de conformidade.

A saída reflete apenas o que o ClickHouse armazena:

Informações descartadas durante a leitura — a elevação de uma posição, bbox, membros adicionais e a distinção entre string e número em um id — não podem ser reproduzidas; veja Limitações de leitura.
As coordenadas são gravadas a partir de valores Float64 usando a representação mais curta que permite ida e volta sem perda.
Um objeto properties obtido diretamente de uma coluna JSON é emitido na ordem canônica das chaves do tipo JSON, que pode diferir da entrada.

As geometrias são gravadas exatamente como armazenadas — a ordem das coordenadas e o sentido são preservados. Por padrão, a validade da forma GeoJSON é verificada na gravação (veja Validação de geometria): uma geometria que não seja uma forma GeoJSON válida, como uma LineString com um ponto ou um anel de Polygon não fechado, é rejeitada para que o documento gravado possa ser lido novamente. Defina format_geojson_validate_geometry = 0 para emitir essas geometrias como estão, produzindo GeoJSON estruturalmente válido, mas fora de conformidade. O invariante da regra da mão direita (sentido) não é aplicado em nenhum dos casos, e a distinção entre um objeto properties null e um vazio é preservada.

Validação de geometria

A configuração format_geojson_validate_geometry controla se o formato aplica as regras de forma geométrica da RFC 7946, em ambos os sentidos. Ela é ativada por padrão. Quando ativada, uma geometria que viola as regras de forma do GeoJSON é rejeitada: uma LineString (ou uma linha de uma MultiLineString) com menos de dois pontos; um anel de Polygon ou MultiPolygon com menos de quatro pontos, ou cujos primeiro e último pontos sejam diferentes (um anel não fechado); ou uma MultiLineString, Polygon ou MultiPolygon vazia. As mesmas regras se aplicam tanto à leitura desse tipo de documento quanto à gravação desse tipo de valor do ClickHouse, de modo que um documento gravado sempre possa ser lido novamente. Quando desativada, essas regras de forma não são aplicadas em nenhum dos sentidos: geometrias degeneradas são lidas como estão e gravadas como estão. Isso permite que valores de geometria do ClickHouse que não sejam geometrias GeoJSON válidas façam ida e volta no formato, ao custo de produzir documentos que não são GeoJSON válidos. A validação é apenas estrutural: ela verifica a contagem de pontos e o fechamento dos anéis. Ela não inspeciona a correção geométrica da forma, portanto uma geometria estruturalmente válida, mas geometricamente degenerada, é aceita em qualquer um dos sentidos — por exemplo, um polígono de área zero, um anel autointersectante ou um polígono cujos buracos (anéis internos) ficam fora do anel externo. Da mesma forma, a orientação dos anéis de polígonos segundo a regra da mão direita (sentido) nunca é aplicada. Uma verificação é independente da configuração: coordenadas não finitas (NaN, Inf) são sempre rejeitadas, porque não podem ser representadas como números JSON.

​Descrição

​Leitura de dados

​Tratamento de tipos de geometria não compatíveis

​Limitações

​Gravação de dados

​Exemplos

​Gravando em um arquivo

​Limitações

​Validação de geometria

Descrição

Leitura de dados

Tratamento de tipos de geometria não compatíveis

Limitações

Gravação de dados

Exemplos

Gravando em um arquivo

Limitações

Validação de geometria