2

this is not going to be a question, I'm just trying to find some advice.

Suppose I have a point shapefile (for example just two points, actually I have many more points) representing water control stations. The attribute table looks like:

ID    X        Y          Cl       Na
A    3480240   5887204   125.70   60.30
B    3480450   5887280   230.30   120.80

Now, how can I join another table that contains repeated measures of Cl and Na for each ID? This could be the example of the other table (measures in different years) I want to join:

ID    Cl       Na      Year
A    100.30   210.20   2010
A    120.40   200.30   2011
A    130.40   198.20   2012
B    101.00   199.20   2009
B    130.40   210.40   2010
B    140.60   210.00   2011
B    166.90   240.20   2012

I have tried both the QGIS standard join option and the mmqgis plugin (join from csv file) but the results are not as expected. QGIS join option adds some columns to each ID, but many fields have "NULL" attributes while mmqgis increased the number of rows for each ID and each field contains the same value (e.g. Cl values are always 120.20 for each year).

So I would just like to know if anybody has worked with this kind of data in order to understand the best way to handle multi-attribute field related to one single feature?

| improve this question | |
  • I would put the data in a postgis or spatialite database. That would give you total control of how the join in the data is done. – Alexandre Neto Dec 18 '13 at 10:20
  • Something like [gis.stackexchange.com/questions/26328/…. – Alexandre Neto Dec 18 '13 at 10:22
  • Thanks Alexandre, so you think that it's better to use other tools and than import the table (or database) in qgis.. – matteo Dec 18 '13 at 10:27
  • If you are ok with ok with some SQL, it would give you more control. There might be some tools or plugins in QGIS to do what you need. Here is another answer (gis.stackexchange.com/questions/10788/…) – Alexandre Neto Dec 18 '13 at 10:37
  • Sorry matteo, I probably made wrong assumption that SQL was acceptable approach. If not, I will drop my answer. – Kazuhito Dec 4 '17 at 14:03
1

SQL approach

Following the comment by Alexandre Neto, a combined table can be obtained using DB Manager:

SELECT ID, Na, Cl, Year_1  FROM my_table
UNION ALL 
SELECT ID, Na, Cl, null AS Year_1 FROM my_points
  • my_table: table without XY
  • my_points: XY points shapefile

(Note) I changed field "Year" of my_table to "Year_1" just to avoid any unforeseen troubles. "Year" could be ok.

enter image description here

To link the above table with the XY locations, above query is modified like:

SELECT combined.ID, combined.Na, combined.Cl, combined.Year_1, my_points.geometry
FROM(
    SELECT ID, Na, Cl, Year_1  FROM my_table
    UNION ALL 
    SELECT ID, Na, Cl, null AS Year_1 FROM my_points
    ) AS combined
CROSS JOIN my_points
ON combined.ID = my_points.ID

enter image description here

When we use identify features tool at the Point A or B, it will return these records from the combined table.

| improve this answer | |

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