5

I've got an Excel file which includes known locations with their exact address (street, house number, city, state). This Excel file does not have a geographical column. Also, I have a shapefile inside those boundaries of buildings.

I want to relate every address in the Excel file to a building from the .shp file in order to represent to the .xls file on a map and for further analysis.

Couldn't find a way to do it in QGIS. Any ideas?

1
  • Does the building data contain addresses?
    – underdark
    Apr 8, 2015 at 17:30

5 Answers 5

5

You might try the plugin MMQGIS, which has geocoding functionality that will output a points shapefile. (You may have to add a COUNTRY column to your XLS file, as well as reproject the output points to your project's CRS.) You could then spatially join the MMQGIS points with building shapefiles you already have.

2
2

For QGIS versions QGIS 3.20 and higher

Starting from QGIS 3.20 it is possible to geocode addresses to coordinates by means of the native tool "Batch Nominatim geocoder", can be found under Vector General in the Processing Toolbox (Ctrl+Shift+T).

Step 1. Prepare your input CSV file which includes an address field i.e. is a concatenation of fields e.g. street, house number, postal code, city, state, country. (Some attributes are optional)

Let's assume there is a CSV file called 'test.csv', see image below.

input

Step 2. Drag&Drop the CSV file into QGIS. Make sure the right encoding was chosen i.e. UTF-8

step_1

Step 3. Apply the "Batch Nominatim geocoder" and press Run

step_2

Step 4. Make use of the "Add X/Y fields to layer" to get coordinates. Change the CRS if needed

Step 5. Get the final output

result

11
  • 2
    Yes, it works. Just tested with Königstraße 50 70173 Stuttgart Germany
    – Taras
    Nov 10, 2021 at 8:15
  • 1
    So non-latin characters will be the next challenge...
    – Babel
    Nov 10, 2021 at 8:15
  • 2
    Also works, just tested with Зодчих 22 03194 Київ Україна (That was my secondary school @_@)
    – Taras
    Nov 10, 2021 at 8:20
  • 1
    Great - both that it works and to see where your school was. So this is definitely an answer that should be heavily upvoted as it in fact replaces the earlier answers that become kind of outdated.
    – Babel
    Nov 10, 2021 at 8:26
  • 1
    Healthy Propaganda of this thread :)
    – Taras
    Nov 10, 2021 at 8:45
0

I assume the building shape file has address details. If not this needs to be reverse geocoded. Once you have the buildings addresses sorted you should create a csv format of the Excel file and import it into the QGIS. CAUTION you need to make sure that the addresses are written is the same format or else it would not give the desired result. In any case you might have to prepare the addresses to the same format. Once the csv has been imported you can do a simple table join, here is a simple tutorial on Table Join http://www.qgistutorials.com/en/docs/performing_table_joins.html Hope this helps.

0

You need to first geocode your XLS by converting it to CSV and then (as described in another answer) geocode it using MMQGIS 'geocode CSV with google maps' to create a point shapefile of your addresses.

You then need to run a spatial intersect in QGIS using the Vector > Spatial Query tool on your address points and your buildings. You will end up with a point shapefile with information from each of the buildings they intersect.

0

For an exact geocoding, you may be aware to define the street name and house number together as the address field in the .csv file. It means the address filed in the .CSV file should contain street name and house address. The format I used was having a space between them.

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.