[Qgis-user] Unique IDs in a PostGIS view

[Árni Geirsson]

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.Toutant at 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-bounces at lists.osgeo.org> a écrit : -----
> A : Karl Magnus Jönsson <Karl-Magnus.Jonsson at kristianstad.se>
> De : Árni Geirsson
> Envoyé par : "Qgis-user"
> Date : 12/04/2017 07:13
> Cc: "qgis-user at lists.osgeo.org" <qgis-user at 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-bounces at lists.osgeo.org] *För *Árni
>> Geirsson
>> *Skickat:* den 12 april 2017 11:34
>> *Till:* qgis-user at 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 at 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.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20170412/1bde53f8/attachment.html>