2

I have a lines layer (roads) and a points layer (accidents). I'm projecting the points on the lines with st_closestpoint so that I'm sure the accidents are located on the closest road line. I now want to associate the points to the lines based on their (overlapping) position. I do not want to use a buffer. The problem is that after I projected the points on the lines I, weirdly, cannot associate the resulting points (now located on the lines) with the lines themselves. I tried with st_intersect and st_crosses but with no success, as if the points and lines aren't actually sharing any coordinate.

What am I doing wrong?

How else could I classify the lines based on the number of points located on them?

2
  • 2
    The "Join attributes by location" or "Join attributes by nearest" processing algorithms should work fine for you, you can link the $id of the line feature or any other unique field from the line onto the points. than you can count each occurence of the same "id" in your point layer to know how many points are on your line Jul 6 at 13:02

1 Answer 1

7

Contrary to intuition, deriving a Point from a Line does not guarantee that it will be included in the vector that defines said Line - this is mostly due to floating point arithmetic and its inaccuracies.

You want to use a classic (K)NN search, with additional localization and aggregation, instead:

SELECT
  rd.id,
  COUNT(cs.frac) AS ac_count,
  ARRAY_AGG(cs.frac ORDER BY frac) AS ac_locs
FROM
  <roads> AS rd
  CROSS JOIN LATERAL (
    SELECT
      ST_LineLocatePoint(rd.geom, ac.geom) AS frac
    FROM
      <accidents> AS ac
    ORDER BY
      rd.geom <-> ac.geom
    LIMIT
      1
  ) AS cs
GROUP BY
  rd.id
;

where (added as examples)

  • ac_count simply counts the <accidents>.geom that are closest to each <road>.geom
  • ac_locs lists the fraction of line-length at which each of the found <accidents>.geom occurred; you could replicate geometries from those fractions by using ST_LineInterpolatePoint(ln.geom, cs.frac)

An index on <accidents>.geom makes this type of query highly performant.


You could run a simple

SELECT
  id,
  cnt,
  DENSE_RANK() OVER(ORDER BY ac_count DESC) AS rnk
FROM
  <above_query>
;

to categorize your roads based on accident count, with 1 being the 'most dangerous' road.


I recommend to further read on multiple topics concerning (K)NN queries, to understand LATERAL queries, distances and index usage.

Related:

0

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