Calculate road distance between two points in PostGIS - Resultant distance is not accurate (not even close to the actual distance) [closed]

Question

SELECT
torigin1.id AS origin_id,
tdest1.id AS destination_id,
CASE
    WHEN ST_LineLocatePoint(troad.line_string, torigin1.geom) < ST_LineLocatePoint(troad.line_string, tdest1.geom)
    THEN ST_Length(ST_LineSubstring(troad.line_string, 
        ST_LineLocatePoint(troad.line_string, torigin1.geom), 
        ST_LineLocatePoint(troad.line_string, tdest1.geom)
    ))
    ELSE ST_Length(ST_LineSubstring(troad.line_string, 
        ST_LineLocatePoint(troad.line_string, tdest1.geom), 
        ST_LineLocatePoint(troad.line_string, torigin1.geom)
    ))
END AS road_distance
FROM troad
INNER JOIN torigin1 ON troad.gid = torigin1.gid
INNER JOIN tdest1 ON troad.gid = tdest1.gid
WHERE torigin1.gid IN (2) AND tdest1.gid IN (2);

The result I got is not close to the actual distance. All three datasets used are in UTM projection 32644 as the region I worked is in India.

I have tried using ST_ClosestPoint as well but still the answer is not correct.

Answer 1

A more versatile, more performant approach:

SELECT
  ori.id AS origin_id,
  dst.id AS destination_id,
  ST_Length(tr.line_string) * ABS(dst.frac - ori.frac) AS road_distance
FROM
  troad AS tr
  CROSS JOIN LATERAL (
    SELECT
      o1.id,
      ST_LineLocatePoint(tr.line_string, o1.geom) AS frac
    FROM
      torigin1 AS o1
    WHERE
      o1.gid = tr.gid
  ) AS ori
  CROSS JOIN LATERAL (
    SELECT
      d1.id,
      ST_LineLocatePoint(tr.line_string, d1.geom) AS frac
    FROM
      tdest1 AS d1
    WHERE
      d1.gid = tr.gid
  ) AS dst
WHERE
  tr.gid IN (2)
;

For each row in troads we find the fractions of line length of the projections of both ori.geom & dst.geom (having the same gid) onto the inital LineStrings.

We then simply take the (absolute) difference between both fractions and apply this to the length of the respective initial LineString.

---

Note that this can only ever work correctly if all troad.line_string geometries are single, simple LineStrings, and there is exactly one pair of torigin1 & tdest1 rows per troads row! If that is not the case, and there are multiple pairs per road, you would want to loop over the point tables instead, executing the same calculations.

If these distances are then still not correct I would assume there's a projection issue, or the geometries are not suitable.