Best way to run ST_intersects on features inside one table?

Question

I have a table of LineString features and I wish to identify which lines intersect. ST_Intersects(geom1, geom2) needs two geometries from two different tables. Right now i am creating two different references back to the same table and it just doesn't seem like the right approach.

I am currently using the following bit of code, and I am curious if there is some better way of accomplishing this. Surely running an intersect on features within one table must be a common task.

    SELECT a.link_id as a_link_id,
           b.link_id as b_link_id,
           st_intersects(a.geom, b.geom)
    INTO results_table 
    FROM table_one a, table_one b
    WHERE  a.link_id != b.link_id;

PostGIS 2.4.0 PG 9.6.5

Answer 1

Your approach is ok. The only problem with this is that it will return duplicate records. e.g if two lines are intersecting with IDs 10 and 11 respectively. There will be two rows for each ID in the result, even the lines are intersecting only once. You can cater this with > or < operator in place of !=. And intersect condition comes in where i guess

SELECT a.link_id as a_link_id,
           b.link_id as b_link_id
    INTO results_table 
    FROM table_one a, table_one b
    WHERE  a.link_id < b.link_id AND st_intersects(a.geom, b.geom)