Unique IDs in a PostGIS view

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Unique IDs in a PostGIS view

Árni Geirsson
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
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: Unique IDs in a PostGIS view

Karl Magnus Jönsson

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:[hidden email]] För Árni Geirsson
Skickat: den 12 april 2017 11:34
Till: [hidden email]
Ä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
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: Unique IDs in a PostGIS view

Árni Geirsson
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 // +354 897 9549

On 12 April 2017 at 09:45, Karl Magnus Jönsson <[hidden email]> 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:[hidden email]] För Árni Geirsson
Skickat: den 12 april 2017 11:34
Till: [hidden email]
Ä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
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: Unique IDs in a PostGIS view

Steve.Toutant
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" <[hidden email]> a écrit : -----
A : Karl Magnus Jönsson <[hidden email]>
De : Árni Geirsson
Envoyé par : "Qgis-user"
Date : 12/04/2017 07:13
Cc: "[hidden email]" <[hidden email]>
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 // +354 897 9549

On 12 April 2017 at 09:45, Karl Magnus Jönsson <[hidden email]> 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:[hidden email]] För Árni Geirsson
Skickat: den 12 april 2017 11:34
Till: [hidden email]
Ä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
[hidden email]
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
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: 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 
(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 <[hidden email]> 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" <[hidden email]> a écrit : -----
A : Karl Magnus Jönsson <[hidden email]>
De : Árni Geirsson
Envoyé par : "Qgis-user"
Date : 12/04/2017 07:13
Cc: "[hidden email]" <[hidden email]>
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 // <a href="tel:582%205000" value="+3545825000" target="_blank">+354 582 5000 // <a href="tel:897%209549" value="+3548979549" target="_blank">+354 897 9549

On 12 April 2017 at 09:45, Karl Magnus Jönsson <[hidden email]> 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:[hidden email]] För Árni Geirsson
Skickat: den 12 april 2017 11:34
Till: [hidden email]
Ä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
[hidden email]
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
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: Unique IDs in a PostGIS view

Karl Magnus Jönsson

Good! And good to know.

 

Karl-Magnus

 

Från: Árni Geirsson [mailto:[hidden email]]
Skickat: den 12 april 2017 15:19
Till: Steve Toutant
Kopia: Karl Magnus Jönsson; [hidden email]
Ämne: Re: [Qgis-user] Unique IDs in a PostGIS view

 

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 <[hidden email]> 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" <[hidden email]> a écrit : -----

A : Karl Magnus Jönsson <[hidden email]>
De : Árni Geirsson
Envoyé par : "Qgis-user"
Date : 12/04/2017 07:13
Cc: "[hidden email]" <[hidden email]>
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 // <a href="tel:582%205000" target="_blank">+354 582 5000 // <a href="tel:897%209549" target="_blank"> +354 897 9549

 

On 12 April 2017 at 09:45, Karl Magnus Jönsson <[hidden email]> 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:[hidden email]] För Árni Geirsson
Skickat: den 12 april 2017 11:34
Till: [hidden email]
Ä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
[hidden email]
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
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user