Blog — open.gis.lab

Continuing on my open source GIS server experiment, part 3 goes over how to create a PostGIS database and batch import to PostGIS from an ESRI file gdb.

Most of my data are in geodatabases so this is what I will be using for this portion of the experiment. I downloaded some shapefiles from the City and County of Honolulu (CCH) HoLIS website and imported them into a file geodatabase, specifying the Web Mercator projection (EPSG: 3857).

Connecting to PostgreSQL and create a new PostGIS database

Open pgAdmin 3 or 4 (I am using pgAdmin 3 here) - if you haven't previously provided login creditials (e.g. postgres/postgres) then enter them and save it for next time
Double click on your Server (e.g. PostgreSQL 9.6 localhost: 5432) to connect to it.
Right click on Databases >> New Database.

Properties tab: type in a name for your database (e.g CCHGIS) and the owner (e.g. postgres)

Definition tab: for template, select a sample template to use if you have one (e.g. postgis_23_sample). Note if you are using a sample database as the template to create a new database, make sure it is not in use or connected otherwise you will get … — Definition tab: for template, select a sample template to use if you have one (e.g. postgis_23_sample). Note if you are using a sample database as the template to create a new database, make sure it is not in use or connected otherwise you will get an error.

Here is the newly created CCHGIS database based on the postgis_23_sample which already has the extensions added that I want.

Loading File GDB to PostGIS Database Using Ogr2ogr

Required:

GDAL with ESRI file gdb support must be installed. See Part 1 of this series for installing QGIS with OSgeo4W that you can use to install GDAL with fgdb support - download pdf.
Have your own file geodatabase and make sure you have write access to it.. Optionally to make things easier when you build your web mapping app, make sure the layers in your geodatabase are in either Web Mercator projection (EPSG: 3857) or WGS84 (EPSG: 4326).

Optional:

Add the path to OSGeo4W/bin (ex: “C:\OSGeo4W\bin”) to the Path Environment Variables (Right click Computer > Properties > Advanced system settings > Environment Variables > System Variables > Path). This keeps you from having to type the full path of the application so you can type ogr2ogr instead of having to type C:\OSGeo4W\bin\ogr2ogr.exe

Using the Ogr2ogr command:

Type the following command in cmd terminal (Start >> run cmd)

ogr2ogr -f "PostgreSQL" “PG:host=[host] port=[port] dbname=[dbName] user=[user]" “[PathtoFileGDB]” -overwrite -skipfailures –progress --config PG_USE_COPY YES

Example:

ogr2ogr –f “PostgreSQL” “PG:host=localhost port=5432 dbname=CCHGIS user=postgres” “C:\Temp\CCH_HoLIS_PostGIS.gdb” –overwrite –skipfailures -progress –-ogr2ogrconfig PG_USE_COPY YES

Note: by default, postgresql deny all connection if it's not from "localhost". If you get an error that says PGConnectdb failed; no pg_hba.conf entry for host “x.x.x.xx” , user “postgres”, database “CCHGIS”, SSL off – you need to change the host = localhost instead of host = 1.2.3.45.6 or you need to modify the pg_hba.conf file.

Try this: Find pg_hba.conf file and open it in a text editor (e.g. C:\Program Files\PostgreSQL\9.6\data\pg_hba.conf)
Add the following line as the first line of pg_hba.conf. This should allow access to all databases for all users with an encrypted password:

# TYPE DATABASE USER CIDR-ADDRESSMETHOD
hostallall 0.0.0.0/0 md5

Here is a snapshot showing all the layers from my CCHGIS geodatabase imported into my PostGIS database (e.g CCHGIS that I created earlier)

File geodatabase layers are imported into PostGIS CCHGIS database under Tables.

The next part of this series, Part 4 will be on Installing GeoServer and Importing PostGIS to GeoServer. I will try to post it soon - maybe the first week of April 3rd, 2017.

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

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

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

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

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

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

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 i… — 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

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.

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

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

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.