Re: [Qgis-user] can't editing view using rules

[Karl-Magnus Jönsson]

Hi again!
I've made some more testing. By adding a RETURNING clause to the rule I 
succeeded to insert features. But since it appears that the RETURNING clause 
has to return all the attributes in the view and my view is complex and 
consists of attributes from several tables I couldn't just add RETURNING * (It 
will just return the attributes from the table inserted into). I had to list 
all fields in right order, make some attributes up and cast them to the right 
data type. Like this:

CREATE OR REPLACE RULE lekplatsredskap_ins AS
ON INSERT TO park.v_lekplatsredskap DO INSTEAD  INSERT INTO 
park.skotselpunkt (kod, antal, inkopsar, fabrikat, nr, fritext, utforare, geom)
  VALUES (new.kod, new.antal, new.inkopsar, new.fabrikat, new.nr, new.fritext, 
new.utforare, new.geom)
  RETURNING skotselobj_id,1,kod, 'text'::character 
varying,1234,4567,'plkod'::text, antal, inkopsar, fabrikat, nr, 
fritext,utforare,'2017-01-18'::date,'b_resultat'::character 
varying,'status'::character varying,geom ;

Please let me know if you find a way to solve this better or a way to turn this 
feature off in QGIS.

Karl-Magnus Jönsson

Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] För Karl-Magnus 
Jönsson
Skickat: den 17 januari 2017 17:32
Till: Luca Lanteri
Kopia: qgis-user
Ämne: Re: [Qgis-user] can't editing view using rules

Could it be connected with the new feature to "execute expressions on the 
server side if possible"? Then you should get the new primay key from the 
database before you save edits so you can use it for childs in subforms etc.

/Karl-Magnus

17 jan 2017 kl. 11:16 skrev Luca Lanteri 
mailto:lklant...@gmail.com>>:
This is the postgres log [0]:
In fact the 2.18 query add a RETURNING clause that is non present in QGIS 2.14

I've just opened this ticket: http://hub.qgis.org/issues/16083

Luca

[0]
With QGIS 2.18
2017-01-17 11:09:32 CET [25093]: [5-1] user=l_lanteri,db=sigeo,app=QGIS 
STATEMENT:  INSERT INTO 
"sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","prog_opera","localita","sponda","alt_min","alt_max","lung","tipologia","efficienza","stato","mat_massi","mat_c_secc","mat_c_int","mat_a_secc","mat_a_int","mat_gabbio","mat_cls","mat_legna","i_manuten","i_nessuna","i_prolung","i_pulizia","i_ricostru","i_sottomur","i_svuotam","so_dissest","so_interra","so_scalzat","so_sifonat","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro")
 VALUES 
(st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
 RETURNING "oper_id"

With QGIS 2.14
2017-01-17 11:12:01 CET [26026]: [3-1] user=l_lanteri,db=sigeo,app=QGIS LOG:  
execute addfeatures: INSERT INTO 
"sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","prog_opera","localita","sponda","alt_min","alt_max","lung","tipologia","efficienza","stato","mat_massi","mat_c_secc","mat_c_int","mat_a_secc","mat_a_int","mat_gabbio","mat_cls","mat_legna","i_manuten","i_nessuna","i_prolung","i_pulizia","i_ricostru","i_sottomur","i_svuotam","so_dissest","so_interra","so_scalzat","so_sifonat","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro")
 VALUES 
(st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)


2017-01-17 10:41 GMT+01:00 Karl-Magnus Jönsson 
mailto:karl-magnus.jons...@kristianstad.se>>:
Hi!
Ok. When I try another view with an INSERT rule I have the same problem in 
2.18.2. Works in 2.14. UPDATE and DELETE are ok as well. Can we check the 
resulting SQL from QGIS? Must be some difference there. I don't know if we can 
turn on logging on our database.

Karl-Magnus Jönsson

Från: mesca...@gmail.com 
[mailto:mesca...@gmail.com] För Luca Lanteri
Skickat: den 16 januari 2017 14:44
Till: Karl-Magnus Jönsson
Kopia: qgis-user
Ämne: Re: [Qgis-user] can't editing view using rules

Hi Magnus, thanks for the answer.

I think my rules are ok also because they work fine with QGIS 2.14.
The strange things is that the error say about an "INSERT RETURNING" when I 
don't need any RETURING clause.
The problem happens only with INSERT rules. UPDATE and DELETE are ok.


this is my INSERT rule:

CREATE OR REPLACE RULE dif_spon_insert AS
ON INSERT TO sicod.v_dif_spon DO INSTEAD  INSERT INTO sicod.dif_spon 
(padr_id, sigla_rile, prog_opera, localita, sponda, alt_min, alt_max, lung, 
tipologia, efficienza, stato, mat_massi, mat_c_secc, mat_c_int, mat_a_secc, 
mat_a_int, mat_gabbio, mat_cls, mat_legna, i_manuten, i_nessuna, i_prolung, 
i_pulizia, i_ricost

Re: [Qgis-user] can't editing view using rules

[Matthias Kuhn]

Hi Karl-Magnus,

What datatype is your primary key? I think returning is only required
for 64 bit integer or compound primary keys.

For an example of a complex view with return, have a look here:

https://github.com/QGEP/datamodel/blob/master/view/vw_qgep_wastewater_structure.sql

Best regards

Matthias


On 18/01/17 09:11, Karl-Magnus Jönsson wrote:
>
> Hi again!
> I’ve made some more testing. By adding a RETURNING clause to the rule
> I succeeded to insert features. But since it appears that the
> RETURNING clause has to return all the attributes in the view and my
> view is complex and consists of attributes from several tables I
> couldn’t just add RETURNING * (It will just return the attributes from
> the table inserted into). I had to list all fields in right order,
> make some attributes up and cast them to the right data type. Like this:
>
>  
>
> CREATE OR REPLACE RULE lekplatsredskap_ins AS
>
> ON INSERT TO park.v_lekplatsredskap DO INSTEAD  INSERT INTO
> park.skotselpunkt (kod, antal, inkopsar, fabrikat, nr, fritext,
> utforare, geom)
>
>   VALUES (new.kod, new.antal, new.inkopsar, new.fabrikat, new.nr,
> new.fritext, new.utforare, new.geom)
>
>   RETURNING skotselobj_id,1,kod, 'text'::character
> varying,1234,4567,'plkod'::text, antal, inkopsar, fabrikat, nr,
> fritext,utforare,'2017-01-18'::date,'b_resultat'::character
> varying,'status'::character varying,geom ;
>
>  
>
> Please let me know if you find a way to solve this better or a way to
> turn this feature off in QGIS.
>
>  
>
> *Karl-Magnus Jönsson*
>
>  
>
> *Från:*Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] *För
> *Karl-Magnus Jönsson
> *Skickat:* den 17 januari 2017 17:32
> *Till:* Luca Lanteri
> *Kopia:* qgis-user
> *Ämne:* Re: [Qgis-user] can't editing view using rules
>
>  
>
> Could it be connected with the new feature to "execute expressions on
> the server side if possible"? Then you should get the new primay key
> from the database before you save edits so you can use it for childs
> in subforms etc.
>
> /Karl-Magnus
>
>
> 17 jan 2017 kl. 11:16 skrev Luca Lanteri  >:
>
> This is the postgres log [0]:
>
> In fact the 2.18 query add a RETURNING clause that is non present
> in QGIS 2.14
>
>  
>
> I've just opened this ticket: http://hub.qgis.org/issues/16083
> 
>
>  
>
> Luca 
>
>  
>
> [0]
>
> With QGIS 2.18
>
> 2017-01-17 11:09:32 CET [25093]: [5-1]
> user=l_lanteri,db=sigeo,app=QGIS STATEMENT:  INSERT INTO
> 
> "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","prog_opera","localita","sponda","alt_min","alt_max","lung","tipologia","efficienza","stato","mat_massi","mat_c_secc","mat_c_int","mat_a_secc","mat_a_int","mat_gabbio","mat_cls","mat_legna","i_manuten","i_nessuna","i_prolung","i_pulizia","i_ricostru","i_sottomur","i_svuotam","so_dissest","so_interra","so_scalzat","so_sifonat","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro")
> VALUES
> 
> (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
> RETURNING "oper_id"
>
>  
>
> With QGIS 2.14
>
> 2017-01-17 11:12:01 CET [26026]: [3-1]
> user=l_lanteri,db=sigeo,app=QGIS LOG:  execute addfeatures: INSERT
> INTO
> 
> "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","prog_opera","localita","sponda","alt_min","alt_max","lung","tipologia","efficienza","stato","mat_massi","mat_c_secc","mat_c_int","mat_a_secc","mat_a_int","mat_gabbio","mat_cls","mat_legna","i_manuten","i_nessuna","i_prolung","i_pulizia","i_ricostru","i_sottomur","i_svuotam","so_dissest","so_interra","so_scalzat","so_sifonat","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro")
> VALUES
> 
> (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
>
>  
>
>  
>
> 2017-01-17 10:41 GMT+01:00 Karl-Magnus Jönsson
>  >:
>
> Hi!
>
> Ok. When I try another view with an INSERT rule I have the same
> problem in 2.18.2. Works in 2.14. UPDATE and DELETE are ok as
> well. Can we check the resulting SQL from QGIS? Must be some
> difference there. I don’t know if we can turn on logging on our
> database.
>
>  
>
> *Karl-Magnus Jönsson*
>
>  
>
> *Från:*mesca...@gmail.com 
> [mailto:mesca...@gmail.com ] *För *Luca
> Lant

Re: [Qgis-user] can't editing view using rules

[Karl-Magnus Jönsson]

Integer (4 bytes). I'll have a look at the view.

Karl-Magnus Jönsson

Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] För Matthias Kuhn
Skickat: den 18 januari 2017 09:21
Till: qgis-user@lists.osgeo.org
Ämne: Re: [Qgis-user] can't editing view using rules


Hi Karl-Magnus,

What datatype is your primary key? I think returning is only required for 64 
bit integer or compound primary keys.

For an example of a complex view with return, have a look here:

https://github.com/QGEP/datamodel/blob/master/view/vw_qgep_wastewater_structure.sql

Best regards

Matthias

On 18/01/17 09:11, Karl-Magnus Jönsson wrote:
Hi again!
I've made some more testing. By adding a RETURNING clause to the rule I 
succeeded to insert features. But since it appears that the RETURNING clause 
has to return all the attributes in the view and my view is complex and 
consists of attributes from several tables I couldn't just add RETURNING * (It 
will just return the attributes from the table inserted into). I had to list 
all fields in right order, make some attributes up and cast them to the right 
data type. Like this:

CREATE OR REPLACE RULE lekplatsredskap_ins AS
ON INSERT TO park.v_lekplatsredskap DO INSTEAD  INSERT INTO 
park.skotselpunkt (kod, antal, inkopsar, fabrikat, nr, fritext, utforare, geom)
  VALUES (new.kod, new.antal, new.inkopsar, new.fabrikat, new.nr, new.fritext, 
new.utforare, new.geom)
  RETURNING skotselobj_id,1,kod, 'text'::character 
varying,1234,4567,'plkod'::text, antal, inkopsar, fabrikat, nr, 
fritext,utforare,'2017-01-18'::date,'b_resultat'::character 
varying,'status'::character varying,geom ;

Please let me know if you find a way to solve this better or a way to turn this 
feature off in QGIS.

Karl-Magnus Jönsson

Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] För Karl-Magnus 
Jönsson
Skickat: den 17 januari 2017 17:32
Till: Luca Lanteri
Kopia: qgis-user
Ämne: Re: [Qgis-user] can't editing view using rules

Could it be connected with the new feature to "execute expressions on the 
server side if possible"? Then you should get the new primay key from the 
database before you save edits so you can use it for childs in subforms etc.

/Karl-Magnus

17 jan 2017 kl. 11:16 skrev Luca Lanteri 
mailto:lklant...@gmail.com>>:
This is the postgres log [0]:
In fact the 2.18 query add a RETURNING clause that is non present in QGIS 2.14

I've just opened this ticket: http://hub.qgis.org/issues/16083

Luca

[0]
With QGIS 2.18
2017-01-17 11:09:32 CET [25093]: [5-1] user=l_lanteri,db=sigeo,app=QGIS 
STATEMENT:  INSERT INTO 
"sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","prog_opera","localita","sponda","alt_min","alt_max","lung","tipologia","efficienza","stato","mat_massi","mat_c_secc","mat_c_int","mat_a_secc","mat_a_int","mat_gabbio","mat_cls","mat_legna","i_manuten","i_nessuna","i_prolung","i_pulizia","i_ricostru","i_sottomur","i_svuotam","so_dissest","so_interra","so_scalzat","so_sifonat","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro")
 VALUES 
(st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
 RETURNING "oper_id"

With QGIS 2.14
2017-01-17 11:12:01 CET [26026]: [3-1] user=l_lanteri,db=sigeo,app=QGIS LOG:  
execute addfeatures: INSERT INTO 
"sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","prog_opera","localita","sponda","alt_min","alt_max","lung","tipologia","efficienza","stato","mat_massi","mat_c_secc","mat_c_int","mat_a_secc","mat_a_int","mat_gabbio","mat_cls","mat_legna","i_manuten","i_nessuna","i_prolung","i_pulizia","i_ricostru","i_sottomur","i_svuotam","so_dissest","so_interra","so_scalzat","so_sifonat","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro")
 VALUES 
(st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)


2017-01-17 10:41 GMT+01:00 Karl-Magnus Jönsson 
mailto:karl-magnus.jons...@kristianstad.se>>:
Hi!
Ok. When I try another view with an INSERT rule I have the same problem in 
2.18.2. Works in 2.14. UPDATE and DELETE are ok as well. Can we check the 
resulting SQL from QGIS? Must be some difference there. I don't know if we can 
turn on logging on our database.

Karl-Magnus Jönsson

Från: mesca...@gmail.com 
[mailto:mesca...@gmail.com] För Luca Lanteri
Skickat: den 16 januari 2017 14:44
Till: Karl-Magnus Jönsson
Kopia: qgis-user
Ämne: Re: [Qgis-user] can't editing view using rules

Hi Magnus, thanks for the answer.

I think my rules

Re: [Qgis-user] can't editing view using rules

[Luca Lanteri]

Hi Karl,

I've tried to run the same way but also my views are complex and I've a
huge number of view so it's a big deal to change all my views.
I think the issue should be considered a regression.
I've seen that the ticket was automatically assigned to Jurgen Fisher by
the system, I think as PostGIS Data Provider maintenancer.
I Hope to have some news.

Thanks
Luca

2017-01-18 9:11 GMT+01:00 Karl-Magnus Jönsson <
karl-magnus.jons...@kristianstad.se>:

> Hi again!
> I’ve made some more testing. By adding a RETURNING clause to the rule I
> succeeded to insert features. But since it appears that the RETURNING
> clause has to return all the attributes in the view and my view is complex
> and consists of attributes from several tables I couldn’t just add
> RETURNING * (It will just return the attributes from the table inserted
> into). I had to list all fields in right order, make some attributes up and
> cast them to the right data type. Like this:
>
>
>
> CREATE OR REPLACE RULE lekplatsredskap_ins AS
>
> ON INSERT TO park.v_lekplatsredskap DO INSTEAD  INSERT INTO
> park.skotselpunkt (kod, antal, inkopsar, fabrikat, nr, fritext, utforare,
> geom)
>
>   VALUES (new.kod, new.antal, new.inkopsar, new.fabrikat, new.nr,
> new.fritext, new.utforare, new.geom)
>
>   RETURNING skotselobj_id,1,kod, 'text'::character
> varying,1234,4567,'plkod'::text, antal, inkopsar, fabrikat, nr,
> fritext,utforare,'2017-01-18'::date,'b_resultat'::character
> varying,'status'::character varying,geom ;
>
>
>
> Please let me know if you find a way to solve this better or a way to turn
> this feature off in QGIS.
>
>
>
> *Karl-Magnus Jönsson*
>
>
>
> *Från:* Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] *För *Karl-Magnus
> Jönsson
> *Skickat:* den 17 januari 2017 17:32
> *Till:* Luca Lanteri
>
> *Kopia:* qgis-user
> *Ämne:* Re: [Qgis-user] can't editing view using rules
>
>
>
> Could it be connected with the new feature to "execute expressions on the
> server side if possible"? Then you should get the new primay key from the
> database before you save edits so you can use it for childs in subforms etc.
>
> /Karl-Magnus
>
>
> 17 jan 2017 kl. 11:16 skrev Luca Lanteri :
>
> This is the postgres log [0]:
>
> In fact the 2.18 query add a RETURNING clause that is non present in QGIS
> 2.14
>
>
>
> I've just opened this ticket: http://hub.qgis.org/issues/16083
>
>
>
> Luca
>
>
>
> [0]
>
> With QGIS 2.18
>
> 2017-01-17 11:09:32 CET [25093]: [5-1] user=l_lanteri,db=sigeo,app=QGIS
> STATEMENT:  INSERT INTO "sicod"."v_dif_spon"("geom","
> oper_id","padr_id","sigla_rile","cod_opera","prog_opera"
> ,"localita","sponda","alt_min","alt_max","lung","tipologia",
> "efficienza","stato","mat_massi","mat_c_secc","mat_c_
> int","mat_a_secc","mat_a_int","mat_gabbio","mat_cls","mat_
> legna","i_manuten","i_nessuna","i_prolung","i_pulizia","i_
> ricostru","i_sottomur","i_svuotam","so_dissest","so_
> interra","so_scalzat","so_sifonat","data_rilev","note","
> utente_crea","data_crea","utente_mod","data_mod","fonte"
> ,"desc_fonte","font_elab","font_sopr","font_altro") VALUES
> (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'
> 0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0'
> ,'0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
> RETURNING "oper_id"
>
>
>
> With QGIS 2.14
>
> 2017-01-17 11:12:01 CET [26026]: [3-1] user=l_lanteri,db=sigeo,app=QGIS
> LOG:  execute addfeatures: INSERT INTO "sicod"."v_dif_spon"("geom","
> oper_id","padr_id","sigla_rile","cod_opera","prog_opera"
> ,"localita","sponda","alt_min","alt_max","lung","tipologia",
> "efficienza","stato","mat_massi","mat_c_secc","mat_c_
> int","mat_a_secc","mat_a_int","mat_gabbio","mat_cls","mat_
> legna","i_manuten","i_nessuna","i_prolung","i_pulizia","i_
> ricostru","i_sottomur","i_svuotam","so_dissest","so_
> interra","so_scalzat","so_sifonat","data_rilev","note","
> utente_crea","data_crea","utente_mod","data_mod","fonte"
> ,"desc_fonte","font_elab","font_sopr","font_altro") VALUES
> (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'
> 0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0'
> ,'0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
>
>
>
>
>
> 2017-01-17 10:41 GMT+01:00 Karl-Magnus Jönsson  kristianstad.se>:
>
> Hi!
>
> Ok. When I try another view with an INSERT rule I have the same problem in
> 2.18.2. Works in 2.14. UPDATE and DELETE are ok as well. Can we check the
> resulting SQL from QGIS? Must be some difference there. I don’t know if we
> can turn on logging on our database.
>
>
>
> *Karl-Magnus Jönsson*
>
>
>
> *Från:* mesca...@gmail.com [mailto:mesca...@gmail.com] *För *Luca Lanteri
> *Skickat:* den 16 januari 2017 14:44
> *Till:* Karl-Magnus Jönsson
> *Kopia:* qgis-user
> *Ämne:* Re: [Qgis-user] can't editing view using rules
>
>
>
> Hi Magnus, thanks for the answer.
>
>
>
> I

[Qgis-user] QGIS server proxy settings?

[G. Allegri]

Hi,
we need QGIS server access the newtork through a proxy. In QGIS the network
proxy can be configured easily but I can't figure out how to do it for QGIS
Server.

I couldn't find any env variable to set... Please don't tell me it's not
possible! :(

giovanni
___
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

Re: [Qgis-user] Applying cumulative usage to road sections

[Andrew Harfoot]

I think this can be solved in two stages:

1. Given the transport network, source and destination locations, a 
shortest path calculation run repeatedly should generate a series of 
routes. Commonly these functions output the route in terms of a list of 
the network sections to follow.


Options for doing this calculation in QGIS include: PostGIS (pgRouting), 
Spatialite, GRASS v.net functions, QGIS Road Graph plugin


2. Once the routes have been generated, then they can be combined and 
summarised to generate the total weight traversing each network section, 
which would probably be as a new table that would be joined back to the 
network. This step could be done within a database (PostGres, Spatialite 
etc.), in core QGIS, or even Excel.


Cheers,

Andy


On 15/01/2017 21:07, rossnixon wrote:

I don't know if the following can be solved with a QGIS plugin. In MapInfo it
requires a 1500 Euro add-on.
But perhaps someone has had a similar problem in the past? Maybe with road
networks, or watercourse volumes?

I would like to calculate the total 'weight' of goods which will travel
across *each* road section.
Goods will arrive at the closest road section, and then travel along
subsequent road sections to the destination. So, road sections nearest the
destination will have multiple loads travelling over it which need to be
added together. Example image attached.




--
View this message in context: 
http://osgeo-org.1560.x6.nabble.com/Applying-cumulative-usage-to-road-sections-tp5303505.html
Sent from the QGIS - User mailing list archive at Nabble.com.
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user



--
Andy Harfoot

GeoData Institute
University of Southampton
Southampton
SO17 1BJ

Tel:  +44 (0)23 8059 2719

www.geodata.soton.ac.uk

___
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

[Qgis-user] Problem seeing/setting Oracle table's primary key

[Simon Greener]

Folks,

I am using QGIS 2.18.3 to connect to Oracle (12.1) table with a primary key:

create table foo (
   fid number(10),
   an_attribute varchar2(10),
   geom sdo_geometry
);

INSERT INTO USER_SDO_GEOM_METADATA (table_name,column_name,DimInfo,srid) VALUES 
('FOO', 'GEOM', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 5979462.12680312, 
6024838.75881869, 0.005), SDO_DIM_ELEMENT('Y', 2085800.17222035, 
2131294.00019577, 0.005)), 2872);
commit;

insert into foo(fid,an_attribute,geom) values 
(1,'D',SDO_GEOMETRY(2003,2872,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(5992342.97800986,2112457.60086077,
 5992462.06832285,2112461.87775511, 5992461.15953203,2112486.86130093, 
5992342.06921902,2112482.5844066, 5992342.97800986,2112457.60086077)));
insert into foo(fid,an_attribute,geom) values 
(2,'I',SDO_GEOMETRY(2007,2872,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(5992230.58945511,2112203.40320644,
 5992352.0678867,2112207.76605861, 5992351.15909585,2112232.74960443, 
5992229.68033619,2112228.38675226, 5992230.58945511,2112203.40320644)));
commit;

create index foo_geom on foo(geom) indextype is mdsys.spatial_index 
parameters('sdo_indx_dims=2 layer_gtype=MULTIPOLYGON');

-- Create primary key (lazy method) -> automatically creates unique index with 
same name
alter table foo add constraint foo_pk primary key (fid);

-- Check Unique Index name same as primary constraint
-- Query extracted from SQL tracing of QGIS session.

COLUMN column_name FORMAT A11
SELECT column_name
  FROM all_ind_columns a
   JOIN all_constraints b ON a.index_name=constraint_name AND 
a.index_owner=b.owner
 WHERE b.constraint_type='P' AND b.owner='TWEED' AND b.table_name='FOO';

/*
COLUMN_NAME
---
FID
*/

-- Now we know we have a QGIS compliant primary key name, connect QGIS to 
Oracle and check if primary key name appears
--> No Primary key displayed.

-- Can anyone tell me why the name of the primary key is not being returned?

-- If any developer is listening, the QGIS query that finds the column_name of 
the primary key does not cope with situations where a unique index is created 
BEFORE the primary key is defined as follows:

-- Create primary key using more flexible approach

alter table foo drop constraint foo_pk;
create unique index foo_fid_uidx on foo(fid);
alter table foo add constraint foo_pk primary key (fid) using index 
foo_fid_uidx;

-- Execute normal QGIS SQL:
SELECT column_name FROM all_ind_columns a JOIN all_constraints b ON 
a.index_name=constraint_name AND a.index_owner=b.owner WHERE 
b.constraint_type='P' AND b.owner='TWEED' AND b.table_name='FOO';

--> no rows selected.

-- Actual query should be:
SELECT column_name
FROM all_ind_columns a
INNER JOIN
all_constraints b
ON ( a.index_owner=b.owner AND a.table_name=b.table_name AND 
a.index_name=b.index_name )
WHERE b.constraint_type='P' AND b.owner='TWEED' AND b.table_name='FOO';
/*
COLUMN_NAME
---
FID
*/

-- Note: ALTER ADD CONSTRAINT PRIMARY KEY without USING INDEX clause creates 
the index and then inserts its name (same as constraint) into metadata so that 
index_name is always constraint_name except when USING INDEX clause is used, 
then index_name in user_constraints is name of user defined unique index.

DELETE FROM USER_SDO_GEOM_METADATA where table_name = 'FOO';
commit;
drop table foo purge;

regards
Simon
--
Holder of "2011 Oracle Spatial Excellence Award for Education and Research."
SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL 
Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius 
Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
Email: si...@spatialdbadvisor.com
Voice: +61 362 396397
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
GeoHash: r22em9r98wg
NAC:W80CK 7SWP3___
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

[Qgis-user] Merging Raster Scenes

[Grant Boxer]

I am trying to merge bands from two ASTER scenes and would like to get the
bands to merge with the same greyscale stretch and avoid the different grey
scale shade of each scene which results in a colour change across the join.
I have created the rasters using the SCP plug-in using the DOS1 and Celsius
corrections/calculation. I can get them to merge OK but would like a
seamless join. Any suggestions?

 

Grant Boxer

Perth, Western Australia

 

___
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