Connection between PostGIS and QGIS



GIS applications are tools that allow users to create interactive queries (user-created searches), analyze spatial information, edit data in maps, and present the results of all these operations. Geographic information science is the science underlying geographic concepts, applications, and systems and What goes beyond a GIS is a spatial data infrastructure, a concept that has no such restrictive boundaries. For handling the spatial data infrastructure we use tools like PostGIS and QGIS. this post will help guides you about How can we establish a Connection between PostGIS and QGIS with the help of some GIS operations.

Connection between PostGIS and QGIS

PostGIS and QGIS are two major platforms used for handling GIS operations. PostGIS adds support for geographic objects allowing location queries to be run in SQL. As PostGIS is a spatial database extender for PostgreSQL object-relational database. In our previous post you can read about PostGIS and PostgreSQL.

Now we are going the discuss the details of how We can establish connection between PostGIS and QGIS and before that let’s just discuss some important topics in brief.

QGIS

QGIS is one of the most common open source application used by GIS analyst, Geographers, Civil Engineers and many other professionals. QGIS is a Desktop based Geographic Information System (GIS) Application for creating maps, editing, viewing and analyzing Geospatial data. Quantum GIS is a cross-platform, free and open source application. It can be run on multiple operating system like Mac OS X, Windows, Ubuntu, Linux and Unix.

PostGIS

PostGIS is an open source software program that adds support for geographic objects to the PostgreSQL object-relational database. PostGIS follows the Simple Features for SQL specification from the Open Geospatial Consortium (OGC).
Technically PostGIS was implemented as a PostgreSQL’s extension. And There are a large number of software products that can use PostGIS as a database backend.

PostgreSQL

PostgreSQL is an open source relational database management system ( DBMS ) developed by a worldwide team of volunteers. PostgreSQL is not controlled by any corporation or other private entity and the source code is available free of charge.
When we installed PostgreSQL database server, the PostgreSQL installer also installed some useful tools for working with the database server. You can connect to the PostgreSQL database server by using the psql or pgAdmin tool.
psql is an interactive terminal program provided by PostgreSQL. You can do a lot with psql tool e.g., execute SQL statements, manage database objects, etc. and The second way to connect to a database is using pgAdmin GUI application. By using pgAdmin GUI application, you can interact with PostgreSQL database server via an intuitive user interface.

Connection between PostGIS and QGIS

  • Import Shapefile into PostGIS

    Connection between PostGIS and QGIS
    Connection between PostGIS and QGIS

    We are going to import some Shapefiles into PostGIS for creating a table in PostgreSQL database. Importing shapefiles into PostGIs using psql is already discussed in our previous post “Import Shapefile in Postgresql – shp2pgsql”.
    Shapefile: The most popular geospatial vector data format. First developed by ESRI. Because of it’s open specification it is now predominantly used to share GIS data between open source and proprietary software.
    We can also do this import with pgShapeloader tool (also known as “PostGIS Shapefile Import/Export Manager” or “PostGIS Shapefile and DBF loader”). It provides another option for loading shapefiles into PostGIS database tables. It is the graphical user interface (GUI) equivalent of the command line shp2pgsql tool.
    The pgShapeloader tool combines the two data loading stages, converting data into SQL commands and running those commands against the target database, into one operation.
    On Linux pgShapeloader can be launched from the terminal with the shp2pgsql-gui command which generates this.

    now click View connection details and enter the connection information in the PostGIS Connection section.

    Click OK to return to the main application. The shapefile loader uses the supplied connection details to connect to the target database; the connection status is reported in the Log Window.
    Connection between PostGIS and QGIS

    To select your source files, click Add File to open the Select a Shape File dialog box. Navigate to the location of your shapefile, click the shapefile you wish to load, and click Open. Multiple files can also be added in the same way. and Once all import options have been added and configured, click OK and click Import to start the conversion.
    Connection between PostGIS and QGIS

    Now these shape files are added in the PostGIS databases as tables you can view them and their attributes in pgAdmin or psql.
    Connection between PostGIS and QGIS

  • Add and Modify PostGIS data files in QGIS

    Now we are going to add and modify the PostGIS database files in QGIS application.

    • Open QGIS application, you will see PostGIS option in Browser toolbar here.
      Connection between PostGIS and QGIS
    • Right click on the PostGIS option and click on the “New Connection” that will open a “Create a New PostGIS connection” toolbar.

      Connection between PostGIS and QGIS
      Connection between PostGIS and QGIS
    • Enter the Database Connection details and then click the “Test Connection” button. this button will open up an authentication form for server details. fill the authentication details and click Ok.
      Connection between PostGIS and QGIS
    • you will see a message on toolbar i.e connection to is successful. Now click OK and you have successfully connected your database with the QGIS. You can see the Database and the Shapefiles we imported in PostGIS below the PostGIS in browser window in QGIS.
      Connection between PostGIS and QGIS
    • Now for adding these Shapefile as layers in QGIS, Right click on the files and select “Add Selected layers to Canvas” then the file will show as a layer on canvas and in layers window in QGIS. you can also see the attributes of the Shapefile  by right-click on the filename in Layers window and selecting Open attribute table.
      Connection between PostGIS and QGIS
    • Now for Modifying the Shapefiles , I prefer you to go to Split Sub Divide polygon layer using QGIS – Shapefile, kml, GeoJson.
      There you can modify the Shapefile and we can see that as the polygon is Sub-divided It increases the attributes of the Shapefile.
  • Now as we can see that the attributes of Shapefile after modifying has increased compare to the attributes when the file was imported from PostGIS database.

When the Shapefile is saved after modification in QGIS by default the file is also saved in PostGIS database with the new and changed attributes it has before.

The connection between QGIS and PostGIS is proves that if any data file is altered in QGIS, the file also gets modified in the PostGIS database system.

Download free Shapefile Maps

This is how we can show the establishment of the Connection between PostGIS and QGIS.

Author: Akshay Upadhyay

Owner and Director of a Private Limited company which serves individual to large scale industries in the field of Maps and GIS. He is a Gold Medalist in M.Tech(Spatial Information Technology) and owns some famous Technology blogs and website... Know more