3

Currently, I am working on SQL in PostgreSQL and I execute query of vector data by "ST_Union" function for the spatial data. Now, I want to access the result of query in QGIS 2.14, but I can't. Is there any one who can help me?

6
  • How are you storing the result? May 24, 2016 at 9:39
  • I was storing the result by exporting from SQL query in CSV format. N ow, I want to open in QGIS since it was a vector data. Thanks.
    – Yow
    May 24, 2016 at 9:40
  • I am not aware of storing the result by exporting form SQL in CSV, can you provide a sample? What exactly do you get the result as? May 24, 2016 at 9:43
  • Did you connect to your database from within QGIS? May 24, 2016 at 9:45
  • I am using postgres database with an extension of POSTGIS and connected to QGIS.
    – Yow
    May 24, 2016 at 11:47

2 Answers 2

5

You can execute SQL queries and load results as layers in QGIS using the DB Manager. Screenshot of the DB Manager

If you still want to go through csv you must take care that WKT-geometry is included in the csv. You can make the query and save the result to CSV with ogr2ogr using the following command:

ogr2ogr -f CSV -lco GEOMETRY=AS_WKT -sql "select avi, st_union(geom) geom from maastokartta_250k.hallintoalue group by avi" avis.csv PG:"host=** dbname=** user=** password=**"

Then you can just drag and drop the csv to QGIS (This works at least in the 2.14 not sure about the older versions).

0
4

Another way to approach this, that may give you some flexibility for the longer term, is to create a view based on your query in PostgreSQL, then load that into QGIS with the standard PostGIS data loader.

  1. Basic SQL Query in Postgres: Basic SQL Query

  2. Create View from SQL Query, with unique ID View from SQL Query in PostgreSQL

  3. Load View in QGIS Load view in QGIS from PostgreSQL

  4. Unioned layer displayed in QGIS Union layer in QGIS

The benefit to saving the view in PostgreSQL is that you are then able to reuse that SQL query in the case that others need the same information.
This method also moves the processing as much as possible to the database, so QGIS only has to handle the display regeneration.

Finally, as with any software, especially the open-source packages out there, I encourage you to read the documentation. It is particularly good for both QGIS and PostgreSQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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