Sometimes a web service you use in your GIS application is offline and that results in a GIS application that doesn’t work as it should or doesn’t show the correct data. To avoid these problems you should have a local copy of the data of that web service. For a WFS service the data can be copied to a PostGIS database. This can be done using the command line utility ogr2ogr. This isn’t too hard but some people might be afraid to use the command line. So I wrote this guide about how to handle this from installation to automating the import procedure. Because this guide became pretty long I divided it up into 3 parts. Part 1 was about the installation of GDAL and ogr2ogr. Part 2 is about the use of ogr2ogr in a Windows batch file and part 3 will be about automating the import procedure.
PART 2: use ogr2ogr in a batch file
What are batch files?
To explain what batch files are, I’ll use a definition from Wikipedia: “A batch file is a kind of script file in DOS, OS/2 and Microsoft Windows. It consists of a series of commands to be executed by the command-line interpreter, stored in a plain text file.” So we will be adding a series of commands in the batch file and then make Windows run these commands one by one. All these commands together make the entire import procedure from WFS to PostGIS. In Windows a batch file always has the extension .bat.
Making batch files
A batch file is nothing more than a text file that contains one or more commands. Because of that we’ll use a text editor to create or edit the batch file. Very good text editors are Notepad++ and Brackets, but you could just as well use Windows Notepad. Important!! Never use Microsoft Word to create batch files.
Running batch files
In Windows batch files can be run in different ways:
- The simplest way is to double click on the batch file in Windows Explorer. This is fast and simple, but if anything goes wrong when running the batch file, you probably didn’t see the reason, because Windows closes the command line window immediately after running the batch file.
- A better way is to first open the Windows command line window, then navigate to the location of the batch file and type the name of the batch file and press enter. That way the batch file will be run, but after running the Windows command line window will remain open. This way you can see the error messages if anything goes wrong when running the batch file.
- A third method is to use the Windows Task Scheduler, but more about that can be found in part 3 of this series: automating the import procedure.
How do I use ogr2ogr to import data into my PostGIS database?
The import procedure
This is probably the most important part of this manual, because now we are going to transfer data from the WFS service into the PostGIS database.
Before we start, we first have to ask ourselves some questions:
1. What WFS service and which map layer in that service do you want to import? An up-to-date list of WFS services with data for Belgium (and some with data for the entire world) can be found on wfs.michelstuyts.be. On this website can be found what map layers are available per WFS service. In this example we’ll be importing the bus stops from Belgian bus company De Lijn.
2. What are the credentials to login to your PostGIS database? If you don’t know them yourself, you best ask the person who installed or manages the PostgreSQL database.
- Server: This can be an url (for example
www.mydatabase.org), an IP address (for example127.0.0.1) or a name (for examplelocalhostormydatabase). - Username: This must be the name of a user that has rights to create and edit tables in the PostGIS database. In many cases this won’t be the same as your credentials to login to Windows. In this example we’ll use
myusernameas username. - Password: In this example we’ll use
mypasswordas password. - Database: Each PostGIS server can contain multiple databases. So you should know which database you want to use. In this example we’ll use
gisdatabase.
3. Then we have to choose some settings. Usually you can just choose what fits you best, but for this manual we’ll use the following settings:
- Table name we’ll be making in PostGIS:
postgistable - The name of the schema in PostGIS where you want to save your table: We’ll use
public. If you want to use another schema, make sure you create it beforehand in PostGIS. The schema public is created by default in PostGIS and will already exist in your database. - The projection of the data we want to transfer from WFS to PostGIS:
EPSG:31370(Belgian Lambert-72 projection)
4. We have to know where the ogr2ogr utility is located, that we want to use. Make sure you use at least GDAL version 2.0 (see part 1 at the section about the installation). In this example we’ll use ogr2ogr that is located in the folder of the 64bit version of OSGeo4W. Usually this will be C:\OSGeo4W64\bin\ogr2ogr.exe.
Our batch file will contain different commands. For a better readability, the file below will contain an empty line after each command, but that isn’t necessary. Every command should just be on a new line. You can copy and paste from the text below to a text file. Remember to change some values to your own settings so that the commands use the WFS service and PostGIS database of your choice. First you’ll get the entire batch file and after that each command from the file is explained, so you’ll understand better what’s happening.
The entire batch file with the name importmydata.bat
@echo offsetlocal ENABLEDELAYEDEXPANSIONset PGPASSWORD=mypasswordset schema=publicset table=postgistableset schematable=!schema!.!table!set dllocation="http://geoservices.informatievlaanderen.be/overdrachtdiensten/Haltes/wfs?service=WFS&version=1.1.0&request=GetFeature&typeName=Haltes:Halte&srsName=EPSG:31370&bbox=151743,203580,156874,207300""C:\OSGeo4W64\bin\ogr2ogr.exe" -lco GEOMETRY_NAME=geom -overwrite -f "PostgreSQL" -a_srs EPSG:31370 PG:"dbname=gisdatabase user=myusername host=localhost" -nln !schematable! "WFS:!dllocation!"if %ERRORLEVEL%==0 ("C:\OSGeo4W64\bin\psql.exe" -h localhost -U myusername -p 5432 -d gisdatabase -c "COMMENT ON TABLE !schematable! IS 'Most recent update: %date% %time% (download from !dllocation!)';")ENDLOCAL
Each command from the batch file explained
@echo off
This command hides the commands that are run. Only the results of those commands will be shown. This is an optional command and if you want to see everything that’s happening, you’ll better remove this command.
setlocal ENABLEDELAYEDEXPANSION
With this we’ll enable the use of parameters in the form !myparameter!, so we can use this format in the next commands. This means that for example the parameter schema first gets the value public and afterwards we can use !schema! if we need that parameter.
set PGPASSWORD=mypassword
We set mypassword as the password that has to be used to login to the PostGIS database.
set schema=public
We set public as the schema to use in PostGIS.
set table=postgistable
With this we set what PostGIS table the data should be saved to.
set schematable=!schema!.!table!
In PostGIS a table is named by a schema name and a table name, connected with a dot. This PostGIS table name gets defined here. The result is that !schematable! contains the value public.postgistable.
set dllocation="http://geoservices.informatievlaanderen.be/overdrachtdiensten/Haltes/wfs?service=WFS&version=1.1.0&request=GetFeature&typeName=Haltes:Halte&srsName=EPSG:31370&bbox=151743,203580,156874,207300"
We define which url should be used to download the data from the WFS service. This url contains different parts:
- http://geoservices.informatievlaanderen.be/overdrachtdiensten/Haltes/wfs: This is the base url of the WFS service. If ogr2ogr doesn’t want to use the https version of the WFS service, you can try to use the http version. This can happen for the waterinfo.be services for example.
- service=WFS: With this we say we want to use it as a WFS service.
- version=1.1.0: This says we want to download the data as WFS version 1.1.0. sometimes you have to use version 1.0.0, 1.1.0 or 1.2.0 and in many cases you can just leave this out. What’s needed depends on the WFS server you want to use. In the GetCapabilities document you can find which version you have to use. For example: https://geoservices.informatievlaanderen.be/overdrachtdiensten/Haltes/wfs?REQUEST=GetCapabilities&SERVICE=WFS
- request=GetFeature: This says that we want to download the features from the WFS service.
- typeName=Haltes:Halte: Here we define that we want to download the layer
Haltes:Haltefrom the WFS service. The name of a layer always is made up of two words with a colon in between them. - srsName=EPSG:31370: With this we say that we want to download the data in the Belgian Lambert 72 projection.
- bbox=151743,203580,156874,207300: This is an optional parameter, because it defines from within which bounding box on the map you want to download data. Most WFS services have a maximum limit on how many features you can download. For example the Informatie Vlaanderen services have a limit of 10.000 features. If you don’t define a boudning box, the WFS will give the first 10.000 features in it’s database. By using bbox we limit the area we want to download data from, so we wont reach that limit and we can download all the data we need. If it’s a map layer that contains a limited number of features, this parameter can be left out. The coordinates in this parameter will be in the projection we defined earlier (in this case Belgium Lambert 72) en is made up of the maximum and minimum X and Z values of the bounding box (bbox).
"C:\OSGeo4W64\bin\ogr2ogr.exe" -lco GEOMETRY_NAME=geom -overwrite -f "PostgreSQL" -a_srs EPSG:31370 PG:"dbname=gisdatabase user=myusername host=localhost" -nln !schematable! "WFS:!dllocation!"
With this we will actually run the ogr2ogr command with all the parameters we have set. The command is built up of multiple parts:
- “C:\OSGeo4W64\bin\ogr2ogr.exe”: As said during the installation, there can be installed multiple versions of ogr2ogr and we need at least version 2.0. So it is important to indicate where the ogr2ogr version we want to use is located. This part of the command is between quotation marks, because it can contain spaces.
- -lco GEOMETRY_NAME=geom: When creating the PostGIS table, a field is added that contains the geometry of the data. With this part of the command you define the name of that geometry field as
geom. This is an optional setting, because if this is left out,geometrywill be used as default field name. - -overwrite: If a table with the same name already exists in PostGIS, this option will make the command overwrite that existing table. If this option is omitted and the table name already exists, ogr2ogr will give an error message and will not import anything.
- -f “PostgreSQL”: We indicate that the target is a PostGreSQL database.
- -a_srs EPSG: 31370: The projection of the table in PostGreSQL that we are going to create, will be in Belgian Lambert 72 projection (EPSG: 31370)
- PG:”dbname=gisdatabase user=myusername host=localhost”: This sets the database name as
gisdatabase, the username in PostGreSQL asmyusernameand the server on which PostGreSQL runs islocalhost. - -nln !schematable!: This defines the name of the table in PostGreSQL that we are going to create using the variable that we previously named
!schematable!. - “WFS:!dllocation!”: This defines the url of the WFS based on the variable that we previously named
!dllocation!. This location is preceded byWFS:and is enclosed in quotation marks, because it can contain spaces.
if %ERRORLEVEL%==0 (
"C:\OSGeo4W64\bin\psql.exe" -h localhost -U myusername -p 5432 -d gisdatabase -c "COMMENT ON TABLE !schematable! IS 'Most recent update: %date% %time% (download from !dllocation!)';"
) These commands are optional , but very useful. With this we save the current date and the source of the data in the Comment of the PostGIS table that we create or update. If the import procedure is automated afterwards and something goes wrong with that import, you know when the import was successful the last time.
- if% ERRORLEVEL% == 0 (): This command checks whether the previous command has been successfully completed. If so, the command in the brackets will be executed.
- “C:\OSGeo4W64\bin\psql.exe” -h localhost -U myusername -p 5432 -d gisdatabase -c “COMMENT ON TABLE !schematable! IS ‘Most recent update: %date% %time% (download from !dllocation!)’;”: This command places a text with the date, time and source of the most recent update in the comment of the table. This comment can for example be checked in pgAdmin or QGIS.
ENDLOCAL This ends the earlier command ENABLEDELAYEDEXPANSION .
This guide continues in PART 3 : automating the import procedure.