DEV Community ๐Ÿ‘ฉโ€๐Ÿ’ป๐Ÿ‘จโ€๐Ÿ’ป

DEV Community ๐Ÿ‘ฉโ€๐Ÿ’ป๐Ÿ‘จโ€๐Ÿ’ป is a community of 963,673 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
Cover image for Using QGIS and Amazon Aurora PostgreSQL & PostGIS to Display Location Data
Yasunori Kirimoto for AWS Community Builders

Posted on • Updated on

Using QGIS and Amazon Aurora PostgreSQL & PostGIS to Display Location Data



I used QGIS and Amazon Aurora PostgreSQL & PostGIS to display location data ๐ŸŽ‰

I remembered that I wrote an article, "I tried to display location data using QGIS and Cloud SQL PostgreSQL & PostGIS," about two years ago. This time, I tried to build PostgreSQL & PostGIS database with Amazon Aurora and display it with QGIS!

Configure Amazon Aurora

To begin, create a database in Amazon Aurora.

In the AWS console, go to Amazon RDS and click "Create Database." Next, select Standard Creation, Amazon Aurora, PostgreSQL, Provisioned, PostgreSQL 13.3, and Development/Testing.
img

Set the DB cluster name, user name, and password. Otherwise, set to default.
img

Set public access, create a new security group, and put the initial database name. All other settings are default. Although it will not be used in the production environment, it can be accessed directly from the local PC by setting it to Public Access.
img

After the database is created, click Cluster.
img

Copy the endpoint as it will be needed for the host information.
img

This completes the configuration of Amazon Aurora ๐Ÿ‘

Configure PostGIS

The next step is to install PostGIS and import the GIS data.

This time we will use DBeaver to connect to Amazon Aurora. Set the endpoint, port, database name, user name, and password you copied to the hostname.
img

After connecting to Amazon Aurora, install PostGIS.

CREATE EXTENSION postgis;
Enter fullscreen mode Exit fullscreen mode

Verify that it is installed.

SELECT postgis_version();
Enter fullscreen mode Exit fullscreen mode

img

As for importing data, I imported 1 million point data.
Regarding the import method, I imported the data with the DB manager of QGIS. Still, I could not import the data correctly with Amazon RDS as I wrote in the previous article. This time, I used "shp2pgsql" to import the data.
img

This completes the configuration of PostGIS ๐Ÿ‘

Display in QGIS

Finally, connect to Amazon Aurora in QGIS and display the data.

Layer โ†’ Data Source Manager
Set the endpoint, port, database name, user name, and password that you copied to the hostname, and connect to Amazon Aurora.
img

Select and add the imported data.
img

You can see one million data displayed in real-time.
img

I was able to display location data using QGIS and Amazon Aurora PostgreSQL & PostGIS ๐Ÿ‘

By using QGIS and Amazon Aurora, I checked the data directly from my local PC. This seems to be a good use when you want to share data within your company, etc.! However, I think the disadvantage is a pay-as-you-go fee, unlike preparing a DB locally.

I want to try PostgreSQL & PostGIS in Amazon RDS in the future ๐Ÿ’ช

Top comments (0)

Take a look at this:

Settings

Go to your customization settings to nudge your home feed to show content more relevant to your developer experience level. ๐Ÿ›