Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 6 down vote favorite
2

apologies for what I'm guessing is a really easy problem to solve. GIS user, not programmer here.

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) 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?

Thanks for the suggestions, I've downloaded the OSgeo4w installer but it didn't seem to change th efunctionality of the Join tool - not sure what I'm meant to use/do?

I will have a go at the eVis plugin tonight (and report back).

I assume this is a common need in QGIS? I'm taking a table that contains members of an organisation who are spread around a University (so there might be 4-5 members per building) and trying to join that to a shapefile of the University's buildings but I'm only ever left with the first row of my members' table, the other data does not get appended to the shapefile. This is really a relate as opposed to a join isn't it? Would I be better trying to link/relate to the file in Access? rambling now.....

share|improve this question
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 Jun 9 '11 at 11:33
I think there is a real need for this kind of feature in QGIS, but its up to us community members to pass the information along. I personally do have a need for this functionality but I don't know where to list this as a desired feature with the community?! Maybe someone here can point us in the right direction. – Ando Jun 10 '11 at 9:36
1  
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 Jul 5 '11 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 Sep 27 '11 at 13:25

5 Answers

up vote 4 down vote

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.

share|improve this answer
1  
I can confirm it works in QGIS 1.8.0 too. – afwatl Jul 24 '12 at 21:29
up vote 3 down vote

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

share|improve this answer
up vote 2 down vote

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.

share|improve this answer
up vote 1 down vote

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.

share|improve this answer
up vote 0 down vote

If you open your people layer and join the buildings to it, you'll get one row per person. Is that what you need?

enter image description here

share|improve this answer
Underdark I would like to have one row per person connected to one polygon (property) please explain your answer in more detail? thanks. I have properties with more than one owner and would like to represent each of them with their own row or line and connect them to the property they own. Allowing me to query for each of them and their characteristics. – Ouaouaron Apr 12 '12 at 11:36
@Ouaouaron: I'd recommend Greg's approach gis.stackexchange.com/a/19017/187 if you want to duplicate the geometry features. – underdark♦ Apr 12 '12 at 12:16

Your Answer

 
discard

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.