Skip to content

Add a database table (PostGIS or Oracle Spatial)

A PostGIS or Oracle Spatial table can be added via the plugin under the following conditions:

Several similar connections

If several connections have been configured for databases with identical names, it is possible totell the plugin which connection to use to access data stored in the database concerned.

Connection configured in QGIS data source manager

The first way to set up a database connection is to add a data source in QGIS:"Layer" menu >"Data source manager">"PostgreSQL" or"Oracle" tab > click on"New".

Whether it's an Oracle or PostgreSQL database, for a connection to be usable by the Isogeo plugin, the username and password must have been entered and stored:

For PostgreSQL databases only

You can enter the name of a service in the"Service" field of the form used to create a new database connection (see screenshot above).

This service must have been specified in a pg_service.conf configuration file.

The location of the pg_service.conf file must be saved in a "PGSERVICEFILE" environment variable configured in the OS and in QGIS. To configure the variable in QGIS: Menu"Preferences" >"Options..." > "System" tab > "PGSERVICEFILE" item >"System" tab >"Environment" section > check "Use custom variables..." > click on > click on "+".

Connection configured in the _user\db_connections.json file

The second way to configure a database connection is to enter the connection information in the db_connections.json file in the _user folder, which is located in the QGIS Isogeo plugin installation directory:

  • to the following location under Windows: C:\Users\%userprofile%AppData\Roaming\QGIS\QGIS3\profiles\default\python\plugins\isogeo_search_engine_user\db_connections.json
  • and on Ubuntu: /home/%userprofile%/.local/share/QGIS/QGIS3/profiles/default/python/plugins/isogeo_search_engine/_user/db_connections.json.

Check the contents of the _user/db_connections.json file

If the file doesn't seem to be taken into account by the plugin, take the time to check that it has been filled in with valid information. You can also use this tool (or an equivalent) to check that the file content complies with JSON format specifications.

In addition to conforming to JSON format specifications, to be taken into account by the Isogeo plugin, this file must :

  • contain a single object with the following 2 keys :
    • Oracle
    • PostgreSQL
  • each of these keys must have the value of a list which can be :
    • vacuum
    • or composed of object(s) with the following keys :
      • connection_name connection name, must be unique, each connection must have a different name
      • host the host where the database is hosted
      • port base connection port
      • database database name
      • username user name used to log in
      • password user password used to log in
      • database_alias optional key presented just after

For Oracle databases only

It is sometimes necessary to add a database_alias entry.

When the value of the "Resource location" field in the metadata record is different from the database name, the database_alias entry must be filled in with the value of the"Resource location" field in the metadata record.

{
    "Oracle" : [
        {
            "connection_name" : "Nom de la connexion Oracle (il doit être unique)",
            "host" : "hôte",
            "port" : "port", 
            "database" : "nom de la base de données",
            "database_alias" : "emplacement de la ressource",
            "username" : "nom d'utilisateur",
            "password" : "mot de passe de l'utilisateur"
        }
    ],
    "PostgreSQL" : []
}

The default content of the _user\db_connections.json file is as follows (no connections are configured):

{
    "Oracle" : [],
    "PostgreSQL" : []
}

Take a look at the examples below.

Examples of _user/db_connections.json file contents

Configuring a single connection to a PostgreSQL database :

{
    "Oracle" : [],
    "PostgreSQL" : [
        {
            "connection_name" : "Nom de la connexion A (il doit être unique)",
            "host" : "hôte",
            "port" : "port", 
            "database" : "nom de la base de données",
            "username" : "nom d'utilisateur",
            "password" : "mot de passe de l'utilisateur"
        }
    ]
}

Configure 2 connections to PostgreSQL databases and 1 connection to an Oracle database:

{
    "Oracle" : [
        {
            "connection_name" : "Nom de la connexion A (il doit être unique)",
            "host" : "hôte",
            "port" : "port", 
            "database" : "nom de la base de données",
            "username" : "nom d'utilisateur",
            "password" : "mot de passe de l'utilisateur"
        }
    ],
    "PostgreSQL" : [
        {
            "connection_name" : "Nom de la connexion B (il doit être unique)",
            "host" : "hôte",
            "port" : "port", 
            "database" : "nom de la base de données",
            "username" : "nom d'utilisateur",
            "password" : "mot de passe de l'utilisateur"
        },
        {
            "connection_name" : "Nom de la connexion C (il doit être unique)",
            "host" : "hôte",
            "port" : "port", 
            "database" : "nom de la base de données",
            "username" : "nom d'utilisateur",
            "password" : "mot de passe de l'utilisateur"
        }
    ]
}