Wednesday 6 April 2016

postgresql - Create a point in intersection of 2 lines postgis


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?




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...