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?