Aceleração de consultas geoespaciais com otimização de pesquisa¶
Este recurso exige Enterprise Edition (ou superior). Para se informar sobre a possibilidade de upgrade, entre em contato com o suporte Snowflake.
O serviço de otimização de pesquisa pode melhorar o desempenho de consultas com predicados que utilizam funções geoespaciais com objetos GEOGRAPHY.
Nota
Você deve habilitar este recurso para colunas específicas usando a cláusula ON no comando ALTER TABLE … ADD SEARCH OPTIMIZATION. Por exemplo:
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON GEO(mygeocol);
Consultas que utilizam funções geoespaciais não são melhoradas se você omitir a cláusula ON.
As seções seguintes fornecem mais detalhes:
Nota
Objetos GEOMETRY ainda não são compatíveis.
Predicados compatíveis com funções geoespaciais¶
Para consultas com predicados que utilizam as seguintes funções:
O serviço de otimização de pesquisa pode melhorar o desempenho se:
Uma expressão de entrada é uma coluna GEOGRAPHY em uma tabela, e
A outra expressão de entrada é uma constante de GEOGRAPHY (criada por meio de uma função de conversão ou construção).
Para ST_DWITHIN, o argumento da distância é uma constante REAL não negativa.
Observe que esse recurso tem as mesmas limitações que se aplicam ao serviço de otimização de pesquisa.
Outras considerações de desempenho¶
Como o serviço de otimização de pesquisa é projetado para predicados que são altamente seletivos e porque os predicados filtram por proximidade entre objetos geoespaciais, o clustering de objetos geoespaciais por proximidade na tabela pode resultar em melhor desempenho. Você pode agrupar seus dados, especificando a ordem de classificação ao carregar os dados ou usando o Clustering automático, dependendo se a tabela base muda com frequência:
- Carregamento de dados pré-classificados
Se os dados em sua tabela base não mudarem com frequência, você poderá especificar a ordem de classificação ao carregar os dados. Você pode então ativar a otimização de pesquisa na coluna GEOGRAPHY. Por exemplo:
CREATE TABLE new_table AS SELECT * FROM source_table ORDER BY st_geohash(geom); ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
Após cada grande alteração feita em seus dados base, você poderá reordenar os dados manualmente.
Clustering automático¶
Se houver atualizações frequentes em sua tabela base, você poderá usar o comando ALTER TABLE … CLUSTER BY … para ativar o Clustering automático para que a tabela seja automaticamente clusterizada de novo à medida que muda.
O exemplo a seguir adiciona uma nova coluna geom_geohash do tipo VARCHAR e armazena o índice geohash ou H3 da coluna GEOGRAPHY geom nessa nova coluna. Em seguida, ele permite o Clustering automático na nova coluna como a chave de cluster. Esta abordagem reterá automaticamente as partes da tabela que mudarem.
CREATE TABLE new_table AS SELECT *, ST_GEOHASH(geom) AS geom_geohash FROM source_table;
ALTER TABLE new_table CLUSTER BY (geom_geohash);
ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
Exemplos que utilizam funções geoespaciais¶
As seguintes instruções criam e configuram a tabela usada nos exemplos desta seção. A última instrução usa a cláusula ON no comando ALTER TABLE … ADD SEARCH OPTIMIZATION para adicionar otimização de pesquisa para a coluna g1 GEOGRAPHY.
CREATE OR REPLACE TABLE geospatial_table (id NUMBER, g1 GEOGRAPHY);
INSERT INTO geospatial_table VALUES
(1, 'POINT(-122.35 37.55)'),
(2, 'LINESTRING(-124.20 42.00, -120.01 41.99)'),
(3, 'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
ALTER TABLE geospatial_table ADD SEARCH OPTIMIZATION ON GEO(g1);
Exemplos de predicados compatíveis¶
A consulta a seguir é um exemplo de uma consulta compatível com o serviço de otimização de pesquisa. O serviço de otimização de pesquisa pode usar caminhos de acesso de pesquisa para melhorar o desempenho dessa consulta:
SELECT id FROM geospatial_table WHERE
ST_INTERSECTS(
g1,
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'));
A seguir estão exemplos de predicados adicionais que são compatíveis com o serviço de otimização de pesquisa:
...
ST_INTERSECTS(
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
g1)
...
ST_CONTAINS(
TO_GEOGRAPHY('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
g1)
...
ST_CONTAINS(
g1,
TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'))
...
ST_WITHIN(
TO_GEOGRAPHY('{"type" : "MultiPoint","coordinates" : [[-122.30, 37.55], [-122.20, 47.61]]}'),
g1)
...
ST_WITHIN(
g1,
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
...
ST_COVERS(
TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'),
g1)
...
ST_COVERS(
g1,
TO_GEOGRAPHY('POINT(0 0)'))
...
ST_COVEREDBY(
TO_GEOGRAPHY('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))'),
g1)
...
ST_COVEREDBY(
g1,
TO_GEOGRAPHY('POINT(-122.35 37.55)'))
...
ST_DWITHIN(
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
g1,
100000)
...
ST_DWITHIN(
g1,
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
100000)
Exemplos de constantes de GEOGRAPHY de construção¶
A seguir, exemplos de predicados que utilizam diferentes funções de conversão e construção para a constante de GEOGRAPHY.
...
ST_INTERSECTS(
g1,
ST_GEOGRAPHYFROMWKT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
...
ST_INTERSECTS(
ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
g1)
...
ST_CONTAINS(
ST_GEOGRAPHYFROMEWKT('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
g1)
...
ST_WITHIN(
ST_GEOGRAPHYFROMWKB('01010000006666666666965EC06666666666C64240'),
g1)
...
ST_COVERS(
g1,
ST_MAKEPOINT(0.2, 0.8))
...
ST_INTERSECTS(
g1,
ST_MAKELINE(
TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'),
TO_GEOGRAPHY('POINT(0.8 0.2)')))
...
ST_INTERSECTS(
ST_POLYGON(
TO_GEOGRAPHY('SRID=4326;LINESTRING(0.0 0.0, 1.0 0.0, 1.0 2.0, 0.0 2.0, 0.0 0.0)')),
g1)
...
ST_WITHIN(
g1,
TRY_TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'))
...
ST_COVERS(
g1,
ST_GEOGPOINTFROMGEOHASH('s00'))