Create a point in intersection of 2 lines postgis

Question

As an ArcGIS user this is simple but in PostGIS has became a nightmare. But well I guess this is typical in beginners like me.

I have 2 layers (multilinestring) called roads and waterways, I'm trying to do a intersection between them that will result in points, but so far I had problems with geometry, this is what I have done:

SELECT r.gid as rgid, r.name as rname, r.type as rtype, w.gid as wgid, w.name as wname, w.type as wtype, ST_DUMP(ST_INTERSECTION(w.geom, r.geom)) as rw_geom, 
COUNT(DISTINCT r.gid)                   
FROM waterways w, roads r 
WHERE ST_INTERSECTS(w.geom, r.geom) AND w.gid < r.gid 
GROUP BY r.gid,w.gid,ST_INTERSECTION(w.geom, r.geom);

The first thing was renaming the attributes, because it shows error for duplicity, then I break the multiline and intersect them, after uses functions to avoid duplicity (according to what I have read in manuals and other questions) and group (in this point if I don't use r.gid and w.gid it won't work)

After all this I get this as result

What I'm doing wrong?

Answer 1

the ST_CollectionExtract function lets you specify what type of geometry you want in your output. So for a point, 1 is chosen. this should be your intersection function on your first line

ST_CollectionExtract(ST_Intersection(w.geom, r.geom), 1) as rw_geom

and you do not need the st_intersection in the group by