I left the over clause empty. Based on your suggestion, I tried the following:
SELECT row_number() over ( order by fillline_a.id, (st_dumppoints(fillline_a.geom)).path, st_x((st_dumppoints(fillline_a.geom)).geom), st_y((st_dumppoints(fillline_a.geom)).geom) ) as id, (fillline_a.z)::real AS z, (st_dumppoints(fillline_a.geom)).path[1] as p, st_x((st_dumppoints(fillline_a.geom)).geom) as x, st_y((st_dumppoints(fillline_a.geom)).geom) as y, ((st_dumppoints(fillline_a.geom)).geom)::geometry(Point,3057) AS geom FROM v0103.fillline_a The problem here is that everything in the over clause remains constant for each line and therefore the row_number is not incremented for each point. Maybe I am doing something wrong. Anyway, I wonder if it should count as a bug in QGIS that I can use the nextval() method to see the correctly constructed view when listed as a table in DB Manager but not on the map canvas. Anyway, I found a simple way around the numbering problem: with t as ( select z, (st_dumppoints(fillline_a.geom)).geom from v0103.fillline_a) select row_number() over () as id, z, geom from t Problem solved :) Thanks for the input. Árni Árni Geirsson *Alta ehf* // +354 582 5000 // +354 897 9549 On 12 April 2017 at 12:48, Steve Toutant <steve.tout...@inspq.qc.ca> wrote: > It would help to see how you used row_number and what field you put in it. > > You could create row_number after the dumping > select row_number() OVER (ORDER BY path,st_x,st_y.......) AS id, * > from > (select .ST_DumpPoints..... from ...) > > -----"Qgis-user" <qgis-user-boun...@lists.osgeo.org> a écrit : ----- > A : Karl Magnus Jönsson <karl-magnus.jons...@kristianstad.se> > De : Árni Geirsson > Envoyé par : "Qgis-user" > Date : 12/04/2017 07:13 > Cc: "qgis-user@lists.osgeo.org" <qgis-user@lists.osgeo.org> > Objet : Re: [Qgis-user] Unique IDs in a PostGIS view > > > Thanks for the suggestion Karl. > I have used row_number() also but in the case of dumping vertex points > from a line, multiple points are created from each line feature and > therefore, the row_number is repeated for all points on the same line. > > Árni > > > Árni Geirsson > *Alta ehf* // +354 582 5000 <582%205000> // +354 897 9549 <897%209549> > > On 12 April 2017 at 09:45, Karl Magnus Jönsson <Karl-Magnus.Jonsson@ > kristianstad.se> wrote: > >> Hi! >> >> I’ve used something like this to get unique Ids: >> >> SELECT row_number() OVER (ORDER BY s.omrade_id, s.kod) AS id, …. ORDER BY >> s.omrade_id, s.kod; >> >> >> >> If the ordering isn’t necessary I guess you can skip that. >> >> >> >> https://www.postgresql.org/docs/current/static/functions-window.html >> >> >> >> >> >> *Karl-Magnus Jönsson* >> >> >> >> *Från:* Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] *För *Árni >> Geirsson >> *Skickat:* den 12 april 2017 11:34 >> *Till:* qgis-user@lists.osgeo.org >> *Ämne:* [Qgis-user] Unique IDs in a PostGIS view >> >> >> >> I am trying to create a view that extracts vertices from lines using >> ST_DumpPoints and to get unique IDs I have set up a sequence to generate >> the numbers using nextval(). The query executes normally in the DB Mananger >> and I see the results as a table in the table view. However, when I attempt >> to load the results of the view as a layer in QGIS, I get an error: >> "Database error: ERROR: cannot execute nextval() in a read-only >> transaction". No features are loaded. >> >> Are there any smart tricks out there to work around this or other means >> of generating the IDs? >> >> >> >> Árni Geirsson >> > > _______________________________________________ > Qgis-user mailing list > Qgis-user@lists.osgeo.org > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user > > Si vous n'êtes pas le destinataire de ce message, veuillez le détruire > près avoir informé l'expéditeur de son erreur. Par ailleurs, il est > interdit de copier ou de modifier ce courriel sans l'autorisation de > l'auteur. > > L'Institut national de santé publique du Québec n'assume aucune > responsabilité à l'égard du contenu des messages personnels envoyés par ses > employés. >
_______________________________________________ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
