Integrate CARTO & QGIS Using the New Direct SQL Connection
Manipulating geospatial data in a powerful desktop environment that allows the use of advanced editing tools and the derivation of new datasets —by applying complex geoprocessing models to existing datasets— is essential for GIS professionals.
QGIS is the most widely used desktop GIS application in the open source space, which is why we want to ensure that CARTO and QGIS work well together. Following our recent blog post introducing the Direct SQL Connection, we now want to deep dive into how we can use this functionality to integrate QGIS and CARTO.
Until now, it was possible to use QGIS to view and edit CARTO data by using the OGR Virtual Format (VRT) driver as described in this blog post. With the new Direct SQL Connection, we can access CARTO’s database from QGIS using a regular PostGIS connection.
How it works
The best way to understand how something works is to walk through an example. These are the steps to connect QGIS to your CARTO database and create a geoprocessing model that reads two CARTO datasets, writing the results back to a new CARTO dataset.
- Prepare your CARTO database for connections
Go to your dashboard, add the IP address you will be connecting from, and generate the certificate files needed to validate your identity against the CARTO database. You can follow this guide from the help center.
- Add a connection to your CARTO database from QGIS
Import the downloaded certificate files in QGIS and create a new authentication configuration. Then, add a PostGIS connection using the parameters provided and the authentication configuration. Once your connection is successfully established, you will see a list of all your CARTO datasets under the schema corresponding to your username.
- Add the datasets to your QGIS project
For this blog post, we are going to use the well-known datasets regarding the cholera outbreak analysis by John Snow, including a point layer with cholera death locations and another point layer with pump locations. The analysis is simple but illustrates how we can chain analysis operations.
Upload the datasets to your CARTO account and add the cholera deaths and pumps layers (the_geom - 4326) to your QGIS project so we can select them easily when running the model.
- Create a new geoprocessing model
For this analysis, we will follow the approach described in the QGIS processing guide.
Open the processing modeler and add two vector layer inputs, one for the cholera death locations and the other for the pump locations. Select the "Algorithm" tab, search for the Voronoi polygons algorithm, and add it to the model, linked to the pumps input.
Add a "Count Points in Polygon" algorithm over the cholera deaths layer to get the number of deaths in each Voronoi polygon, using "COUNT" as the weight field, "DEATHS" as the Count field name and "voronoi_pumps" as the Count field (output layer name).
- Execute the geoprocessing model
Click the "Run" button to execute the model and select the corresponding layers as the inputs. For the "voronoi_pumps" output layer, choose the "Save to PostGIS table…" option, select the schema holding your CARTO datasets and give it a name.
Click the "Run" button. You will receive an SSL error because the parameters sent by QGIS do not include the SSL configuration. Click the "Cancel" button and copy the SSL parameters (sslmode and authcfg) from the input parameters in the "Log" tab. Go back to the "Parameters" tab and edit directly the textbox for the output layer including the two parameters copied before (for instance between the port and table parameters). Click the "Run" button again.
You will see that a new layer has been added to your QGIS project and has been stored in your CARTO account. If we look at the table, we will see there is a pump with a much higher number of deaths.
- Make the table available in your CARTO dashboard
If you want the table to appear within your datasets section in the CARTO dashboard, you must first prepare it in CARTO canonical form by executing the cdb_cartodbfytable stored procedure. This procedure will try to add a primary key on the cartodb_id field but the QGIS processing framework already adds a primary key on a new column with a default value. That means that the procedure will raise an error. To avoid this problem we must drop that column before executing the procedure.
How to get started
This feature is available right now to all enterprise customers. You can find information about the new Direct SQL Connection and tutorials for connecting with different applications, including QGIS, in the help center.