locked
Finding the nearest airport to a location based on geography type RRS feed

  • Question

  • I am using SQL Server 2014 and spatial geography types.  I have two tables.  The table [Locations] contains locations that I want to match with the nearest airport. The table [Airports] has all the available airports in the USA.

    Both tables have Latitude and Longitude fields.  I added the field [Coordinates] (of type geography) to both tables.

    In addition, I added fields to the table [Locations] for [airport_id] and [distance]. [airport_id] is to be the foreign key to the nearest airport in the [airports] table.

    I stored the geographical point coordinates in the [Coordinates] fields using the following:

        UPDATE dbo.<[Locations] or [Airports]>
        SET [Coordinates] = geography::STPointFromText('POINT(' + CAST([Lon] AS VARCHAR(20)) + ' ' + CAST([Lat] AS VARCHAR(20)) + ')', 4326) ;

    Table [Locations] has a clustered primary key on the field [Location_ID].  The table [Airports] has a clustered primary key on the field [Airport_Id].  I added spatial indexes to both tables as follows:

    CREATE SPATIAL INDEX SIndx_Locations_geography_Coordinates
         ON [Locations]([Location_ID]);

    CREATE SPATIAL INDEX SIndx_Airports_geography_Coordinates
         ON [Airports]([Airport_Id]);

    Now I am trying to determine the best way to find the closest airport for each location.  I could scan both tables and do a match on the TOP 1 "closest airport" based on least distance but I am afraid the query would be a performance killer.

    Can anyone point me to an example of how to populate the [airport_id] and [distance] fields in the table [Locations]?

    Thanks ahead of time for any help you can provide,

    Tom

    Tuesday, March 29, 2016 7:12 PM

Answers