In advance, I used QGIS to prepare random points (1 million in blue) and polygons for spatial search (three in yellow) in shapefile format, a standard supported by Amazon Redshift.
To begin, we will create an Amazon Redshift cluster.
This completes the creation of the Amazon Redshift cluster 👍
Next, we will configure the role settings for accessing S3 from Amazon Redshift and the public access settings for connecting to Amazon Redshift from DBeaver.
This will complete the configuration of roles and public access 👍
Next, we will use DBeaver to connect to Amazon Redshift and import location data.
Let's import a random point (1 million points). Create a table in advance, specify the role and destination of the shapefile to be imported, and execute.
CREATE TABLE points ( wkb_geometry GEOMETRY, id BIGINT ); COPY points FROM 's3://redshift-geo-data/points.shp' FORMAT SHAPEFILE CREDENTIALS 'aws_iam_role=arn:aws:iam::xxxxxxx:role/redshift-geo-role-2112';
Let's try importing the three polygons for spatial search. Create a table in advance, specify the destination and role of the shapefile to be imported, and execute.
CREATE TABLE polygon ( wkb_geometry GEOMETRY, id BIGINT ); COPY polygon FROM 's3://redshift-geo-data/polygon.shp' FORMAT SHAPEFILE CREDENTIALS 'aws_iam_role=arn:aws:iam::xxxxxxx:role/redshift-geo-role-2112';
This completes the import of location data in DBeaver 👍
Finally, we will try to see if we can perform spatial functions in Amazon Redshift. As a typical example, we will use "ST_AsGeoJSON" to convert data into GeoJSON format and "ST_Within" to execute a spatial function that extracts points within a polygon from 1 million points.
First, let's use "ST_AsGeoJSON" to convert the data to GeoJSON format.
SELECT ST_AsGeoJSON(wkb_geometry) FROM public.polygon;
Next, try extracting the points inside the polygon using "ST_Within."
SELECT public.points.id, public.points.wkb_geometry FROM public.points, public.polygon WHERE ST_Within(public.points.wkb_geometry, public.polygon.wkb_geometry);
Next, use "ST_Within" to extract the count of points within the polygon.
SELECT COUNT(*) FROM public.points, public.polygon WHERE ST_Within(public.points.wkb_geometry, public.polygon.wkb_geometry);
Finally, I would like to check if the spatial search results match those using QGIS.
Using Amazon Redshift and DBeaver, we confirmed that we could import location data and perform a spatial search with Amazon Redshift. It looks like it can be used successfully for location data analysis in the future.
Initially, I’ve tried to use Query Editor V2, which is available in the service, but it hasn’t seemed to support location data import or spatial functions at present. If it is supported in the future, it may become easier to use, or it may become easier to use with Amazon Redshift Serverless, whose preview version was announced this week 👍