4

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

Line intersecting 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?

Community
  • 1
Miro
  • 9,744
  • 8
  • 49
  • 87
  • 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 thisenter image description here 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 – Andre Luiz teixeira leite Nov 22 '19 at 14:36

1 Answers1

4

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

  • Layer -> Add Layer -> Add 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. enter image description here

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

Matt
  • 1,672
  • 16
  • 24
Pierma
  • 901
  • 4
  • 10
  • 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 '16 at 03: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 '16 at 02: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. – Pierma Jun 24 '16 at 02: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 '16 at 03: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). – Pierma Jun 24 '16 at 03:26
  • Thank you. I did that before - I replaced both r "FROM r AS b" and "FROM r AS a" by the part in WITH clause and it worked well. Though the performance of that query is very very slow. I was wondering if with "WITH" the performance is better... – Miro Jun 24 '16 at 03:32
  • With my small data test, both are instant. It is the final part of the query who is very long for big dataset. This part create the position indexes. What about use the python plugin ArMoraer create for you here :http://gis.stackexchange.com/questions/192048/is-there-a-way-to-assign-row-number-for-sorted-table-by-column-in-qgis/192052#192052 – Pierma Jun 24 '16 at 05:17