Tested on QGIS 2.18 and QGIS 3.4
I can suggest using a
"Virtual Layer" through
Layer > Add Layer > Add/Edit Virtual Layer...
Let's assume we have seven features in
"cities" (yellow), nine in
"conflicts" (red), and three in
"countries" (green) accordingly, see image below.
With the following Query, it is possible to achieve the result
SELECT ct.Name AS city, c.Name AS conflict, cnt.Name AS country,
ROUND(MIN(ST_Distance(ct.geometry, c.geometry)),2) AS distance,
ct.id || '_' || c.id AS uniqueid
FROM cities AS ct, conflicts AS c, countries AS cnt
WHERE st_within(c.geometry, cnt.geometry) AND st_within(ct.geometry, cnt.geometry)
GROUP BY ct.Name
ORDER BY distance DESC
The output Virtual Layer will generate the shortest lines between cities and conflicts that are located within the same country, including the following attributes. 'city', 'conflict', 'country', 'uniqueid', and 'distance' (in meters).
P.S. mind the CRS
UPDATE: replying to @Kerim's questions left in the comment line
1) I think in the line starting with "SELECT", "ct.Name", "c.Name", "cnt.Name" are existing variables and I give them new names ("city", "conflict", "country") for the new virtual layer. Is that correct?
It is right to a certain extent. If all attributes' names within all of the layers that you are running the query on are unique then you can simply use their names with no changes. However, in my query, each shapefile has its own column
"Name", so if you refer in a Virtual Layer just to
"Name" it simply does not know which
"Name" attribute to use. It can be solved by means of an alias, see this w3schools | SQL | SQL AS Keyword.
"ct.Name", "c.Name", "cnt.Name" are existing variables, nevertheless, you can consider any other that your shapefiles may contain. I used the most common, e.g.
You can give them new names, simply not be confused by values with the same attribute names.
2) Your code has ".geometry" objects. However, my points don't have 1 single geometry object; they have latitude and longitudes. Should I first create these geometry objects?
geometry is so to say a "hidden" column, that stores geometry of your layer. So, I simply extracted the geometry of each shapefile in the query. Additionally, you can find more details here: QGIS Virtual Layer | Performance issues and Chapter 4. Using PostGIS: Data Management and Queries | 4.2. PostGIS Geography Type
Moreover, if you prefer you can simply use columns with latitudes and longitudes as well with
ST_SetSRID(ST_MakePoint(lon, lat), 'srid'), however,
geometry IMHO, in fact, is a bit more sophisticated and comprehensive.