Connect QGIS to an Oracle Autonomous Database
QGIS is an open-source tool used frequently by GIS specialists to work with geospatial data. The Oracle Autonomous Database is a fully automated Oracle Database service that makes it easy for all organizations to develop and deploy application workloads regardless of complexity, scale, or criticality. The service is available in the Oracle Cloud Infrastructure (OCI) and via Microsoft Azure. Oracle Database is a great platform to securely store, process, and query geospatial data.
How can QGIS connect to an Oracle Autonomous Database? With three simple steps, you can access geospatial data managed in an Autonomous Database and use it in your QGIS projects.
- Download the client credentials (aka “Wallet”) for your Autonomous Database and extract them into a folder of your choice.
- Use the information from within the wallet to set up the connection in QGIS. Test the connection.
- Use the spatial data stored in your Autonomous Database instance.
Once, you have followed the steps, the result of testing your connection should give you confidence.
But let´s do it step-by-step.
Step 1: Download and unzip the wallet
As a prerequisite, I assume you have access to the Oracle Cloud and set up an Autonomous Database instance, either an Autonomous Data Warehouse (ADW) or an Autonomous Transactional Processing (ATP) database instance, depending on the type of workload.
In the following recording, I use an Always Free instance of Autonomous Transactional Processing with a public endpoint.
Step 2: Extract the connection details from tnsnames.ora and set up the connection in QGIS
In QGIS, to connect to an Oracle Database, you need to provide the connection details. You can extract most of them from the tnsnames.ora file contained in the wallet you have just downloaded and extracted.
Then either use:
- Layer > Data Source Manager > Oracle or
- Browser Panel > (Right click on) Oracle
to create a new Oracle connection.
Fill out the necessary parameters.
- Name: <Free text as connection name>
- Database: <service_name>?wallet_location=<path of your wallet location>&https_proxy=<proxy_server>&https_proxy_port=<proxy port>
- Host: <protocol>://<host>
- Port: <Insert the Autonomous Database port here, typically 1522>
Please note two things:
- The two parameters
&https_proxy=<proxy_server>&https_proxy_port=<proxy port>
added after the wallet location are optional. - You need to specify tcps as protocol in front of the hostname.
Testing your connection is simple. Just click on the “Test Connect” button. If everything is properly configured, you get a success message.
Step 3: Use your spatial data in a QGIS project
Now you can open your connection, check the existing spatial data, and add them as layers to your QGIS projects.
That´s it.
Congratulations! You have successfully connected QGIS to your Autonomous Database instance.
Do you want to learn more about how to use Oracle Spatial technologies, then check out the following resources:
- Medium (= all articles with “Oracle Spatial” tag)
- Oracle Database Insider (= all post for category “Spatial”)
- Stack Overflow (= questions with “oracle-spatial” tag)
- Slack AnDOUC (= invitation link, please visit #spatial)
- Slack OracleDevRel (= invitation link, please visit #oracle-db-spatial)