Following a previous article ( Start with Postgres / Postgis ), we will discuss an introduction to the administration of Postgres / postgis databases, the loading of a shapefile and the connection and loading of the Postgis layer from QGis.
An update of this article using pgAdmin 4 is available by following this link.
The most convenient way for administrating PostgreSQL databases is to use the pgAdmin3 graphic interface. This tool is installed automatically when PostgreSQL is installed. It can launched from the program bar:
For the purposes of this article, load a shapefile as a Postgis layer in your new database, you do not actually have to perform any special administration task. But it’s easier to understand how it works by following the base modifications.
An excellent express tutorial created by Jean FABRE ) is reproduced below:
Note : if you place the cursor on the image, the automatic scrolling stops
But rest assured, for the time being you have nothing to create. When installing Postgis, we requested the creation of a model database. It is this model that we will use for the rest of the article.
pgAdmin III allows you to administer a PostgreSQL / PostGIS database without worrying about the corresponding SQL syntax . When you launch the application, the first screen allows you to connect to server to the desired database.
Right click on an element opens the context menu that offers you different available operations for this element .
At the time of installation we have created automatically two bases:
- A PostgreSQL classic database, called postgres
- a PostgreSQL / PostGIS database, called postgis_22_sample
It is this second one that we are going use to load a shapefile. But before, let’s take advantage to take a look and see which the differences between these two bases are.
If you look at each other extensions, you will find that the Postgis base has a sensitive series of extensions specific for managing the spatial information. It is the presence of these extensions that transforms a PostgreSQL database in a Postgres / Postgis base. When you create a database, you must add these extensions to make it usable with Postgis.
By right clicking extensions and then Add an extension, you will have the list of available extensions.
Add a shapefile to a Postgis base. Firstly, what does it mean adding a shapefile to a PostGIS database? A shapefile «file» is, in fact, a group of files with the same name and different extensions. For example:
All of these files constitute the OuessantCLC_L93 “shapefile”. A file contains the geometries, another the attributes data, etc. The information contained in these files, will be loaded as table in the database. A single table will contain all the information dispersed in shapefiles.
You can see in the previous image the level into which the data will be stored, and you will notice that the level “Tables” is a sub-level of a “Diagram” which is a sub- level of “Database”. Therefore, when loading the shapefile, you will have to indicate which database and, possibly, which diagram has to be used.
In our example database there is a diagram “Public” which has, unlike the other diagrams present, all the functions (1280). We will come back to this concept later. Just remember that functions are added to a diagram when and as required. In our example, all functions are already loaded; it avoids us these additional operations.
We can load the shapefile with the program “Postgis 2.0 Shapefile and DBF Loader”. You can launch it as a standalone application, from the Windows program bar:
Or from the pgAdmin III console
The application window opens:
If you start pgAdmin III from the console, the connection to your database is already established. If not, you will have to establish it. Click on “Add File” and navigate to select the shapefile file to be loaded. Click Open, the information file to load is displayed in the Import List
Click the Import … button
Done. Now you can see on your pgAdmin III console (after having updated the display) your imported table
Entering the coordinate system
Perhaps you have noticed that, in the Import List window, the SRID (coordinate system) is zero. This indicates that the loading program could not interpret correctly the file . prj shapefile. In our case it should be 2154 (Lambert 93 EPSG code / RGF93).
Then, we will update the information corresponding to the geometry column of the table we have created . Open the pgAdmin III SQL window:
In the Edit SQL window type the following query:
SELECT UpdateGeometrySRID ( ‘ouessantclc_l93’, ‘geom’, 2154);
Just change the table name (ouessantclc_l93) to the name of your table and the code of the SRID (2154) to the EPSG code of your coordinate system.
Run the query by pressing the Execute button (indicated by the blue arrow in the image). You will have a message indicating that the request has been correctly executed:
Loading the Postgis layer into QGis To ensure that everything run properly, open QGis and select the load button of a Postgis layer
The login window opens. The first time you get connected to a database, it must configure the connection. Click “New”, then fill in the connection information
You have everything necessary in the pgAdmin III console window. At the Server level you have ‘ “PostgreSQL 9.5 (localhost : 5432 )” what is the Host / Port of the login window. The name is not used only to display the drop – down menu of connections. You are free to return any name. The Service will leave it blank.
The database where you have loaded the shapefile is “postgis_22_sample”.
You must enter the username: for the time being, we will use “postgres” and the password that you have defined for this user at the time of installation.
Click on “Test the connection” to make sure all the information is entered correctly, and then click OK. The new connection is added to the list. Click “Connect”. You will have the different schemes available and in each one, the list of spatial tables:
Select the only available line of Public and click the Add button . The coordinate system definition window appears. Select the data SRC ( in this example Lambert 93)
The layer is loaded in QGis.
Now, you can work in this layer as in any other layer you have ever used (display, symbology… )
