Filter attributes from a data layer into a point layer

Question

So, I have two layers:

-A point layer with a number of venues (around 2000 venues). The attribute table has 7 columns:

1. Venue ID (Foursquare)
2. Latitude
3. Longitude
4. Venue category name (Foursquare)
5. Country code.

-A data layer has information about visitors to venues. But it contains 30.000 visitors (which becomes hard to work it). The attribute table has 4 columns.

1. User ID (anonymized)
2. Venue ID (Foursquare)
3. UTC time
4. Timezone offset in minutes

I want to filter from the second layer only the User ID's that visited the Venues ID from the points in the point layer.

And then I would like to know how many times a venue (from the point layer) the was visited.

P.S. I have only basic knowledge of QGIS

Answer 1

Would this help:

1. Join the two layers first. Go to the Visitors-layer properties (right click) and Join > Add a new join from the green plus button and join to the venue layer with the Venue ID. Then you get a visitor table with all the venues and users that have visited the venues.

2. After this you can create statistics based on venues. One way to do this: Go to a tool called "Statistics by categories" where you use the Visitors-layer as input and field to calculate statistics on: User ID and Venue ID as field with categories. The you get a temporary layer with statistics filtered by venues: e.g. count of the unique visitor visits.

Answer 2

Another way (possibly too tricky) is to get the statistics on the map with creating a relationship of 1:N to the layers. Following these instructions: https://docs.qgis.org/3.22/en/docs/user_manual/working_with_vector/attribute_table.html#creating-one-or-many-to-many-relations we can create a relatioship between the two datasets.

And then visualize e.g. the visit counts by size on the map with a function in the layer styling.

1. choose graduated symbology for venues
2. add an expression in the value field where you create a count from the relation based on user ID-field.

3. Choose the size as the method and classify