How to write id of nearby areas in PostgreSql?

please help!

The database contains geodata of cities in WKB format. In each row in columns d1, d2, d3 (delivery companies), the values are 1 or 0. 1 means that there is delivery in this city. 0 means no.

If in the column d1 for any row there is 0 (no delivery), I need to find the nearest (by distance) zone for this company, where there is delivery and write the id of this zone in a new column d1_nearest (or update the data if the column is already created).

In the same way, for other delivery companies d2 in d2_nearest, d3 in d3_nearest. If d1 = 1 is set (this means that there is delivery in this zone), and d1_nearest is = "NULL".

Source table:

|id   |name   |geom          |d1 |d2 |d3 |
|-----|-------|--------------|---|---|---|
|7499 |Rublevo|010300 ... B40|1  |0  |0  |
|7534 |Troitsk|010300 ... B40|1  |0  |0  |
|9629 |Maryino|010300 ... B40|1  |0  |0  |
|9937 |Vnukovo|010300 ... B40|1  |1  |0  |
|10724|Pihtino|010300 ... B40|1  |1  |0  |
|10996|Bobrovo|010300 ... B40|1  |1  |0  |

I search result like this:

|id   |name   |geom          |d1 |d2 |d3 |d1_nearest|d2_nearest|d3_nearest|
|-----|-------|--------------|---|---|---|----------|----------|----------|
|7499 |Rublevo|010300 ... B40|1  |0  |0  |NULL      |8248      |8248      |
|7534 |Troitsk|010300 ... B40|1  |0  |0  |NULL      |9937      |10723     |
|9629 |Maryino|010300 ... B40|1  |0  |0  |NULL      |9937      |10723     |
|9937 |Vnukovo|010300 ... B40|1  |1  |0  |NULL      |NULL      |10723     |
|10724|Pihtino|010300 ... B40|1  |1  |0  |NULL      |NULL      |10723     |
|10996|Bobrovo|010300 ... B40|1  |1  |0  |NULL      |NULL      |11022     |

Etc.

I did this, but for this option i need two tables. And I still did not understand how to make a cycle for the entire table and setting values for each column from the delivery companies ... I am not very good at programming.

Please help with the task!

SELECT
t1.name As name,
t1.geom As geom,
t2.name As name,
t2.geom As geom,
ST_Distance (
ST_Transform (t1.geom :: geometry, 3857),
ST_Transform (t2.geom :: geometry, 3857)
) as dist
FROM cities11 As t1, cities10 As t2
WHERE t1.id = '7499' AND t1.id <> t2.id AND t2.d1 = '1'
ORDER BY ST_Distance (t1.geom, t2.geom)
LIMIT 1

Sample code from this page

Sample CSV

Many Many Thx!!!

Solution

I am not quite sure about your tablenames and about geometry. But if you replace the tablename and <distance_function(c.geom, c2.geom)> to something giving you the distance between the cities, this might help:

UPDATE cities c
SET d1_nearest = (
    SELECT c2.id
    FROM cities c2
    WHERE c2.d1 = 1
    ORDER BY <distance_function(c.geom, c2.geom)>
    LIMIT 1
) WHERE d1 = 0;

For the other two columns it is analogous. Of course you need to create the new columns in advance.