I would like to find out all intersections of a
LineString (River) through
Polygons (Municipalities). I would like to list the order of municipalities which the river crosses, using linear referencing. I already have found which municipalities are crossed by the river using
st_intersects, but this is will not provide me the order and how many times municipalities are crossed. I am not expecting a full solution, rather a modus operandi that I will implement by my self.
PostGIS 2 extension with
WITH A AS ( SELECT curves.gid AS cgid, shapes.gid AS sgid, curves.geom AS cgeom, shapes.geom AS sgeom, st_intersection(curves.geom, shapes.geom) AS mgeom FROM curves, shapes), B AS ( SELECT cgid, sgid, cgeom, sgeom, (st_dump(mgeom)).geom AS geom FROM A), C AS ( SELECT row_number() OVER ()::INTEGER as gid, cgid, sgid, cgeom, sgeom, geom, st_startPoint(geom) AS p0, st_endPoint(geom) AS p1 FROM B), D AS ( SELECT C.*, st_Line_Locate_Point(cgeom, p0) AS r0, st_Line_Locate_Point(cgeom, p1) AS r1 FROM C ORDER BY r0) SELECT * FROM D
The query above find entry points and may be used in Linear Referencing in order to follow the original LineString through municipalities. I have to discriminate many use case and I am a little lost. This is my very first queries in GIS. Table
Polygons that have no intersections and
LineString that pass through those
The expected output is, for each
LineString, the list of
Polygons that it crosses (order and redondance matters).