Batch Import PostGIS — Blog

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.