3

I have the following data:

Question I. For a boundary layer

How to create a dummy variable being 1 if a district lies on the coast and 0 otherwise. Alternatively, I also want to know the distance from the district to the closest coast.

example1

Question II. For a river layer

How to create a dummy variable where the dummy is 1 if a county is crossed by a river and 0 otherwise. The thing now is, that each river is assigned a scale rank variable. I only want the dummy to be 1 if rivers with scale ranks between 1 and 5 cross the county .

example2

EXTENSION: I still did not manage doing the task using the field calculator. If someone could help me out, I would be very grateful!

  • 2
    This is also possible using the field calculator. I don't know how that method compares to the virtual layer method suggested by @Taras; perhaps the virtual layer method is more efficient. – csk Jun 7 at 18:25
  • How to do it using the field calculator? The virtual layer method does not work for me so far... – philsch Jun 8 at 12:15
2

How to find out how far a county is from the nearest coastline:

  1. Install the NNJoin plugin.
  2. Run the NNJoin plugin, using the county layer as the first input, and the coastline as the second input. It will create a duplicate of the county layer, with an additional 'distance' field that tells how far the nearest coastline is.

How to create a field that tells whether a district is on the coast

  1. Install the RefFunctions plugin (this adds custom functions to the Field Calculator, including the intersecting_geom_count function)
  2. Use the Field Calculator to create a field using an expression like: intersecting_geom_count('coastline')>0. The field value will be 0 (false) when the county doesn't intersect a coastline, and 1 (true) when it does.

Note: If the coastline layer doesn't perfectly match the county boundaries layer there may be coastal counties that don't get counted because their mapped boundaries don't actually intersect the coastline layer. To solve this issue, create a small buffer of the coastline layer. Use the coastline buffer instead of the coastline layer in step 2 above.

How to create a field that tells whether a district has a river of rank 1 through 5:

  1. Filter the river layer by the rank field, using this filter expression: "rank" <= 5 AND "rank" > 0
  2. Follow steps 1 and 2 above, using the river layer instead of the coastline layer.
  • 1
    Thanks, this works perfectly fine and only takes a managable amount of time! Thanks to @Taras again as well! – philsch Jun 13 at 11:24
8

This solution might be not the best one in terms of time consumption and performance because of the data size (calculating for the whole world). However, if you want to achieve a "rapid" result (here you do not need to set up anything) IMHO this is what you need. Nevertheless, handling large data sets will be much more efficient by means of PostgreSQL with PostGIS extension. Perhaps writing a PyQGIS script is even better or as was suggested by @csk via using the Field Calculator.


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 the following layers "boundaries", "coastlines", and "rivers", see image below. Numbers over the lines represent the "scalerank". I did not use the data provided in the question, because of the large volume.

input


Question I. For a boundary layer.

With the following query, it is possible to create a dummy variable being 1 if a district lies on the coast and 0 if not, including a distance from the district to the closest coast (this will only give a value when a district does not intersect the coastline).

SELECT "boundaries".*,
(CASE
    WHEN "boundaries".id IN
        (SELECT "boundaries".id
         FROM "boundaries", "coastlines"
         WHERE st_intersects("boundaries".geometry, "coastlines".geometry))
    THEN '1'
    ELSE '0'
    END) AS dummy_var,
    ROUND(ST_Length(ST_ShortestLine("boundaries".geometry, "coastlines".geometry)),2) AS distance
FROM "boundaries", "coastlines"
GROUP BY "boundaries".id
ORDER BY MIN(ST_Length(ST_ShortestLine("boundaries".geometry, "coastlines".geometry)))

The output Virtual Layer with its Attribute table will look as following

output_1

In this case, be careful with geometric predicates since "boundaries" and "coastlines" cannot only intersect, as well as touch etc. Hence, you may extend the query with the OR operator, i.e.

WHERE st_intersects("boundaries".geometry, "coastlines".geometry) OR st_touches("boundaries".geometry, "coastlines".geometry)

Moreover, mind the CRS. Work with one that provides a distance in miles, meters etc.


Question II. For a river layer.

With the following query, it is possible to create a dummy variable 1 if a river crosses a district and 0 otherwise and for rivers with scale ranks between 1 and 5.

WITH "rivers_1_5" AS (
    SELECT *
    FROM "rivers"
    WHERE "scalerank" >= 1 AND "scalerank" <= 5
)

SELECT "boundaries".*,
(CASE
    WHEN "boundaries".id IN
        (SELECT "boundaries".id
         FROM "boundaries", "rivers_1_5"
         WHERE st_intersects("boundaries".geometry, "rivers_1_5" .geometry))
    THEN '1'
    ELSE '0'
    END) AS dummy_var
FROM "boundaries"
GROUP BY "boundaries".id

The output Virtual Layer with its Attribute table will look as follows.

output_2


Answering the question in comments: "Where/How can I enter the code?"

In QGIS 2 paste the code and then run Ok

paste_code_here_v2

In QGIS 3 paste the code and then run Add

paste_code_here_v3


  • Thanks a lot. As I am new to QGIS, only using the interface so far, I have no experience with coding. Therefore my question: Where/How can I enter the code? – philsch Jun 7 at 8:01
  • So far QGIS crashes when applying the code, so I do not know whether it works or not. Doing it with the US is just a sample, actually I want to the procedure for the whole world! – philsch Jun 7 at 9:12
  • 1
    I don't recommend adding the PyQGIS tag unless you're prepared to make a code attempt. Any question asking for a coded answer that doesn't include a code attempt will almost certainly be closed. (This site policy is meant to deter people just using this site as a free coding service.) @Taras if you want to volunteer an answer using pyqgis you can do that whether they specifically request it or not. – csk Jun 7 at 18:23
  • Thank you very much for your solution! Your code works just fine for small datasets. Unfortunately QGIS always crashes when applying it to larger ones (also when doing overnight-runs). – philsch Jun 12 at 7:11
  • 1
    I recommend you to ask it as a new question. "Field Calculator" shall do the work for a big data set or perhaps somebody will suggest a better option/workflow for you. Just a small hint: When asking a new question please some efforts of what you have tried, for instance in "Field calculator" you try with the usage of CASE, aggregate, intersects functions. – Taras Jun 12 at 7:23

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.