Use a .shp file to map multiple entries in a .csv in QGIS

Question

I have a .csv file with several entries with data for events which happened in places, and a corresponding .shp file which shows the locations where the events took place. How would I go about mapping a point for each of these events, using the .shp file to provide the locations? The .csv file has more entries than the .shp file (ie some places have multiple events attached to them), so when I've tried joining one layer to the other, the best I can do is create one event point for each of the locations, when there should be several (ie the number of points mapped is limited to the number of location points in the .shp file, but there are more events in the .csv than there are locations).

To be clear, the .csv and .shp both have a field with corresponding entries denoting the location, but some of these are repeated over several entries (events) in the .csv, whereas the .shp only contains unique entries.

Apologies if the answer to this is obvious or available elsewhere, this is my first time using QGIS so I wasn't quite sure what to search for to get the right answer.

I'm using QGIS 3.4.

Edit: I've just spent a while trying to get this to work, but to no avail - I think it's because the .csv isn't (yet) spatially referenced in any way, so even once I've created a relation, it won't let me open the feature attributes window.

Answer 1

The best solution without using a database is to use the QGIS DB Manager SQL console.

Once the CSV and SHP are loaded, they are available as 'virtual layers' which you can write SQL against.

I'll assume the following:

• events is the CSV
• locations is the SHP
• both have an EVENT_ID to relate to one another

Open the SQL Window and build a query similar to this:

select 
e.*
, l.geometry
from events as e 
join locations as l on e.EVENT_ID = l.EVENT_ID

When you run the query you should see all the records in the events (CSV) with the added geometry from the locations (SHP).

You can now load this joined data into your QGIS project via the 'load as new layer' option (checked) and selecting the geometry column in the drop-down. Then click 'Load' and a new layer should appear with all the points.

You can style / visualize as you like.

This type of problem is exactly why we moved to PostGIS/MS SQL Server, as a traditional 'GIS' is unable to handle this seemingly easy problem.

Answer 2

Presuming you imported CSV and shape file in QGIS.

You can use Join attributes by field value tool. For this to work both corresponding fields (location id in CSV and location id in shape) need to be of the same type. Something on this is already disscused here. Dont forget to choose One-to-many join type. This is probably what went wrong when you managed to get joined data but only one event per location.

You could also use relations. In Project menu go to Properties, Relations tab:

Your referenced layer (Parent) would be shapefile, Referencing layer CSV. After you set up relation you can view all events data of chosen location in form view: .

What method you use depends what you want to do with the data. I think the "Relation way" is more practical for viewing data while Joining is suitable for further analyses..

Answer 3

I want to show up another alternative using expressions:

1. Load your Shapefile and your .CSV into QGIS
2. Open the attribute table of your CSV
3. Open Field Calculator and choose "Text, unlimited length" as output field type
4. Enter the expression

--

geom_to_wkt(geometry(get_feature('Shapefilelayername','eventid_fieldname_in_shp',attribute($currentfeature,'eventid_fieldname_in_csv'))))

--

5. Now save your CSV as a new CSV or whatever you want
6. Import this newly saved CSV with its new geometry. (Unfortunately I dont know of a way changing geometry source of an already added CSV file, thats why "save and readd")