DEV Community

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

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

img

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).
img

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

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

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.
img

Click AWS Management Console → Athena.
img

Click on "Check query editor for details.”
img

Click on "View Settings.”
img

Click "Manage.”
img

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

The query destination is set.
img

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."
img

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

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

The retrieved records are displayed.
img

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("geojson-database"."geojson-polygon-table"."wkt") 
FROM "geojson-database"."geojson-polygon-table";
Enter fullscreen mode Exit fullscreen mode

img

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

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";
Enter fullscreen mode Exit fullscreen mode

img

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

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("geojson-database"."geojson-point-table"."wkt", "geojson-database"."geojson-polygon-table"."wkt");
Enter fullscreen mode Exit fullscreen mode

img

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

Try to retrieve only the points included in 1 million polygons. The response time is fast even when recovering 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("geojson-database"."geojson-randompoint-table"."wkt", "geojson-database"."geojson-polygon-table"."wkt");
Enter fullscreen mode Exit fullscreen mode

img

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

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

Related Articles

References
Amazon Athena
Amazon S3
QGIS

Discussion (0)