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.”
Specify the S3 bucket where you want to save the query → Click "Save."
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"));
Visualize the downloaded data in QGIS to confirm the processed data.
By using Amazon Athena, a spatial search of data registered in S3 becomes possible!
Related Articles
Spatial Search with Amazon Redshift and DBeaver
Yasunori Kirimoto for AWS Community Builders ・ Dec 3 '21
Using QGIS and Amazon Aurora PostgreSQL & PostGIS to Display Location Data
Yasunori Kirimoto for AWS Community Builders ・ Nov 3 '21
References
Amazon Athena
Amazon S3
QGIS
Top comments (0)