Search code examples
sqlpostgresqlpostgis

Calc distance Between two tables

I have a problem with my PostgreSQL Database trigger.

I am trying to calculate the distance between two Geo points with PostGIS tools. The two points are in separate tables, the KEY between those two tables is the site_id column.

The sites table structure is:

CREATE TABLE sites(
  site_id INT,
  site_name text,
  latitude float ( 6 ),
  longitude float ( 6 ),
  geom geometry

);

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 goal is the make a trigger that automatically calculates the distance when new Dots are added.

The trigger I created is:

CREATE TRIGGER dots_insert_site_dist_trigger
  AFTER UPDATE OF "geom"
  ON "dots"
  FOR EACH ROW
  EXECUTE PROCEDURE dots_insert_site_dist_update();``

The function itself:

  RETURNS trigger 
  AS
$$
BEGIN
        UPDATE dots SET dist_from_site =
  (SELECT ROUND(100*ST_Distance(NEW.geom, sites.geom))
  FROM
  dots,
  sites
  WHERE sites.site_id = NEW.site_id);
  RETURN NEW;
END;
$$
 LANGUAGE 'plpgsql';

The Error:

ERROR: more than one row returned by a subquery used as an expression CONTEXT: SQL statement "UPDATE dots SET dist_from_site = (SELECT ROUND(100*ST_Distance(NEW.geom, sites.geom)) FROM dots, sites WHERE sites.site_id = NEW.site_id)" PL/pgSQL function dots_insert_site_dist_update() line 3 at SQL statement

Site Table: 1

Dots Table: 2

Solution

  • at first look seems you missed join condition in subquery

    SELECT ROUND(100*ST_Distance(NEW.geom, sites.geom))
FROM  dots
INNER JOIN sites on dots site_id = sites.site_id
WHERE sites.site_id = NEW.site_id

    and this should generate a cartesian product (m x n rows between the tables .. and as a suggestion you should avoid the old join syntax based on comma separated table's name and where condition and use the explicit join syntax with on clause

    and you could try using the query as subqiery for update

      UPDATE dots 
  SET dist_from_site = t.my_dist
  FROM (
      SELECT dots.site_id, ROUND(100*ST_Distance(dots.geom, sites.geom)) my_dist
      FROM  dots 
      INNER JOIN sites on dots.site_id = sites.site_id 
      WHERE sites.site_id = dots.site_id
  ) t 
  WHERE dots.site_id = t.site_id