[postgis-users] return points where line intersects polys

[Puneet Kishor]

Given a line, I want to get the points at which it intersects a polygon dataset.

                 +--------------+
                 |              |
        +--------+              |
        |    a   |      b       |
    ====o========o==============o=======
    p0  |p1      |p2            |p3     pn
        +--------+              |
                 |              |
                 |              |
                 +--------------+
                 
In the figure above, I want to get the following

    poly_id point1  point2
    ------- ------  ------
    a       p1      p2
    b       p2      p3
    
    SELECT ST_AsText(ST_Intersection(ST_GeomFromText('LINESTRING(<p0>, <p2>)', 4326), the_geom))
    FROM table;
    
But I back N 'GEOMETRYCOLLECTION EMPTY' where N = number of rows in the 'table'. From what I understand, that indicates "the geometries do not share any space (are disjoint)". But, I know that is not correct. So, my query is formulated incorrectly. What am I doing wrong?


--
Puneet Kishor