Open Source GIS Implementation - Part 3 Creating PostGIS Database

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)  

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 an error.   

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
Example of command as shown in the cmd terminal

Example of command as shown in the cmd terminal

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.

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.

Open Source GIS Implementation - an Experiment

This post documents my attempt at implementing opensource GIS Desktop and Server. It describes the trials and errors of the process. It may or may not work for your purpose, but I hope others will find it useful in terms of knowing what is involved.

This is the open source GIS software stack I used.

The implementaion process is broken up into serveral parts:

Part 1: Installing QGIS. This is just the installation of the opensource desktop GIS, QGIS, which I've already written a document on how to do this.  Although it is for QGIS 2.14 the process will be the same for the current version 2.18.

Part 2: PostgreSQL/PostGIS Installation.

Part 3: Creating a PostGIS database. This part goes over how to import an ESRI file GDB to a PostGIS database using ogr2ogr command line utility.

Part 4: Installing GeoServer.

Part 5: Publishing PostGIS to GeoServer. This part goes over how to publish or push PostGIS database to GeoServer

Part 6:  Using Leaflet and OpenLayers to test that layers GeoServer can be called. 

I tried OpenGeoSuite 4.8 (free version from Boundless but require email sign up) which was really easy to install and used. The version of OpenGeoSuite I tested uses slightly older versions of GeoServer and PostgreSQL/PostGIS. I wanted to use more current versions of the software in the stack as well as wanting to know how to each component worked, so I tried my hand at implemnting each components of the stack myself. Of course it's not as easy as just  using OpenGeoSuite but at least I learned a lot in the process.

Here are the specs on the machines that I tested.

Tested on Windows OS Machines
Installation of Postgres/PostGIS on both a Windows 10 OS x64 laptop and a Windows Server 2012 R2 virtual machine for testing. The Windows Server VM already has a web server (Windows IIS) already set up at my work by IT so I used that. Also for my laptop I just used/enabled the Windows IIS already available. 

Laptop Specs: Dell XPS 13

Windows Server 2012R VM

2017-01-19_091615.jpg