0

I've used ST_Intersects and ST_Crosses but between two different tables. I have a table of streets and intersections want to know them. For example select all streets that cross the Street A. Can anyone give me an example?

Edited for karpfen**

This is my table of streets, each street segment is a geometry table. The image of the QGIS layer can better understand, analyze and think it would be appropriate to use ST_Touches and select geometry that touches the other at its end. But how do it in the same table?? I can not do a join.

enter image description here

1
SELECT
  a.*,
  b.*
FROM table1 AS a, 
 (SELECT * from table1) AS b 
WHERE ST_Crosses(a.way, b.way)
  • You know how to do this in Oracle SQL I tried and the error [Err] ORA-00920: invalid relational operator – csf May 27 '14 at 20:24
0

You need to perform a spatial join on the two tables. Try something like

SELECT *
FROM table1
JOIN table2 ON (st_crosses(table1.geometry,table2.geometry))

EDIT:

As the solution above only applies to geometries in multiple tables, here is another one:

You can use PostGIS topology tables and have all intersections stored as nodes in it. Here is a good tutorial for that (the tutorial aims at cleaning up road networks, but it'll serve your purpose as well though).

  • Sorry, I may not have been clear in my question. But I have only one table is a table of streets and wonder intersections of streets. I edited the question. – csf May 19 '14 at 16:45

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.