2

I've got a map with spatial data that I want to merge with some non-spatial tables. The tables will become spatial based on unique numerical identifiers contained in a column shared with the spatial data.

I import the tables as .csv files and join it to the relevant layer without problem. However, I noticed that when I then query the combined attributes some have not made it into the joined table. This leaves me with mystery gaps in my data that are not easy to discover in most cases.

Has anyone had this experience before? Using QGIS 2.18.11

Here's an image of the attribute table of the spatial layer to which I will join the .csv layer

Here is an example of the .csv data I join to the spatial layer

  • Could you give us an example of "not joined" data, from the csv and the shapefile? – ArMoraer Aug 16 '17 at 15:27
  • Did you save it as a new layer or are you doing the query with the join 'on fly'? – aldo_tapia Aug 16 '17 at 15:30
  • aldo, I query the new attribute table in the existing spatial layer. I want to create a new layer based on the results. ArMoraer, I've added images of the attribute tables for the two layers above. – lepula Aug 16 '17 at 16:04
1

It sounds like you're table of non-spatial data with the UID's has a many-to-one relationship with your spatial table (which also contains UID's) - that is, there are (for example) 10 records in your non-spatial table with UID 1234 and 1 record in your shapefile with UID 1234.

This type of join is a shortcoming of a GIS GUI such as QGIS (ArcMap also cannot do this), and a main reason we moved to PostGIS / MS SQL Server aka a relational database.

I would suggest loading both the table and shapefile into your QGIS map, then using the DB Manager to write a SQL query against what are now Virtual Layers.

Your query might look something like this:

select 

t.* 
, s.geometry

from table as t

left outer join shapefile as s on t.uid = s.uid

The above query ensures all records in the table (t) are joined on UID to the shapefile (s), then select all columns from t (t.*), and the geometry from your shapefile (s.geometry)

The GIS GUI join experience always assumes you're going to join 1 non-spatial record to 1 spatial record, which I think is why you're losing data.

  • Thank you. I will give this a try and report back, though it shouldn't be a many to one join that I'm performing. Each UID should connect only to one UID; it's possible there were some mistakes leading to duplication but I tried to make the table so that it would be a one to one. – lepula Aug 17 '17 at 0:25
  • I believe the problem is that converting the excel table to .csv form is corrupting some of the cells, which are then not able to join because their UID contains a ? or some other symbol which doesn't show up in the attribute table but does if I open the .csv in excel. – lepula Aug 17 '17 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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