Application Description
I am using PostGIS as a database for a PHP application for pilots. They can plan their flights there and see all the airspace information along the track.
I have airspace data stored as Polygons and the planned track is a LineString.
The planned track can look like this, the airspace is the LKD10 rectangle, starting at ground level, going up to 1000 feet above the ground:
I have implemented a vertical profile of planned track with the airspace:
Bug Description
Everything works fine until the track intersects itself. Then the vertical profile goes mad:
The airspace should be shown along the whole chart and only once.
Logic description
This is how the logic looks like:
- I select every airspace intersecting the linestring:
SELECT name, type, bound_lower, bound_lower_datum, bound_upper, bound_upper_datum
FROM airspace
WHERE ST_Intersects(location, ST_GeomFromText('LINESTRING(16.63089752197266 48.847773057644694, 16.571159362792972 48.87148983809234)', 4326)) AND bound_lower_datum NOT LIKE 'FL' AND type
NOT IN ('SECTOR', 'FIR')
ORDER BY type ASC, name ASC
- For each result I find the start and the end point of the intersection and locate it on the linestring:
SELECT
ST_LineLocatePoint(
track::geometry, ST_StartPoint((intersection).geom)
) as start,
ST_LineLocatePoint(
track::geometry, ST_EndPoint((intersection).geom)
) as end
FROM
(
SELECT track, ST_Dump(intersections) as intersection
FROM
(
SELECT
track::geometry as track,
ST_Intersection(
track,
(
SELECT location
FROM airspace
WHERE type='D' AND name='LKD10 DOLNI DUNAJOVICE')::geometry) as intersections
FROM ST_GeomFromText('LINESTRING(16.63089752197266 48.847773057644694, 16.571159362792972 48.87148983809234)', 4326) as track
) as a
) as b;
- Then I get the intersection position in percentage and can plot it in the JS canvas correctly.
Question
I figured out that this is caused by non-simple linestring when the ST_Dump(intersections)
returns more than one result for single intersection. Is there any way I can prevent this?