<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Luca,</p>
<p>I downloaded the csv file from <a class="moz-txt-link-freetext" href="https://ourairports.com/data/">https://ourairports.com/data/</a> and
constructed the following sql script.</p>
<p>Your routes ....<br>
</p>
<p>10 LOWI - Mieminger Plateau - Fernpass - Reutte - Pfronten - EDMK
4700</p>
<p>... have a start and end airport with "waypoints" in between that
look like topographic features or town names.</p>
<p>You need to get a latitude/longitude for these intermediate
points from another dataset - you mentioned OpenStreetMap - and
then import those as well.</p>
<p><b>The work you have to do is to create "routes" from these two
input sources.</b></p>
<p>If you can get them in you can generate the routes as follows....<br>
</p>
<p>-- ************************************************* <br>
</p>
-- <a class="moz-txt-link-freetext" href="https://ourairports.com/data/">https://ourairports.com/data/</a><br>
<br>
drop table if exists public.airports;<br>
<br>
create table public.airports (<br>
id integer,<br>
ident varchar(100),<br>
type varchar(100),<br>
name varchar(1000),<br>
latitude_deg float,<br>
longitude_deg float,<br>
elevation_ft float,<br>
continent varchar(10),<br>
iso_country varchar(10),<br>
iso_region varchar(10),<br>
municipality varchar(1000),<br>
scheduled_service varchar(1000),<br>
gps_code varchar(100),<br>
iata_code varchar(100),<br>
local_code varchar(100),<br>
home_link varchar(1000),<br>
wikipedia_link varchar(500),<br>
keywords varchar(1000)<br>
);<br>
<br>
drop table if exists public.topography;<br>
<br>
create table public.topography (<br>
id integer,<br>
name varchar(1000),<br>
latitude_deg float,<br>
longitude_deg float<br>
);<br>
-- Load intermediate waypoints<br>
-- eg ....<br>
insert into public.topography (id,name,latitude_deg,longitude_deg)
values<br>
(1,'Mieminger Plateau',null,null),<br>
(2,'Fernpass',null,null),<br>
(3,'Reutte',null,null),<br>
(4,'Pfronten',null,null);<br>
<br>
-- \COPY public.airports FROM '/Users/Simon/Downloads/airports.csv'
WITH (DELIMITER E',', FORMAT CSV, NULL '', ENCODING 'UTF8')<br>
<br>
-- Now the suggested routes includes features as waypoints not in
the airports.csv file<br>
-- 10 LOWI - Mieminger Plateau - Fernpass - Reutte - Pfronten - EDMK
4700<br>
-- These need to come from another dataset eg OpenStreetMap<br>
-- And need to be integrated into the corridor_points...<br>
--<br>
with required_routes as (<br>
select route_no,<br>
row_number() over (order by 1) as point_no,<br>
parts as waypoint<br>
from (select 10 as route_no,unnest(string_to_array('10 LOWI -
Mieminger Plateau - Fernpass - Reutte - Pfronten - EDMK 4700', ' '))
AS parts <br>
union all<br>
select 11 as route_no,unnest(string_to_array('11 LOWI -
Inntal - Landeck - Arlberg - Feldkirch - LOIH 7000', ' ')) AS parts
<br>
union all<br>
select 12 as route_no, unnest(string_to_array('12 LOWI -
Autobahn A12 - LOIK 2600', ' ')) AS parts <br>
) as f<br>
where parts <> '-' and parts !~ '^\d+$'<br>
)<br>
, corridor_points as (<br>
select route_no,<br>
point_no,<br>
ST_MakePoint(latitude_deg,longitude_deg,4283)::geography
as point<br>
from required_routes as rr<br>
inner join public.airports as a on a.ident = rr.waypoint<br>
union all<br>
select route_no,<br>
id,<br>
ST_MakePoint(latitude_deg,longitude_deg,4283)::geography
as point<br>
from required_routes as rr<br>
inner join public.topography as a on a.name = rr.waypoint<br>
--where a.latitude_deg is not null<br>
)<br>
SELECT route_no, ST_AsGeoJSON( ST_MakeLine(point::geometry ORDER BY
route_no,point_no) ) AS geo_json<br>
FROM corridor_points<br>
GROUP BY route_no;<br>
<p>-- *************************************************<br>
</p>
<div class="moz-cite-prefix">On 7/02/2025 8:32 am, SPDBA wrote:<br>
</div>
<blockquote type="cite"
cite="mid:f33e7284-1679-4b76-aac9-73c064ee61db@spdba.com.au">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="auto"><br>
<br>
</div>
<div dir="auto"><!-- tmjah_g_1299s -->39 Cliff View Drive, Allens
Rivulet, 7150, Tasmania, Australia<!-- tmjah_g_1299e --><br>
</div>
<div dir="auto"><!-- tmjah_g_1299s -->(P) 03 62 396 397<!-- tmjah_g_1299e --><br>
</div>
<div dir="auto"><!-- tmjah_g_1299s -->(M) 0418 396 391<!-- tmjah_g_1299e --><br>
</div>
<div dir="auto"><!-- tmjah_g_1299s -->(W)
<!-- tmjah_g_1299e --><a href="http://www.spdba.com.au"
moz-do-not-send="true"><!-- tmjah_g_1299s --></a><a
href="http://www.spdba.com.au" moz-do-not-send="true">www.spdba.com.au</a><!-- tmjah_g_1299e --></div>
<div class="gmail_quote">On 7 Feb 2025, at 05:38, Regina Obe <<a
href="mailto:lr@pcorp.us" target="_blank"
moz-do-not-send="true" class="moz-txt-link-freetext">lr@pcorp.us</a>>
wrote:
<blockquote class="gmail_quote"
style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt">I don’t
think OpenStreetMap data is going to help you with
constructing flight corridors. As I recall these are 3
dimensional areal paths.</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">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.</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"></span></p>
<p> </p>
<p class="MsoNormal"><span style="font-size:11.0pt">I was
thinking sources like <a href="https://openflights.org"
moz-do-not-send="true" class="moz-txt-link-freetext">https://openflights.org</a>
or <a href="https://opensky-network.org/"
moz-do-not-send="true" class="moz-txt-link-freetext">https://opensky-network.org/</a>
might have it but not seeing corridor data there</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"></span></p>
<p> </p>
<p class="MsoNormal"><span style="font-size:11.0pt"></span></p>
<p> </p>
<p class="MsoNormal"><span style="font-size:11.0pt">But if
you do have reliable points, to Simon’s point, here is a
pseudo coded example</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"></span></p>
<p> </p>
<p class="MsoNormal"><span style="font-size:11.0pt">CREATE
TABLE corridor_points(name, sort_order, geom
geometry(Point, 4326) ) </span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"></span></p>
<p> </p>
<p class="MsoNormal"><span style="font-size:11.0pt">INSERT
INTO corridor_points(name, sort_order, geom)</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">SELECT
name, sort_order, ST_Point(longitude, latitude, 4326) </span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">FROM
your_table_source;</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"></span></p>
<p> </p>
<p class="MsoNormal"><span style="font-size:11.0pt"></span></p>
<p> </p>
<p class="MsoNormal"><span style="font-size:11.0pt">SELECT
name, ST_AsGeoJSON( ST_MakeLine(geom ORDER BY
sort_order) ) AS geo_json</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">FROM
corridor_points</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">GROUP BY
name;</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"></span></p>
<p> </p>
<p class="MsoNormal"><span style="font-size:11.0pt">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</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"></span></p>
<p> </p>
<p class="MsoNormal"><span style="font-size:11.0pt">SELECT
name, ST_AsGeoJSON( ST_Buffer(ST_MakeLine(geom ORDER BY
sort_order)::geography, 500) ) AS geo_json</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">FROM
corridor_points</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">GROUP BY
name;</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"></span></p>
<p> </p>
<p class="MsoNormal"><span style="font-size:11.0pt"></span></p>
<p> </p>
<p class="MsoNormal"><span style="font-size:11.0pt"></span></p>
<p> </p>
<div
style="border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt">
<div>
<div
style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif"
class=""> SPDBA <a class="moz-txt-link-rfc2396E" href="mailto:simon@spdba.com.au"><simon@spdba.com.au></a> <br>
<b>Sent:</b> Thursday, February 6, 2025 3:50 AM<br>
<b>To:</b> Luca Bertoncello
<a class="moz-txt-link-rfc2396E" href="mailto:lucabert@lucabert.de"><lucabert@lucabert.de></a><br>
<b>Cc:</b> <a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<b>Subject:</b> Re: Help creating "corridors"</span></p>
</div>
</div>
<p> </p>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">Not
familiar with these tables. </p>
</div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">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. </p>
</div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">Simon</p>
</div>
<div>
<p class="MsoNormal">39 Cliff View Drive, Allens
Rivulet, 7150, Tasmania, Australia</p>
</div>
<div>
<p class="MsoNormal">(P) 03 62 396 397</p>
</div>
<div>
<p class="MsoNormal">(M) 0418 396 391</p>
</div>
<div>
<p class="MsoNormal">(W) <a
href="http://www.spdba.com.au"
moz-do-not-send="true">www.spdba.com.au</a></p>
</div>
<div>
<p class="MsoNormal">On 6 Feb 2025, at 19:37, Luca
Bertoncello <<a href="mailto:lucabert@lucabert.de"
target="_blank" moz-do-not-send="true"
class="moz-txt-link-freetext">lucabert@lucabert.de</a>>
wrote:</p>
<blockquote
style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in">
<pre>Am 06.02.2025 09:16, schrieb SPDBA:</pre>
<blockquote
style="border:none;border-left:solid #729FCF 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in;margin-bottom:6.0pt">
<pre> 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.</pre>
</blockquote>
<pre>
I'll use planet_osm_point and planet_osm_polygon from OpenStreetMaps.
See
<a
href="https://wiki.openstreetmap.org/wiki/OpenStreetBrowser/Database_Structure"
moz-do-not-send="true" class="moz-txt-link-freetext">https://wiki.openstreetmap.org/wiki/OpenStreetBrowser/Database_Structure</a>
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
(<a href="mailto:lucabert@lucabert.de" moz-do-not-send="true"
class="moz-txt-link-freetext">lucabert@lucabert.de</a>)</pre>
</blockquote>
</div>
</div>
</div>
</blockquote>
</div>
</blockquote>
<pre class="moz-signature" cols="72">--
Simon Greener
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia
(m) +61 418 396 391
(w) <a class="moz-txt-link-abbreviated" href="http://www.spdba.com.au">www.spdba.com.au</a>
(m) <a class="moz-txt-link-abbreviated" href="mailto:simon@spdba.com.au">simon@spdba.com.au</a></pre>
</body>
</html>