For one of my apps, I was responsible for the database, and there were some tricky things with Postgres! This post is part of a tutorial series on how to use Postgres!
Note: I also use Windows 10 and hosted my Postgres database on Digital Ocean, so I will be covering all the tricky parts I ran into!
This is the third post in the series, and this is on how to install and use PostGIS with Postgres via pgAdmin 4. This tutorial leaves off from Part 2: How to Install pgAdmin 4 on Windows 10.
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!