QGIS » Quantum GIS Desktop

SQL window, retriving the data worked, loading as layer didn't (but didn't visually report any error)

Can you reproduce the problem with something that doesn't need the latest postgis?

BTW you should "visually" see the error in the message log (and the popup over the in the main window status bar).

I have changed my code slightly from the original code I posted on the mailing list and I get a slightly different error:

This query works and adds a layer into QGIS:

uri.setDataSource('',"(SELECT int4(row_number() OVER ()) AS id,ST_GeomFromText('POINT(451583 129144)',27700) As geom)", "geom",'',"id")

The following doesn't work:

uri.setDataSource('',"(SELECT * From \"designations\".\"table\" where ST_DWithin(ST_GeomFromText('POINT(451583 129144)',27700), \"table\".\"geom\", 2000)", "geom",'',"gid")

This gives the following error:

Unable to execute the query.
The error message from the database was:
ERROR: syntax error at or near "AS"
LINE 1: ... 129144)',27700), "listed_building"."geom", 2000) AS "subQue...
^
.
SQL: SELECT * FROM (SELECT * From "designations"."listed_building" where ST_DWithin(ST_GeomFromText('POINT(451583 129144)',27700), "listed_building"."geom", 2000) AS "subQuery_0" LIMIT 1


Does seem like there is an extra SELECT statement added to the from of the query as it says SELECT * FROM (SELECT * From. The from in my code is From not FROM.

James Stott wrote:

The following doesn't work:

uri.setDataSource('',"(SELECT * From \"designations\".\"table\" where ST_DWithin(ST_GeomFromText('POINT(451583 129144)',27700), \"table\".\"geom\", 2000)", "geom",'',"gid")

And that's not just because the closing paren is missing after "2000)"?

Adding the ) leads to this error:

Unable to execute the query.
The error message from the database was:
ERROR: subquery in FROM must have an alias
LINE 1: SELECT * FROM (SELECT * From designations.listed_building wh...
^
HINT: For example, FROM (SELECT ...) [AS] foo.
.
SQL: SELECT * FROM (SELECT * From designations.listed_building where ST_DWithin(ST_GeomFromText('POINT',27700), listed_building.geom, 2000)), geom,'',gid) AS "subQuery_0" LIMIT 1

James Stott wrote:

SQL: SELECT * FROM (SELECT * From designations.listed_building where ST_DWithin(ST_GeomFromText('POINT',27700), listed_building.geom, 2000)), geom,'',gid) AS "subQuery_0" LIMIT 1

Please check the syntax again. Looks like you misplaced the " this time ;)

Apologies, it was a syntax problem.

This worked:

query = "(SELECT * From \"designations\".\"table\"where ST_DWithin(ST_GeomFromText('POINT(" + str(self.dlg.getEasting()) + " " + str(self.dlg.getNorthing()) + ")',27700), \"table\".\"geom\","+ self.dlg.getBuffSize() +"))"
uri.setDataSource('', query, "geom",'',"gid")

This query doesn't need the latest postgis, nor any table:

select (ST_Dump(ST_ConvexHull(st_collect('POINT(0 0)'::geometry)))).*

Try it, replacing "unioned" and "wrongsnap" with a valid geometry column and table name.
It's still failing as of:

QGIS version 1.9.0-Master
QGIS code revision fe8385e
Compiled against Qt 4.8.1
Running against Qt 4.8.1
Compiled against GDAL/OGR 2.0dev
Running against GDAL/OGR 2.0dev
GEOS Version 3.4.0dev
PostgreSQL Client Version 9.1.5
SpatiaLite Version 2.4.0
QWT Version 5.2.2
PROJ.4 Version 480

This copy of QGIS writes debugging output.

I still tried trough the DBManager, don't know if it's possible (or how) to reproduce directly against the PostGIS Data Provider. Maybe Giuseppe knows ?

Sandro Santilli wrote:

I still tried trough the DBManager, don't know if it's possible (or how) to reproduce directly against the PostGIS Data Provider. Maybe Giuseppe knows ?

Try the following code from QGis Python Console:

uri = QgsDataSourceURI()
uri.setConnection(host, u"%s" % port, dbname, user, passw)
uri.setDataSource( "", u"(%s\n)" % query, geomcol, "", keycol )

vl = QgsVectorLayer( uri.uri(), "layername", "postgres" )
QgsMapLayerRegistry.instance().addMapLayer( vl )

Giuseppe Sucameli wrote:

Try the following code from QGis Python Console: [...]

Should that reproduce the problem? Because it doesn't here.

Jürgen Fischer wrote:

Giuseppe Sucameli wrote:

Try the following code from QGis Python Console: [...]

Should that reproduce the problem? Because it doesn't here.

I know, neither here. But that code is quite identical to the one used in DBManager.

I've no time to debug this. Can't you reproduce it there Giuseppe ?

No way to estimate extent of a query. You must necessarely run it.

See also http://trac.osgeo.org/postgis/ticket/510

select 1 as id, (ST_Dump(ST_ConvexHull(st_collect('POINT(0 0)'::geometry)))).* FROM "mytablename" 

It works until the "use table estimated metadata" option is checked in the connection settings, in that case I get the following error:

Erroneous query: SELECT DISTINCT CASE WHEN upper(geometrytype("geom")) IN ('POINT','MULTIPOINT','POINTM','MULTIPOINTM') THEN 'POINT' WHEN upper(geometrytype("geom")) IN ('LINESTRING','MULTILINESTRING','LINESTRINGM','MULTILINESTRINGM') THEN 'LINESTRING' WHEN upper(geometrytype("geom")) IN ('POLYGON','MULTIPOLYGON','POLYGONM','MULTIPOLYGONM') THEN 'POLYGON' END, st_srid("geom") FROM (SELECT "geom" FROM ""."(select 1 as id, (ST_Dump(ST_ConvexHull(st_collect('POINT'::geometry)))).* FROM ""province""
) AS ""subQuery_0""" WHERE "geom" IS NOT NULL LIMIT 100) AS t returned 7 [ERROR: zero-length delimited identifier at or near """"
LINE 1: ...ON' END, st_srid("geom") FROM (SELECT "geom" FROM ""."(selec...
^
]

EDIT:

the problem occurs also with very simple queries like:

SELECT 1 as "id", 'POINT(1 10)'::geometry as "geom"