1

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.

2
  • What type of shapes are they? Points, lines, or polygons?
    – Kingfisher
    May 24, 2019 at 18:28
  • @Kingfisher they're points May 24, 2019 at 21:12

3 Answers 3

2

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.

3
  • Thanks for this, unfortunately I don't have any SQL knowledge and I haven't been able to get this to work without getting a query error when I try to execute it - could you explain what each bit actually does? May 24, 2019 at 21:55
  • Also, does the EVENT_ID field need to be an integer? At the moment it's just matching strings with the location's names, but obviously I could change this if needs be. May 24, 2019 at 21:56
  • @MatthewLaw integer preferred, but SQL can deal with text-matching using LIKE '%text%' operators... if you are able to share your data, email me via the address in my profile... May 25, 2019 at 0:35
1

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:

Relations

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: enter image description here.

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..

0

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

--

  1. Now save your CSV as a new CSV or whatever you want
  2. 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")

Your Answer

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

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