2

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?

4
  • Join the tables
    – BERA
    Oct 17, 2020 at 14:15
  • Thanks for the suggestion but if you import the first table A (as CSV without geometry), and table B (as having geometry with the lat/long), then join table A (with the names Todtmoos etc as strings in the city column), to the Table B (also with matching string in the city column), you just end up with NULL values in the new columns added to the Table A. This doesn't work like a regular table join, it seems. Oct 17, 2020 at 14:21
  • Use the join by attribute value tool.
    – Al rl
    Oct 17, 2020 at 14:50
  • Thanks, that sounds promising: guessing you mean one of the join tools in the processor toolbox though I find nothing exactly under name you mentioned (join attribute by field value, perhaps? that finds some exact matches but merely adds columns). I played with a few of them, but don't see how that gets to the desired end result (end result being points from table a, on the map, in the lat/long locations from table b). Thanks again however, maybe someone could be more specific? Oct 17, 2020 at 15:20

1 Answer 1

3
+50

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.

enter image description here

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

1
  • This is brilliant and exactly what I was hoping for. My thanks for your time. I will select this as the answer and looks like I'm required to wait a few hours before awarding the bounty. Oct 20, 2020 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.