[Qgis-user] Constraints on postgresql views

[Lionel Roubeyrie]

Ok, seems to be a view problem (and a QGIS one...), because using
directly this view on the source table 'tmp' :
SELECT max(f.gid), st_multi(st_union(f.the_geom)) AS the_geom
   FROM ( SELECT gid, (st_dump(tmp.the_geom)).geom AS the_geom
           FROM tmp WHERE numero='D941') f
QGIS complaints about no unique values, but if I run the same query
through a CREATE TABLE, it works!
Is there a reason for that?


2010/4/2 Lionel Roubeyrie <lionel.roubeyrie at gmail.com>:
> My previous message was send to quickly, sorry (gmail and his keyboard
> shotcuts...)
> In fact I have a linestring table containing roads, each road is
> represented with multiple portions, each portion having a attribute
> corresponding to the daily traffic average.
> I want to simplify the table (too many records) by merging portions
> (lines) of the same named road AND the same traffic, but keeping the
> real geometry of the road, if 2 portions start/finish by the points A
> B/C D, I don't want the result to be BACD, then I try some
> possibilities with ST_collect, don't know if it's the good choice...
>
> 2010/4/2 Lionel Roubeyrie <lionel.roubeyrie at gmail.com>:
>> Hi Yves,
>> thanks to respond, but v_temp is also a view containing a gid::serial
>> derived from the tmp table. If I try this:
>> SELECT max(f.gid), st_multi(st_union(f.the_geom)) AS the_geom
>>   FROM ( SELECT gid, (st_dump(v_tmp.the_geom)).geom AS the_geom
>>           FROM v_tmp) f
>> I get :
>> The view 'public.v_tmp2' 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:
>> 'gid' derives from 'public.tmp.gid' and is suitable.
>> 'the_geom' derives from 'public.tmp.the_geom' and is not suitable
>> (type is geometry) and does not have a suitable constraint)
>> Note: 'gid' initially appeared suitable but does not contain unique
>> data, so is not suitable.
>>
>> 2010/4/2 Yves Jacolin <yjacolin at free.fr>:
>>> Le vendredi 02 avril 2010 10:16:40, Lionel Roubeyrie a écrit :
>>>> Hi all,
>>>> I try to construct views from a lines table to group roads' portions
>>>> together, but I fall on this view when called from QGIS :
>>>> SELECT 1::oid AS oid, ST_Multi(ST_Union(f.the_geom)) as the_geom
>>>> FROM (SELECT (ST_Dump(the_geom)).geom As the_geom FROM v_tmp) As f
>>>>
>>>> but qgis says :
>>>> The view 'public.v_tmp2' 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:
>>>> 'the_geom' derives from 'public.tmp.the_geom' and is not suitable
>>>> (type is geometry) and does not have a suitable constraint)
>>>>
>>>> thanks if someone can explain me why this doesn't work
>>> Lionel,
>>>
>>> Just add an int4 column like this :
>>>
>>> SELECT  f.id,  AS oid, ST_Multi(ST_Union(f.the_geom)) as the_geom FROM (SELECT
>>> id,_Dump(the_geom)).geom As the_geom FROM v_tmp) As f
>>>
>>> id column in v_tmp must exists. You can add one in v_temp if v_temp is not a
>>> view:
>>> ALTER TABLE v_temp ADD COLUMN id (serial);
>>>
>>> Regards,
>>>
>>> Y.
>>> --
>>> Yves Jacolin
>>>
>>> http://yjacolin.gloobe.org
>>> _______________________________________________
>>> Qgis-user mailing list
>>> Qgis-user at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>>>
>>
>>
>>
>> --
>> Lionel Roubeyrie
>> lionel.roubeyrie at gmail.com
>> http://youarealegend.blogspot.com
>>
>
>
>
> --
> Lionel Roubeyrie
> lionel.roubeyrie at gmail.com
> http://youarealegend.blogspot.com
>



-- 
Lionel Roubeyrie
lionel.roubeyrie at gmail.com
http://youarealegend.blogspot.com