Re: [Qgis-user] QGIS Input Form for PostGIS
Hi Jakob, Thank you for this additional information. With this at hand, I am cc'ing Matthias and Nyall - both of them much more knowledgeable about the internals of QGIS with respect to relations and PostgreSQL. I know that Matthias is working on improving the performance of relations and their corresponding relation widgets - but I am not sure if the work is only in the master branch (QGIS 3.x) or already in 2.18. @Matthias can you please have a look at this thread and comment? The table sizes you mention are perhaps larger than average, but QGIS should be able to handle them. If not, I consider it a bug or an issue. If you have the opportunity to test the same setup in a QGIS 3 nightly, I would be interested if the situation improved over QGIS 2.18. You can get the latest QGIS 2.99 (3x) version by running the OSGEO4W installer (see http://www.qgis.org/en/site/forusers/alldownloads.html#windows) and choosing qgis-dev. This can be installed in parallel with 2.18 on a production system. If you want something that works NOW with 2.18 - it may be worth testing the "Data Driven Input Mask" that Bernhard mentioned. This should do all the "heavy" stuff definitely in the database, and not on the client. See http://plugins.qgis.org/plugins/DataDrivenInputMask/ I hope that Matthias and/or Nyall can comment more on the issue. Andreas On 2017-08-09 09:32, Jakob Miksch wrote: > Hello, > > thanks for the response! > > ## What means "big" (how many records are you dealing with)? > > These are the sizes (number of records) of the main tables: > > - location (points) ~ 450 000 > > - event ~ 150 000 > > - occurrence ~ 270 000 > > ## I know this sounds silly - but are your tables properly indexed (you > > don't have to index primary keys, but other columns that are used in > > relations need an index) > > There are indexes for the foreign keys of the main tables. Also all > "controlled vocabulary" columns are indexed. > > Th referencing tables are usually smaller than 500 records. > > ## What exact QGIS version are you using? 2.18.x - in the about dialogue > > you can find this, including the exact github version string > > I tried it with: > > - Ubuntu 16.04 (in virtual machine on Windows 10): 2.18.11 > https://github.com/qgis/QGIS/commit/29548e7 > > - Windows 10: 2.18.11 https://github.com/qgis/QGIS/commit/29548e7 > > ## How I set everything up > > I created "relations" (location-event and event-occurrence) in the project > properties. > > In the "fields" tab of the layers properties I also changed respective "edit > widgets" to "Relation Reference". > > For the controlled vocabulary I changed the "edit widgets" to "Value > Relation" (I used the Autoform plugin https://github.com/sourcepole/AutoForm > for it and checked it manually) > > I kept "Attribute editor layout" with the "Autogenerate" setting. > > ## What I want to have: > > I want to be able to add new events (and occurrences) to locations. > > ## What happens: > > When I want to create a new **location**, I have to wait for a few minutes. > Then the input window pops up, but I cannot access it because QGIS has > frozen. > > When I want to create a new **event**, an empty window opens, QGIS freezes > and nothing happens for many minutes. > > When I want to create a new **occurrence**, the table opens slowly, but I can > enter all necessary values and store it to the database. > > ## Question: > > Can QGIS handle tables of the sizes mentioned above? > > Do you have an idea, what I could have done wrong? > > I could also provide more information and maybe even a minimal example. > > Thanks and best regards, > > Jakob > > FROM: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] ON BEHALF OF > Andreas Neumann > SENT: fredag 4. august 2017 09.39 > TO: qgis-user@lists.osgeo.org > SUBJECT: Re: [Qgis-user] QGIS Input Form for PostGIS > > Hi Jakob, > > I don't think there is much background information or documentation available > on relations. > > The obvious questions to you are: > > - What means "big" (how many records are you dealing with)? > > - I know this sounds silly - but are your tables properly indexed (you don't > have to index primary keys, but other columns that are used in relations need > an index) > > - What exact QGIS version are you using? 2.18.x - in the about dialogue you > can find this, including the exact github version string > > - if we can't help you further, it may help to see your create table > stat
Re: [Qgis-user] QGIS Input Form for PostGIS
Hello, thanks for the response! ## What means "big" (how many records are you dealing with)? These are the sizes (number of records) of the main tables: - location (points) ~ 450 000 - event ~ 150 000 - occurrence ~ 270 000 ## I know this sounds silly - but are your tables properly indexed (you don't have to index primary keys, but other columns that are used in relations need an index) There are indexes for the foreign keys of the main tables. Also all "controlled vocabulary" columns are indexed. Th referencing tables are usually smaller than 500 records. ## What exact QGIS version are you using? 2.18.x - in the about dialogue you can find this, including the exact github version string I tried it with: - Ubuntu 16.04 (in virtual machine on Windows 10): 2.18.11 https://github.com/qgis/QGIS/commit/29548e7 - Windows 10: 2.18.11 https://github.com/qgis/QGIS/commit/29548e7 ## How I set everything up I created "relations" (location-event and event-occurrence) in the project properties. In the "fields" tab of the layers properties I also changed respective "edit widgets" to "Relation Reference". For the controlled vocabulary I changed the "edit widgets" to "Value Relation" (I used the Autoform plugin https://github.com/sourcepole/AutoForm for it and checked it manually) I kept "Attribute editor layout" with the "Autogenerate" setting. ## What I want to have: I want to be able to add new events (and occurrences) to locations. ## What happens: When I want to create a new **location**, I have to wait for a few minutes. Then the input window pops up, but I cannot access it because QGIS has frozen. When I want to create a new **event**, an empty window opens, QGIS freezes and nothing happens for many minutes. When I want to create a new **occurrence**, the table opens slowly, but I can enter all necessary values and store it to the database. ## Question: Can QGIS handle tables of the sizes mentioned above? Do you have an idea, what I could have done wrong? I could also provide more information and maybe even a minimal example. Thanks and best regards, Jakob From: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] On Behalf Of Andreas Neumann Sent: fredag 4. august 2017 09.39 To: qgis-user@lists.osgeo.org Subject: Re: [Qgis-user] QGIS Input Form for PostGIS Hi Jakob, I don't think there is much background information or documentation available on relations. The obvious questions to you are: - What means "big" (how many records are you dealing with)? - I know this sounds silly - but are your tables properly indexed (you don't have to index primary keys, but other columns that are used in relations need an index) - What exact QGIS version are you using? 2.18.x - in the about dialogue you can find this, including the exact github version string - if we can't help you further, it may help to see your create table statements (including all indexes) Andreas On 03.08.2017 11:47, Jakob Miksch wrote: Hello, I have a (rather big) Postgres/PostGIS database that basically looks like this: https://ibb.co/bK7PKv I want to use QGIS for editing and inserting data. I already found these ressources: https://docs.qgis.org/2.18/en/docs/user_manual/working_with_vector/vector_properties.html#fields-properties https://docs.qgis.org/2.18/en/docs/user_manual/working_with_vector/attribute_table.html#creating-one-or-many-to-many-relations https://docs.qgis.org/2.18/en/docs/training_manual/create_vector_data/forms.html I tried it: I created the relations and built custom forms. However, when I want to insert or edit something. QGIS becomes really slow and crashes finally. But before I describe the problems in more detail I want to read more about it. I find the documentation (linked above) a bit too little. So, my question: Is there any documentation I have missed? Is there any book (chapter) you can recommend? Do you know of any example where QGIS is used for entering data into many related tables in Postgres/PostGIS at once? I am thankful for any hint or resource. Jakob ___ Qgis-user mailing list Qgis-user@lists.osgeo.org<mailto: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 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] QGIS Input Form for PostGIS
Hi Jakob, although a bit late: Have you tried the plugin DataDrivenInputMask [1]? By using it there is no need to fiddle with QGIS relations, the drawback is that there is no undo. You have to properly define foreign keys and not null columns for the plugin to work. And, as Andreas already pointed out, indices on foreign key columns are normally advisable. Bernhard [1] http://plugins.qgis.org/plugins/DataDrivenInputMask/ Am 04.08.2017 um 09:39 schrieb Andreas Neumann: Hi Jakob, I don't think there is much background information or documentation available on relations. The obvious questions to you are: - What means "big" (how many records are you dealing with)? - I know this sounds silly - but are your tables properly indexed (you don't have to index primary keys, but other columns that are used in relations need an index) - What exact QGIS version are you using? 2.18.x - in the about dialogue you can find this, including the exact github version string - if we can't help you further, it may help to see your create table statements (including all indexes) Andreas On 03.08.2017 11:47, Jakob Miksch wrote: Hello, I have a (rather big) Postgres/PostGIS database that basically looks like this: https://ibb.co/bK7PKv I want to use QGIS for editing and inserting data. I already found these ressources: https://docs.qgis.org/2.18/en/docs/user_manual/working_with_vector/vector_properties.html#fields-properties https://docs.qgis.org/2.18/en/docs/user_manual/working_with_vector/attribute_table.html#creating-one-or-many-to-many-relations https://docs.qgis.org/2.18/en/docs/training_manual/create_vector_data/forms.html I tried it: I created the relations and built custom forms. However, when I want to insert or edit something. QGIS becomes really slow and crashes finally. But before I describe the problems in more detail I want to read more about it. I find the documentation (linked above) a bit too little. So, my question: Is there any documentation I have missed? Is there any book (chapter) you can recommend? Do you know of any example where QGIS is used for entering data into many related tables in Postgres/PostGIS at once? I am thankful for any hint or resource. Jakob __ Information from ESET Mail Security, version of virus signature database 15884 (20170809) __ The message was checked by ESET Mail Security. http://www.eset.com ___ 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] QGIS Input Form for PostGIS
Hi Jakob, I don't think there is much background information or documentation available on relations. The obvious questions to you are: - What means "big" (how many records are you dealing with)? - I know this sounds silly - but are your tables properly indexed (you don't have to index primary keys, but other columns that are used in relations need an index) - What exact QGIS version are you using? 2.18.x - in the about dialogue you can find this, including the exact github version string - if we can't help you further, it may help to see your create table statements (including all indexes) Andreas On 03.08.2017 11:47, Jakob Miksch wrote: Hello, I have a (rather big) Postgres/PostGIS database that basically looks like this: https://ibb.co/bK7PKv I want to use QGIS for editing and inserting data. I already found these ressources: https://docs.qgis.org/2.18/en/docs/user_manual/working_with_vector/vector_properties.html#fields-properties https://docs.qgis.org/2.18/en/docs/user_manual/working_with_vector/attribute_table.html#creating-one-or-many-to-many-relations https://docs.qgis.org/2.18/en/docs/training_manual/create_vector_data/forms.html I tried it: I created the relations and built custom forms. However, when I want to insert or edit something. QGIS becomes really slow and crashes finally. But before I describe the problems in more detail I want to read more about it. I find the documentation (linked above) a bit too little. So, my question: Is there any documentation I have missed? Is there any book (chapter) you can recommend? Do you know of any example where QGIS is used for entering data into many related tables in Postgres/PostGIS at once? I am thankful for any hint or resource. Jakob ___ 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 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
