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