1

When using QGIS 2.14.4 (Essen). I am trying to join data about North Sea Platforms with their corresponding licence block in the North Sea. My data is in excel, and contains the Block ID (see image 1), which matches the Block ID of a shapefile of the blocks themselves.

enter image description here

I am able to convert the excel into a csv, and then add it to QGIS Project as "No geometry (attribute only)" and then join it to the corresponding Block shapefile, allowing me to display my data by block, as desired. However, I have noticed that when two entries share the same block eg. "Alwyn A" and "Alwyn B" the resultant joined shapefile only contains information about one of them (see image 2)

enter image description here

Is there any way around this problem? I had thought of splitting the excel to make two shapefiles where repeats occurred, and then merging, but this seems like alot of effort, where a simpler solution may be out there.

Improve this question
1

1 Answer 1

Reset to default
3

--- new solution according to discussion on the chat ---

go to layer > add layer > add virtual layer

Import both layers.

Paste following code in the query pannel (you can change/replace featereA, featureB, featureZ with your own column-titles:

select "ukcs_block".geometry,"ukcs_block"."featureA","ukcs_block"."featureB", "ukcs_block"."featureZ",group_concat("Anchor_Database_test"."Platform") as platform
from "ukcs_block"
left join "Anchor_Database_test" on "Anchor_Database_test"."UKCS Block" = "ukcs_block"."ALL_LABELS"
group by "ukcs_block"."ALL_LABELS"

enter image description here

click test to see if everyting is correct. If everything is ok, click ok.

A new layer will be added to your project. This one you can save as a new shapefile to send it to anyone else.

--- old solution ---

If you perform a join, only the first of the similar records is picked to do the join.

I think in your situation it is better to make a relation between both. You can do that via project > project properties > tab relations.

enter image description here

Assign your csv as child-layer and block shapefile as parent layer.

Improve this answer
8
  • Pieter, thanks for the answer, but does the creation of this relation assign any values to the shapefile itself? I'm looking to create a shapefile which contains all of the data by block at the end, in order to share and deliver this information to other users. Does the relation allow this?
    – C.McCabe
    Commented Aug 10, 2016 at 9:26
  • No, the relation does not provide extra data to the shapefile.
    – PieterB
    Commented Aug 10, 2016 at 9:59
  • It is not clear to me how the shapefile relates to the records in the CSV. Does Alwyn A shares the same geometry with Alwyn B?
    – PieterB
    Commented Aug 10, 2016 at 10:05
  • It is a shame to hear that the relation does not add data to the shapefile as a join does. Alwyn A and B share the same UKCS Block in the North Sea, basically a designated area for the oil and gas industry, and the block layer provides the geometry. I want to display the data by block, and so after some digging I think this may be better described as a one-to-many join (many platforms to the one block). There doesn't seem to be an easy method for this join in QGIS, unless anything comes to mind?
    – C.McCabe
    Commented Aug 10, 2016 at 10:15
  • how do you want to display the data by block? As a table without geometry?
    – PieterB
    Commented Aug 10, 2016 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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