Android Question The best way to do massive searches

[PABLO2013]

Greetings, I am trying to read from a txt or a csv the location of an airport with respect to distance to my location, what happens is that the number of airports is 350,000 and it takes too long, the information could be cut or converted, for example, to sql or so. ...thank you

Sub CheckNearbyAirports(path As String, Filename As String, mylat As Double,mylon As Double)

   
        Dim lst As List = File.ReadList(path, Filename)
       
        For i = 0 To lst.size -1
           
            Dim line As String = lst.Get(i)
            line = line.Trim.Replace("  ", "")
            Dim xyz() As String = Regex.Split(",", line)

            Dim airportName As String = xyz(0)
            Dim airportLat As Double = xyz(5)
            Dim airportLon As Double = xyz(6)

    Log(i)

If Abs(mylat-airportLat) < 0.001 Then

            Dim distance As Double = HaversineDistance(mylat, mylon , airportLat, airportLon)
            If distance < 100 Then
                Log("Near to : " & airportName)
             
            End If
 End If
 
   Next
 

End Sub

 

[emexes]

Since Amsterdam Schiphol was not on the list and is closer (at about 347km) than Paris, you can start to see the flaws of this approach.
Beyond a certain distance (280km?) the completeness of the list is unreliable.

Probably because longitudinal degrees get shorter as we move away from the equator. At Greenwich, is down to 87 km, thus only guaranteed to find airports within +/- n deltas of that = 174 km.

We could increase the delta or just remove airports beyond 280km from the list.

Agreed set delta to +/- Ceil(desired radius / longitudinal degree length) and then your array-of-lists structure will quickly reduce the number of airports potentially within that radius to a much more manageable number.

But watching out for diminishing longitudinal degree length as get closer to poles.

 

[emexes]

I also didn't know that different languages deal with this differently. In the link you provided, Fortran is not even on the list!

Maybe because: why bother, Fortran does it right

 

[Pablo Torres]

Greetings, I am trying to read from a txt or a csv the location of an airport with respect to distance to my location, what happens is that the number of airports is 350,000 and it takes too long, the information could be cut or converted, for example, to sql or so. ...thank you

Sub CheckNearbyAirports(path As String, Filename As String, mylat As Double,mylon As Double)

  
        Dim lst As List = File.ReadList(path, Filename)
      
        For i = 0 To lst.size -1
          
            Dim line As String = lst.Get(i)
            line = line.Trim.Replace("  ", "")
            Dim xyz() As String = Regex.Split(",", line)

            Dim airportName As String = xyz(0)
            Dim airportLat As Double = xyz(5)
            Dim airportLon As Double = xyz(6)

    Log(i)

If Abs(mylat-airportLat) < 0.001 Then

            Dim distance As Double = HaversineDistance(mylat, mylon , airportLat, airportLon)
            If distance < 100 Then
                Log("Near to : " & airportName)
            
            End If
 End If
 
   Next
 

End Sub

If I were you:
1) I will store all latitude, longitude, name of all 350.000 airports in an AWS aurora serverless
2) I will make an api with a method that receives 4 parameters: latitude (LA), longitude (LO), distance (D), and max (M)
I will use this four parameters in a preexisting query to obtains max (M) airports from database whose distance to LA,LO is smaller than D, order by distance.
That api will give you back all the data you need in the correct order, it will always take less than a second and you don’t have to do anything else.
Write me if you need a more detailed explanation, I can help you with that

 

[emexes]

it will always take less than a second

That's pretty impressive.

But on a related note, given that this airport search thing might be for use when your plane's going down and you're desperately looking for somewhere flat to land... would you stake your life on that internet connection?

 

[Peter Simpson]

Which link allows you to download 350000. Can you post an exact link to it.

Can you upload or put a link to download the csv of 350000 airpotrts. I have an idea on how to approach your issue, but reluctant until I take a look at the complete csv file.

Hello @Mahares,
Were you thinking about importing the data into an SQLite database then, indexing and/or loading the database into memory for faster search results?

And this is where you either say no, something like that it Pete, you're miles away lol

 

[PABLO2013]

Thanks P Simpson. Yes, I can convert the database to SQL and then do the search, but I don't know which method within SQL makes the search more efficient.

 

[DonManfred]

ut I don't know which method within SQL makes the search more efficient.

learn on how to set the correct indices in your Tables.

Top 10 Steps to Building Useful Database Indexes

Designing an appropriate set of indexes can be one of the more troubling aspects of developing efficient relational database applications. Perhaps, the most important thing you can do to assure optimal application performance when accessing data in a relational/SQL database is to create the...

www.dbta.com

Google it. You´ll find hundrets of Tutorials

 

[PABLO2013]

tks + 1000, emexes,Alex_197,maharas,Hamied Abou Hulaikah,William Lancee,visión azul,donmanfred

​

 

[Mahares]

tks + 1000 , emexes,Alex_197,maharas,Hamied Abou Hulaikah,William Lancee,visión azul

Where is the link to download 350000 records you memntioned in your first post, so we can test it for you. Or better yet, can you post your project.

 

[emexes]

Where is the link to download 350000 records you mentioned in your first post, so we can test it for you.

I haven't seen the data but I think it includes (i) individual runways within an airport, and/or (ii) private airstrips eg on farms and other remote/rural sites.

The second list would certainly be useful to glider pilots who run out of sunshine. Rough landing sites can look deceptively good from the air, and a live GPS map showing nearby good landing and recovery sites would be very popular, if indeed it doesn't already exist.

My last gliding was done last century, but I'm still interested in seeing the 350000 records too, just out of curiosity.

 

[emexes]

a live GPS map showing nearby good landing and recovery sites would be very popular, if indeed it doesn't already exist.

Lol this is the closest I've found so far...

https://download.cnet.com/copilot-landing-glider-map/3000-31711_4-78254926.html

for the game Fortnite... = close, but no cigar

 

[emexes]

I wonder how they came up with that last piece of advice here about field landings:

 

[William Lancee]

@emexes
I can attest to inquisitive cows (long story), but "One cow may be a bull" means
be very careful landing. I know from below, but how do you know from above?

Of interest: https://deepblue.lib.umich.edu/bitstream/handle/2027.42/140509/6.2015-1798.pdf?sequence=1

 

[Mahares]

I haven't seen the data but I think it includes (i) individual runways within an airport, and/or (ii) private airstrips eg on farms and other remote/rural sites.

I think this is the download link for the OP's CSV file. It has over 371000 records. Most of them are not airports and only deals with places in America. I am not sure it is suitable for looking up lat/lon for nearby airports.

FAA UAS FacilityMap Data Primary

Primary Test Dataset for USS Onboarding.

laancussob-faa.opendata.arcgis.com