Help creating "corridors"
Simon SPDBA Greener
simon at spdba.com.au
Thu Feb 6 14:08:04 PST 2025
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 <lr at pcorp.us> 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 <simon at spdba.com.au>
> *Sent:* Thursday, February 6, 2025 3:50 AM
> *To:* Luca Bertoncello <lucabert at lucabert.de>
> *Cc:* postgis-users at lists.osgeo.org
> *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 <lucabert at lucabert.de>
> 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
> (lucabert at lucabert.de)
>
--
Simon Greener
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia
(m) +61 418 396 391
(w)www.spdba.com.au
(m)simon at spdba.com.au
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20250207/3e989588/attachment.htm>
More information about the postgis-users
mailing list