I have a table of 100k+ non-overlapping POLYGON features, and a table of 50k+ simple LINESTRING features (each line has only 2 vertices). The below is a common arrangement of these features on a map, showing 1 line (red, going left to right) and 4 individual polygons (blue):
Imagine the above scenario, but in thousands of different locations. The lines and polygons do vary in length/size across the map (the above is just a simplified example).
I can split the line features by the polygon features by using ST_INTERSECTION(lines, polygons), so that the above example would have the line split into 4 segments.
After successfully splitting the line into 4, I want to obtain the length of only the second segment (this is the important measurement for my particular project).
I can ST_LENGTH the result of question 1, but how can I extract only the second measurement?
This is especially tricky as the ST_INTERSECTION does't output the line segments in order (left to right here).
Perhaps I could somehow add a column containing an identifier for each segment, with 'a' being the first, 'b' being the second (and the one I want to measure!), then 'c', 'd' and so on... Then:
SELECT ST_LENGTH FROM line_segments WHERE label = 'b'. But I need to get the segments in order somehow first...
If you want to test, here's the geometries for the example above.
Polygons (the ids are random in the real dataset):
SELECT 'ogb1' as id, ST_GeomFromText('POLYGON((2 2, 2 4, 4 4, 4 2, 2 2))') as geom UNION SELECT 'ogb2' as id, ST_GeomFromText('POLYGON((4 2, 4 4, 5 4, 5 2, 4 2))') as geom UNION SELECT 'ogb3' as id, ST_GeomFromText('POLYGON((5 2, 5 4, 5.5 4, 5.5 2, 5 2))') as geom UNION SELECT 'ogb4' as id, ST_GeomFromText('POLYGON((5.5 2, 5.5 4, 7 4, 7 2, 5.5 2))') as geom
and the line:
SELECT 1234 as id, ST_GeomFromText('LINESTRING(3 3, 6 3)') as geom