ogr2ogr to import data into PostGIS

ogr2ogr to import data into PostGIS

Tin this tutorial cartogeek.com will show you the GDAL / OGR geospatial library has the functionality ogr2ogr that is of great interest to import vector data in formats as diverse as Shapefile, Geopackage, CSV or GeoJSON to our PostgreSQL database – PostGIS.

In this short article we will see how to import Shapefile, Geopackage and GeoJSON files into PostGIS with ogr2ogr easily using the command line.

Using ogr2ogr to import data into PostGIS and transform GIS formats

Why use GDAL / OGR to import geospatial data into PostGIS?

The advantages of using this functionality are several:

• It allows to launch processes on any machine from the terminal easily, both in Linux and Windows.
• The load is really fast, since we do not use any intermediate software such as QGIS or some database manager such as pgAdmin.
• We can easily customize the execution parameters using a series of « flags » in the command.
• The GDAL / OGR documentation it is really well prepared and easy to interpret.
• Become familiar with GDAL / OGR commands It will help us learn to exploit its potential beyond the ogr2ogr functionality that we discuss in this post.
• It allows us to establish routines and program the execution of said processes easily to automate the import of geospatial data into PostGIS.
• We do not need to know any programming language, although more can be done by combining it with knowledge of Python for GIS.

How to run GDAL / OGR commands and use ogr2ogr functionality

The commands of the ogr2ogr functionality are executed using the console or Windows command line. To access it, just search for « cmd » in the Windows search engine ( System Symbol ) or by pressing the Windows key + R and then write « cmd ». A session will open with your user where you can start entering commands.

In the case of Linux, you must open the terminal that is installed with your distribution. In Ubuntu you can open the Gnome terminal or press Ctrl + Alt + T to access quickly.

In order to use the ogr2ogr functionality you must have GDAL / OGR installed or, if you already have an installation of QGIS or PostgreSQL, add the location of the functionality to your PATH in case of using Windows. The installation site is generally C: \ Program Files \ PostgreSQL \ version_de_PostgreSQL \ bin for the installation of PostgreSQL – PostGIS and C: \ Program Files \ version_de_QGIS \ bin for the installation of QGIS.

Indeed, both QGIS as PostgreSQL use, among other libraries, GDAL / OGR in your guts for data conversion and other geospatial processes.

You can make sure that ogr2ogr the following command is accessible from any directory by writing in your console / terminal session:

This command should return the installed version of GDAL to you. In that case, your system should be ready to run GDAL / OGR commands. If not, you should review the configuration of the environment variables and add the directory corresponding to the PATH.

Ogr2ogr principles and common patterns in commands

Most ogr2ogr commands are characterized by having a series of common instructions. So when we want to run commands that modify the format of a file or interact with PostgreSQL – PostGIS we will see structures repeat in the statements.

We could say that usually three parts are distinguished within a command:

• In the first part of a command the functionality is invoked first, then the file output format is indicated or, rather, which driver will be used to run the conversion, followed by the path and the name of the resulting file.
• The second part of the command sets the source of the information, be it a local file or a PostgreSQL – PostGIS database.
• Finally, optional commands are added to define the file structure, parameters that affect geometries, conversions between coordinate reference systems, etc … That is, creation options that are established through the flag « -lco ».

Let’s see an example, converting a shapefile file to Geopackage:

Thus, the complete command to convert a Shapefile to the Geopackage format with ogr2ogr would be as follows, indicating the used driver ( GPKG ), the path and the destination file, the path and the source file, and finally the flags « -lco » with creation options:

How to import a Shapefile into PostGIS with ogr2ogr?

Let’s see an example of how to import a Shapefile with ogr2ogr on PostGIS. In the following case we try to load a layer of the Basque Country forest inventory on our PostGIS database installed on our local machine.

We will use the same scheme or command pattern that we have seen previously, although with slight variations:

In this case we are not indicating that the destination is a file, but rather we indicate a connection to a PostgreSQL database – PostGIS. To do this, we define the connection with PG: » connection parameters ». Among them, the name of the database, the host or host, the connection port, user, password, scheme, etc…

Later we indicate, as usual, the source file ( in this case the forest inventory shapefile ) that we are going to import into the indicated PostGIS connection.

Finally, the options for creating the layer ( o are detailed Layer Creation Options, that’s why « -lco » in reference to its initials…) where we indicate that the name of the geometry field in PostGIS is called « geom » and the unique identifier field will be called « gid ».

Finally, the flag « -progress » allows you to display an account of the command advance. It is useful to know the state of execution of the command, especially before heavy files.

How to import a Geopackage into PostGIS with ogr2ogr?

In the same way that Shapefile files can be imported, we can also import files in Geopackage format with ogr2ogr to PostGIS, another of the main standards within the GIS sector today for data exchange.

Leaving aside the particularities of this format ( would give for one or more detailed posts ), it is essential to know that the same Geopackage file can contain multiple layers. Rather, multiple tables, as it is simply a kind of compact, portable database based on SQLite.

In the following example, we are going to load the « t_polygon » layer from the Geopackage file « siose_2014_eus.gpkg » on the indicated database.

The « -nln » flag you can see refers to the name of the layer to be created in the database. Then we add the flags « -lco » and « -progress ». In this way, we avoid loading all the tables in the file Geopackage, but only the desired layer and naming it at our discretion.

If we ignored the flag « -nln » the name of the table in PostGIS would be the one you receive inside the Geopackage file.

How to import a file in GeoJSON format to PostGIS using ogr2ogr?

In case of wanting import a GeoJSON to PostGIS with ogr2ogr the procedure is very similar to that described above. The driver used and the command structure is very similar.

In this case, the GeoJSON format is an adaptation of the commonly used JSON format ( JavaScript Object Notation ) in the web environment or in NoSQL databases. In it, fields and values are defined as key-value pairs. The definition of geometries is done using the OGC WKT format.

An example of import of a GeoJSON to PostGIS with ogr2ogr:

If you want to know more about this functionality, you can visit the official website of the GDAL / OGR library and visit the vector drivers section. Specifically, in this space dedicated to PostgreSQL-PostGIS driver You have more details and examples on how to write connection and data manipulation commands for importation into a PostGIS database.

Read More

what is the future of GIS and the geospatial sector according to OpenAI

postGIS tutorial space join in PostGIS