Open Source GIS Implementation - Part 2 PostgreSQL/PostGIS Installation

This post is part 2 of my Open Source GIS Implementation Experiment. Part 1 was installing QGIS, an open source desktop GIS - download pdf.

Thoughts on the installation process

I tried both installers from both EnterpriseDB and BigSQL to install PostgreSQL 9.6. Both installers include the option to install PostGIS 2.3.1 bundle. Although the EnterpriseDB included the current pgAdmin 4 and the BigSQL installer package I used included pgAdmin 3 LTR. The installation process was fairly easy. After testing and playing around I decided to use the EnterpriseDB installer to install PostgreSQL 9.6 with PostGIS bundle 2.3.1. I then did a separate installation of pgAdmin 3.

Thoughts on pgAdmin 4

On the laptop pgAdmin did not work the first time I tried it. Just said "loading" for a long time before I manually ended the process. The second time worked but the display resolution was horrible on my laptop's very high resolution (3200x1800) screen. The pgAdmin 4 GUI was tiny; text and graphics were so small I couldn't see anything. My laptop OS is Windows 10 so I'm not sure if it's just and issue particular to Windows 10 or not. I tried changing the program's properties to "Disable display scaling on high DPI settings "as well as "Run this program in compatibility mode for" for Windows 7 and 8; these options didn't seem to make a difference.

PgAdmin 4 on the Windows Server had no display resolution problem. However, it was very slow to start up. Slow start up was an issue on the laptop as well. I started application a few times and everytime it took anywhere from 1-5 minutes or it just hangs. At this point, I'd rather stick with pgAdmin 3 for now.

Installation Notes

  • PostgreSQL must be installed first before PostGIS. Versions for both should match - e.g. if you are using the x64 PostgreSQL make sure you also use the x64 PostGIS installer too
  • A caveat about BigSQL installer - it will install pgAdmin 3 but it does not include an option to install a sample PostGIS database, which I find useful for doing a quick test since it already has most of the extensions enabled. It also doesn't include shp2pgsql-gui to import shapefiles to PostGIS - you have to do this manually - see section on Installing pgAdmin 3 below.
  • The EnterpriseDB installer does include shp2pgsql-gui but it is not added to pgAdmin 4 as a plugin like with pgAdmin 3. It is a separate application under the PostGIS Bundle 2.3 in the startup menu. If you don't see it in your startup menu, it should be where you install PostgreSQL (e.g. C:\Program Files\PostgreSQL\9.6\bin\postgisgui).
  • See PostgreSQL website for more information

Install PostgreSQL/PostGIS using EnterpriseDB Installer

Download the PostgreSQL 9.6.1 from EnterpriseDB site. I am using the x64 installer. After downloading PostgreSQL, start the installation and follow prompts:    

 Specify installation folder or use the default  

Specify installation folder or use the default  

 Specify data folder or use default  

Specify data folder or use default  

  Enter a password for superuser postgres. You can use the default user/password of postgres/postgres for now and change it later.

 Enter a password for superuser postgres. You can use the default user/password of postgres/postgres for now and change it later.

 Use the default port 5432 or enter one that is not being used. If you don't know then leave as default

Use the default port 5432 or enter one that is not being used. If you don't know then leave as default

 Advanced Options: Choose default locale

Advanced Options: Choose default locale

 After completion it will ask if you want to launch Stack Builder – check the box and click Finish. This will lauch Stack Builder

After completion it will ask if you want to launch Stack Builder – check the box and click Finish. This will lauch Stack Builder


Stack Builder: Download and install PostGIS 2.3 bundle 

Wait for the Stack Builder to Launch. Once it does follow the prompts to install PostGIS.

 After the Stack Builder launches, select PostgreSQL 9.6 (x64) on port 5432

After the Stack Builder launches, select PostgreSQL 9.6 (x64) on port 5432

 Expand Spatial Extensions and check the appropriate PostGIS bundle to download and install

Expand Spatial Extensions and check the appropriate PostGIS bundle to download and install

After downloading finishes, start the installation process. Note: you may want to check Skip Installation if the profile you are using to do the installation do not have administrator rights, in which case you skip installation and go to your PostGIS download directory (e.g. C:\Users\Stephanie) and run the installation as an administrator.  

 Check the box to Skip Installation if you don't have administrative rights to do installations on your computer. Leave uncheck to initial installation process immediately

Check the box to Skip Installation if you don't have administrative rights to do installations on your computer. Leave uncheck to initial installation process immediately

 Choose Components: Select PostGIS and Create Spatial database (if you want a sample db to be created for testing or to use as a template)

Choose Components: Select PostGIS and Create Spatial database (if you want a sample db to be created for testing or to use as a template)

 Chose a destination folder or use the default

Chose a destination folder or use the default

 Database Connection:   Username: postgres   Password: postgres (or whatever you entered above)   Port: 5432 (or whatever you entered above)  

Database Connection:
  Username: postgres
  Password: postgres (or whatever you entered above)
  Port: 5432 (or whatever you entered above)  

 If you chose to create a sample database then enter a name: use default or enter new name then click Install

If you chose to create a sample database then enter a name: use default or enter new name then click Install

 Register GDAL_DATA environment – say yes. This will automatically add GDAL_DATA environment variable putting this path in for you. If you use GDAL already chances are you already have this environment variable set and you may not want to overwrite it.  

Register GDAL_DATA environment – say yes. This will automatically add GDAL_DATA environment variable putting this path in for you. If you use GDAL already chances are you already have this environment variable set and you may not want to overwrite it.  

 Enable Raster Drivers (default option is off) – say yes  

Enable Raster Drivers (default option is off) – say yes  

 Enable Out of Database Raster – say yes  

Enable Out of Database Raster – say yes  

Close the window when PostGIS installation complete. Also close the Stack Builder window when Finish.

Successful installation of PostgreSQL and PostGIS bundle. The EnterpriseDB installer installs pgAdmin 4 as part of PostgreSQL 9.6 menu - there is no pgAdmin 3. Notice PostGIS 2.0 Shapefile to Database is a separate application under PostGIS bundle menu. 

2017-01-30_112851.jpg

Here is a screenshot of pgAdmin 4 on Windows Server

Here is a screenshot of pgAdmin 4 on Windows 10 Laptop. I have a high resolution monitor. Everything on the GUI is really tiny making it impossible to read or even use. Changing the screen resolution or the option to use the application in compatibility mode didn't make a difference.


Installing pgAdmin 3

The screenshots above showed the pgAdmin 4 GUI. However, as I noted earlier not only was it slow to start - basically start up was inconsistent. Sometime it would start up right away, sometimes it took a few minutes, and sometimes it would just hang. It was true for me on both the Windows 10 laptop and Windows Server. You may have a different experience with pgAdmin 4 but for now I will install and use pgAdmin 3.

Download pgAdmin 3. I used version 1.22.2. It looks like it is available only as x32 bit. I did not see a version for x64 bit. Unzip the file and start installation.

 Custom Setup: use the default options or make changes as needed

Custom Setup: use the default options or make changes as needed

 After sucessful installation, check to see if pgAdmin III is in your start up menu

After sucessful installation, check to see if pgAdmin III is in your start up menu

Below is a screenshot of pgAdmin 3 on Windows Server. Note: I ended up with 2 "servers". PostGIS and PostgreSQL 9.6. Connect to the Server. I deleted the one that said PostGIS since it doesn't contain the sample db I wanted. 

After connecting to the Server, I can see the sample database (e.g postgis_23_sample) created during the installation process earlier.


Manually add/enable shp2pgsql-gui plugin

This part is optional. You should only do this portion if you want to be able to access the shapefile loader while in pgAdmin III -  But if you want to access the application as a plug-in when you are using pgAdmin, then this is how you would manually add/enable it for pgAdmin 3. If you have installed PostgreSQL with the PostGIS bundle then you should already have the shp2pgsql-gui application installed. It is a separate application under the PostGIS bundle program menu, called PostGIS Shapefile and DBF Loader -  you can access it in your the startup menu.

  • Open up pgAdmin 3 and check the Plugins menu, noticed there is only the PSQL Console and no PostGIS Shapefile Loader.  

 

  • Check to see if you have the postgisgui folder in your PostgreSQL's bin directory (e.g. C:\Program Files\PostgreSQL\9.6\bin\). If you used EnterpriseDB installer to install PostgreSQL with the PostGIS bundle then you should have a postgisgui folder on your computer.

Note: if you don't have this postgisgui folder then you can download the PostGIS bundle, unzipped it, and copy the postgisgui folder to the bin directory of your PostgreSQL installation. Make sure you download the correct version of the zipped file.

  • Find the plugins.d folder in your pgAdmin 3 installation folder (e.g C:\Program Files \(x86\)\pgAdmin III\1.22\plugins.d\).
  •  In this folder there is a plugins.ini file. Change the permission on it so you can edit it
  • Right click on file >> Properites >> Security tab >> Users >> Edit >> check box for Full Control or Modify and Write option.
 Change the permission on the plugins.ini file so you can edit it

Change the permission on the plugins.ini file so you can edit it

  • After changing the file permission, open plugins.ini in a text editor such as Notepad++ or Notepad and enter the following text near the end of file then save it.

Note: The text below comes from the postgis.shp2pgsgql-gui.ini file that is part of the downloaded PostGIS zipped bundle (e.g. C:\Downloads\postgis-bundle-pg96-2.3.1x64\pgAdmin III\plugins.d\postgis.shp2pgsql-gui.ini)

;
; PostGIS shp2pgsql-gui (Windows):
;
Title=PostGIS Shapefile and DBF loader 2.2
Command="$$PGBINDIR\postgisgui\shp2pgsql-gui.exe" -h "$$HOSTNAME" -p $$PORT -U "$$USERNAME" -d "$$DATABASE" -W "$$PASSWORD"
Description=Open a PostGIS ESRI Shapefile or Plain dbf loader console to the current database.
KeyFile=$$PGBINDIR\postgisgui\shp2pgsql-gui.exe
Platform=windows
ServerType=postgresql
Database=Yes
SetPassword=Yes

Here is an example shown in Notepad++. The highlighted portion is the inserted code.  

  • Open pgAdmin 3 and go to File menu >> Options.
  • In the Options window, under Browser, click Binary Paths. Where it says PG Bin Path - browse to or input the path to your PostgreSQL bin directory (e.g. C:\Program Files\PostgreSQL\9.6\bin).
  • Click OK when done.

After you do the above, the PostGIS Shapefile and DBF Loader (i.e. shp2pgsql-gui application) should be in the Plugins menu. If it's not there then try closing pgAdmin 3 and reopening it.  

Here is what the PostGIS Shapefile and DBF Loader application looks like.  

 The shp2pgsql loader gui

The shp2pgsql loader gui

That is all for part 2 of the open source implementation process. Part 3 will cover how to install GeoServer and how to import data from PostGIS.