1

I use a code that numbering (in a row new_ID) each polygon that is crossed by a polyline. For every polyline entity, the numbering is reset from 1.

SELECT 
   geometry, 
   ROW_NUMBER() over (partition by line_ID order by line_pcent) as new_ID 
FROM (
    SELECT pp.*,
       st_maxX(ll.geometry) line_ID,
       ST_Line_Locate_Point(ll.geometry, st_startpoint(st_intersection(ll.geometry, pp.geometry))) as line_pcent
    FROM pp
    JOIN ll on st_intersects(ll.geometry, pp.geometry)
)

It works but I would like to add another row polyline_row in which I need the numbering of polylines.

  • When the first polyline cross some polygons, these polygons have 1 in their polyline_row
  • When the second polyline cross some polygons, these polygons have 2 in their polyline_row
  • etc.

1 Answer 1

1

I found by myself :

SELECT
  geometry,
  ROW_NUMBER() OVER (PARTITION BY line_ID ORDER BY line_pcent) AS new_ID,
  DENSE_RANK() OVER (ORDER BY line_ID) AS polyline_row
FROM (
  SELECT
    pp.*,
    st_maxX(ll.geometry) line_ID,
    ST_Line_Locate_Point(ll.geometry, st_startpoint(st_intersection(ll.geometry, pp.geometry))) AS line_pcent
  FROM pp
  JOIN ll ON st_intersects(ll.geometry, pp.geometry)
) AS subquery;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.