0

I have polygon layer in QGIS, and for each polygon I would like to calculate the minimum distance to the nearest polygon in another layer. My desired result is an attribute for all features that has the minimum distance from that feature to another layer. (context: it's a sort of (ecological) connectivity analysis)

I got a lead to using PostGIS, so I installed that successfully and loaded the dataset into a PostGIS database. I think the function ST_Distance might be the right tool, but I don't know enough SQL to construct the right query.

Sample of my dataset

Sample of my dataset: I want to know the shortest distance between a red or yellow polygon tot a purple polygon. distance measured between outlines, not centroids or something.

  • You are trying to do a nearest-neighbour analysis. The link would be a good place to start. You can practically copy the first query under 27.1 postgis.net/workshops/postgis-intro/knn.html – LMB Nov 20 '18 at 11:51
  • @LMB Thanks, this comes close. Unfortunately I don't exactly know how to alter this query to what I want: to add a new column to layer A with the distance of every feature to the nearest polygon edge of layer B. The example you point out gives the name of the nearest polygon of layer B to polygon 'x' of layer A. – Rens Nov 21 '18 at 11:05
0

The following SQL code should get the job done. You have to replace the code between "" with the variable applicable for you, without the "". The added column is an 8-byte float. For more about data types see Postgres documentation. Furthermore I assume that the column that you use to store the geometry is called "geom". It is also possible that this is called "the_geom". If you don't know start with:

SELECT * from "table_A" LIMIT 1

to see if the field is called "geom" or "the_geom". For now I assumed it is called "geom". If not, you have to change the below query. Now the below query (actually two queries) first create a column and then populate this column with the distance from each feature in table A to the nearest feature in table B. Note that the value depends on the coordinate system of the tables. So WGS84 would give you a value in Degrees, while UTM will give you a value in meters. Be sure that both the tables (or more exact: geometry columns) have the same coordinate system.

ALTER TABLE "table_A" ADD "column_name" float8;
INSERT INTO "table_A" ("column_name")
VALUES
(
SELECT ST_Distance("table_A".geom, "table_B".geom)
FROM table_A, table_B
ORDER BY ST_Distance("table_A".geom, "table_B".geom)
LIMIT 1
)

I have not tested the query, so it could contain errors.

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.