3

I have 3 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.

I use QGIS 3.8.

4

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.

input

With the following Query, it is possible to achieve the result

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:

  • 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 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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