2

I am having a table lines being MULTILINESTRING (2 Dimensions) and a table points being MULTIPOINT (3 Dimensions). The two are in the same CRS. Now, I am trying to snap all the points to the closest line.

Points not on line

I run this query

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;

Which runs through and returns a table with the same number of rows as the points table. However, upon inspecting the result, I see that all the points (11246 in total) are snapped to one line, overlaying each other.

Points snapped to line after query

Why is that?

four-eyes
  • 3,378
  • 5
  • 33
  • 58

1 Answers1

5

This question is directly related to your earlier one, making it a duplicate - since this one is also addressing an unexpected outcome further downstream the core issue of both posts, I'll answer here.

The core issue with your attempt is the implicit CROSS JOIN, which creates a cartesian product between both tables:

[...]
FROM
  <table_a>,    -- implicit CROSS JOIN using , (comma) notation
  <table_b>
[...]

which translates to:

Join each row in <table_a> to each row of <table_b>.

and returns COUNT(<table_a>) * COUNT(<table_b>) rows in its result set.

Now, the attempted DISTINCT ON (p.id) successfully reduces this cartesian product of both tables to the distinct p.id values of points AS p, but since there are COUNT(lines) rows for every p.id in the result set, PostgreSQL has to choose one of them to satisfy the DISTINCT command - and it does that by picking the first row that is given by the inherent order of the lines table, per p.id; in your case this is the same lines.geom for every distinct p.id.

The requirement for a (k) Nearest Neighbor search can be expressed as

For every row in <table_a> find the k closest row(s) in <table_b>.

which is logically equivalent to a procedural FOR LOOP - and which can be realized with a LATERAL join within PostgreSQL:

SELECT
  ST_ClosestPoint(n.geom, p.geom) AS geom
  ST_Distance(n.geom, p.geom) AS dist
FROM
  points AS p
  CROSS JOIN LATERAL (
    SELECT
      l.geom
    FROM
      lines AS l
    ORDER BY
      l.geom <-> p.geom
    LIMIT
      1
  ) AS n
;

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

Related:

geozelot
  • 30,050
  • 4
  • 32
  • 56
  • Thanks a lot. I thought these might be different questions, but I understand now, that they are different. Is there a way to "see" if a PostGIS function uses a CROSS JOIN? Looking at the documentation, it is not clear to me that PostGIS does that. – four-eyes Jul 28 '22 at 10:16
  • @Stophface oh that is nothing a function does, see the docs on JOINs. Let me add: JOIN behaviors and their set-theoretic differences are at the very heart of RDBs - and I urge you to do yourself a favor and make yourself familiar with the core concepts of relational data models, to being able to properly operate PostgreSQL/PostGIS. Don't forget to accept an answer if it solved your issue ,) – geozelot Jul 28 '22 at 10:24
  • Sorry, I upvoted but forgot to accept! I understand JOINs, but looking at the documentation I did not expect to need a JOIN. I assumed that this is done internally, returning me the closes point on the line. Thanks for your explanation! – four-eyes Jul 28 '22 at 10:27
  • @Stophface ah, okay. No, the set-theoretic dependencies between relations are usually far beyond the scope and responsibilities of a regular, functional API like the PostGIS function family. The idea here is to limit the functionality to result set manipulation, and index access for filter expressions, to support the highest customization, tuning and extensibility of query expressions. – geozelot Jul 28 '22 at 10:36