Classifying lines based on number of points located on them using PostGIS [closed]

Question

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?

Answer 1

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:

• Finding nearest neighbor for each point in the same table
• PostGIS: ST_ClosestPoint join error
• Optimizing my script; taking too long; distance between many objects PostGIS
• Speedup function to calculate distance from centroid of building to river segment in PostgreSQL