How can I find features that do not touch any other feature? I have about 4000 pipeline elements and I want to know which of them are separated from the network. I tried with:

SELECT h1.gid FROM pipelines h1, pipelines h2 WHERE ST_Disjoint(h1.geom, h2.geom)

but I get too many results.

  • 1
    What do you mean by "too much results"? Are you getting incorrect results? Or is the list just too long to handle? If the latter, you can always use the LIMIT clause.
    – L_Holcombe
    Commented Feb 1, 2013 at 5:38
  • my query compares each geom to every of other geoms so I've recived about 17milon results. With query: SELECT DISTINCT h1.gid FROM cjevovodi h1, cjevovodi h2 WHERE ST_Disjoint(h1.geom, h2.geom) I've got 4137 results and that is number of pipeline elements
    – hapa
    Commented Feb 2, 2013 at 8:57
  • This should be as simple as a "<> intersect" command. How is your data structured? Where are the nodes in the polylines / polygons? It will depend on whether all the intersections of the pipelines, and their associated node, break neatly either side of the intersection.
    – Andy TIce
    Commented Feb 2, 2013 at 11:42

4 Answers 4

SELECT h1.gid FROM pipelines h1 
LEFT JOIN pipelines h2 ON ST_Intersects(h1.geom, h2.geom) 
WHERE h2.id is null;
SELECT * FROM pipelines h1
                  WHERE h1.gid!=h2.gid AND ST_INTERSECTS(h1.geom, h2.geom)

I got an idea:

SELECT DISTINCT h1.gid, h1.geom
FROM pipelines h1, pipelines h2
WHERE st_dwithin(h1.geom, h2.geom, 0.05) AND st_disjoint(h1.geom, h2.geom)
ORDER BY h1.gid

that query finds me all pipelines that are close to each other (Within 5cm) and do not touch each other (Disjoint).
This is pretty primitive query but i think that I got what I want


I think that select tries to compare all geoms to all geoms

SELECT count(p.id) from pipelines as p , pipeline as p2 
WHERE ST_TOUCHES(p.the_geom, p2.the_grom) AND p.id != p2.id
GROUP BY p.id 

where count is 0 , and this assumes that your data doesn't have overshoots or accidental intersections.For network topology it would be better if you compare start and endpoints and assume that if geoms touch from other places , then there is error.

  • I've get 0 rows as result
    – hapa
    Commented Feb 2, 2013 at 8:28

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.