Come trovare la distanza tra due punti utilizzando latitudine e longitudine
Ho una tabella ROUTES che ha le colonne SOURCE_AIRPORT e DESTINATION_AIRPORT e descrive un percorso particolare che un aereo prenderebbe per spostarsi dall'una all'altra.
Ho una tabella AEROPORTI che ha le colonne LATITUDINE e LONGITUDINE che descrivono la posizione geografica di un aeroporto.
Posso unire le due tabelle utilizzando colonne che condividono entrambe chiamate SOURCE_AIRPORT_ID e DESTINATION_AIRPORT_ID nella tabella delle rotte e chiamata IATA nella tabella degli aeroporti (un codice di 3 lettere per rappresentare un aeroporto come LHR per Londra Heathrow).
La mia domanda è: come posso scrivere una query SQL utilizzando tutte queste informazioni per trovare, ad esempio, il percorso più lungo da un particolare aeroporto come LHR?
Credo di dover unire le due tabelle e per ogni riga nella tabella delle rotte in cui l'aeroporto di origine è LHR, guarda la latitudine e la longitudine dell'aeroporto di destinazione, calcola quanto è lontano da LHR, salvalo come un campo chiamato " distanza ", quindi ordinare prima i dati in base alla distanza massima. Ma in termini di sintassi SQL sono in perdita.
Risposte
Questa sarebbe stata probabilmente una domanda migliore per Mathematics Stack Exchange, ma qui fornirò alcune informazioni. Se sei relativamente esperto di trigonometria, sono sicuro che potresti capire l'implementazione data questa risorsa:https://en.m.wikipedia.org/wiki/Haversine_formula. Stai cercando di calcolare la distanza tra due punti sulla superficie di una sfera in termini di distanza sulla sua superficie (non una linea retta, non puoi viaggiare attraverso la Terra).
La pagina mostra questa formula: https://wikimedia.org/api/rest_v1/media/math/render/svg/a65dbbde43ff45bacd2505fcf32b44fc7dcd8cc0
Dove
• φ1, φ2 sono la latitudine del punto 1 e la latitudine del punto 2 (in radianti),
• λ1, λ2 sono la longitudine del punto 1 e la longitudine del punto 2 (in radianti).

Se i dati sono in gradi, puoi semplicemente convertirli in radianti moltiplicando per pi greco / 180
Esiste una formula chiamata grande distanza circolare per calcolare la distanza tra due punti. Probabilmente puoi caricarlo come libreria per il tuo sistema operativo. Dimentica l'haversine, il nostro pianeta non è una sfera perfetta. Se utilizzi spesso questo valore, salvalo nella routestabella.
Penso che tu ci sia circa il 90% in termini di metodo di soluzione. Aggiungerò alcuni dettagli aggiuntivi su una potenziale query SQL per ottenere la tua risposta. Quindi ci sono 2 passaggi che devi fare per calcolare le distanze: il passaggio 1 è creare una tabella contenente l'unione della tabella ROUTES alla tabella AIRPORTS per ottenere la latitudine / longitudine sia per SOURCE_AIRPORT che per DESTINATION_AIRPORT sulla rotta. Questo potrebbe assomigliare a questo:
SELECT t1.*, CONVERT(FLOAT, t2.LATITUDE) AS SOURCE_LAT, CONVERT(FLOAT, t2.LONGITUDE) AS SOURCE_LONG, CONVERT(FLOAT, t3.LATITUDE) AS DEST_LAT, CONVERT(FLOAT, t3.LONGITUDE) AS DEST_LONG, 0.00 AS DISTANCE_CALC
INTO ROUTE_CALCULATIONS
FROM ROUTES t1 LEFT OUTER JOIN AIRPORTS t2 ON t1.SOURCE_AIRPORT_ID = t2.IATA
LEFT OUTER JOIN AIRPORTS t3 ON t1.DESTINATION_AIRPORT_ID = t3.IATA;
L'output risultante dovrebbe creare una nuova tabella denominata ROUTE_CALCULATIONS composta da tutte le colonne ROUTES, la longitudine / latitudine per entrambi gli aeroporti SOURCE e DESTINATION e una colonna segnaposto DISTANCE_CALC con valore 0.
Il passaggio 2 calcola la distanza. Questo dovrebbe essere un calcolo e un aggiornamento relativamente semplici.
UPDATE ROUTE_CALCULATIONS
SET DISTANCE_CALC = 2 * 3961 * asin(sqrt((sin(radians((DEST_LAT- SOURCE_LAT) / 2))) ^ 2 + cos(radians(SOURCE_LAT)) * cos(radians(DEST_LAT)) * (sin(radians((DEST_LONG- SOURCE_LONG) / 2))) ^ 2))
E questo dovrebbe fornire la distanza calcolata nella tabella DISTANCE_CALC per tutti i percorsi visualizzati nei dati. Da lì dovresti essere in grado di eseguire qualsiasi analisi del percorso relativa alla distanza che desideri.