[Qgis-user] postgis layer from a view

[Bernhard Ströbl]

Dear Lionel,

I guess the problem is that you join spatial.commune at 
residentiel.nrj_sourcesfixes. If this is a n:1 or n:m - join, gids 
appear several times, thus gid is not unique (postgis provider tells 
you). Try using residentiel.nrj_sourcesfixes' (or one of its underlying 
tables') primary key in the view (just a shot in the dark) in order to 
get unique pk-values.
On the other hand consider if it makes sense that the same geometry is 
allowed to be contained several times. How do you want to show this in QGIS?
One way to avoid this would be to join residentiel.nrj_sourcesfixes at 
spatial.commune but then you will have fewer datasets than now. Try to 
analyze which gids are contained more than once and why.

Benno

Lionel Roubeyrie schrieb:
> Hi Bernhard,
> thanks to responding. I have modified the view to retrieve the "gid"
> column form a table, which is a primary key with unique values, and
> not from a sequence, but postgis provider complains again about no
> unique values oO
> Here is the table :
> CREATE TABLE spatial.commune
> (
>   code_insee character varying(5),
>   region character varying(20),
>   the_geom geometry,
>   gid serial NOT NULL,
>   CONSTRAINT commune_pkey PRIMARY KEY (gid),
>   CONSTRAINT commune_gid_key UNIQUE (gid),
>   CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
>   CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
> 'MULTIPOLYGON'::text OR the_geom IS NULL),
>   CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2154)
> )
> 
> and the problematic view :
> CREATE OR REPLACE VIEW residentiel.spatial_nrj_sourcesfixes AS
>  SELECT com.gid, nrj.id, nrj.cmbl, nrj.commune, nrj.region, nrj.cha,
> nrj.ecs, nrj.cui, nrj.spe, nrj.total, com.the_geom
>    FROM residentiel.nrj_sourcesfixes nrj
>    JOIN spatial.commune com ON nrj.commune::text = com.code_insee::text;
> 
> the residentiel.nrj_sourcesfixes is another view.
> 
> 
> 2010/6/1 Bernhard Ströbl <Bernhard.Stroebl at jena.de>:
>> Dear Lionel,
>>
>> AFAIK the view must contain a field being defined as primary key in the
>> underlying table, moreover this field must be of type integer.
>> The view must be defined in a way that every element (thus every primary-key
>> value) is unique. In case the view contains a join resulting in several
>> primary-key fields you can choose which one QGIS should use.
>> So your view definition lacks the primary-key field, although the sequence -
>> of course - populates gid with unique values.
>> Maybe you could post the complete view definition (and the table
>> definitions, too) and we could see if we could help you in creating a
>> working view.
>>
>> regards
>>
>> Benno
>>
>> Lionel Roubeyrie schrieb:
>>> Hi all,
>>> I can't load datas from a postgis view, the loader complains about no
>>> uniques values and int4 type, however the view starts with "SELECT
>>> nextval('my_seq'::regclass)::integer AS gid, com.the_geom, ..." (the
>>> gid column is not listed in the error message) which works perfectly
>>> if datas are stored in a table. Is there a trick to load datas from
>>> views, and more generally is it possible to force the postgis provider
>>> to create an internal unique key?
>>> Thanks
>>>
>>
>> ________ Information from NOD32 ________
>> This message was checked by NOD32 Antivirus System for Linux Mail Server.
>> http://www.nod32.com
>>
> 
> 
> 



________ Information from NOD32 ________
This message was checked by NOD32 Antivirus System for Linux Mail Server.
http://www.nod32.com