4

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).

2
  • 1
    Show a little of each of your data points? Edit your question to add the results of SELECT id, AsEWKT(the_geom) FROM cable LIMIT 5 and SELECT id, AsEWKT(the_geom) FROM partpoint LIMIT 5. At a guess, you need to handle real number tolerances, but its hard to say without seeing the data.
    – BradHards
    Jul 10, 2017 at 10:39
  • You should remove your edit and instead post it as an answer :)
    – Joseph
    Jul 10, 2017 at 12:07

1 Answer 1

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>))
1
  • Your WHERE clause is not necessary. You can just add this: WHERE pp.id=<anUuid> OR JOIN (SELECT the_geom FROM partpoint WHERE id=<anUuid>) ON [...]
    – pLumo
    Jul 10, 2017 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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