Yasunori Kirimoto for AWS Community Builders

Posted on May 16, 2022 • Edited on Dec 18, 2023

Spatial Search of Amazon S3 Data with Amazon Athena and Visualized It in QGIS

#athena #s3 #qgis #aws

I tried a spatial search of Amazon S3 data with Amazon Athena and visualized it with QGIS 🎉

The following is a detailed explanation.

Advance Preparation
Set the query destination
Table Creation
Spatial Search

Advance Preparation

Prepare GIS data for use with Amazon Athena. This time, we created four types of sample data in QGIS in advance.

We prepared GIS data for points, lines, and polygons in CSV (TSV format).

We prepared an additional 1 million points of GIS data in CSV (TSV format).

The four types of CSV (TSV format) were saved in S3 under an arbitrary name.

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.

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.

Click AWS Management Console → Athena.

Click on "Check query editor for details.”

Click on "View Settings.”

Click "Manage.”

Specify the S3 bucket where you want to save the query → Click "Save."

The query destination is set.

The query destination is now set!

Table Creation

This is how to create a table in Amazon Athena.

Click the Athena editor → Create Table and View → "S3 Bucket Data."

Set table name, database selection, target S3 bucket specification, data format, and column settings. Check the preview → Click "Create Table."

This time we created four arbitrary tables. Target table → Click "Preview Table."

The retrieved records are displayed.

Now your table creation is complete!

Spatial Search

Finally, here is how to do a spatial search in Amazon Athena.

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



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

Visualize the downloaded data in QGIS to confirm the processed data.

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



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

Visualize the downloaded data in QGIS to confirm the processed data.

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



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

Visualize the downloaded data in QGIS to confirm the processed data.

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.



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