Depicting Table of Imported (CSV) Points With Lat/Long in Another Table in QGIS

Question

I have a CSV table of points with descriptive location. Say something like this:

Sarah, Todtmoos
Henry, Paris
John, New York
Elma, Paris
... more rows like this ...

I have another CSV table with the geographic location of these (often repeated) locations. Say something like:

Todtmoos,47.739348, 8.001628
Paris,48.853868, 2.343282
New York,40.737401, -73.994330

I would like to depict the points in the former table as located in the appropriate location which can be found in the latter table (obviously points will overlap due to duplicate locations).

I could use LOOKUP in a spreadsheet application like Excel before I bring the material into QGIS through CSV import with geography from lat/long, or, more tediously, simply do a find-and-replace on each location and replace with its equivalent lat/long.

However, I've been wondering if there is a simple way to do this within QGIS. Something with a field calculator or some other dynamic lookup process with the attribute tables? I'm guessing this is not a normal table or spatial join and would welcome suggestions on most robust way to do this. (I use QGIS 3.10 on OSX). I have seen something about conversion to a database but am hoping for something simpler?

Answer 1

Using simple joins likely won't give you the desired results, because you have to join geometry as well as attributes, and joining the descriptive table to the geography table with the simple join function will only yield one record per city.

It is possible to achieve what you desire by creating a Virtual Layer.

First of all, add your CSV files in QGIS by going to Layer > Add Layer > Add Delimited Text Layer. Make sure the options "Detect field types" and "Trim fields" are checked, it can save you a lot of trouble later. Add your geography table as a point layer using Longitude as X and Latitude as Y, EPSG:4326 as CRS.

Once you have added your two layers, go to Layer > Create Layer > New Virtual Layer.

Paste the following SQL Query in the Query section:

SELECT *, Geography.geometry
FROM Geography
INNER JOIN Descriptive
ON Geography.Location = Descriptive.Location

Here I used Geography for the table name containing the Lat/Lon, Descriptive for the table containing the names, and Location for the City/Location name. Simply change these words according to your actual layer and field names. Select Autodetect for the geometry, then click on Add.

You should see the desired result appear as a Virtual Layer that you can then save as a normal layer anywhere you like.