32

I'm trying to create a join/relate in QGIS where I have a shapefile of buildings and to that I would like to join a non-spatial table (.csv-file) containing people who work in each building. So I have multiple records in my table that I would like to be able to join to my single features in my shapefile.

I can only run a join in QGIS where the first record is joined to the shapefile feature (ie building) but subsequent records get removed.

Could someone let me know how to complete this join/relate in QGIS?

Improve this question
3
  • I had a similar question here gis.stackexchange.com/questions/5797/… RT SQL Layer plugin and the eVis plugins were suggested. I used the eVis plug in but it wasn't the ideal solution.
    – Ando
    Commented Jun 9, 2011 at 11:33
  • 2
    I am having the same problem and indeed joining tables work. The trick is to join the tables with multiple values to the table with the single value (here, from person to building, rather than the other way around). The problem, as far as I experience it, is that by doing this, you cannot then search and export the result as a georeferenced file, which is a bugger (at least to me). Any way to bypass this issue? marc
    – user3555
    Commented Jul 5, 2011 at 11:14
  • What is the end-result you want to achieve? Even if you manage to do a join, do you want to present it on a map? Do statistics? Depending on that, different workflows may be followed, but using a database is usually a good idea if you want to make joins.
    – johanvdw
    Commented Sep 27, 2011 at 13:25

7 Answers 7

Reset to default
16

I solved this problem using QGIS 1.7.0 by first saving the vector layer as a comma separated values (CSV) table with the geometry field converted to well known text. To do this, the layer field of the "Save vector layer as..." dialog must read GEOMETRY=AS_WKT to create a well known text field of the geometry with the column named WKT.

The CSV geometry file was then joined to the non geometry attribute table in a one-to-many join under the properties joins tab of the non geometry attribute table.

The resulting join was saved as another geometry CSV file.

When this table is opened in QGIS each attribute record was represented by its appropriate vector geometry.

In doing all of this I discovered that The Delimited Text Plugin is not needed to open a CSV geometry file if the delimiters are commas and the WKT geometry field is named WKT.

For details on how this is done please see my tutorial at http://georgiawildlife.com/node/2849.

Improve this answer
2
  • Good approach, I did a similar thing just using ogr2ogr to create the CSV files from the Shapefiles. Then a simple R script to merge the files on the common field I wanted and write back to CSV. Then ogr2ogr again to create the merged shapefile.
    – vpipkt
    Commented Jan 4, 2018 at 13:31
  • 1
    This may be rated the highest, but the process meant for this is answered by @underdark below. This is a hack and not at all the best option, it is slow and inefficient and results in larger files and more steps.
    – nittyjee
    Commented Jul 21, 2018 at 5:02
15

Joins created in the layer properies only add columns to existing features. If the vector layer only has 160 features, a join cannot increase the number of features!

Use the virtual layer feature instead. It offers real SQL/database-like joins. For an example see my recent post: Small multiples for OD flow maps using virtual layers

enter image description here

Improve this answer
8

In QGIS 1.7 there is a new engine for Joins (you find it in the vector properties).

Geometryless tables can now be added to a project like any other (vector) layer.

Improve this answer
8

Starting at version 2.2 QGIS supports one-to-many relations. Define them in the Project Properties though, not Vector Layer Properties. And then related records will appear in the layer's Attribute Table or Identify Results via the form.

See answer and link to good guide here.

Improve this answer
4

Do the join in LibreOffice Base.

You can't overcome the fact that you are trying to manage a 1 to many relationship.

For non-spatial to non-spatial it is worth the effort to learn. I find the biggest trick is making sure the join attribute fields have exactly the same terms.

Open the spatial located data as a layer in QGIS, then you select all the records in the attribute table in QGIS (clear selection, invert selection) and copy these into a new sheet in LibreOffice Calc. You will find the first field is WKT, which is a description of the feature which can be universally used in GIS. Calc will give you a dialog to import the pasted table, choose tab delimited, ensure comma is not ticked as it will chew the WKT field to bits. Then highlight this table in Calc, switch to Base, highlight the Tables icon on the left and paste. Base will go through a dialog to import the table. The WKT field can be retained through the join and when the file is imported back into QGIS as a .csv these are used to relocate the features. Just remember the projection.

In that way you will have full control of the join and can do it in "reverse". To do joins in Base ensure the unique data field (the 1) is a primary key and then you point it at the "many" if you are familiar with a 1 to many or 1 to n concept. Joins are set up by making relationships |TOOLS|. As I mentioned you will probably want to kick someone when you find the join fields have the same information but have used different text or syntax to represent it.

hth

Improve this answer
2

I don't think this is possible to link tables with a 1 to many relationship in QGIS. As an alternative, you may want to try gvSIG. Here you can make 1 to many links. After creating the link, selecting a feature in the map will highlight all related records in the linked table.

Improve this answer
1
  • 1
    Since the introduction of virtual layers, this answer is not correct anymore.
    – underdark
    Commented May 20, 2017 at 13:05
0

I think you could now find your way exploring new relations in QGIS (1:1/1:n/m:n)

https://docs.qgis.org/latest/en/docs/user_manual/working_with_vector/attribute_table.html#introducing-1-n-relations

Improve this answer

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.