QGIS » QGIS Application

I have three tables, each with zero-to-many relations to connect from spot (providing location) -> sample -> analysis. I would like to show the analyses at each point using a view. But when I try to add the view to QGIS, I get a message with a error message lecture about unique keys, and I cannot add the view.

This bug can be reproduced using this example schema and data:

Schema¶

CREATE TABLE spot
(
  gid serial PRIMARY KEY NOT NULL,
  geometry geometry,
  id character varying(50) UNIQUE NOT NULL
);

CREATE TABLE sample
(
  sid serial PRIMARY KEY NOT NULL,
  identifier character varying(50),
  spot_id character varying(50),
  CONSTRAINT sample_spot_id_fkey FOREIGN KEY (spot_id)
      REFERENCES spot (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION
);

CREATE TABLE analysis
(
  sid serial PRIMARY KEY NOT NULL,
  sample_sid integer,
  parameter character varying,
  result real,
  CONSTRAINT analysis_sample_sid FOREIGN KEY (sample_sid)
      REFERENCES sample (sid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE OR REPLACE VIEW spot_analysis AS 
 SELECT ana.sid AS analysis_sid, spt.geometry, spt.id AS spot_id, smp.sid AS sample_sid, ana.parameter, ana.result
   FROM spot spt
   JOIN sample smp ON spt.id::text = smp.spot_id::text
   JOIN analysis ana ON smp.sid = ana.sample_sid;

Data¶

INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
    VALUES (_,_,'spot','geometry',2,-1,'POINT');

INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
    VALUES (_,_,'spot_analysis','geometry',2,-1,'POINT');

INSERT INTO spot (geometry, id) VALUES ('0101000000000000000000F03F000000000000F03F', 'spot a');
INSERT INTO spot (geometry, id) VALUES ('0101000000000000000000F03F0000000000000040', 'spot b');

INSERT INTO sample (identifier, spot_id) VALUES ('samp 1', 'spot a');
INSERT INTO sample (identifier, spot_id) VALUES ('samp 2', 'spot b');
INSERT INTO sample (identifier, spot_id) VALUES ('samp 3', 'spot a');
INSERT INTO sample (identifier, spot_id) VALUES ('samp 4', 'spot b');

INSERT INTO analysis (sample_sid, parameter, result) VALUES (1, 'foo', 3.4);
INSERT INTO analysis (sample_sid, parameter, result) VALUES (1, 'bla', 4.1);
INSERT INTO analysis (sample_sid, parameter, result) VALUES (2, 'foo', 3.0);
INSERT INTO analysis (sample_sid, parameter, result) VALUES (2, 'lol', 54.2);
INSERT INTO analysis (sample_sid, parameter, result) VALUES (3, 'lol', 65.2);
INSERT INTO analysis (sample_sid, parameter, result) VALUES (3, 'foo', 2.0);

Description of behaviour¶

Here is the full text of the error message shown after trying to add the PostGIS vector "spot_analysis":
No suitable key column in view

• The view 'public.spot_analysis' has no column suitable for use as a unique key.
• Qgis requires that the view has a column that can be used as a unique key. Such a column should be derived from a table column of type int4 and be a primary key, have a unique constraint on it, or be a PostgreSQL oid column. To improve performance the column should also be indexed.
• The view you selected has the following columns, none of which satisfy the above conditions:
• 'geometry' derives from 'public.spot.geometry' and is not suitable (type is geometry) and does not have a suitable constraint)
• 'id' derives from 'public.spot.id' and is not suitable (type is varchar) and has a suitable constraint)
• 'parameter' derives from 'public.analysis.parameter' and is not suitable (type is varchar) and does not have a suitable constraint)
• 'result' derives from 'public.analysis.result' and is not suitable (type is float4) and does not have a suitable constraint)
• 'sample_sid' derives from 'public.analysis.sample_sid' and is not suitable (type is int4) and does not have a suitable constraint)
• 'sid' derives from 'public.analysis.sid' and is suitable.
• 'spot_id' derives from 'public.sample.spot_id' and is not suitable (type is varchar) and does not have a suitable constraint)
• Note: 'sid' initially appeared suitable but does not contain unique data, so is not suitable.

This analysis is a bit off. It references column names used from the original tables used to construct the view. In this case, I have column names "sid" in sample and analysis. I routinely use similar views that stop at the sample join, a case where there is only one column named "sid" in the mix. This problem appears when several "sid" columns exist in the relations.

The analysis_sid key in the view is unique, and will always be unique given the constraints and use of joins. Here is what the data look like (all columns except geometry):

	analysis_sid		spot_id		sample_sid		parameter		result
	1		spot a		1		foo		3.4
	2		spot a		1		bla		4.1
	3		spot b		2		foo		3
	4		spot b		2		lol		54.2
	5		spot a		3		lol		65.2
	6		spot a		3		foo		2

I can reproduce the behaviour with QGIS 1.0.2 and 1.2.0 via OSGeo4W. I've tried this on different PostGIS servers (versions/platforms), so I'm pretty confident PostGIS has nothing to do with it.

This may be related to #1535