Spatial Search of Amazon S3 Data with Amazon Athena and Visualized It in QGIS
Tags: athena, s3, qgis, aws
Published: 2022-05-16 11:49:12

![img](https://day-journal.com/memo/images/try-089_00.png)

### I tried a spatial search of Amazon S3 data with Amazon Athena and visualized it with QGIS :tada:  

<br>

The following is a detailed explanation.

- Advance Preparation
- Set the query destination
- Table Creation
- Spatial Search

<br>

## Advance Preparation

Prepare GIS data for use with [Amazon Athena](https://aws.amazon.com/athena). This time, we created four types of sample data in [QGIS](https://qgis.org) in advance.

We prepared GIS data for points, lines, and polygons in CSV (TSV format).
![img](https://day-journal.com/memo/images/try-089_01.png)

We prepared an additional 1 million points of GIS data in CSV (TSV format).
![img](https://day-journal.com/memo/images/try-089_02.png)

The four types of CSV (TSV format) were saved in S3 under an arbitrary name.
![img](https://day-journal.com/memo/images/try-089_03.png)

I have registered this sample data on GitHub, so please feel free to use it. 
https://github.com/dayjournal/data/tree/main/try-089

Now you're all set with your preliminary GIS data!

The name "geojson" is included in the S3 bucket name, but we will use CSV (TSV format) this time. Initially, I tried to use GeoJSON format, but currently, GeoJSON format is not supported (Hive JSON SerDe format is available), so I defined "WKT" in CSV (TSV format) this time.

<br>

## Set the query destination

This is how to set the query destination in Amazon Athena.

Prepare an S3 bucket with an arbitrary name for the query destination in advance.
![img](https://day-journal.com/memo/images/amazon-athena-001_01.png)

Click AWS Management Console → Athena.
![img](https://day-journal.com/memo/images/amazon-athena-001_02.png)

Click on "Check query editor for details.”
![img](https://day-journal.com/memo/images/amazon-athena-001_03.png)

Click on "View Settings.”
![img](https://day-journal.com/memo/images/amazon-athena-001_04.png)

Click "Manage.”
![img](https://day-journal.com/memo/images/amazon-athena-001_05.png)

Specify the S3 bucket where you want to save the query → Click "Save."
![img](https://day-journal.com/memo/images/amazon-athena-001_06.png)

The query destination is set.
![img](https://day-journal.com/memo/images/amazon-athena-001_07.png)

The query destination is now set!

<br>

## Table Creation

This is how to create a table in Amazon Athena. 

Click the Athena editor → Create Table and View → "S3 Bucket Data."
![img](https://day-journal.com/memo/images/amazon-athena-002_02.png)

Set table name, database selection, target S3 bucket specification, data format, and column settings. Check the preview → Click "Create Table."
![img](https://day-journal.com/memo/images/amazon-athena-002_03.png)

This time we created four arbitrary tables. Target table → Click "Preview Table."
![img](https://day-journal.com/memo/images/amazon-athena-002_04.png)

The retrieved records are displayed.
![img](https://day-journal.com/memo/images/amazon-athena-002_05.png)

Now your table creation is complete!

<br>

## Spatial Search

Finally, here is how to do a [spatial search](https://docs.aws.amazon.com/athena/latest/ug/geospatial-functions-list-v2.html) in Amazon Athena.

Let's retrieve the point of the center of gravity from a polygon. Download the resulting data.

```sql
SELECT "geojson-database"."geojson-polygon-table"."name", 
ST_Centroid(ST_GeometryFromText("geojson-database"."geojson-polygon-table"."wkt")) 
FROM "geojson-database"."geojson-polygon-table";
```

![img](https://day-journal.com/memo/images/try-089_04.png)

Visualize the downloaded data in QGIS to confirm the processed data.
![img](https://day-journal.com/memo/images/try-089_05.png)

<br>

Try to retrieve the starting point from the line. Download the result data.

```sql
SELECT "geojson-database"."geojson-line-table"."name", 
ST_StartPoint(ST_GeometryFromText("geojson-database"."geojson-line-table"."wkt")) 
FROM "geojson-database"."geojson-line-table";
```

![img](https://day-journal.com/memo/images/try-089_06.png)

Visualize the downloaded data in QGIS to confirm the processed data.
![img](https://day-journal.com/memo/images/try-089_07.png)

<br>

Try to get only the points included in the polygon. Download the result data.

```sql
SELECT "geojson-database"."geojson-point-table"."name", "geojson-database"."geojson-point-table"."wkt" 
FROM "geojson-database"."geojson-point-table", "geojson-database"."geojson-polygon-table" 
WHERE ST_Within(ST_GeometryFromText("geojson-database"."geojson-point-table"."wkt"), ST_GeometryFromText("geojson-database"."geojson-polygon-table"."wkt"));
```

![img](https://day-journal.com/memo/images/try-089_08.png)

Visualize the downloaded data in QGIS to confirm the processed data.
![img](https://day-journal.com/memo/images/try-089_09.png)

<br>

Try to get only the points included in 1 million polygons. The response time is fast even when searching a large amount of GIS data. Download the result data.

```sql
SELECT "geojson-database"."geojson-randompoint-table"."name", "geojson-database"."geojson-randompoint-table"."wkt" 
FROM "geojson-database"."geojson-randompoint-table", "geojson-database"."geojson-polygon-table" 
WHERE ST_Within(ST_GeometryFromText("geojson-database"."geojson-randompoint-table"."wkt"), ST_GeometryFromText("geojson-database"."geojson-polygon-table"."wkt"));
```

![img](https://day-journal.com/memo/images/try-089_10.png)

Visualize the downloaded data in QGIS to confirm the processed data.
![img](https://day-journal.com/memo/images/try-089_11.png)
![img](https://day-journal.com/memo/images/try-089_12.png)

By using Amazon Athena, a spatial search of data registered in S3 becomes possible!

<br>

Related Articles

{% link https://dev.to/aws-builders/spatial-search-with-amazon-redshift-and-dbeaver-3d2d %}

{% link https://dev.to/aws-builders/using-qgis-and-amazon-auroras-postgresql-postgis-to-display-location-data-5ecm %}

<br>

References
[Amazon Athena](https://aws.amazon.com/athena)
[Amazon S3](https://aws.amazon.com/s3)
[QGIS](http://www.qgis.org/ja/site/)