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.roubey...@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 <yjaco...@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@lists.osgeo.org >> http://lists.osgeo.org/mailman/listinfo/qgis-user >> > > > > -- > Lionel Roubeyrie > lionel.roubey...@gmail.com > http://youarealegend.blogspot.com > -- Lionel Roubeyrie lionel.roubey...@gmail.com http://youarealegend.blogspot.com _______________________________________________ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user