<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Franklin Gothic Book";
panose-1:2 11 5 3 2 1 2 2 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Ballongtext Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.gmail-apple-tab-span
{mso-style-name:gmail-apple-tab-span;}
span.E-postmall18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.BallongtextChar
{mso-style-name:"Ballongtext Char";
mso-style-priority:99;
mso-style-link:Ballongtext;
font-family:"Tahoma","sans-serif";
mso-fareast-language:SV;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="SV" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><a name="T_Default_Reply"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Good! And good to know.<o:p></o:p></span></a></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Franklin Gothic Book","sans-serif";color:black">Karl-Magnus</span></b><span style="font-size:10.0pt;font-family:"Franklin Gothic Book","sans-serif";color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">Från:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Árni Geirsson [mailto:arni@alta.is]
<br>
<b>Skickat:</b> den 12 april 2017 15:19<br>
<b>Till:</b> Steve Toutant<br>
<b>Kopia:</b> Karl Magnus Jönsson; qgis-user@lists.osgeo.org<br>
<b>Ämne:</b> Re: [Qgis-user] Unique IDs in a PostGIS view<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">I left the over clause empty. Based on your suggestion, I tried the following:<o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<div>
<p class="MsoNormal">SELECT row_number() over (<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="gmail-apple-tab-span"> </span>
order by <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="gmail-apple-tab-span">
</span><a href="http://fillline_a.id">fillline_a.id</a>,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="gmail-apple-tab-span">
</span>(st_dumppoints(fillline_a.geom)).path, <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="gmail-apple-tab-span">
</span>st_x((st_dumppoints(fillline_a.geom)).geom),<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="gmail-apple-tab-span">
</span>st_y((st_dumppoints(fillline_a.geom)).geom)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="gmail-apple-tab-span"> </span>
) as id,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">(fillline_a.z)::real AS z,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">(st_dumppoints(fillline_a.geom)).path[1] as p,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">st_x((st_dumppoints(fillline_a.geom)).geom) as x,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">st_y((st_dumppoints(fillline_a.geom)).geom) as y,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">((st_dumppoints(fillline_a.geom)).geom)::geometry(Point,3057) AS geom<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">FROM v0103.fillline_a<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<div>
<p class="MsoNormal">with t as (<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">select z, (st_dumppoints(fillline_a.geom)).geom from v0103.fillline_a)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">select row_number() over () as id, z, geom from t<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Problem solved :)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Thanks for the input.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Árni<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><br clear="all">
<o:p></o:p></p>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<p class="MsoNormal"><span style="color:black">Árni Geirsson</span><br>
<b><span style="font-size:10.0pt;color:#666666">Alta ehf</span></b><span style="font-size:10.0pt;color:#666666"> // +354 582 5000 // +354 897 9549</span><o:p></o:p></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">On 12 April 2017 at 12:48, Steve Toutant <<a href="mailto:Steve.Toutant@inspq.qc.ca" target="_blank">Steve.Toutant@inspq.qc.ca</a>> wrote:<o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"">It would help to see how you used row_number and what field you put in it.<o:p></o:p></span></p>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"">You could create row_number after the dumping<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"">select row_number() OVER (ORDER BY path,st_x,st_y.......) AS id, *<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""> from <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"">(select .</span><span style="font-size:9.5pt;font-family:"Verdana","sans-serif"">ST_DumpPoints..... from ...)</span><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><br>
<span style="color:#990099">-----"Qgis-user" <<a href="mailto:qgis-user-bounces@lists.osgeo.org" target="_blank">qgis-user-bounces@lists.osgeo.org</a>> a écrit : -----</span><o:p></o:p></span></p>
<div>
<div style="border:none;border-left:solid black 1.5pt;padding:0cm 0cm 0cm 4.0pt">
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"">A : Karl Magnus Jönsson <<a href="mailto:Karl-Magnus.Jonsson@kristianstad.se" target="_blank">Karl-Magnus.Jonsson@kristianstad.se</a>><br>
De : Árni Geirsson <br>
Envoyé par : "Qgis-user" <br>
Date : 12/04/2017 07:13<br>
Cc: "<a href="mailto:qgis-user@lists.osgeo.org" target="_blank">qgis-user@lists.osgeo.org</a>" <<a href="mailto:qgis-user@lists.osgeo.org" target="_blank">qgis-user@lists.osgeo.org</a>><br>
Objet : Re: [Qgis-user] Unique IDs in a PostGIS view<o:p></o:p></span></p>
<div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><o:p> </o:p></span></p>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"">Thanks for the suggestion Karl.<o:p></o:p></span></p>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"">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.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"">Árni<o:p></o:p></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><br clear="all">
<o:p></o:p></span></p>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><o:p> </o:p></span></p>
</div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:black">Árni Geirsson</span><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><br>
<b><span style="color:#666666">Alta ehf</span></b><span style="color:#666666"> //
<a href="tel:582%205000" target="_blank">+354 582 5000</a> // <a href="tel:897%209549" target="_blank">
+354 897 9549</a></span><o:p></o:p></span></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><o:p> </o:p></span></p>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"">On 12 April 2017 at 09:45, Karl Magnus Jönsson <<a href="mailto:Karl-Magnus.Jonsson@kristianstad.se" target="_blank">Karl-Magnus.Jonsson@kristianstad.se</a>> wrote:<o:p></o:p></span></p>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><a name="m_8332669204377694753_m_-675827643468774"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Hi!</span></a><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I’ve used something like this to get unique Ids:</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">SELECT row_number() OVER (ORDER BY s.omrade_id, s.kod) AS id, …. ORDER BY s.omrade_id,
s.kod;</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">If the ordering isn’t necessary I guess you can skip that.</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><a href="https://www.postgresql.org/docs/current/static/functions-window.html" target="_blank"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif"">https://www.postgresql.org/docs/current/static/functions-window.html</span></a><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><b><span style="font-size:10.0pt;font-family:"Franklin Gothic Book","sans-serif";color:black">Karl-Magnus Jönsson</span></b><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">Från:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Qgis-user [mailto:<a href="mailto:qgis-user-bounces@lists.osgeo.org" target="_blank">qgis-user-bounces@lists.osgeo.org</a>]
<b>För </b>Árni Geirsson<br>
<b>Skickat:</b> den 12 april 2017 11:34<br>
<b>Till:</b> <a href="mailto:qgis-user@lists.osgeo.org" target="_blank">qgis-user@lists.osgeo.org</a><br>
<b>Ämne:</b> [Qgis-user] Unique IDs in a PostGIS view</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">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.<o:p></o:p></p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Are there any smart tricks out there to work around this or other means of generating the IDs?<o:p></o:p></p>
</div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:12.0pt"><span style="color:black">Árni Geirsson</span><o:p></o:p></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><o:p> </o:p></span></p>
</div>
</div>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">_______________________________________________<br>
Qgis-user mailing list<br>
<a href="mailto:Qgis-user@lists.osgeo.org" target="_blank">Qgis-user@lists.osgeo.org</a><br>
List info: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">
https://lists.osgeo.org/mailman/listinfo/qgis-user</a><br>
Unsubscribe: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">
https://lists.osgeo.org/mailman/listinfo/qgis-user</a></span><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><o:p></o:p></span></p>
</div>
</div>
</div>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><br>
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.<br>
<br>
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.<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
</body>
</html>