2

I'm working with PostGIS database. I would like to create temporary table with geometry and add it as a layer to QGIS. There is no problem with regular tables, but I can't figure out how to add temporary tables. I checked source code of DB Manager plugin and found this key line: uri.setDataSource("", u"(%s\n)" % sql, geomCol, "", uniqueCol). With this in mind I created my code:

uri = QgsDataSourceURI()
uri.setConnection(server, port, db_name, login, password)
sql = "CREATE TEMPORARY TABLE temp_1 AS SELECT * FROM some_spatial_table"
uri.setDataSource('', u'(%s\n)' % sql, 'geom', '', 'gid') # 'geom' and 'gid' are present in the table
vlayer = QgsVectorLayer(uri.uri(), 'layer_name', "postgres")
QgsMapLayerRegistry.instance().addMapLayer(vlayer)

The SQL-query is Ok - I'm able to execute it in DB Manager and add resulting table to map. But my code doesn't produce a layer in QGIS and there is no exception raised. What is wrong?

UPD: There is an output in PostGIS tab in QGIS logs:

Unable to execute the query.
            The error message from the database was:
            ERROR:  syntax error at or near "CREATE"
            LINE 2:                         CREATE or replace TEMP VIEW sp_temp_...
                                            ^
            .
            SQL: SELECT * FROM (
                                    CREATE or replace TEMP VIEW sp_temp_2015_10_25_02_29 AS
                                    WITH posts AS (
                                    SELECT post_name, date, pollutant, measurement, type_measure
                                    FROM air_observations
                                    WHERE pollutant = '301' AND date = '2014-05-05'
                                    )
                                    SELECT air_posts.gid, air_posts.geom, air_posts.name,
                                    posts.date, posts.pollutant, posts.measurement, posts.type_measure
                                    FROM air_posts
                                    INNER JOIN posts
                                    ON air_posts.name = posts.post_name;
            ) AS "subQuery_0" LIMIT 1

Starting from "CREATE" and ending with ";" is an actual query that is passed as sql.

2
  • And if you create view with it, can you open it using add postgis layer? Commented Oct 24, 2015 at 20:51
  • @AlexandreNeto, see update to the question. Commented Oct 24, 2015 at 23:35

2 Answers 2

3

The code you are using is for adding and loading a datasource to the registry. But the SQL to use inside uri.setDataSource must return records. That's not the case of your query, that starts with a CREATE.

I believe that you need to create the temporary table in a different steep, and then add it to the registry.

Try this:

# Create the temporary table
db = QSqlDatabase.addDatabase("QPSQL")
db.setHostName(server)
db.setPort(port)
# non spatial table or view
db.setDatabaseName(db_name)
db.setUserName(login)
db.setPassword(password)
query = QSqlQuery(db)
query.exec_("CREATE TEMPORARY TABLE temp_1 AS SELECT * FROM some_spatial_table")

# Add the Layer
uri = QgsDataSourceURI()
uri.setConnection(server, port, db_name, login, password)
sql = "SELECT * FROM temp_1"
uri.setDataSource('', u'(%s\n)' % sql, 'geom', '', 'gid') # 'geom' and 'gid' are present in the table
vlayer = QgsVectorLayer(uri.uri(), 'layer_name', "postgres")
QgsMapLayerRegistry.instance().addMapLayer(vlayer)
1
  • Unfortunately temporary table is created during different transaction than sql that tries to select it, though it doesn't exist for uri.setDataSource() when it tries to access it. In my operational code I used psycopg2 to create temporary table and then select it via uri.setDataSource() and failed. Though I gave a try to your code and it failed as well - temp_1 was not found. Commented Oct 25, 2015 at 9:26
1

Ok, I figured it out. All I needed is to delete CREATE part from query. There is actually no need to create temporary table because we do not use interface that is used to add existing tables and views. Instead we use interface that executes SELECT clause. We just need to pass a clause that selects data that is needed (so it is already virtually created and there is no need to create tables explicitly).

uri = QgsDataSourceURI()
uri.setConnection(server, port, db_name, login, password)
sql = "SELECT * FROM some_spatial_table"
uri.setDataSource('', u'(%s\n)' % sql, 'geom', '', 'gid') # 'geom' and 'gid' are present in the table
vlayer = QgsVectorLayer(uri.uri(), 'layer_name', "postgres")
QgsMapLayerRegistry.instance().addMapLayer(vlayer)

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.