1

I have a linenetwork lines and a lot of points points. The table lines has 42869 rows, the table points has 11264 rows. I am trying to snap each point to its closes line.

SELECT 
    p.id,
    ST_Distance(l.geom, p.geom) as distance,
    ST_ClosestPoint(l.geom, p.geom) as geom 
FROM
    lines AS l,
    points AS p;

This returns me selection with 482876416 rows, which is 42869 * 11264.

I can solve it with a DISTINCT ON

SELECT 
    DISTINCT(p.id) p.id,
    ST_Distance(l.geom, p.geom) as distance,
    ST_ClosestPoint(l.geom, p.geom) as geom 
FROM
    lines AS l,
    points AS p;

But I want to know: Why is it doing that? I would expect the result table to have the sname number of rows as my points table when not using a DISTINCT ON.

four-eyes
  • 3,378
  • 5
  • 33
  • 58
  • I closed this question as duplicate, and in favor, of https://gis.stackexchange.com/questions/436976/how-to-snap-points-to-line. – geozelot Jul 28 '22 at 09:54

0 Answers0