Get the id of the next closest location

Question

Hi.

I have an application in which the user can select a location and view it's distance from several Points Of Interests (POIs).

When I retrieve these distances, I would also like to retrieve the ID's of the locations that are the next closest and the next furthest away from each POI. eg. If we have 10 locations, each of them a mile further away from a certain POI the I would like to return: The name of the POI, The distance from that POI, The ID of the next closest location, and the ID of the next furthest location. An example row of the result set could be: 'Football Ground', '1.5', 24, 784 (because the location we are viewing is 1.5 miles from the football ground and location 24 is the next closest and 784 is the next furthest away.

Note: it is possible that the location we are viewing is the closest to or furthest from a POI, in that case we would need to return -1 as the id of the next closest or furthest location to let the front end know that we can't get any closer or further.

I would like to do this in one statement if possible. I created a function that will calculate the distance between 2 points, and have been using it around the application:

 create FUNCTION [dbo].[fnc_calc_distance]
(
    @lat1 as float,
    @lng1 as float,
    @lat2 as float,
    @lng2 as float
)
RETURNS float
AS
BEGIN
    declare @result as float    
    select @result = (3959*acos(cos(radians(@lat2))*cos(radians(@lat1))*cos(radians(@lng1)-radians(@lng2))+sin(radians(@lat2))*sin(radians(@lat1))))
    RETURN @result
END

And sample table structures/ data are as follows:

CREATE TABLE tbl_locations(
    [houseID] [int] NOT NULL,
    [lat] [decimal](14, 10) not NULL,
    [lng] [decimal](14, 10) not NULL) 

insert into tbl_locations
    values (1, 54.9834400000, -1.6314250000)
insert into tbl_locations
    values (2, 54.9860420000, -1.5912680000)
insert into tbl_locations
    values (3, 54.9882050000, -1.5707710000)

CREATE TABLE tbl_poi(
    [ID] [int] NOT NULL,
    [name] [varchar](32) NOT NULL,
    [lat] [decimal](14, 10) NOT NULL,
    [lng] [decimal](14, 10) NOT NULL)

insert into tbl_poi
    values (1, 'Football Ground', 54.9752430000, -1.6219210000)
insert into tbl_poi
    values (1, 'Train Station', 54.9898610000, -1.6047600000)

I'm using SQL Server 2008.

Thanks in advance.

Chris

Answer 1

The way I would handle this is to return an ordered set of locations relative to the POI. Depending on your application you could limit this to just the first N items or within X distance. If limiting to a certain number, then it would be handy to know how many items there are to know whether you should indicate that more are available. Unfortunately I don't know how to do all of that in a single query.

My tack would be to store the name, id, lat, and long of the chosen point of interest on the page where the user makes the choice (1 query). Then I would pass these back to the server and use them in the second query (to get the ordered list). If necessary, I'd perform a third query to find out how many total items there were (count(*)) to see if I needed to provide a "more items" link.

The second query might look like:

 select TOP 10 t.id, t.name, t.lat, t.long, t.distance
 from (select id, name, lat, long, fnc_calc_distance(lat,@house_lat,long,@house_long) as distance
       from tbl_poi) t
 order by t.distance desc

where you pass in the selected parameters from the user's selection.