Exemplos: consultas geoespaciais - Amazon Athena
Recursos adicionais

Exemplos: consultas geoespaciais

Os exemplos neste tópico criam duas tabelas de dados de exemplo disponíveis no GitHub e consultam as tabelas com base nos dados. Os dados de exemplo, que são apenas para fins ilustrativos e não têm garantia de serem precisos, estão nos seguintes arquivos:

  • earthquakes.csv: lista terremotos ocorridos na Califórnia. A tabela earthquakes de exemplo usa campos desses dados.

  • california-counties.json: lista os dados do condado no estado da Califórnia no formato GeoJSON compatível com ESRI. Os dados incluem muitos campos, como AREA, PERIMETER, STATE, COUNTY e NAME, mas a tabela counties de exemplo usa apenas dois: Name (string) e BoundaryShape (binário).

    nota

    O Athena usa com.esri.json.hadoop.EnclosedEsriJsonInputFormat para converter os dados JSON no formato binário geoespacial.

O exemplo de código a seguir cria uma tabela chamada earthquakes:

CREATE external TABLE earthquakes
(
 earthquake_date string,
 latitude double,
 longitude double,
 depth double,
 magnitude double,
 magtype string,
 mbstations string,
 gap string,
 distance string,
 rms string,
 source string,
 eventid string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE LOCATION 's3://amzn-s3-demo-bucket/my-query-log/csv/';

O exemplo de código a seguir cria uma tabela chamada counties:

CREATE external TABLE IF NOT EXISTS counties
 (
 Name string,
 BoundaryShape binary
 )
ROW FORMAT SERDE 'com.esri.hadoop.hive.serde.EsriJsonSerDe'
STORED AS INPUTFORMAT 'com.esri.json.hadoop.EnclosedEsriJsonInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://amzn-s3-demo-bucket/my-query-log/json/';

A consulta de exemplo a seguir usa a função CROSS JOIN nas tabelas counties e earthquake. O exemplo usa ST_CONTAINS para consultar os condados com limites que incluem locais de terremoto, que são especificados com ST_POINT. A consulta agrupa esses condados por nome, ordena-os por contagem e os retorna em ordem decrescente.

nota

A partir do mecanismo Athena versão 2, funções como ST_CONTAINS deixarão de aceitar o tipo VARBINARY como entrada. Por esse motivo, o exemplo usa a função ST_GeomFromLegacyBinary(varbinary) para converter o valor binário boundaryshape em uma geometria. Para obter mais informações, consulte Alterações nas funções geoespaciais na referência Mecanismo do Athena versão 2.

SELECT counties.name,
        COUNT(*) cnt
FROM counties
CROSS JOIN earthquakes
WHERE ST_CONTAINS (ST_GeomFromLegacyBinary(counties.boundaryshape), ST_POINT(earthquakes.longitude, earthquakes.latitude))
GROUP BY  counties.name
ORDER BY  cnt DESC

Essa consulta retorna:

+------------------------+
| name             | cnt |
+------------------------+
| Kern             | 36  |
+------------------------+
| San Bernardino   | 35  |
+------------------------+
| Imperial         | 28  |
+------------------------+
| Inyo             | 20  |
+------------------------+
| Los Angeles      | 18  |
+------------------------+
| Riverside        | 14  |
+------------------------+
| Monterey         | 14  |
+------------------------+
| Santa Clara      | 12  |
+------------------------+
| San Benito       | 11  |
+------------------------+
| Fresno           | 11  |
+------------------------+
| San Diego        | 7   |
+------------------------+
| Santa Cruz       | 5   |
+------------------------+
| Ventura          | 3   |
+------------------------+
| San Luis Obispo  | 3   |
+------------------------+
| Orange           | 2   |
+------------------------+
| San Mateo        | 1   |
+------------------------+

Recursos adicionais

Para ver exemplos adicionais de consultas geoespaciais, consulte as seguintes postagens de blog: