QGIS display points from query in Postgis

Question

I am creating a point geometries in a view from an x,y column in a postgresql/postgis table.

I do:

create or replace view my_points as select ST_SetSRID(ST_MakePoint(geo.x,geo.y), 2154) as geom, pg_typeof(geom) as type from table1 join table2 geo on table1.id = geo.id

Everything works fine in postgresql and I can see that this returns a view with a geometry in the column geom. Just for the sake of checking the type there is a second column type that checks if the format of geom is a geometry.

Now when I am in QGIS and try to bring the view in my layer list the following message appears:

Layer is not valid: The layer dbname='x' host=x port=5432 user='x' password='x' sslmode=x key='geom' srid=2154 type=POINT table="public"."my_points" (geom) sql= is not a valid layer and can not be added to the map

What is happening? Is not possible to create a geom in a query to be imported in QGIS?

ps: I use QGIS 2.8.1

Answer 1

You can load a PostGIS view in QGIS. However, your view needs to have a column with unique values for each row that can act as primary key. In QGIS, in the "Add PostGIS Table(s)" dialog, you need to specify this column in the "Primary Key" menu (in the screenshot below, this column is called id_tronc; the menu shows all available columns).

EDIT: The above solution will work, but the answer is not completely correct. QGIS does not need a Primary Key, at least newer versions. Problem was probably column type, see @tommaso-di-bucchianico's answer.

Answer 2

The problem is the field geom which is of type regtype. I don't know why exactly do this disturb Qgis but it does.

Change it to text and the view will work:

create or replace view my_points as
   select 
   ST_SetSRID(ST_MakePoint(geo.x,geo.y), 2154) as geom,
   pg_typeof(geom)::TEXT as type
   from table1
   join table2 geo on table1.id = geo.id

Answer 3

While specifying the primary key column in the "Add PostGIS Table(s)" dialog will certainly work, I wanted the convenience of a simple drag and drop (from Browser Panel to Layer Panel). What worked for me was to reorder the columns in the view definition so that the primary key column is first. Then I was able to simply drag the view from the Browser Panel directly into Layers Panel without error.