Calculate minimum distance between feature types in PostGIS?

Question

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: 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.

Answer 1

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.

Answer 2

Assume you have tables table1 and table2 filled with the random points generated inside POLYGON((0 0, -1 0, -1 -1, 0 -1, 0 0)) polygon (I use points here as it's easier to create them, it works the same for polygons though).

DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;

CREATE TABLE table1 (id bigint generated always as identity, geom geometry(point, 4326));
CREATE TABLE table2 (id bigint generated always as identity, geom geometry(point, 4326));

INSERT INTO table1 (geom)
SELECT
    b.geom
FROM (
    VALUES(
        1,
        ST_SetSRID(
            ST_GeomFromText(
                'POLYGON((0 0, -1 0, -1 -1, 0 -1, 0 0))'
            ),
        4326)
    )
) a(id, geom)
CROSS JOIN LATERAL (
    SELECT ST_SetSRID(ST_MakePoint(tmp.x, tmp.y), 4326) geom
    FROM (
        SELECT
            random() * (ST_XMax(a.geom) - ST_XMin(a.geom)) + ST_XMin(a.geom) x,
            random() * (ST_YMax(a.geom) - ST_YMin(a.geom)) + ST_YMin(a.geom) y
        FROM generate_series(0,2000)
    ) tmp
) b;

INSERT INTO table2 (geom)
SELECT
    b.geom
FROM (
    VALUES(
        1,
        ST_SetSRID(
            ST_GeomFromText(
                'POLYGON((0 0, -1 0, -1 -1, 0 -1, 0 0))'
            ),
        4326)
    )
) a(id, geom)
CROSS JOIN LATERAL (
    SELECT ST_SetSRID(ST_MakePoint(tmp.x, tmp.y), 4326) geom
    FROM (
        SELECT
            random() * (ST_XMax(a.geom) - ST_XMin(a.geom)) + ST_XMin(a.geom) x,
            random() * (ST_YMax(a.geom) - ST_YMin(a.geom)) + ST_YMin(a.geom) y
        FROM generate_series(0,2000)
    ) tmp
) b;

CREATE INDEX ON table1 using gist(geom);
CREATE INDEX ON table2 using gist(geom);

It's important to create GIST indexes on geometry columns to speed up the PostGIS query.

The rest is rather easy: you want to get the minimum distance to any feature from the table2, which can be easily done with the use of CROSS JOIN LATERAL clause. You limit the results to just one row ordered by the distance measured by <-> operator.

SELECT
    t1.id,
    distance
FROM table1 t1
CROSS JOIN LATERAL (
    SELECT t1.geom <-> t2.geom distance
    FROM table2 t2
    ORDER BY t1.geom <-> t2.geom ASC LIMIT 1
) a;

The code provided by @LMB would not work, it would just insert single row into the table "table_A" instead of updating all the rows with the calculated distance.