# Assign order number in which line intersect polygons

Is there any tool (plugin) for QGIS to assign order (sequence) number of line intersecting through polygons?

At this moment I use a bit cumbersome solution which works but...

1. I use Qchainage plugin to generate dense points along the line with order
2. With spatial join (Vector / Data Management Tools / Join attributes by location) I assign values from points into polygons
3. I make sure all polygons have assigned some value (in all of them was point), if not I fill in manually missing values
4. I recalculate values so they are in sequence from 1 to n (n is number of polygons the line intersect) with interval 1

There is also possible solution using PostGIS Find all intersections of a LineString and a Polygon and the order in which it intersects it . Maybe it is possible to do something similar in QGIS without PostGIS?

• PIERMA , MATT OR MIRO. PLEASE HELP ME when i do st_startpoint, just like pierma query, it returns the column 'location' with 0 for all records. I tried with 'st_dumppoints' and the return looks like this![enter image description here](i.stack.imgur.com/0Ty11.png) the query : WITH r AS ( SELECT c.pgid AS pgid, geo_rio AS geometry /*:polygon:4674*/ , ST_LineLocatePoint(geo_rio,(ST_startpoint(geo_rio))) AS location FROM ( SELECT rio_teste.id AS lineid, poligono_teste.id as pgid, ST_Intersection(geo_rio, geo_teste) AS geometry FROM public.rio_teste, p Nov 22, 2019 at 14:36

It can be done with Python, but here is a solution using a virtual layer.

• In the "Query" field, use something like :

``````WITH r AS (
SELECT
c.pgid AS pgid,
pg.geometry AS geometry /*:polygon:3163*/ ,
Line_Locate_Point(line.geometry, StartPoint(c.geometry) )  AS location
FROM
(
SELECT line.id AS lineid, pg.id as pgid, ST_Intersection(line.geometry, pg.geometry) AS geometry FROM line, pg
) AS c
JOIN pg ON pg.id = c.pgid JOIN line ON line.id = c.lineid
ORDER BY
Line_Locate_Point(line.geometry, StartPoint(c.geometry) )
)

SELECT * ,
(SELECT count(*) FROM r AS b WHERE a.location >= b.location) AS position
FROM r AS a
``````

Replace (6-7 times for the 2 first):

• "pg" with the name of your polygons layer
• "line" with the name of your line layer
• ":polygon:3163" : the number is the SRID you are using. Replace with yours.

The result will be a new layer with your polygons and 2 new fields :

• location : the position of the first point of the intersection of the polygon and the line (grey points on the image) along the line (between 0.0 and 1.0).
• position : the order number of the polygon you are looking for.

Hope this help. I can try to clarify the query further if needed.

• Thank you, I though virtual layer might be at help here. Seems switching from long term QGIS 2.8 to 2.14 is inevitable :)
– Miro
Jun 21, 2016 at 3:51
• I can't make WITH clause working at all in Create Virtual Layer Query. Always writes syntax error near "WITH". Any hint?
– Miro
Jun 24, 2016 at 2:25
• WITH should works. The debugging information on error messages are not very helpfull because the position of the error is not given. He tell you "near WIDH" because it's the first line. The error can be everywhere inside the with statement. Post your modified query and I will look. Jun 24, 2016 at 2:59
• I can't make it work even with most simple statement like "WITH test AS (SELECT * FROM pg) SELECT * FROM test". The rest of query works as expected.
– Miro
Jun 24, 2016 at 3:12
• It works fine for me. You can rewrite the query without WITH (WITH is just for clarification) : in "FROM r AS b" and in "FROM r AS a", replace "r" by the code inside the WITH statment (with parenthesis). Jun 24, 2016 at 3:26