{"id":4769,"date":"2018-01-23T06:15:01","date_gmt":"2018-01-23T05:15:01","guid":{"rendered":"http:\/\/www.sigterritoires.fr\/?p=4769"},"modified":"2018-02-27T19:56:02","modified_gmt":"2018-02-27T18:56:02","slug":"start-with-postgresql-postgis-introduction-to-pgadmin-4","status":"publish","type":"post","link":"https:\/\/www.sigterritoires.fr\/index.php\/en\/start-with-postgresql-postgis-introduction-to-pgadmin-4\/","title":{"rendered":"Start with PostgreSQL \/ Postgis -Introduction to pgAdmin 4"},"content":{"rendered":"

Following up a previous article (D\u00e9buter avec Postgres\/Postgis<\/a> ), we will address an introduction to Postgres\/postgis database management. We will load a shapefile<\/a>, connect and load the Postgis<\/a> layer from QGis.<\/p>\n

The most \u00a0suitable method to manage PostgreSQL databases is by using the pgAdmin4 GUI.<\/p>\n

This tool is setup automatically during PostgreSQL installation. You can launch it from the programme bar:<\/p>\n

$\"\"$ <\/a><\/p>\n

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.<\/p>\n

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.<\/p>\n

pgAdmin4 allows you to manage a PostgreSQL \/ Postgis database without worrying about the corresponding SQL syntax.<\/p>\n

When you launch the application, the first screen allows you to connect to the desired server and database.<\/p>\n

$\"\"$ <\/a><\/p>\n

Right-clicking on an element opens the contextual menu that proposes the different available operations for this element.<\/p>\n

During the installation procedure, we have created, automatically, two databases :<\/p>\n

a classic PostgreSQL database called postgres <\/strong> <\/em><\/li>\n

a PostgreSQL \/ Postgis database called postgis_24_sample <\/strong> <\/em><\/li>\n<\/ul>\n
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.<\/p>\n
$\"\"$ <\/a><\/p>\n
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.<\/p>\n
When you create a database, you must add these extensions to make it applicable with Postgis.<\/p>\n
$\"\"$ <\/a>Right-clicking extensions <\/strong> <\/em> and Create – > Extension <\/strong> <\/em>, you will have the list of available extensions.<\/p>\n
Add a shapefile to a Postgis database.<\/h2>\n
First, what does it mean to add a shapefile to a Postgis database? A \u00ab\u00a0file\u00a0\u00bb shapefile is actually a group of files with the same name and different extensions. For example:<\/p>\n
$\"exemple$ <\/a>
\nAll of these files constitute the \u00ab\u00a0shapefile\u00a0\u00bb ouessantCLC_L93 <\/em>. One file contains the geometries, another the attribute data, and so on.<\/p>\n
The information included in the files will be loaded as a Table <\/em>into the database. Only one table will include all the information scattered in the shapefile files.<\/p>\n
$\"\"$ <\/a>In the previous image you can examine at which level the data will be stored, and you will notice that the \u00ab\u00a0Tables\u00a0\u00bb level is a sub-level of a \u00ab\u00a0Schema\u00a0\u00bb which is a sub-level of \u00ab\u00a0Database\u00a0\u00bb. When loading the shapefile, you will have to specify which database, and, possibly, which schema to use.<\/p>\n
In our example database there is a \u00ab\u00a0Public\u00a0\u00bb 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.<\/p>\n
We can load the shapefile with the \u00ab\u00a0Postgis 2.0 Shapefile and DBF Loader\u00a0\u00bb programme.<\/p>\n
You can launch it as a stand-alone application, from the Windows programme bar:<\/p>\n
$\"\"$ <\/a><\/p>\n
Unlike pgAdmin III, you will not find a button in pgAdmin 4 to launch this application.<\/p>\n
The application window opens:<\/p>\n
$\"shapefile$ <\/a>
\nYou must first establish the connection with your database<\/p>\n
$\"\"$ <\/a><\/p>\n
Click \u00ab\u00a0 Add File <\/strong> <\/em>\u00a0\u00bb and navigate to select the shapefile to load. Click Open <\/strong> <\/em>, the file information \u00a0to be loaded will appear in the Import List<\/p>\n
$\"pg30\"$ <\/a>
\nClick the Import <\/strong> <\/em> … button
\n $\"\"$ <\/a>
\nHere! It is done. And now you can observe in your pgAdmin 4 (after refreshing) the imported table with your data.<\/p>\n
$\"\"$ <\/a>Fill in the coordinate system<\/h2>\n
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).
\nWe will update the information in the geometry column of the table we have just created.<\/p>\n
Open the SQL window of pgAdmin 4:<\/p>\n
$\"\"$ <\/a><\/p>\n
In the Edit SQL window type the following query:<\/p>\n
SELECT UpdateGeometrySRID (‘ouessantclc_l93’, ‘geom’, 2154); <\/strong> <\/em><\/p>\n
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.<\/p>\n
Run the query by clicking the Execute button<\/em> (indicated by the blue arrow on the image).
\nYou will receive a message stating that the request has been executed successfully:
\n $\"\"$ <\/a><\/p>\n
Load the Postgis layer into QGis<\/h2>\n
To verify that everything went smoothly, open QGis and select the Postgis layer loading button<\/p>\n
$\"pg33\"$ <\/a>
\nThe login window opens. The first time you connect to a database you must configure your connection.<\/p>\n
Click \u00ab\u00a0 New <\/strong> <\/em>\u00a0\u00bb and fill in the login information<\/p>\n
$\"\"$ <\/a><\/p>\n
You have everything you need in the console window pgAdmin 4. At the Server level if you right click on \u00ab\u00a0PostgreSQL 10\u00a0\u00bb – > Properties <\/em>, you will have all the necessary information: Host <\/strong> and Port <\/strong>. The Name <\/strong> is only used to display the pull-down menu of connections. You are free to enter any name. Leave\u00a0Service <\/strong>\u00a0blank.<\/p>\n
The database where you loaded the shapefile is \u00ab\u00a0postgis_24_sample\u00a0\u00bb.<\/p>\n
You must enter the username: we will use \u00a0\u00ab\u00a0postgres\u00a0\u00bb and the password that you defined for this user when running the installation.<\/p>\n
Click Test Connection <\/strong> <\/em> to make sure all information is entered, and then click OK.<\/p>\n
The new connection is added to the list. Click \u00ab\u00a0 Connect <\/em> <\/strong>\u00ab\u00a0. You will have the different Schemes <\/em> available together with their specific list of spatial tables:<\/p>\n
$\"\"$ <\/a>Select the line matching the layer loaded with the Public <\/strong> <\/em> schema and click the Add <\/strong> <\/em> button.<\/p>\n
The coordinate system definition window is displayed. Select the SRC<\/a> of the data (in this example Lambert 93)<\/p>\n
$\"pg36\"$ <\/a>The layer is loaded in QGis.<\/p>\n
$\"pg37\"$ <\/a><\/p>\n
You can now work on this layer as on any other layer you were used to (display, symbology, geometric update or attribute, …)<\/p>\n
<\/p>\n
*<\/p>\n","protected":false},"excerpt":{"rendered":"
Following up a previous article (D\u00e9buter 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 \u00a0suitable method to…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"give_campaign_id":0,"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"sfsi_plus_gutenberg_text_before_share":"","sfsi_plus_gutenberg_show_text_before_share":"","sfsi_plus_gutenberg_icon_type":"","sfsi_plus_gutenberg_icon_alignemt":"","sfsi_plus_gutenburg_max_per_row":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[1296,1338],"tags":[],"class_list":["post-4769","post","type-post","status-publish","format-standard","hentry","category-posts-en","category-postgresql-en"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p6XU0A-1eV","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/4769","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/comments?post=4769"}],"version-history":[{"count":0,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/4769\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media?parent=4769"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/categories?post=4769"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/tags?post=4769"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}