Set Up the PostgreSQL Database

Jackie Wilson
Build a Location-Based Web App With Django and GeoDjango Jackie Wilson 08:59
Recommended Tutorial Ask a Question

Welcome back! You’re ready to build the foundation for a GIS enabled web application. This video will walk you through installing the necessary components for our app, starting with our database system. For review, these are the components of our application:

The data is stored in PostGreSQL with the PostGIS extension. Because we have a GIS enabled database, we will also want a GIS-enabled model, which is able to recognize and work with geographic queries. We can achieve this on our Django web application by enabling the GeoDjango framework.

GeoDjango needs a few dependencies to operate correctly, such as GDAL, GEOS and PROJ.4. These drivers can be installed individually, but getting them to install and work correctly can be tricky.

Since the objective of this tutorial is to get you up and running quickly with a simple location-aware app, we will install an open-source GIS application called QGIS, which just happens to install these drivers correctly and efficiently. The only caveat is, at the time of this video recording, QGIS does not yet work with Python 3.7 so if you don’t have it installed already you’ll need a copy of Python 3.6 on your system as well.

You’ll be installing our components in this order: PostgreSQL with PostGIS extension, followed by a copy of Python 3.6 (if you don’t have it already installed). After that, QGIS (so we have an easy driver installation), then Django through Pip followed by enabling GeoDjango. And if you stick around, I’ve also included a bonus video on adding Leaflet mapping to your web page.

00:00 Welcome back. You’re ready to build the foundation for a GIS-enabled web application. This video will walk you through installing the necessary components for our app, starting with the database system. For review, these are the components of our application.

00:18 The data is stored in PostgreSQL with the PostGIS extension. Because we have a GIS-enabled database, we will also want a GIS-enabled model, which is able to recognize and work with geographic queries.

00:33 We can achieve this on our Django web application by enabling the GeoDjango framework. GeoDjango needs a few dependencies to operate correctly, such as GDAL, GEOS, and PROJ.4. These drivers can be installed individually, but getting them to install and work correctly can be tricky.

00:53 Since the objective of this tutorial is to get you up and running quickly with a simple location-aware app, we will install an open-source GIS application called QGIS, which just happens to install these drivers correctly and efficiently.

01:08 The only caveat is at the time of this recording, QGIS does not yet work with Python 3.7. So if you don’t have it installed already, you’ll need a copy of Python 3.6 on your system as well.

01:23 You’ll be installing your components in the following order: PostgreSQL with PostGIS extension; followed by a copy of Python 3.6 if you don’t have it already installed; after that, QGIS so that we have an easy driver installation;

01:44 then Django, through pip; followed by enabling GeoDjango. And if you stick around, I’ve also included a bonus video on adding Leaflet mapping to your webpage.

02:00 Let’s get started with the PostgreSQL installation. You’ll start at the PostgreSQL website at postgresql.org. From there, click the Download button on the home page, followed by your operating system.

02:18 Then follow the link to download the installer. You will typically not want to install the newest version. At the time of this recording, version 11.2 is relatively new, so let’s choose 10.7.

02:46 Once the installer has finished downloading, launch it. If you get a security warning, make sure to whitelist the installer in your system settings.

03:00 When the wizard opens, choose Next and keep defaults for installation folder components and Data Directory. You’ll then need to choose a password for the Postgres superuser account. I’m going to choose a password 123456789.

03:16 By default, PostgreSQL listens on port 5432. Accept defaults for Locale, confirm settings, and finish the installation.

03:29 When the install completes, you’ll be prompted to launch Stack Builder. This is how we’re going to add PostGIS, so leave this selected and click Finish. When Stack Builder launches, it will find your Postgres instances.

03:43 You should see your new install in the dropdown list. If you had a remote installation of Postgres, say on AWS, you’d configure that by choosing remote and entering your ARN and credentials.

03:54 But since we’re configuring our local instance, choose that one. We are using Stack Builder to add PostGIS to our PostgreSQL installation. You will find this option under the category Spatial Extensions.

04:08 Select the check box and click Next.

04:16 Make note of the PostGIS version and click Next again to complete the download of the installer. When the Setup Wizard starts, click Next, then continue past the Upgrade Mode option without selecting it.

04:33 On the next screen, enter your password for the Postgres superuser account that you created earlier, which for me was 123456789.

04:46 Let the installation proceed and when complete, click Finish to exit both the PostGIS installation and the Stack Builder wizards.

05:03 Once installed, you will find a PostgreSQL folder in your start menu or applications folder depending on your system. Open it, notice the Stack Builder is there if we ever need it again, but launch the application called pgAdmin 4. This will give you a web-based admin console for your PostgreSQL application and databases.

05:28 When it opens, you’ll see in the left pane a navigation hierarchy of your PostgreSQL resources. To expand your new server, you’ll need to once again enter your Postgres superuser password.

05:45 Below your server, you’ll have three categories. Let’s look inside Databases. You’ll have a postgres database by default to store your administrative tables, but you’ll also have a database called template_postgis, which is a sample to show you how the PostGIS extension is applied.

06:04 If we look inside, we can see the first effect by expanding Extensions. Also, under Schemas, which is where our PostgreSQL tables are stored, you can expand Functions.

06:21 This will show the additional spatial functions provided by the PostGIS extension. If we want to have GIS capabilities in a database, we have to add the extension per database.

06:33 You’ll see that the PostGIS extension is not added to the admin database.

06:41 Next, you need to create a new database to hold the shop data. Let’s call it shops and we’ll use the pgAdmin console to set it up. To create a new database, simply right-click databases and choose Create.

06:57 You’ll be prompted for a name, type in shops, and then click Save. The shops database is created, but it’s not quite live and we still have to configure it with the PostGIS extension.

07:13 There are a few ways to add the PostGIS extension to shops. Because we’re using pgAdmin, which is a graphical admin interface, one way is to expand our shops database, right-click Extensions, choose Create > Extension, and then select postgis from the list.

07:36 Before choosing Save, notice the SQL that was generated as a result of this action. We could perform the same thing by executing this SQL query if we chose to. After adding the extension, if we expand our database in the admin tool and look under Extensions, we now see postgis. In addition, we now see spatial functions under Schemas > Functions.

08:04 Now I want to make you aware of something that tends to confuse new users of pgAdmin. If you close your browser window and then attempt to return to pgAdmin by relaunching it from the app folder, you’ll find that it doesn’t open and you might mistakenly think that you have to kill the process to restart it.

08:22 Never fear. You should see an elephant head in your task bar, such as the one on this screen. Clicking that will return you to a session in the admin tool.

08:32 Hopefully, you’ve made it all the way through this video and now have a working installation of PostgreSQL with PostGIS support added in. If so, congratulations!

08:41 You now have a powerful tool that can serve as a data store for many projects to come. We haven’t finished this project yet, however, so join me in the next video so we can get QGIS installed and make sure our GIS drivers are working properly. See you there.

Avatar image for rootxy

rootxy on April 30, 2019

Thank you!

Avatar image for Stephen Paden

Stephen Paden on July 25, 2019

I accidentally ran it with upgrade checked, and now I don’t see the example DB in pgadmin. Is that a dealbreaker?

Avatar image for Stephen Paden

Stephen Paden on July 25, 2019

Nevermind, I broke out the Mac Mini and it went in np. Now my problem is downgrading to 3.6.

Avatar image for Stephen Paden

Stephen Paden on July 25, 2019

Yeah, I don’t know how to install Python 3.6 from the source apparently (even though I did the ./configure, make, and make install. I’m going to have to pass on this tutorial unfortunately.

Avatar image for kilodalton

kilodalton on Aug. 8, 2019

The tool pyenv is an easy way to install different Python versions on one machine. https://github.com/pyenv/pyenv

Avatar image for lesgod

lesgod on June 11, 2020

Hello I Have a problem. I dont now how to deal with it

“could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host “localhost” (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host “localhost” (127.0.0.1) and accepting TCP/IP connections on port 5432?”

Avatar image for Dr Andrea Kocsis

Dr Andrea Kocsis on Feb. 27, 2023

Can I connect these three in conda?

Avatar image for Dr Andrea Kocsis

Dr Andrea Kocsis on Feb. 27, 2023

update: I could. I used the following tutorial then conda forged the other packages (not separately, but together with the qgis package to overcome dependency issues). gist.github.com/gwangjinkim/f13bf596fefa7db7d31c22efd1627c7a Works like a (py)charm.

Avatar image for ross157

ross157 on Oct. 19, 2023

If you are running through this tutorial and discover pgAdmin will not show template_postgis don’t be concerned. Under Preferences Browser/Display scroll down to the option “Show template databases?”

While a nice option, not sure the default should be “don’t show.”

Avatar image for Mercy Orangi

Mercy Orangi on Feb. 14, 2024

Thanks @ross157 ! I was stuck there for a moment! I also wonder why don’t show is the default.

Become a Member to join the conversation.