I use this sql query to draw a rectangle from a point layer (overview):

create or replace view view_test as
select name, width, height, ST_SetSRID(ST_GeomFromText('POLYGON (('
 ||ST_X(geom)-(width * (scale * 0.1))/2||' '||ST_Y(geom)-(height*(scale*0.1))/2||','
 ||ST_X(geom)-(width * (scale * 0.1))/2||' '||ST_Y(geom)+(height*(scale*0.1))/2||','
 ||ST_X(geom)+(width * (scale * 0.1))/2||' '||ST_Y(geom)+(height*(scale*0.1))/2||','
 ||ST_X(geom)+(width * (scale * 0.1))/2||' '||ST_Y(geom)-(height*(scale*0.1))/2||','
 ||ST_X(geom)-(width * (scale * 0.1))/2||' '||ST_Y(geom)-(height*(scale*0.1))/2||'))'),32632) as geom
from overview;

But QGIS isn't loading the results:

2017-07-14T21:38:12 1   dbname='XXXX' host=XX.XX.XXX.XXX port=5432 user='postgres' sslmode=disable key='width' table="public"."view_test" (geom) sql= is an invalid layer - not loaded

Whats wrong with my code?

  • 1
    forgot to include scale in the select clause? does that have an effect? – Steven Kay Jul 15 '17 at 17:30
  • right, scale was missing! Now I got the error, that there is no primary key defined. How do I solve that? – MAP Jul 15 '17 at 19:19
  • If you do not have a unique field in the table, you must define a field with a SQL subquery (SELECT row_number() over () AS uid, * from overview) – Mike Jul 17 '17 at 16:23

From the QGIS wiki:

When you don't have a unique id for each row you want to display, you can create on on the fly using the SQL windows to load your geometries. Simply add in the select : SELECT row_number() OVER( ORDER BY field1,field2 ) AS id_qgis , and select id_qgis as the id column in the QGIS list. It is necessary to specify the Order By clause to ensure the same ID is always assigned to the same row, for every query, regardless of the usage of fields and joins that do affect the row order in the result set.

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.