1

I just started to work with POSTGIS and I am currently working with signalised intersections. I have the center of the intersection as a point and the connecting roads as linestrings. I need to find the point, along the linestring, that is X meters from the center. I think the problem becomes moving the point along a line.

I used ST_LineInterpolatePoints to do that, and it works fine for the segments that start on the intersection. For the segments that end at the intersection, it doesn't work as intended and there's no way for me to know beforehand which ones start and end at the intersection.

This image illustrates the problem.

2 Answers 2

2

Assuming:

  • An existing point layer with IDs
  • An existing line layer with IDs

We can create a buffer at a set distance around the points and select where this intersects the lines.

--POINTS ON LINE x from intersection 
DROP TABLE IF EXISTS public.point_on_line_buff_points CASCADE;

CREATE TABLE public.point_on_line_buff_points AS
SELECT DISTINCT ST_Intersection(l.geom,ST_BOUNDARY(ST_BUFFER(p.geom,10))) geom, p.id point_id, l.id line_id
FROM point_on_line_intersects p, point_on_line_lines l
WHERE 
ST_GeometryType(ST_Intersection(l.geom,ST_BOUNDARY(ST_BUFFER(p.geom,10)))) = 'ST_Point';
--AND p.id=2 AND l.id=14 --specify point and/or line ids here if desired

--give the intersection points a unique id
ALTER TABLE public.point_on_line_buff_points ADD COLUMN id SERIAL PRIMARY KEY;

-Use ST_BOUNDARY to convert the buffer to a line
-Set the desired distance of the buffer (10 in this case 'ST_BUFFER(p.geom,10')
-ignore all non point intersects with 'ST_GeometryType = ST_Point'

If you want to restrict the output to a certain line or a certain intersection point we can do that by adding in extra WHERE statements. i.e. WHERE p.id=2 AND l.id=14

Example Output Image

3
  • I can't thank you enough, this is just perfect. Not only it's exactly what I need, but is damn elegant. The only questions I have it, how can I use meters? I can see that the 10 here is a different unit. Feb 2 at 0:50
  • I think I found the answer for that, I was passing a geometry to the ST_BUFFER; if I pass a geography, then the input is interpreted as meters. It seems so, are you confirm it? Feb 2 at 0:55
  • 1
    It all depends on what EPSG your data is in. Best to transform your data using ST_Transform to an EPSG who's unit is meters, i.e. EPSG:28355 in my case. If you found the answer useful and it solves your case please upvote and accept.
    – Cushen
    Feb 2 at 3:20
0

A simple, robust and fast solution using spheroidal distances (in meter) and ST_LineInterpolatePoint - assuming all edges in the given network topology are correctly noded at intersections:

SELECT
  ln.<id>,
  ln.<...>,
  ST_LineInterpolatePoint(
    ln.geom::GEOGRAPHY,
    -- from start of line
    <threshold_in_meter> / ST_Length(ln.geom::GEOGRAPHY)
  )::GEOMETRY(POINT, 4326) AS geom
UNION ALL
SELECT
  ln.<id>,
  ln.<...>,
  ST_LineInterpolatePoint(
    ln.geom::GEOGRAPHY,
    -- from end of line
    1.0 - (<threshold_in_meter> / ST_Length(ln.geom::GEOGRAPHY))
  )::GEOMETRY(POINT, 4326) AS geom
:
2
  • That's what I was using at first, and it worked fine for the segments that started at the center of the intersection. For the segments that ended at the center, it didn't produce the desired result. I will try your answer. Thanks for contributing! Feb 2 at 10:53
  • This query above is a UNION of two query results - one for the interpolation at the beginning of each line and one for their ends. The operations are performed using a highly accurate, spheroidal mathematical definition of the lines, and the respective measures are in meter. This is efficient overall, even for very large datasets.
    – geozelot
    Feb 2 at 11:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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