Qgis, postgis and updateable views
|
|
Ricardo Filipe Soares Garcia da
|
Hello list
I am using Qgis 1.3.0 compiled from the release tarball on Kubuntu 9.04. I am trying to create an updateable postgis view that will be updated through Qgis. I have a postgis DB with spatial and non-spatial tables. I'm using a view to abstract the relational connections between several tables and a spatial table and present the user (me) with a flat-file-like type of visualization through Qgis, so that I can see the real values of my spatial data instead of a bunch of foreign keys. This visualization is working fine, I have created the postgis view and can use it inside Qgis. Now I'd like to be able to use Qgis to insert and update my view and have postgresql do the real inserting and updating to my tables in the background. For this purpose I am creating some postgresql rules as in shown in [1] and [2]. So far I have succeeded in creating the UPDATE rule, which is working really nice and I think I have successfully created the INSERT rule as well, but I'm unable to test it due to Qgis crashing. My problem lies with the creation of new geometries. So far Qgis is crashing with the 'Segmentation fault' message when I try to create a new polygon on the view layer: To replicate the crash: 1 - load postgis layer (this layer is a postgis view, definded with SQL code in [3]) in Qgis 2 - toggle the edit mode to 'on' 3 - select the 'capture polygon' tool 4 - click anywhere on the map canvas 5 - ugly crash (segmentation fault reported on the console) Is it not possible to draw a new feature on a view layer? can someone replicate this behaviour? If not, it really should because the insert rule on postgres's side will (hopefully and most likely) accept that. Please note than I can update an already existing feature (for example moving some vertices around) and it works ok. Thanks in advance [1] - http://www.postgresonline.com/journal/index.php?/archives/11-Database-Abstraction-with-Updateable-Views.html [2] - http://www.postgresql.org/docs/8.1/interactive/rules-update.html [3] - SQL code follows: CREATE OR REPLACE VIEW salasp1 AS SELECT s.sa_gid, s.the_geom, e.sigla AS edificio, p.numero AS piso, s.nome AS sala, t.tipo, f.funcionalidade, d.sigla AS sigla_dep_servico, d.nome AS dep_servico, s.descricao, s.lugares, s.tomadas, s.telefones FROM sala s, tipo t, funcionalidade f, piso p, edificio e, deptoservico d WHERE p.numero=1 AND p.pi_gid=s.pi_gid AND s.ti_id=t.ti_id AND s.fu_id=f.fu_id AND e.sigla='C8' AND d.dep_id=s.depserv_id; -- ___________________________ ___ __ Ricardo Garcia Silva _______________________________________________ Qgis-user mailing list [hidden email] http://lists.osgeo.org/mailman/listinfo/qgis-user |
||||||||||||||||
Rob Marjot
|
Hi Ricardo,
I am planning to do sort of the same thing in the near future... Any progress? Keep us updated! Sorry I'm not of any help... What you could try: how do other GIS packages interact with your updatable view?
Rob
2009/10/24 Ricardo Filipe Soares Garcia da <[hidden email]> Hello list _______________________________________________ Qgis-user mailing list [hidden email] http://lists.osgeo.org/mailman/listinfo/qgis-user |
||||||||||||||||
|
Ricardo Filipe Soares Garcia da
|
Hi Rob and the rest of the list
Strangely enough, today I'm not experiencing the crash that motivated my previous e-mail. Qgis is allowing me to create new geometries in my postgis VIEW layer. The crash was being caused by some other factor that I haven't been able to track down. Now I'm getting another error when I try to save my changes, but it is probably due to some error in my INSERT rule on postgreSQL's side, as I didn't have time to test it properly. Today I am busy with other issues but I will get back at it tomorrow and will report my progress. If you are interested I can try to make a detailed explanation showing how to get the whole 'postgis editable views layers' working, when I do manage to get them going that is ;) As a side note, I have been experiencing apparently random crashes that happen when I click on a layer in the 'Layers' dock window. I don't know what is causing them... I'll try to nail it down and file a ticket. On Mon, Oct 26, 2009 at 12:13 PM, Rob Marjot <[hidden email]> wrote: > Hi Ricardo, > I am planning to do sort of the same thing in the near future... Any > progress? Keep us updated! > Sorry I'm not of any help... What you could try: how do other GIS packages > interact with your updatable view? > > Rob > > 2009/10/24 Ricardo Filipe Soares Garcia da <[hidden email]> >> >> Hello list >> I am using Qgis 1.3.0 compiled from the release tarball on Kubuntu 9.04. >> >> I am trying to create an updateable postgis view that will be updated >> through Qgis. >> >> I have a postgis DB with spatial and non-spatial tables. I'm using a >> view to abstract the relational connections between several tables and >> a spatial table and present the user (me) with a flat-file-like type >> of visualization through Qgis, so that I can see the real values of my >> spatial data instead of a bunch of foreign keys. This visualization is >> working fine, I have created the postgis view and can use it inside >> Qgis. >> >> Now I'd like to be able to use Qgis to insert and update my view and >> have postgresql do the real inserting and updating to my tables in the >> background. For this purpose I am creating some postgresql rules as in >> shown in [1] and [2]. So far I have succeeded in creating the UPDATE >> rule, which is working really nice and I think I have successfully >> created the INSERT rule as well, but I'm unable to test it due to Qgis >> crashing. >> >> My problem lies with the creation of new geometries. >> So far Qgis is crashing with the 'Segmentation fault' message when I >> try to create a new polygon on the view layer: >> To replicate the crash: >> 1 - load postgis layer (this layer is a postgis view, definded >> with SQL code in [3]) in Qgis >> 2 - toggle the edit mode to 'on' >> 3 - select the 'capture polygon' tool >> 4 - click anywhere on the map canvas >> 5 - ugly crash (segmentation fault reported on the console) >> >> Is it not possible to draw a new feature on a view layer? can someone >> replicate this behaviour? If not, it really should because the insert >> rule on postgres's side will (hopefully and most likely) accept that. >> Please note than I can update an already existing feature (for example >> moving some vertices around) and it works ok. >> >> Thanks in advance >> >> [1] - >> http://www.postgresonline.com/journal/index.php?/archives/11-Database-Abstraction-with-Updateable-Views.html >> [2] - http://www.postgresql.org/docs/8.1/interactive/rules-update.html >> [3] - SQL code follows: >> >> CREATE OR REPLACE VIEW salasp1 AS >> SELECT s.sa_gid, s.the_geom, >> e.sigla AS edificio, p.numero AS piso, >> s.nome AS sala, t.tipo, f.funcionalidade, >> d.sigla AS sigla_dep_servico, d.nome AS dep_servico, >> s.descricao, s.lugares, s.tomadas, s.telefones >> FROM sala s, tipo t, funcionalidade f, piso p, edificio e, >> deptoservico d >> WHERE p.numero=1 AND p.pi_gid=s.pi_gid >> AND s.ti_id=t.ti_id AND s.fu_id=f.fu_id >> AND e.sigla='C8' AND d.dep_id=s.depserv_id; >> >> >> -- >> ___________________________ ___ __ >> Ricardo Garcia Silva >> _______________________________________________ >> Qgis-user mailing list >> [hidden email] >> http://lists.osgeo.org/mailman/listinfo/qgis-user > > -- ___________________________ ___ __ Ricardo Garcia Silva _______________________________________________ Qgis-user mailing list [hidden email] http://lists.osgeo.org/mailman/listinfo/qgis-user |
||||||||||||||||
| Free Embeddable Forum Powered by Nabble | Help |
