Using QGIS and Amazon Aurora PostgreSQL & PostGIS to Display Location Data
Tags: amazonaurora, postgis, qgis, foss4g
Published: 2021-11-03 12:33:06

![](https://day-journal.com/memo/images/try-066_00.png)
<br>

### I used QGIS and Amazon Aurora PostgreSQL & PostGIS to display location data :tada:  

I remembered that I wrote an article, "[I tried to display location data using QGIS and Cloud SQL PostgreSQL & PostGIS](https://day-journal.com/memo/try-023)," about two years ago. This time, I tried to build PostgreSQL & PostGIS database with [Amazon Aurora](https://aws.amazon.com/rds/aurora/?aurora-whats-new.sort-by=item.additionalFields.postDateTime&aurora-whats-new.sort-order=desc) and display it with [QGIS](http://www.qgis.org)!
<br>

## 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](https://day-journal.com/memo/images/try-066_01.png)

Set the DB cluster name, user name, and password. Otherwise, set to default.
![img](https://day-journal.com/memo/images/try-066_02.png)

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](https://day-journal.com/memo/images/try-066_03.png)

After the database is created, click Cluster.
![img](https://day-journal.com/memo/images/try-066_04.png)

Copy the endpoint as it will be needed for the host information.
![img](https://day-journal.com/memo/images/try-066_05.png)

This completes the configuration of Amazon Aurora :thumbsup: 
<br>

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

This time we will use [DBeaver](https://dbeaver.io) to connect to Amazon Aurora. Set the endpoint, port, database name, user name, and password you copied to the hostname.
![img](https://day-journal.com/memo/images/try-066_06.png)

After connecting to Amazon Aurora, install PostGIS.

```sql
CREATE EXTENSION postgis;
```

Verify that it is installed.

```sql
SELECT postgis_version();
```

![img](https://day-journal.com/memo/images/try-066_07.png)

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](https://day-journal.com/memo/try-023). This time, I used "shp2pgsql" to import the data.
![img](https://day-journal.com/memo/images/try-066_08.png)

This completes the configuration of PostGIS :thumbsup: 
<br>

## 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](https://day-journal.com/memo/images/try-066_09.png)

Select and add the imported data.
![img](https://day-journal.com/memo/images/try-066_10.png)

You can see one million data displayed in real-time.
![img](https://day-journal.com/memo/images/try-066_00.gif)
<br>

#### I was able to display location data using QGIS and Amazon Aurora PostgreSQL & PostGIS :thumbsup: 

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 :muscle: