[Qgis-user] Constraints on postgresql views

[Lionel Roubeyrie]

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