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?
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.
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.
Step 2. Drag&Drop the CSV file into QGIS. Make sure the right encoding was chosen i.e. UTF-8
Step 3. Apply the "Batch Nominatim geocoder" and press Run
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
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.
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.
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.
