Joining by 'almost' identical attributes using QGIS?

Question

So I have two point shapefiles - their attribute table consist of names, lat, lon, and others.

I would like to join the attributes of shapefile 1 to shapefile 2. A normal join between the names would to the job.

The problem is that the two datasets have slight differences in the 'name' field as well as in the lat/lon. For example, if I have one point in The Hague, Shapefile 1 will calle it 'Den Hague' whereas Shapefile 2 would calle it 'T. Hague'. Also spatially they are not identical (they do not overlap) so the location between both of them would differ by few kms in spite of being the same point. I have over 7000 points which make it tough for manual corrections. When some points are indeed correct.

Is there a way to do a matching which takes into account ~80% of similarity in the names?

I am using QGIS.

Answer 1

Let me answer to your Is there a way to do a matching which takes into account ~80% of similarity in the names?.

QGIS has a function levenshtein() which calculates Levenshtein edit distance. It is under Fuzzy matching group, and you will find detailed description about this function in the help area.

In short, we can quantify how much two input strings are different.

Above is an example showing the possible usage ---

• "Led" (Levenshtein edit distance) field was calculated by levenshtein('Den Hague', "city") which is the difference betwen a text 'Den Hague' and each names in the "city" field.
• If "city" is Den Hague, they are identical. So the "Led" returns 0. And Rotterdam, completely different, returns 9.
• Please note this is case sensitive (see #7).

TLNR;

Above example shows a "YesNo" field which determines if two strings match (greater than 80%), by an expression below:

CASE WHEN
(length("city") -  levenshtein('Den Hague', "city"))/length('Den Hague') > 0.8
THEN 'Y'
ELSE 'N'
END

I am aware your ultimate goal is to use this matching in following Join operation. To do that you will need to modify fixed 'Den Hague' string to a link to your another layer. Please add some more information such as layer name and field names if you need further assistance. Anyway, hope this helps.

Answer 2

With QGIS 3.8, now you can use aggregate function to find the closest match to your name using levenshtein distance.

Assuming the target layer is layer2 and field name is name, add a new field fuzzymatch in layer1 with the following expression and do a join based on that field.

array_first(aggregate(
layer:= 'layer2',
aggregate:='array_agg',
expression:=name,
filter:=levenshtein(name, attribute(@parent, 'name')) <= 2,
order_by:=levenshtein(name, attribute(@parent, 'name'))
))

I have outlined the steps at https://spatialthoughts.com/2019/09/26/fuzzy-table-joins-in-qgis/