How can I check if two attributes match between a point and the area within which the point is located? in QGIS

Question

I would like to verify a geocoding process. I have used googles API and have geocoded about 35.000 adresses within Austria. By inspecting the locations it is obvious that a few lie outside of Austria (5 total) and that around another 30 are not coded in the right counties. This I could confirm visually, but I´m not sure how much I can trust the rest of the results and can´t check 35000 points visually. Therefore I would like to see if the adresses lie in the right local communities. I have a point layer containing the street adresses and the respective community and a vector layer containing all the administrative boundaries. So I would like to do a sort of spatial query by location AND attribute, to see if the local community contained in my point layer matches the name of the community that is contained in my administrative boundary layer to root out obvious mistakes. Does anybody have an idea how I could do this?

Answer 1

If you have PostGIS with your QGIS (or maybe you could use virtual layers instead !), you can run an SQL like the following to select the points that fall within admin boundaries and have matching names with the administrative boundaries they fall within:

SELECT  p1.point_id, p1.community_name, p2.admin_boundary_name
FROM points p1, admin_boundaries p2
WHERE ST_Within(p1.geom, p2.geom) 
AND p1.community_name = p2.admin_boundary_name

I tried to use descriptive names, you will have to replace them with whatever you have, and if you also want the geometries of the points returned, just add p1.geom to the select.

Answer 2

This is probably most simply done using a two-step process of spatial join and then adding a field to check for matches. So...

1. Spatial join (Join attributes by location) - your input layer is your points and your join layer is your administrative boundary polygons. This should join your administrative boundary names to your points.
2. Open the attribute table of your output, and click field calculator. Use the field calculator to create a new field (called "match" or something similar) and use the expression "Community Name" == "Admin Name" to flag matches vs non-matches.