For this post, I referenced a great Youtube video called Boston PostgreSQL Group: Introduction to PostGIS Part 1 of 4. This video was a great resource, and they have a great website with a great tutorial. But tech advances quickly, and the video is from 2013, so a lot has already changed! The basics are still there, but my blog post should be a quick and updated guide for 2017. I also ran into small blips with Postgres 9.6 and pgAdmin 4, so I cover how to get around those blips as well!
Part 3: How to Install and Use PostGIS with Postgres via pgAdmin 4
Open pgAdmin 4
Right-click on PostgreSQL 9.6 and select “Create Database”
Name the database (I named mine ‘postgis_test’). I kept the user (owner) as the main user ‘postgres’
You will now see the database you created under “Databases”
Click on the name of your database. For example, I clicked on ‘postgis_test.’ Submenus come up. Right-click on ‘Extensions’. A dropdown menu will appear. Select ‘Create’. Then select ‘Extension’.
A screen will pop up. Click into the ‘Name’ section. It will load a list of extensions you can add to your Postgres database. You can type in or you can select the ‘postgis’ extension
You could have ran the SQL command for this as well, which you can see when you click on the ‘SQL’ tab of this pop up.
Click ‘Save’. Now in the left hand file tree, you should see under extensions ‘plpgsql’ and ‘postgis’
In older versions, there was an easy way to access the Postgis GUI via pgAdmin 4, but when I downloaded it (they may have fixed it by now), there wasn’t a command or menu to get to the Postgis GUI, so if you are like me, and you cannot find the Postgis GUI in pgAdmin, go to Local Disk (C:) > Program Files > PostgreSQL > 9.6 > bin > postgisgui. Double-click shp2pgsql-gui.exe.
The PostGIS GUI (or Shapefile Import/Export Manager) loads up. It looks something like this:
Next you want to load your shape files. I was following the Youtube tutorial, so I got my shape files from the Massachusetts MassGIS website. Like the tutorial, I downloaded the shape files for MBTA Bus Routes and MBTA Rapid Transit. Then I clicked “Add File” and added both shape files to my Import List.
Like the tutorial, make sure the SRID is filled in! For Massachusetts, it had a SRID of 26986.
Next click “Import”. The PostGIS GUI will then ask your PostGIS connection credentials. For some reason, it selects your Windows username by default! So change it to your database owner username (You can see I received error messages for not realizing this)! In this current example, my database owner username is postgres (which is the default Postgres database owner). Make sure to type in the correct password, host, port and database name as well.
Once you have been authenticated, the shapefiles will import. They will show similar completed messages when you are done:
Now the shapefiles are tables you can use with your Postgres database! You can go back to pgAdmin 4. On the file tree on the left, double-click ‘Schemas.’ More options appear. Double-click on ‘public.’ Then double click on ‘Tables’. You will see that your shape files are now tables! You can now view the entries. You can right-click on a table such as mbta_arc, then select ‘View/Edit Data’. Then select ‘First 100 Rows.’
Once the query has been executed, a new tab on the top of pgAdmin 4 will appear with the data returned:
And there you have it! Your PostGIS shapefiles have been turned into Postgres tables.
The next post in the series will be how to use qGIS to render your PostGIS data (post to come).
Click here for more tutorials related to PostgreSQL.
If you have any questions, feel free to leave a comment below!
pgAdmin 4 is graphical interface for interacting with PostgreSQL databases. It is an alternative to the terminal or pSQL console. You can read more about it at the pgAdmin website.
Part 2: How to Install pgAdmin 4 for Windows 10
Now the pgAdmin 4 installer is up. Click Next:
The wizard will then ask you to read over the license agreement. You should agree and accept. Then click next.
It will then ask where you want to save your program files for pgAdmin 4. I keep it in the default/suggested folder. Once done, select next:
Now it will start installing
When it’s done installing, you can select Launch pgAdmin 4 and Finish
pgAdmin 4 may take some time to launch, and when it does, it will look something like:
There will be a default PostgreSQL server on your system. You can access it on pgAdmin. On the left hand Browser, click the + next to Servers, and it will show the server. It will named something like PostgreSQL 9.6. For PostgreSQL servers, there are always a default user called “postgres”. For Windows computers, you will need to create a password for this default user (remember to keep the password).
Using pgAdmin 4, you can now explore your PostgreSQL server that hosts your database. It will be pretty empty.
Click on the link to your operating system. I selected Windows.
On this new page, you have 2 options. You can download Postgres via EnterpriseDB or BigSQL. I used the interactive installer by EnterpriseDB because it easily allows you to download extensions (such as PostGIS) right after downloading PostgreSQL and pgAdmin 4.
It takes you to the EnterpriseDB page to download. Select the version of Postgres (I selected 9.6.5 because it was the newest at the time).
Select your operating system.
Then click download.
Once completed, it will download PostgreSQL. (It may download Microsoft Visual C++ first).
If the installer asks you, please make sure to select to download pgAdmin 4 and Stack Builder (so you can download PostGIS).
You know you are finished with installing PostgreSQL when an installer for pgAdmin 4 comes up.
This edition of Manatee Mistakes is about the error I encountered:
Error: listen EADDRINUSE :::8080
I was running node (or nodemon to be precise) in GitBash. I thought my selected window was Sublime, so I pushed control + z to undo some typed text. However, I was actually in GitBash, and the control + z was executed in GitBash. This ungracefully closed my nodemon.