RE: Help creating "corridors"

Luca,

I downloaded the csv file from https://ourairports.com/data/ and 
constructed the following sql script.

Your routes ....

10 LOWI - Mieminger Plateau - Fernpass - Reutte - Pfronten - EDMK 4700

... have a start and end airport with "waypoints" in between that look 
like topographic features or town names.

You need to get a latitude/longitude for these intermediate points from 
another dataset - you mentioned OpenStreetMap - and then import those as 
well.

*The work you have to do is to create "routes" from these two input 
sources.*

If you can get them in you can generate the routes as follows....

-- *************************************************

-- https://ourairports.com/data/

drop table if exists public.airports;

create table public.airports (
     id integer,
     ident varchar(100),
     type  varchar(100),
     name  varchar(1000),
     latitude_deg float,
     longitude_deg float,
     elevation_ft float,
     continent varchar(10),
     iso_country varchar(10),
     iso_region  varchar(10),
     municipality varchar(1000),
     scheduled_service varchar(1000),
     gps_code varchar(100),
     iata_code varchar(100),
     local_code varchar(100),
     home_link varchar(1000),
     wikipedia_link varchar(500),
     keywords varchar(1000)
);

drop table if exists public.topography;

create table public.topography (
     id integer,
     name varchar(1000),
     latitude_deg float,
     longitude_deg float
);
-- Load intermediate waypoints
-- eg ....
insert into public.topography (id,name,latitude_deg,longitude_deg) values
(1,'Mieminger Plateau',null,null),
(2,'Fernpass',null,null),
(3,'Reutte',null,null),
(4,'Pfronten',null,null);

-- \COPY public.airports FROM '/Users/Simon/Downloads/airports.csv' WITH 
(DELIMITER E',', FORMAT CSV, NULL '', ENCODING 'UTF8')

-- Now the suggested routes includes features as waypoints not in the 
airports.csv file
-- 10 LOWI - Mieminger Plateau - Fernpass - Reutte - Pfronten - EDMK 4700
-- These need to come from another dataset eg OpenStreetMap
-- And need to be integrated into the corridor_points...
--
with required_routes as (
select route_no,
        row_number() over (order by 1) as point_no,
        parts as waypoint
   from (select 10 as route_no,unnest(string_to_array('10 LOWI - 
Mieminger Plateau - Fernpass - Reutte - Pfronten - EDMK 4700', ' ')) AS 
parts
         union all
         select 11 as route_no,unnest(string_to_array('11 LOWI - Inntal 
- Landeck - Arlberg - Feldkirch - LOIH 7000', ' ')) AS parts
         union all
         select 12 as route_no, unnest(string_to_array('12 LOWI - 
Autobahn A12 - LOIK 2600', ' ')) AS parts
        ) as f
  where parts <> '-' and parts !~ '^\d+$'
)
, corridor_points as (
     select route_no,
            point_no,
            ST_MakePoint(latitude_deg,longitude_deg,4283)::geography as 
point
       from required_routes as rr
            inner join public.airports as a on a.ident = rr.waypoint
     union all
     select route_no,
            id,
            ST_MakePoint(latitude_deg,longitude_deg,4283)::geography as 
point
       from required_routes as rr
            inner join public.topography as a on a.name = rr.waypoint
      --where a.latitude_deg is not null
)
SELECT route_no, ST_AsGeoJSON( ST_MakeLine(point::geometry ORDER BY 
route_no,point_no) )  AS geo_json
   FROM corridor_points
GROUP BY route_no;

-- *************************************************

On 7/02/2025 8:32 am, SPDBA wrote:
>
>
> 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia
> (P) 03 62 396 397
> (M) 0418 396 391
> (W) <http://www.spdba.com.au>www.spdba.com.au <http://www.spdba.com.au>
> On 7 Feb 2025, at 05:38, Regina Obe <[email protected]> wrote:
>
>     I don’t think OpenStreetMap data is going to help you with
>     constructing flight corridors.  As I recall these are 3
>     dimensional areal paths.
>
>     You ultimately need 3 dimensional data – data that has altitude
>     restrictions, and the city point in openstreetmap is pretty
>     useless as I think that might just give you the centroid of a city
>      which does not necessarily align with a corridor path. 
>     OpenStreetMap is more focused on visible things on the ground.
>
>     I was thinking sources like https://openflights.org  or
>     https://opensky-network.org/ might have it but not seeing corridor
>     data there
>
>     But if you do have reliable points, to Simon’s point, here is a
>     pseudo coded example
>
>     CREATE TABLE corridor_points(name, sort_order, geom
>     geometry(Point, 4326) )
>
>     INSERT INTO corridor_points(name, sort_order, geom)
>
>     SELECT name, sort_order, ST_Point(longitude, latitude, 4326)
>
>     FROM your_table_source;
>
>     SELECT name, ST_AsGeoJSON( ST_MakeLine(geom ORDER BY sort_order) )
>      AS geo_json
>
>     FROM corridor_points
>
>     GROUP BY name;
>
>     To create a true corridor you probably should buffer the line
>     string too  and add an altitude  something like below which would
>     expand the linestring to 500 meter area and then maybe you add a
>     upper altitude / lower altitude as separate columns for
>     restrictions, but I think even the altitude restrictions change
>     across the corridor so even this might not be enough to assume
>     it’s constant across the corridor
>
>     SELECT name, ST_AsGeoJSON( ST_Buffer(ST_MakeLine(geom ORDER BY
>     sort_order)::geography, 500)   )  AS geo_json
>
>     FROM corridor_points
>
>     GROUP BY name;
>
>     *From:*SPDBA <[email protected]>
>     *Sent:* Thursday, February 6, 2025 3:50 AM
>     *To:* Luca Bertoncello <[email protected]>
>     *Cc:* [email protected]
>     *Subject:* Re: Help creating "corridors"
>
>     Not familiar with these tables.
>
>     Someone will answer with an actual solution, but it looks like you
>     need to query the base osm_point to return a set of points in the
>     order of your flight lines. Start with that, then aggregate the
>     resulting points using the reference I sent.
>
>     Simon
>
>     39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia
>
>     (P) 03 62 396 397
>
>     (M) 0418 396 391
>
>     (W) www.spdba.com.au <http://www.spdba.com.au>
>
>     On 6 Feb 2025, at 19:37, Luca Bertoncello <[email protected]>
>     wrote:
>
>         Am 06.02.2025 09:16, schrieb SPDBA:
>
>               Can you show us the structure of a table containing the codes and the
>               latitude longitude columns? There are ways to take an ordered list of
>               points in a table and generate linestrings as I assume this is what
>               you want to do.
>
>         I'll use planet_osm_point and planet_osm_polygon from OpenStreetMaps.
>         See
>         https://wiki.openstreetmap.org/wiki/OpenStreetBrowser/Database_Structure
>
>         For the airfield I think, I'll just search for the city in the both
>         tables. It must not be soooo precise...
>
>         Thanks
>         Luca Bertoncello
>         ([email protected])
>
-- 
Simon Greener
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia
(m) +61 418 396 391
(w)www.spdba.com.au
(m)[email protected]