torigin1.id AS origin_id,
tdest1.id AS destination_id,
    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.

  • 4
    "Not close" is not descriptive. What is the actual value? What was computed? What data values would reproduce the behavior?
    – Vince
    Sep 6 at 11:29
  • joining the road segments and origin/dest by ID seems counterintuitive, especially since you then fetch a geometry which may or may not be near the associated segment. Also it may work only if a single road segment is retrieved (by id)
    – JGH
    Sep 6 at 11:45
  • You mention "road distance". Do you mean the distance via the road network? If so, this is a job for pgrouting, but your table troad needs to be in a particular format (columns: id, source, target, cost, where source and target are the ids of the nodes at each end).
    – amball
    Sep 7 at 23:55

1 Answer 1


A more versatile, more performant approach:

  ori.id AS origin_id,
  dst.id AS destination_id,
  ST_Length(tr.line_string) * ABS(dst.frac - ori.frac) AS road_distance
  troad AS tr
      ST_LineLocatePoint(tr.line_string, o1.geom) AS frac
      torigin1 AS o1
      o1.gid = tr.gid
  ) AS ori
      ST_LineLocatePoint(tr.line_string, d1.geom) AS frac
      tdest1 AS d1
      d1.gid = tr.gid
  ) AS dst
  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.

  • 1
    Geozelot, you should write a PostGIS book, your answers are grand, thank you! Sep 6 at 15:01
  • @TimothyDalton that is a very nice thing to hear, much appreciated! You would so much not want to read a book I wrote, though...
    – geozelot
    Sep 6 at 18:31

Not the answer you're looking for? Browse other questions tagged or ask your own question.