0

I'm going to set up a small db for my own use in QGIS. I can see only a few of the many tables will be spatial, but the ease of use and capacity of the widgets in QGIS are attractive. I won't writing (much) sql on the command line.

How will I; - transfer the relationships / joins between computers - share the data and with anyone else (can the joins only be reproduced via a QGIS project) - use the relationships for this dataset, as a subproject of other analysis

I understand that tables (spatial or not) can exist in postgresql/postgis but important rdb function like foreign keys are not understood by QGIS. Instead joins, default attribute values, some attribute typing, need to be defined in the QGIS widgets. These constraints then become ephemeral with the project file.

Saving the widgets as part of each layer style is I presume possible, but will not provide a robust solution I think because there will be a lot of intermediate tables.

As far as I know QGIS projects don't save to pgsql.

This earlier question explores some of the issues QGIS managing relationships and these posts from Opengis.ch are helpful too. https://www.opengis.ch/author/signedav/ But what workflows are out there?

My use case is for a bunch of data that exists almost entirely in a spreadsheet, it is only once column that needs to be exposed to a spatial join. However to produce a report back the other way, i.e. select a point on the map and reveal the joined attributes, I believe the rdb needs to live in QGIS. I have spent some time setting up in DBeaver and pgsql, but foreign key joins don't get recognised in QGIS.

Any thoughts appreciated

Improve this question
  • since one answer, just gone in to do this as if all in QGIS Geopackage. DBManager won't create a new nongeo-table in a gpkg, so create layers/tables back in the Browser window of QGIS. But if not enough columns, can't do that via Browser... Therefore go to DBManager and make a new column there... New nongeo-tables in the gpkg have a unique primary key built in, it can be see via DBManager. But for certainty, I'm making my own pkey column, the only way to enforce, unique, not-null, is to use the widget editor once the layer is available in the QGIS project. Inelegant. – BJW Feb 22 '20 at 1:15
1

It sounds like your whole workflow revolves around the ability to 'select a point on the map and reveal the joined attributes'. This is a concept developed in ArcGIS and implemented in QGIS, but in my mind, isn't realistic for desktop software nor is it a realistic concept in a relational database.

It is, however, something that should be utilized in a web application with a user-driven workflow.

With that said, I would be hesitant to design your data system around that one particular workflow given that you're going to eliminate the power of a spatial/relational database in favour of a desktop GUI.

My suggestion would be to load it all into one database and build the data you want using SQL and either views or materialized views (if the data is going to change).

Not completely understanding your project, it seems the database approach and configuring QGIS to consume that data would be the way to go, and QGIS projects do save to PGSQL, so that is an option for sharing.

Improve this answer
  • Thankyou @DPSSpatial . What normally happens in these cases? Export a one-time view and link that to the spatial system? I guess for my purposes I could have a manual index of the spatial features, then input this to the non-spatial rdb. But even then I'm keen to have the db online so as to have one table which is the one source of truth. A convenient online db is postgresql. Thank you for the answer. – BJW Feb 21 '20 at 22:04
  • 1
    @BillWilliamson added a few more lines regarding sharing and building everything into the database... happy to chat more about this as it seems doable. Not sure about the QGIS showing the 'related features' but perhaps someone could chime in about that...? – DPSSpatial Feb 21 '20 at 22:23
  • Have spent a huge waste of time trying to establish many to many relationship in gpkg, will now be writing another question.... – BJW Feb 22 '20 at 9:04
  • @BJW let me know where that question ends up on here... – DPSSpatial Feb 22 '20 at 16:21
  • I just found the howto about setting the connection to allow save project to pgsql bostongis.com/blog/index.php?/archives/… – BJW Feb 23 '20 at 6:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.