Search code examples
postgresqlpostgis

Find minimum distance and update column with PostGIS

I need help in my PostGIS database to calculate the distance between two points. The goal is to find for each row in the "Dots" table the distance from the closest point in the "reflayer" points table and save it in meter in the "dist_from_ref" column.

The dots table structure is:

CREATE TABLE dots
(
    dot_id INT,
    site_id INT,
    latitude float ( 6 ),
    longitude float ( 6 ),
    rsrp float ( 6 ),
    dist INT,
    project_id INT,
    dist_from_site INT,
    geom geometry,
    dist_from_ref INT;
);

The reflayer structure is:

CREATE TABLE reflayers 
(
    layer_name varchar,
    latitude float ( 6 ),
    longitude float ( 6 ) ,
    geom geometry
);

Dots table

Dots table

Reflayer table

Reflayer table

Does anyone have a solution that can update the "dist_from_ref" column with the minimal distance the query can find?

Edit:

UPDATE dots d
SET dist_from_ref = 100 * ROUND (1000 * ST_Distance(d.geom, r.geom))
FROM reflayers r 
WHERE d.dist_from_ref IS NULL 
  AND r.geom = (SELECT r.geom
                FROM reflayers r 
                ORDER BY ST_Distance(d.geom, r.geom) ASC LIMIT 1);

This query updates the columns as I want to, but it stuck on my PostGIS server with 60K rows.

I used it on 70 rows and I worked fine any suggestions to improve it?

Before and After

Dots table before

Dots Table Before

Dots table after

Dots Table After

Text table

dot_id | site_id | latitude  | longitude |  rsrp  | project_id | dist_from_site | dist_from_ref |                        geom
--------+---------+-----------+-----------+--------+------------+----------------+---------------+----------------------------------------------------
      1 |   42047 | 31.902857 | 34.919445 |  -90.9 |          1 |             21 |          7200 | 0101000020E6100000F5F6E7A221E73F4041BCAE5FB0754140
      2 |   42047 | 31.902857 | 34.919445 |  -89.5 |          1 |             21 |          7200 | 0101000020E6100000F5F6E7A221E73F4041BCAE5FB0754140
      3 |   42047 | 31.902857 | 34.919445 |  -89.5 |          1 |             21 |          7200 | 0101000020E6100000F5F6E7A221E73F4041BCAE5FB0754140
Solution

  • Place the subquery in the SELECT clause and reference it to the each row of the outer query, e.g.

    SELECT *,(
  SELECT min(ST_Distance(d.geom, r.geom))
  FROM reflayers r) as distance
FROM dots d;

    To update just do the same ..

    UPDATE dots SET dist_from_ref = (
  SELECT min(ST_Distance(dots.geom, r.geom))
  FROM reflayers r)

    Note: Depending on the table size this operation can become very time consuming. Since you have no way to join both tables, you let the query run a full scan in every single record of refLayers for every single record on dots in order to find the closest distance.