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.
I use PostGIS
2 extension with PostgreSQL
9.3
MWE:
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 shapes
contains Polygons
that have no intersections and curves
contains LineString
that pass through those Polygons
.
The expected output is, for each LineString
, the list of Polygons
that it crosses (order and redondance matters).
Best Answer
Assuming every row in Rivers table is a linestring with an entire river. Here's a query that will get you how many municipalities are crossed, the subquery t , will get you all the municipalities that each river crosses but yes they will not be in any guaranteed order.
here's a query that will get you all the points from the linestring that intersected the polygon in the correct order.
Unfortunately there is no way to know how many times a river crossed a municipality without some code, but with this query well get you very close. As result you will get something like this:
In this example the river crossed 2 times the same municipality, and the way to know it is the mun_id column pattern (It was on mun_id 1 and then changed to 2, and then back to 1).