Identify points contained inside a linestring

Question

This question is, I think, very simple but I can't resolve it by myself.

I have a table 'cable' like this :

CREATE TABLE cable (
  id uuid primary_key,
  the_geom geometry(LineString, 2154)
);

And another table let's call it partpoint

CREATE TABLE partpoint (
  id uuid primary key,
  the_geom geometry(Point, 2154)
);

I want to know if, for a specific partpoint, this partpoint is part of linestring in the cable table. The first request was :

SELECT id 
FROM cable 
WHERE ST_CONTAINS(the_geom, 
                  (SELECT the_geom 
                   FROM partpoint 
                   WHERE id=<anUuid>))

But this does not work :-( Do I need to loop over all the table ? (with a for loop).

Answer 1

Find it ! I didn't know at that time that you can join spatially on a table. So the resulting query is :

 SELECT id 
 FROM partpoint pp 
 JOIN cable c ON (ST_contains(the_geom, pp.the_geom) 
 WHERE ST_CONTAINS(c.the_geom, 
                  (SELECT the_geom 
                   FROM partpoint 
                   WHERE id=<anUuid>))