1

I have to tables inside my database (postgis). Now I will do based on QGIS execute a sql-query based on these two tables. How can I do this? There exists a tool "Execute SQL" but there I can only choose one layer. I will realize the query inside my database in postgis.

I have a Processing model. The first step was to load the two tables inside my database. The next step is to execute the sql query. How can I do this?

|improve this question
2
QGIS --> DB Manager --> DB Manager --> Database --> SQL Window

You will have to choose database on the left hand tree structure. Test your connection f.ex. by executing:

SELECT PostGIS_full_version();

It should return something like:

postgis_full_version
POSTGIS="2.3.1 r15264" GEOS="3.6.0-CAPI-1.10.0 r4265" PROJ="Rel. 4.9.1, 04 March 2015" 
GDAL="GDAL 2.1.1, released 2016/07/07" LIBXML="2.7.8" LIBJSON="0.12" RASTER
|improve this answer
  • thanks it works. I can execute the query inside the Processing model. But how can I import the table I just created into QGIS with the processing model?? I will import the table inside Qgis and than convert this layer as shp. If I do it manually I can double click on the layer inside my database layer. But how can I realize this in processing model? – Moehre Feb 15 '17 at 12:36
  • Drag the database table onto your map. No need to convert. – Fjellrev Feb 15 '17 at 13:27
  • no i am using the processing modeler to do the process automatically. I have imported the shapefiles into postgis. After this I have executed a sql query on the database and now I will add this created table in the model. Not manually with the graphical modeler (processing model) – Moehre Feb 15 '17 at 13:40
1

As Fjellrev said, you can use the DB-Manager for this.

  1. connect to the postgis-DB by clicking on the icon with the postgresql-logo (elephant head) by clicking New and fill the form to connect (mainly host, port and user) and close the window

  2. Go to menu mentioned by Fjellrev and open the SQL-Window

  3. To show your query as a layer in qgis, make sure you have a unique id and a geometry field in your sql field list (select id, geom etc.), you should then be able to add the result to your map canvas

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.