4

In QGIS 3.8 I have three layers:

  1. world map divided into countries;
  2. layer of points representing cities;
  3. layer of points representing conflict events (e.g. firefight).

My goal: For each city, calculate the nearest conflict event, conditional on the city and the conflict event being in the same country. Assume that all countries have at least one conflict event and one city.

I know this is a "nearest neighbor" calculation, but I could not find an example where this calculation is done within polygons. When I try to do this without taking the world map (country polygons) into account, sometimes it matches cities and conflicts in different countries, which is not what I want.

Taras
  • 32,823
  • 4
  • 66
  • 137
Kerim
  • 43
  • 4
  • @Taras I have copied your code but unfortunately, I get this empty results in the virtual layer. Any ideas what is going wrong? I don't receive any errors.. – Niels Janssens Oct 19 '20 at 13:38

1 Answers1

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.

input

With the following query, it is possible to calculate the nearest conflict event for each city, conditional on the city and the conflict event being in the same country.

SELECT
    ct.Name AS city,
    c.Name AS conflict,
    cnt.Name AS country,
    make_line(ct.geometry, c.geometry),
    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).

result

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.

    Yes, "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. "id" and "Name".

    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.

References:

Taras
  • 32,823
  • 4
  • 66
  • 137
  • Thank you very much. Unfortunately I need two clarifications, because I have only used QGIS with the drop-down menus. My questions: 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? 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?

    Thank you for answering these questions or directing me to a tutorial.

     – Kerim Jul 22 '19 at 16:14