1

Question : Is it possible to create a table in QGIS that will be a dynamic reference for geographical data entries?

As I'm sure this will sound very general and unclear, here's the actual situation I would like to address :

  • let's say I have a vector (points) map of trees on a site.
  • Each tree is identified by a unique number, and is further identified by a code which indicates its species (to the cultivar level).
  • Each species has their own characteristics, such as environmental preferences (soil, sun, climate, etc.), pests, uses (fruits, timber, wildlife habitat, etc.) behavior (size, root system, blooming time, etc.), and much more

-> My goal : create a table which lists all these characteristics by species, so that when I add a tree on my vector layer, I only enter the unique number (or let QGIS do it for me), and the species code, and it dynamically retrieves all the info from the species list, and includes it in the vector layer's attributes for that tree. Of course, I'd use the same field names and type in both tables. And in reverse, let's say I modify some characteristics for a species, and it automatically updates all the unique trees in my vector layer.

I've experimented with many things in QGIS, and one thing that bugs me is that nothing seems to "stay" or be dynamic. For instance, to make QGIS calculate a polygone surface for an 'Area' field, I need to redo the formula every time I add new entities. Can't it be dynamic?

Context :

I'm laying out the needs and solutions to map a future farm on QGIS. Not just trees but shrubs, perennials, annual crops, animals, and much more...

I have a moderate (fortunately fruitful) experience with mapping with QGIS, but it has been 100% self-taught and I'm limited by the database aspect of it, now that I want to push it further. The last time I really dealt with databases was on Access at university, about 10 years ago, and it wasn't super deep.

I just downloaded DB Browser for SQLite (I'm on OS X), which gives me the pleasant ability to do simple edits that QGIS won't let me, such as changing the order of fields. It also seems to give me the ability to quickly mess up my data, which is why I decided to join Stackexchange and go beyond self-learning. I'm having a tough time analyzing what is feasible in QGIS itself or requires external handling of the DB.

2

A solution could be having a table in for editing your layer, and a view for reading.

For instance, if your table is defined the following way:

CREATE TABLE trees
(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  species_id INTEGER,
  FOREIGN KEY(species_id) REFERENCES species(id)
);
SELECT AddGeometryColumn('trees', 'geom', 4326, 'POINT', 'XY');

You can create a view where you perform the joins to dynamically retrieve species attributes, or perform spatial computations, e.g:

CREATE VIEW trees_view AS
SELECT trees.id AS id,
  trees.geom AS geom,
  species.name AS species_name,
  species.soil AS species_soil,
  species.blooming_time AS species_blooming_time
FROM trees
LEFT JOIN species ON (trees.species_id = species.id);
  • Well you totally took me off guard here : I've a very shallow understanding of Python (I went through a 10 hours introductory course), so just enough to understand most of the logic in your example. I'm surprised that one would have to go to such depth (from my point of view, not yours obviously), to perform what seems to me like a very basic database function. I have never used views. I will have to get back to the QGIS training manual! – Grob Jan 12 '18 at 7:27
  • You're welcolme! BTW there is nothing about Python nor QGIS here, just database stuff ;-) Views are actually a quite basic and essential feature of relational databases. Views allow you to use a SQL query as if it was a table, you should think about them when you need to dynamically compute some fields. There are other options, for instance you could use a trigger to dynamically recompute some fields on update / insert operations, that would be more efficient since you don't compute the field on each query, but also slightly more complicated to setup than views. – Dim' Jan 15 '18 at 0:26
  • Right, at first sight I though it was Python, then realized that it wasn't. SQL seems fairly accessible, though, unlike some more gibberish languages. For my example above, I figured I can rely on simple joins, but I will definitely need views for the next step which requires some filtering and crossing data between tables. – Grob Jan 15 '18 at 3:06
0

Why not just have related table with those lookup values. I recommend using Geopackage Sqlite instead of Spatialite.

  • I realized that I can just do a simple join for this example, and keep the more complicated stuff on several views. Why not Spatialite? – Grob Jan 15 '18 at 3:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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