<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Aptos;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:12.0pt;
font-family:"Aptos",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:"Consolas",serif;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:"Aptos",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;
mso-ligatures:none;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple style='word-wrap:break-word'><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.<o:p></o:p></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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>I was thinking sources like <a href="https://openflights.org">https://openflights.org</a> or <a href="https://opensky-network.org/">https://opensky-network.org/</a> might have it but not seeing corridor data there<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></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<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>CREATE TABLE corridor_points(name, sort_order, geom geometry(Point, 4326) ) <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>INSERT INTO corridor_points(name, sort_order, geom)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>SELECT name, sort_order, ST_Point(longitude, latitude, 4326) <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>FROM your_table_source;<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>SELECT name, ST_AsGeoJSON( ST_MakeLine(geom ORDER BY sort_order) ) AS geo_json<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>FROM corridor_points<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>GROUP BY name;<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></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<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></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<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>FROM corridor_points<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>GROUP BY name;<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></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'> SPDBA <simon@spdba.com.au> <br><b>Sent:</b> Thursday, February 6, 2025 3:50 AM<br><b>To:</b> Luca Bertoncello <lucabert@lucabert.de><br><b>Cc:</b> postgis-users@lists.osgeo.org<br><b>Subject:</b> Re: Help creating "corridors"<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal style='margin-bottom:12.0pt'>Not familiar with these tables. <o:p></o:p></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. <o:p></o:p></p></div><div><p class=MsoNormal style='margin-bottom:12.0pt'>Simon<o:p></o:p></p></div><div><p class=MsoNormal>39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia<o:p></o:p></p></div><div><p class=MsoNormal>(P) 03 62 396 397<o:p></o:p></p></div><div><p class=MsoNormal>(M) 0418 396 391<o:p></o:p></p></div><div><p class=MsoNormal>(W) <a href="http://www.spdba.com.au">www.spdba.com.au</a><o:p></o:p></p></div><div><p class=MsoNormal>On 6 Feb 2025, at 19:37, Luca Bertoncello <<a href="mailto:lucabert@lucabert.de" target="_blank">lucabert@lucabert.de</a>> wrote:<o:p></o:p></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:<o:p></o:p></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<br> latitude longitude columns? There are ways to take an ordered list of<br> points in a table and generate linestrings as I assume this is what<br> you want to do.<o:p></o:p></pre></blockquote><pre><br>I'll use planet_osm_point and planet_osm_polygon from OpenStreetMaps.<br>See <br><a href="https://wiki.openstreetmap.org/wiki/OpenStreetBrowser/Database_Structure">https://wiki.openstreetmap.org/wiki/OpenStreetBrowser/Database_Structure</a><br><br>For the airfield I think, I'll just search for the city in the both <br>tables. It must not be soooo precise...<br><br>Thanks<br>Luca Bertoncello<br>(<a href="mailto:lucabert@lucabert.de">lucabert@lucabert.de</a>)<o:p></o:p></pre></blockquote></div></div></div></body></html>