How to compute a line intersection point in a street layer in PostGIS 1.4?

I have a street layer downloaded from OSM. I need to calculate intersection point as we do in QGIS from vector menu -> Analysis tool -> Line intersections.

I am new to PostGIS/PostgreSQL, I managed to download and install it spending whole day on Ubuntu machine. Using shp2psql-gui I imported data with pgAdmin3.

I know how to do in QGIS, but tell me how to do the same in PostGIS. I have heard some st_intersects etc. but it can't help. I want to return something like this.

Below are snapshot for more clearer picture of my issue. Blue indicates street layer.

name_1 | name_2 | laitude | longitude
xyz st | abc st | #value| #value
lmn st | pqr st | #value| #value
xyz st | pqr st | #value| #value
pqr st | abc st | #value| #value
lmn st | xyz st | #value| #value
lmn st | abc st | #value| #value

Answer

You are looking for ST_Intersection, not ST_Intersects.

http://postgis.refractions.net/docs/ST_Intersection.html

also:

http://postgis.net/docs/ST_Intersection.html

If you have lines that intersect at multiple locations (sometimes it happens), you may need to look into ST_Dump to get all of those intersection points.

The query might look like this (not tested, and you should include IDs as well as street names in your resulting tables):

SELECT name_1, name_2, ST_X(points) AS latitude, ST_Y(points) AS longitude
FROM
(
  SELECT name_1, name_2, (ST_Dump(possiblePoints)).geom AS points
  FROM

  (
    SELECT A.StreetName AS name_1, B.StreetName AS name_2, ST_Intersection(A.geom, B.geom) AS possiblePoints
    FROM   LinesTable A
           INNER JOIN
           LinesTable B
           ON ST_Intersects(A.geom,B.geom)
              AND
              A.ID <> B.ID
  ) AS X
) AS XX