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

enter image description here

What I'm doing wrong?


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

  • Perfect! thank you, I spend all day looking for this :) I just eliminate the DUMP funtion – Alicia Rijo Nov 29 '16 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.