Import CSV and join with another table to add geometry in QGIS

Question

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:

1. Open CSV or XLSX with no geometry (e.g List of postcodes with a number we need to theme)
2. Open postcode layer with ~2.6 million points
3. Expose the XY coordinates as fields on the postcode layer
4. Create join on the CSV layer and bring across the XY fields
5. Save the CSV join as a CSV
6. Import new CSV this time selecting the new XY fields to create points

Another way I have tried in QGIS is:

1. Open CSV and postcode table
2. Create join on postcode table and bring across the field we need to theme, lets call it "Total Visits".
3. 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)
4. 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.
5. Save the full joined postcode layer to a new file
6. Open the new table and filter to only show relevant postcodes
7. 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?

Answer 1

I would suggest looking at this post Join by attribute / spatialite with SQL / left outer join in QGIS Here is the content below.

Use Spatialite Database!

It is a lightweight file based spatial DB supported out of the box by QGIS.

1. First set-up a spatialite DB following theses instructions

2. Push your two tables to this spatialite DB using QGIS DB manager

3. Assuming that your tables are called "polygon" and "line" run the following SQL command in DB manager query interface.

SELECT polygon.id,
polygon.lib, -- Place here any field releveant for you (they must also be 
  in grouping clauses, see below)
group_concat(line.id,',') as list_id_line -- this function concatenate the 
  id of every line that touch you polygon
FROM polygon LEFT OUTER JOIN line
ON Intersects(polygon.geom,line.geom) -- Spatial Dabatabase Rule !
GROUP BY polygon.id, polygon.lib -- theses are the grouping clauses

More explanations and fun by reading about SQLite aggregate functions here and spatialite functions here