Joining attributes in CSV by location using QGIS

africaoverlapping-featurespoint-in-polygonqgisspatial-join

I have a shapefile which divides Africa in cells of 0.5×0.5 decimal degrees. This documents provides the longitude, latitude of the centroid of the cell and a unique identified of each cell.

On the other hand, I have a CSV file with different observations and different variables, including longitude and latitude among the variables. I transform this as a GeoPackage and they appears as a point in the QGIS.

Taking advantage of the latitude and longitude of the CSV file, I use a "Join Attributed by Location". Input Layer: my shp file; Join layer: my GeoPackage (before my CSV file); Geometric Predicate: I use the option "Intersects".

My main objective is to add to the grid cell the number of events of the CSV. For example, if according to the longitude and latitude of the CSV file, there are five events in a grid cell, I would like to obtain five observations in this grid cell. If I do not have any observation in one grid cell, the variables remain missing.

I do it without any problem. However, when I check the CSV that I export after doing the join, the number of observations are lower.

How is it possible? What I am doing wrong?

It should be greater than my original CSV, because it has to include all the observations of the CSV plus the grid cells that does not have any event.

I show the following picture. It is Africa divided in the grid cell and the points are the CSV. So, I would like to join the points to the grid cell. My version of QGIS is 3.20.1

This is the screen shot of my GeoPackage file (before CSV)

This is the screenshot of my shapefile:

Best Answer

I guess you should use the tool "Join attributes by location (summary)".

Here is a picture explaining quickly how you should proceed:

Differences between "join by location (summary)" and "join by location":

Related Solutions

[GIS] QGIS merge multiple layers and keep highest valued polygon / grid cell

Nice answer by HowToInQGIS!

Assuming both your layers contain grid cells which align perfectly when they overlap, you could do a join using one of the following processing algorithms to merge both layers into one:

Join attributes table

This will only work properly if both your grid layers contain some sort of ID field (i.e. a field containing a unique identification value for each grid cell).

Join attributes by location

If the grid cells do not have a unique value to match against then you can use this algorithm provided that, as mentioned above, your grid cells align perfectly.

The output layer should hopefully contain all fields from both layers. You can then use the Field Calculator to create a new field to find which grid cell contains the higher number. The following expression adds a 0 if the number of events in the "orange_count" field (or whatever field you named it) is less than the number of events in the "blue_count" field, else it adds a 1:

if("orange_count" < "blue_count", 0, 1)

Save the edits then right-click the output layer and go to Properties > Style, select a style such as Categorised or Rule-based and set the values accordingly:

Missing points when I import from CSV to QGIS

Quick solution

When loading the CSV, use Custom delimiters, select Semicolon and be sure to have all others empty. When loading the CSV you provided in your comment, I get 187532 points loaded - that seem to be all.

Identify problematic entries

To find the problematic entries, import the CSV as NO geometry (attribute only table). Check if lat/lon values are correct: in the Layer properties / Source tab / Query builder and filter invalid values for lat/lon using this query (see screenshot below):

latitude is NULL or 
longitude is NULL or 
(not (latitude >= -90 and latitude <=90))  or 
(not (longitude>= -180 and longitude<=180))

Click Test to check if there are any features returned: if so, these are the problematic features. Apply the filter and open the attribute table: it should now contain only the filteres features with values outsite the range of -90/90 for lat and -180/180 for lon. You could also directly use the same expression with Select by expression to directly select these entries (however, in a large dataset, the attribute table will take a while to load).

By the way: you could search in your latitude and longitude fields for any characters that are not numbers or points (commas, spaces etc.). As you have many decimal digits, you might round the values to get rid of potentially problematic characters: round (latitude, 5) - 5 digits should be precise enough. Even try to convert the lat/lon values to integers, creating a new field: to_int (latitude), then try creating points with these values to see if like that all points are created.

The problematic entries, here selected with select by expression:

See how to import CSV without creating geometries:

Edit

Inspecting the data you provided (the 389 features that produced an error), it becomes clear what went wrong: the latitude field contains names. It seems that the content of the fields was shifted one column to the right: