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.
- 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:
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 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.
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.
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.
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.
- 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.
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.