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

  • 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 '17 at 10:39
  • You should remove your edit and instead post it as an answer :) – Joseph Jul 10 '17 at 12:07
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>))
  • 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 '17 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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