1

I have two shapefiles ("tax_parcels" and "buffalo") that I would like to join based on the address fields ('PropAddres' and 'physical_a') of their attribute tables. The problem is that one column provides building number, street number and suffix abbreviations (ex st, ave), where as the other provides only building number and street name. Below are examples of values:

PropAddres: 1 Geneva St, 100 Reiman St, 256 Sumner Ave.

physical_a: 1 Geneva, 100 Reiman, 256 Sumner

For this I am using QGIS. As far as I am aware, QGIS has function called levenshtein, however I have never worked with this function, which is why I am not sure how I can use it.

3
  • Are all adresses in each table unique? For example there's only one 1 Geneva, 100 Reiman, 256 Sumner in buffalo shapefile? How many rows in are there in each shapefile?
    – Bera
    Commented Mar 1, 2022 at 6:24
  • What you are describing is not a spatial join, but a join based on attributes. My first suggestion would be create a new field in tax_parcels where you copy the values in PopAddres replacing strings like " St," with ",". You also mention that both datasets are shapefiles, so maybe you could go for a spatial join or intersect?
    – ChloeG
    Commented Mar 1, 2022 at 7:03
  • Thank you, ChloeG. I will definitely try what you suggest. For one of the shapefiles, I have it in CSV format as well. Commented Mar 2, 2022 at 5:30

1 Answer 1

2

You can try this:

1 Create a virtual layer which is the cross join of your two layers:

select buff."kommunnamn", tax."kommunnamn"
from buffalo1 as buff
cross join
taxparcels1 as tax

enter image description here Right click this and export as a geometryless geopackage table.

2 Field Calculate a similarity field in the output with levenshtein( "kommunnamn", "kommunnamn:1" )

3 Delete rows with a similarity above some threshold, I use >4

4 Calculate a uuid column uuid()

5 Join this uuid to your two start tables, then join these together by uuid.

Or if possible skip 4 and 5 and join by the columns in the geopackage

4
  • Thank you, BERA. When I am trying to create the virtual layer, it says that there are no layers with name "buffalo1" and "taxparcels1". Also it says no such column as buff.kommunnamn. Commented Mar 1, 2022 at 14:50
  • Well you need to adjust the code to match the names of your layers and fields
    – Bera
    Commented Mar 1, 2022 at 14:51
  • So I replace Kommunamn with the column names? Commented Mar 1, 2022 at 14:52
  • select buff."PropAddres", tax."physical_a" from buffalo as buff cross join tax_parcels as tax
    – Bera
    Commented Mar 1, 2022 at 17:46

Your Answer

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

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