3

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.

  • In SQL, doing a WHERE NAME_A %LIKE% NAME_B usually does a pretty OK job... – DPSSpatial Feb 12 at 23:56
  • Thanks, but when using this function it says expression invalid? – hpnk85 Feb 13 at 0:04
  • what is your expression? can you share a screenshot? – DPSSpatial Feb 13 at 0:08
  • I would try Levenshtein edit distance. – Kazuhito Feb 13 at 0:13
  • It seems I cant paste here a screenshot... I created a distance matrix for both datasets to see what is the nearest point. In that matrix I put 'WHERE "GR_name" %LIKE% "WRI_Name" ' trying to select those fields with a similar name – hpnk85 Feb 13 at 0:16
5

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.

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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