How to write id of nearby areas in PostgreSql?

Question

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!!!

Jim Jones · Answer 1 · 2019-11-14T11:52:53.643

Have you tried using CASE conditions to make this filter? Maybe this CTE query will help you further:

WITH j AS (
SELECT id,
  CASE WHEN d1 = 0 THEN 
      (SELECT id FROM t t2 WHERE d1 = 1
       ORDER BY ST_Distance(t1.geom,t2.geom) ASC LIMIT 1)
    ELSE NULL
  END AS d1_nearest,
  CASE WHEN d2 = 0 THEN 
      (SELECT id FROM t t2 WHERE d2 = 1
       ORDER BY ST_Distance(t1.geom,t2.geom) ASC LIMIT 1)
    ELSE NULL
  END AS d2_nearest,
  CASE WHEN d3 = 0 THEN 
      (SELECT id FROM t t2 WHERE d3 = 1
       ORDER BY ST_Distance(t1.geom,t2.geom) ASC LIMIT 1)
    ELSE NULL
  END AS d3_nearest
FROM t t1
)
UPDATE t 
  SET d1_nearest = j.d1_nearest,d2_nearest = j.d2_nearest,d3_nearest = j.d3_nearest
FROM j WHERE t.id = j.id;

And here is your updated table:

SELECT 
  id, name, d1, d2, d3, d1_nearest AS np1, d2_nearest AS np2, d3_nearest AS np3 
FROM t ORDER BY id;    

  id   |              name              | d1 | d2 | d3 | np1  |  np2  |  np3  
-------+--------------------------------+----+----+----+------+-------+-------
  6600 | Горки                          |  1 |  0 |  0 |      |  8248 |  7497
  6733 | Восточный                      |  1 |  0 |  0 |      |  8248 |  8248
  6734 | Милицейский посёлок            |  1 |  0 |  0 |      |  8248 |  8248
  6752 | Захарьино                      |  1 |  0 |  0 |      |  8248 |  8248
  6753 | Липки                          |  1 |  0 |  0 |      |  8248 |  8248
  7103 | Ермолино                       |  1 |  0 |  0 |      |  8248 |  7104
  7104 | Видное                         |  1 |  0 |  1 |      | 10715 |      
  7122 | Тарычёво                       |  1 |  0 |  1 |      |  8248 |      
  7214 | Котельники                     |  1 |  1 |  1 |      |       |      
  7309 | Красногорск                    |  1 |  1 |  1 |      |       |      
  7368 | Люберцы                        |  1 |  0 |  0 |      |  8248 |  8248
  7376 | Немчиновка                     |  0 |  1 |  1 | 8248 |       |      
  7377 | Трёхгорка                      |  1 |  0 |  1 |      |  8248 |      
  7381 | Мамоново                       |  1 |  0 |  0 |      |  8248 |  7391
  7386 | Никонорово                     |  1 |  0 |  1 |      |  8248 |      
  7389 | Лохино                         |  1 |  0 |  1 |      |  8248 |      
  7391 | Одинцово                       |  1 |  0 |  1 |      |  8248 |      
  7496 | Щербинка                       |  1 |  0 |  0 |      |  8248 |  8248
  7497 | Долгопрудный                   |  1 |  0 |  1 |      |  8248 |      
  7498 | Химки                          |  1 |  1 |  1 |      |       |      
  7499 | Рублёво                        |  1 |  0 |  0 |      |  8248 |  8248
  7523 | Дзержинский                    |  1 |  0 |  1 |      |  8248 |      
  7524 | Королёв                        |  1 |  0 |  0 |      |  8248 |  8248
  7534 | Троицк                         |  1 |  0 |  0 |      |  9937 | 10723
  7638 | Зеленоград                     |  1 |  0 |  0 |      |  7498 |  7498
  7802 | Реутов                         |  1 |  1 |  1 |      |       |      
  8026 | Заречье                        |  0 |  1 |  1 | 8248 |       |      
  8118 | Мякинино                       |  0 |  0 |  1 | 8248 |  8248 |      
  8128 | Михалково                      |  0 |  1 |  0 | 7309 |       |  7309
  8223 | Московский                     |  1 |  0 |  0 |      |  8248 |  8248
  8224 | Института Полиомиелита         |  1 |  0 |  0 |      |  9937 | 10723
  8245 | Лапшинка                       |  1 |  0 |  0 |      | 10724 | 10723
  8247 | Мосрентген                     |  0 |  0 |  1 | 8248 |  8248 |      
  8248 | Москва                         |  1 |  1 |  1 |      |       |      
  8506 | Новоивановское                 |  1 |  0 |  1 |      |  8248 |      
  8507 | Сетунь Малая                   |  0 |  0 |  1 | 8248 |  8248 |      
  8508 | Марфино                        |  1 |  1 |  1 |      |       |      
  8509 | Рождествено                    |  1 |  0 |  0 |      |  8248 |  8248
  8615 | Грибки                         |  1 |  0 |  0 |      |  8248 |  7497
  8730 | Коммунарка                     |  1 |  1 |  1 |      |       |      
  8731 | Газопровод                     |  1 |  1 |  1 |      |       |      
  8776 | Знамя Октября                  |  1 |  0 |  0 |      |  8248 |  8248
  8892 | Балашиха                       |  1 |  0 |  0 |      |  8248 |  8248
  9103 | Сапроново                      |  1 |  0 |  0 |      |  8248 |  7104
  9106 | Развилка                       |  1 |  1 |  1 |      |       |      
  9398 | Мытищи                         |  1 |  0 |  0 |      |  8248 |  8248
  9453 | Инновационный центр 'Сколково' |  0 |  1 |  0 | 7391 |       |  7391
  9629 | Марьино                        |  1 |  0 |  0 |      |  9937 | 10723
  9680 | Дрожжино                       |  1 |  1 |  0 |      |       |  8248
  9937 | Внуково                        |  1 |  1 |  0 |      |       | 10723
 10417 | Солнцево-Парк                  |  1 |  1 |  0 |      |       | 10723
 10541 | Молоково                       |  0 |  1 |  0 | 7523 |       |  7523
 10620 | Отрадное                       |  1 |  1 |  0 |      |       |  7498
 10710 | Битца                          |  0 |  0 |  1 | 8248 |  8248 |      
 10715 | совхоза имени Ленина           |  1 |  1 |  1 |      |       |      
 10723 | Рассказовка                    |  1 |  0 |  1 |      |  8248 |      
 10724 | Пыхтино                        |  1 |  1 |  0 |      |       | 10723
 10996 | Боброво                        |  1 |  1 |  0 |      |       | 11022
 11022 | Бутово                         |  1 |  1 |  1 |      |       |      
 11205 | Саларьево                      |  1 |  0 |  1 |      |  8248 |      
 11315 | Кнутово                        |  1 |  0 |  0 |      |  9937 | 10723
 11558 | Речник                         |  1 |  1 |  1 |      |       |      
 11643 | Путилково                      |  1 |  1 |  1 |      |       |      
 12229 | Виноградово                    |  1 |  0 |  0 |      |  8248 |  8248
(64 Zeilen)

This will definitely help me in my next task, filtering trips with Beijing ONLY — arilwan, Jun 17 '20 at 17:50
@arilwan this might be also helpful for your use case, you could create a heat map of areas with more/less "traffic": https://stackoverflow.com/a/50845811/2275388 — Jim Jones, Jun 17 '20 at 18:07
Oh yes, I will be interested in generating a heatmap of Beijing and Porto cities. I shall studies this answer in more detail. You make my day! — arilwan, Jun 17 '20 at 18:19
@arilwan in this answer I simplified the function a bit using a BBOX instead of 4 different parameters: https://stackoverflow.com/a/59853843/2275388 — Jim Jones, Jun 17 '20 at 18:41

score 1 · Accepted Answer · answered Nov 13 '19 at 21:13

1

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.

answered Nov 13 '19 at 21:13

Islingre

2,030
6
18

Thx for response!! my knowledge does not allow me to compose a function for determining the location in the specified context. Thanks anyway! – Denis Or Nov 14 '19 at 08:10
1

You have something similar to ```ST_Distance ( ST_Transform (c2.geom :: geometry, 3857), ST_Transform (c.geom :: geometry, 3857) )``` in the code of your question. I do not know what it does, but you could have a try with it. No need to write your own distance function, I guess – Islingre Nov 14 '19 at 13:55

How to write id of nearby areas in PostgreSql?

2 Answers2