Hi guys. Good day to all.
This is the first time I've post in this forum though I was for sometime now looking at the posts of others to gain knowledge in this great RDBMS and to learn from the best on this field.
I wanted to ask some help from the community about optimizing the query I've made in finding the nearest airport based on latitude and longitude using STDistance method of the Geography datatype.
Here's the scenario, I've imported a csv file that contains all the airports in the world which is around 40k plus records. Also, I've imported another csv file which contain the logs from an airplane. It contains the lat/long, fuel used, altitude, etc. The size of this file will depend on how long the airplane is flying. One file I have imported consists of 12k rows. The table is structured something like this:
1, 40.3215, -112.35, 2000
2, 39.5423, -111.45, 1500
3, 41.2563, -113.53, 1500
MCIAA, Mactan Cebu Airport, 10.6543, -15.1233, 0x00013213112
LAX, Los Angeles, 75.1235, -55.2135, 0x000132654546
NAIA, Ninoy Airport, 15.4566, -26.5463, 0x4564789797
Based on the Log_Details table records, I wanted to find the nearest airport based on the Latitude and Longitude. I made a test query to see how long does it take get the nearest airport based on the Log_Details. This is the only set based query I can think of to get the nearest airport.
FROM dbo.Log_Details l
CROSS APPLY (SELECT TOP(1) Airport_Ident,Airport_Name,Airport_Latitude,Airport_Longitude FROM dbo.Airport
ORDER BY Airport_Geog.STDistance((GEOGRAPHY:
oint(Log_Detail_Latitude, Log_Detail_Longitude, 4326))));
When I ran this query, after it reached 1 min plus, I cancelled it.
That's why I wanted the help of the community if there is any other way that I could speed up the performance of the query considering there are many records in both table? Or is there another way of getting the record without using STDistance method. I don't if this method is slow. Just wanted to know if there is any faster alternatives.
Thank you in advance. :-)