Home Forums SQL Server 2008 SQL Server 2008 - General Optimize query for finding nearest airport based on latitude and longitude using STDistance RE: Optimize query for finding nearest airport based on latitude and longitude using STDistance

  • April 29, 2012 at 12:44 pm

    #1480958

    facturan.antonio (4/28/2012)

    Hello Jeff.

    I was thinking about your suggestion. Is it somehow similar to this?

    I will get the min/max lat and long based on the flight log. Get the distance between the min/max lat/long and limit the airports based on that distance?

    Thank you.

    Ummm... kind of but a little bit differently.

    1. Get the distance between the two airports for the flight and divide that distance by two.

    2. Convert that distance to degrees of Latitude.

    3. Subtract and Add that those degrees of Latitude from the Latitude for the given flight point. Those should be your new min an max Latitudes to search for closest airports for points during the flight. It will greatly increase the speed of your original calculation because it can eliminate virtually all other airports in the world for shorter flights.

    If you create the same limits for Longitude (taking curvature of the Earth into consideration), you can quickly focus in on a pretty tight window.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)