| 1 | create view FlightAirspaceConflicts (FlightID, FlightNumber, Departure, Arrival, Origin, Destination,
|
|---|
| 2 | Distance, AirspaceID, AirspaceName, AirspaceType, ConflictSegment, ConflictLengthKm) as
|
|---|
| 3 | select fr.FlightID, fr.FlightNumber, fr.Departure, fr.Arrival, fr.Origin, fr.Destination,
|
|---|
| 4 | fr.Distance, ra.ID,ra.Name,at.Name,
|
|---|
| 5 | ST_Intersection(fr.Route, ra.Boundary::geometry),
|
|---|
| 6 | ST_Length(ST_Intersection(fr.Route, ra.Boundary::geometry)::geography) / 1000.0
|
|---|
| 7 | from FlightRoutes as fr
|
|---|
| 8 | join RestrictedAirspace as ra on ST_Intersects(fr.Route, ra.Boundary::geometry)
|
|---|
| 9 | join AirspaceType as at on at.ID = ra.TypeID
|
|---|
| 10 | where (ra.ActiveFrom is null or fr.Departure >= ra.ActiveFrom) and (ra.ActiveTo is null or fr.Arrival <= ra.ActiveTo); |
|---|
