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… )
