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:
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.
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.
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?