Creating a Geospatial Database Using PostGIS and PostgreSQL

This chapter will introduce you to PostGIS, a spatial extension for PostgreSQL that allows the user to store and spatially query geographic data. PostGIS is free and open source software. We will install the PostGIS extension for PostgreSQL, and then load spatial data into a database. Finally, after creating a spatial database, we will execute a few spatial queries that demonstrate some of its functionality.

Through the project demonstrated in this chapter, you will learn how to display a geographic map of a city in our banking PostgreSQL 12 RDS from AWS. Through PostGIS and QGIS, on the city map, all of the ATM locations will be marked and linked to an ATM network.

The following topics will be covered in this chapter:

• Installing PostGIS for RDS on AWS
• Importing spatial data files into PostgreSQL...

Technical requirements

This chapter will take developers around 10-12 hours of work to develop a PostGIS application.

The code files for this chapter are available at https://github.com/PacktPublishing/Developing-Modern-Database-Applications-with-PostgreSQL/tree/master/Chapter05.

Installing PostGIS for RDS on AWS

On AWS, PostGIS installation is done through SQL statements because RDS does not provide support for PostGIS installation by source. Therefore, it is quite simple to create the extensions required by PostGIS using a few CREATE EXTENSION statements in our RDS. The steps are as follows:

1. Use pgAdmin to connect to our ATM RDS on AWS and select the ATM database to install PostGIS.
2. Then navigate to the top menu bar Tools ⇒ Query Tool, and then execute the below SQL statements by pressing the Execute/Refresh icon (or pressing the F5 key) on the toolbar

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;

The following is a screenshot of the output after the first query is executed:

Figure 5.1 – Installing PostGIS for RDS on AWS

postgis_topology (PostGIS Topology) allows topological vector data to be stored in a PostGIS...

Importing spatial data files into PostgreSQL

For each of the ATM locations that we currently store inside our ATM database, we will have to supply the geographical data, including longitude and latitude data. After providing these geographical points for PostGIS, the extension will be able to show a world map with location points on it. The steps for this are as follows:

1. Download the zipcoordinates.sql file from GitHub at https://github.com/lequanha/PostgreSQL-12-Development-and-Administration-Projects/blob/master/Chapter 5/zipcoordinates.sql. On your browser, you will see that the script includes a CREATE TABLE statement and an INSERT statement to create and to populate a new table named Zip coordinates:

Figure 5.4 – GitHub file for spatial data

2. Next, execute this SQL file inside pgAdmin to create the new Zip coordinates database table inside the PostgreSQL RDS, as shown in the following screenshot:

Figure 5.5 – Creating the Zip coordinates table

3. Right...

Setting up QGIS

QGIS, previously known as Quantum GIS, is a free open source tool for viewing, editing, and analyzing geospatial data. To handle spatial data, follow these steps to establish a connection between PostGIS and QGIS:

1. Choose the below Amazon Machine Image (AMI): Microsoft Windows Server 2019 Base - ami-077f1edd46ddb3129. This AMI can be seen at the top in the following screenshot:

Figure 5.11 – AMI for Microsoft Windows Server

2. Launch a new EC2 instance from the AMI, as shown in the following screenshot:

Figure 5.12 – The new Microsoft Windows Server is ready for QGIS installation

3. Open the new EC2 instance using remote desktop connection, as shown in the following screenshot, and then download QGIS from https://qgis.org/en/site/forusers/download.html. The QGIS installation package is osgeo4w-setup-x86_64.exe:

Figure 5.13 – Download QGIS

4. Select the Express Desktop Install type for QGIS, as shown in the following screenshot...

Loading spatial data using QGIS

QGIS is a very common open source tool for GIS analysts, geographers, civil engineers, and many other professionals for creating maps and editing, viewing, and analyzing geospatial data. PostGIS is open source for PostgreSQL databases only, whereas QGIS is a wider open source spatial tool for many other databases, such as MSSQL, Oracle, and DB2. Hence, many developers have chosen QGIS as their GIS tool. In the following steps, we will see how PostGIS can be integrated into QGIS by setting a connection between them:

1. Right-click on PostGIS in the list under the Browser panel, as shown in the following screenshot, and select New Connection:

Figure 5.18 – QGIS main window

2. Fill in the RDS information, as shown in the following screenshot, click on the Basic tab for authentication, and then enter the following PostgreSQL database credentials:

• User name: dba
• Password: bookdemo

Figure 5.19 – Connecting QGIS to PostGIS

...

Executing PostGIS queries

In this section, we will run some spatial queries in pgAdmin to demonstrate some PostGIS functionality. We will go back to pgAdmin and get into our ATM database to try to run a couple of sample queries. The first query will list ATM locations by their proximity to the Brooklyn Bridge, and the second query will capture all of the ATM locations within 1 kilometer of another specific place, Times Square. 

Ordering ATM locations by distance from the Brooklyn Bridge

The steps for this are as follows:

1. We will use latitude 40.709677 and longitude -74.00365 for the Brooklyn Bridge in the following SQL statement:

SELECT atm."BankName", atm."Address"
FROM "ATM coordinates" atm
ORDER BY geog <-> ST_SetSRID(ST_MakePoint(-74.00365, 40.709677), 4326);

The result of the preceding query is shown in the following screenshot: 

Figure 5.21 – The ATM machines ordered by proximity to the Brooklyn Bridge

2. You can change the location from the Brooklyn Bridge to any other location to test your SELECT query; for instance, you could use New York City Hall (the latitude and longitude coordinates are 40.712772, -74.006058), the Rockefeller Center (latitude 40.758740, longitude -73.978674), and the Metropolitan Museum of Art (latitude 40.778965, longitude -73.962311).

Finding ATM locations within 1 kilometer of Times Square

The steps for this are as follows:

1. The second query is a common issue for every pedestrian. Suppose that you are visiting Times Square in New York City and you suddenly need to find the nearest ATM. We will use latitude 40.755101 and longitude -73.99337 for Times Square and use 1000 meters to form the following SQL statement:

SELECT atm."BankName", atm."Address"
FROM "ATM coordinates" atm
WHERE ST_DWithin(geog, ST_SetSRID(ST_MakePoint(-73.99337, 40.755101), 4326), 1000);

 The result of the preceding query is shown in the following screenshot:

Figure 5.22 – The ATMs nearest Times Square

We can also practice around with this query by changing Time Square to other locations into the preceding query such as New York City Hall, the Rockefeller Center, the Metropolitan Museum of Art, and the Brooklyn Bridge, and so on.

Summary

This chapter has introduced you to GIS and spatial data in PostgreSQL via a step-by-step project using the PostGIS extension. In this project, you first learned how to install PostGIS for RDS on AWS. Then, you learned how to declare and import spatial data files into PostGIS and how to show spatial data with the Geometry Viewer feature of PostGIS. After that, we also showed you another GIS open source variant named QGIS so that you can broaden your ideas about GIS, since QGIS is a wider tool that can also be applied to many other databases, such as Oracle, DB2, and MSSQL databases. Finally, we concluded the chapter with a couple of sample spatial queries for ATM locations in New York City.

In the next chapter, we will study PostgREST, a RESTful API for PostgreSQL databases.