This relatively simple task has me stumped in QGIS so want to check if I'm missing something.
We get a lot of data supplied in spreadsheets. These datasets have no spatial information but will contain a field we can join with a spatial dataset (e.g postcode, hospital ID, census code etc)
In Mapinfo we have a few options:
Open CSV (or XLSX), open the spatial TAB and use the "Geocode" function to spatialise the CSV based on the relevant ID column.
Use the "SQL Select" function to join the CSV to TAB and then save the query results as a new TAB.
If the CSV has coordinates (rarely) or we have joined with a TAB that does contain coordinates, but no geometry (more likely) then we just use "Create Points", select the XY columns and we're done.
Either of those options takes literally seconds to do and this is something we do a lot.
My current workflow(s) in QGIS is like this, which seems long winded/doesn't produce satisfactory results:
- Open CSV or XLSX with no geometry (e.g List of postcodes with a number we need to theme)
- Open postcode layer with ~2.6 million points
- Expose the XY coordinates as fields on the postcode layer
- Create join on the CSV layer and bring across the XY fields
- Save the CSV join as a CSV
- Import new CSV this time selecting the new XY fields to create points
Another way I have tried in QGIS is:
- Open CSV and postcode table
- Create join on postcode table and bring across the field we need to theme, lets call it "Total Visits".
- We now have a table of 2.6 million irrelevant postcodes with only a handful of records containing values for "Total Visits" (i.e the ones we want to map)
- I try and filter the postcode table down to only show the records with a "Total Visits" value but the joined fields are not available to the filter tool.
- Save the full joined postcode layer to a new file
- Open the new table and filter to only show relevant postcodes
- Save filtered results to new file
I also tried "Join Attributes Table" from the Processing toolbox - while this was a bit quicker than the second option above, you still end up with the full postcode table that needs to be filtered down to only contain the joined records.
Also tried the MMQGIS plugin "Join By Attribute" but when run it claims my postcode layer has no geometry... but it does.
None of those options are very efficient or produce the desired output. Please tell me I'm missing something simple like a hidden option or a plugin (I have searched for various terms but not found a suitable plugin that works in this particular scenario).
At this stage it seems quicker to:
- A) Just do it in Mapinfo beforehand
- B) Load the CSV into a SQL database and do the join there
So is there a way to essentially do a full outer (or left outer) SQL join within QGIS?