Following up a previous article (Débuter avec Postgres/Postgis ), we will address an introduction to Postgres/postgis database management. We will load a shapefile, connect and load the Postgis layer from QGis.
The most suitable method to manage PostgreSQL databases is by using the pgAdmin4 GUI.
This tool is setup automatically during PostgreSQL installation. You can launch it from the programme bar:
No special management procedure is needed when loading a shapefile as a Postgis layer into your new database. But it’s easier to understand the operation by following the changes in the database.
Nevertheless, you can rest assured that, for the time being, you will not have to create anything else. When installing the Postgis, we have, already , requested the creation of a sample database. And, it is this database that we will be using for the purpose of this article.
pgAdmin4 allows you to manage a PostgreSQL / Postgis database without worrying about the corresponding SQL syntax.
When you launch the application, the first screen allows you to connect to the desired server and database.
Right-clicking on an element opens the contextual menu that proposes the different available operations for this element.
During the installation procedure, we have created, automatically, two databases :
- a classic PostgreSQL database called postgres
- a PostgreSQL / Postgis database called postgis_24_sample
And, it is the latter, that we will be using to load a shapefile. But before, take a look and observe which are the differences between these two databases.
If you have a look at each database extensions, you will discover that the Postgis database has a series of specific extensions that can be used to manage spatial information. It is the presence of these extensions that makes a PostgreSQL database a Postgres / Postgis database.
When you create a database, you must add these extensions to make it applicable with Postgis.
Right-clicking extensions and Create – > Extension , you will have the list of available extensions.
Add a shapefile to a Postgis database.
First, what does it mean to add a shapefile to a Postgis database? A “file” shapefile is actually a group of files with the same name and different extensions. For example:
All of these files constitute the “shapefile” ouessantCLC_L93 . One file contains the geometries, another the attribute data, and so on.
The information included in the files will be loaded as a Table into the database. Only one table will include all the information scattered in the shapefile files.
In the previous image you can examine at which level the data will be stored, and you will notice that the “Tables” level is a sub-level of a “Schema” which is a sub-level of “Database”. When loading the shapefile, you will have to specify which database, and, possibly, which schema to use.
In our example database there is a “Public” schema which, unlike the other available schemas, includes all the functions (1454). We will explain this concept later. Let’s just remember that we add functions to a schema as and when needed. In our example, all functions are already loaded, it avoids these additional operations.
We can load the shapefile with the “Postgis 2.0 Shapefile and DBF Loader” programme.
You can launch it as a stand-alone application, from the Windows programme bar:
Unlike pgAdmin III, you will not find a button in pgAdmin 4 to launch this application.
The application window opens:
You must first establish the connection with your database
Click “ Add File ” and navigate to select the shapefile to load. Click Open , the file information to be loaded will appear in the Import List
Click the Import … button
Here! It is done. And now you can observe in your pgAdmin 4 (after refreshing) the imported table with your data.
Fill in the coordinate system
Perhaps you have noticed in the Import List window that the SRID (coordinate system) is at zero. This indicates that the loader was unable to correctly interpret the shapefile’s .prj file. In our case it should be 2154 (Lambert 93 EPSG code / RGF93).
We will update the information in the geometry column of the table we have just created.
Open the SQL window of pgAdmin 4:
In the Edit SQL window type the following query:
SELECT UpdateGeometrySRID (‘ouessantclc_l93’, ‘geom’, 2154);
You only have to change the name of the table (ouessantclc_l93) ) using the name of your table, and the code of the SRID (2154) using the EPSG code of your coordinate system.
Run the query by clicking the Execute button (indicated by the blue arrow on the image).
You will receive a message stating that the request has been executed successfully:
Load the Postgis layer into QGis
To verify that everything went smoothly, open QGis and select the Postgis layer loading button
The login window opens. The first time you connect to a database you must configure your connection.
Click “ New ” and fill in the login information
You have everything you need in the console window pgAdmin 4. At the Server level if you right click on “PostgreSQL 10” – > Properties , you will have all the necessary information: Host and Port . The Name is only used to display the pull-down menu of connections. You are free to enter any name. Leave Service blank.
The database where you loaded the shapefile is “postgis_24_sample”.
You must enter the username: we will use “postgres” and the password that you defined for this user when running the installation.
Click Test Connection to make sure all information is entered, and then click OK.
The new connection is added to the list. Click “ Connect “. You will have the different Schemes available together with their specific list of spatial tables:
Select the line matching the layer loaded with the Public schema and click the Add button.
The coordinate system definition window is displayed. Select the SRC of the data (in this example Lambert 93)
The layer is loaded in QGis.
You can now work on this layer as on any other layer you were used to (display, symbology, geometric update or attribute, …)
*
nice work
Thank you, really helpful resource!