Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Looking to create a database for QGIS where each point represents a tree (with name attribute) and another table of observations about each tree over time. Ideally I can update tree location in QGIS without breaking the relationship (sometimes they are inaccurate).

share|improve this question

A simple way to go at this would be to add a time column to your observations. That way, a tree can have many rows of observations ( so long as the time and tree combinations are unique ).

share|improve this answer

Additionally, you might consider using a foreign key constraint on your observations table so that each observation correctly references an existing tree. For example in spatialite:

CREATE TABLE trees (tree_id integer primary key autoincrement, 
   tree_name text, 
   ... <other attrib columns>);
SELECT AddGeometryColumn('trees','Geometry',<your SRID>,'POINT,'XY');
CREATE TABLE observations (observe_id integer primary key autoincrement,
   tree_id INTEGER REFERENCES trees(tree_id),
   observe_date date,
   observe_data1 integer, observe_data2 text, ...<more observation data>);

With a structure like this,

  • You can update the trees table (correct locations, or add new trees) and the link between the trees and observations will remain.
  • Further, you will be able to add observations only for trees that actually exist in the trees table.
  • And, when it comes time to aggregate some observation data over time for each tree, you're all set, using a JOIN between the tables on the tree_id columns.
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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