By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,414 Members | 1,586 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,414 IT Pros & Developers. It's quick & easy.

Get the id of the next closest location

P: 5
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. create FUNCTION [dbo].[fnc_calc_distance]
  3. (
  4.     @lat1 as float,
  5.     @lng1 as float,
  6.     @lat2 as float,
  7.     @lng2 as float
  8. )
  9. RETURNS float
  10. AS
  11. BEGIN
  12.     declare @result as float    
  13.     select @result = (3959*acos(cos(radians(@lat2))*cos(radians(@lat1))*cos(radians(@lng1)-radians(@lng2))+sin(radians(@lat2))*sin(radians(@lat1))))
  14.     RETURN @result
  15. END
  16.  
  17.  
And sample table structures/ data are as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE TABLE tbl_locations(
  3.     [houseID] [int] NOT NULL,
  4.     [lat] [decimal](14, 10) not NULL,
  5.     [lng] [decimal](14, 10) not NULL) 
  6.  
  7. insert into tbl_locations
  8.     values (1, 54.9834400000, -1.6314250000)
  9. insert into tbl_locations
  10.     values (2, 54.9860420000, -1.5912680000)
  11. insert into tbl_locations
  12.     values (3, 54.9882050000, -1.5707710000)
  13.  
  14. CREATE TABLE tbl_poi(
  15.     [ID] [int] NOT NULL,
  16.     [name] [varchar](32) NOT NULL,
  17.     [lat] [decimal](14, 10) NOT NULL,
  18.     [lng] [decimal](14, 10) NOT NULL)
  19.  
  20. insert into tbl_poi
  21.     values (1, 'Football Ground', 54.9752430000, -1.6219210000)
  22. insert into tbl_poi
  23.     values (1, 'Train Station', 54.9898610000, -1.6047600000)
  24.  
Im using SQL Server 2008.

Thanks in advance.

Chris
Jan 10 '10 #1
Share this Question
Share on Google+
5 Replies


Delerna
Expert 100+
P: 1,134
Hi Chris
I am here to assist people to overcome stumbling blocks in something they are writing, not to fill orders for complete working queries for free. I apologise if I have gotten you wrong but that is how your post comes accross, at least it does to me because there is no question anywhere in it!

If you have a question please post, along with what you have tried and I will be glad to assist.
What you did post is nicely done by the way.
Jan 11 '10 #2

P: 5
EDIT: I have missed a column from each of the tables. Both tables need a column adding called region of type int. For the purposes of this, the values in this column of every row should be 1
Jan 11 '10 #3

P: 5
I guess what I am asking is what is the best way to go to to get this done.

I have tried several methods, each one with it's own pitfalls. I didnt include them because I wanted people to look at this problem from a fresh slate, however I will detail two methods I have used:

Expand|Select|Wrap|Line Numbers
  1. declare @locationid int = 1
  2.  
  3. select p.id,
  4.     dbo.fnc_calc_distance(p.lat, p.lng, l.lat, l.lng) as distance,
  5.     c.houseID as closerid,
  6.     dbo.fnc_calc_distance(p.lat, p.lng, c.lat, c.lng) as closerDistance,
  7.     f.houseid as furtherdistance,
  8.     dbo.fnc_calc_distance(p.lat, p.lng, f.lat, f.lng) as furtherDistance
  9. from tbl_poi p
  10.     left join tbl_locations l on l.region = p.region
  11.     left join tbl_locations c on c.region = p.region
  12.     left join tbl_locations f on f.region = p.region
  13. where l.houseID = @locationid
  14.     and dbo.fnc_calc_distance(p.lat, p.lng, c.lat, c.lng) < dbo.fnc_calc_distance(p.lat, p.lng, l.lat, l.lng)
  15.     and dbo.fnc_calc_distance(p.lat, p.lng, f.lat, f.lng) > dbo.fnc_calc_distance(p.lat, p.lng, l.lat, l.lng)
  16.  
This method fails because if the location if the closest or further from a POI then that POI will not be returned. The problem is in the where clause.

That led me onto this:

Expand|Select|Wrap|Line Numbers
  1. declare @locationid int = 1
  2.  
  3. select p.ID, p.name, 
  4.     x.closerid, y.furtherid,
  5.     x.closerDistance, y.furtherDistance
  6. from tbl_poi p
  7.     left join (
  8.         select p.id,
  9.             dbo.fnc_calc_distance(p.lat, p.lng, l.lat, l.lng) as distance,
  10.             c.houseID as closerid,
  11.             dbo.fnc_calc_distance(p.lat, p.lng, c.lat, c.lng) as closerDistance
  12.         from tbl_poi p
  13.             left join tbl_locations l on l.region = p.region
  14.             left join tbl_locations c on c.region = p.region
  15.         where l.houseID = @locationid
  16.             and dbo.fnc_calc_distance(p.lat, p.lng, c.lat, c.lng) < dbo.fnc_calc_distance(p.lat, p.lng, l.lat, l.lng))x
  17.         on p.ID = x.id
  18.     left join (
  19.         select p.id,
  20.             f.houseid as furtherid,
  21.             dbo.fnc_calc_distance(p.lat, p.lng, f.lat, f.lng) as furtherDistance
  22.         from tbl_poi p
  23.             left join tbl_locations l on l.region = p.region
  24.             left join tbl_locations f on f.region = p.region
  25.         where l.houseID = @locationid
  26.             and dbo.fnc_calc_distance(p.lat, p.lng, f.lat, f.lng) > dbo.fnc_calc_distance(p.lat, p.lng, l.lat, l.lng))y
  27.         on p.ID = y.id
  28.  
Which WILL return every POI regardless if the location is the closest or furthest from it. BUT, what I need to do now is only return the closest and the next furthest location, not every location that is closer or further away. (A bit confusing I know but just bear with me).

Thanks again

Chris
Jan 11 '10 #4

Delerna
Expert 100+
P: 1,134
OK, your attempts show me more clearly what you are trying to do and convinces me I was wrong in my assumption.

I don't have your complete scenario I think, so I will present Ideas
that you can play with that might lead you to a solution.


Anyway, please note that I am using SQL 2000 so sytax might vary as well as 2008 having better ways of doing certain things

First a query that returns every possible combiation of two locations whithin a region along with the distance from the selected POI
Expand|Select|Wrap|Line Numbers
  1.    declare @locationid int 
  2.    set @locationid= 1 
  3.  
  4.     select a.ID,a.Name,b.HouseID,dbo.fnc_calc_distance(a.Lat,a.Lng,b.Lat,b.lng) as Distance
  5.     from (SELECT * from tbl_poi) a
  6.     join (SELECT * from tbl_locations) b on a.region=b.Region
  7.     where a.ID=2
  8.  
If you run that you will notice that the current location joined to the current location returns 0.0 as the distance

wrap the above up as a sub query sorted by distance and selecting the top 2 records with the current joined to current location filtered out, should give you the next 2 closest POI's as two records
Expand|Select|Wrap|Line Numbers
  1. select Top 2 Name,a.HouseID,Distance
  2. from
  3. (
  4.     select a.ID,a.Name,b.HouseID,dbo.fnc_calc_distance(a.Lat,a.Lng,b.Lat,b.lng) as Distance
  5.     from (SELECT 1 as j,* from tbl_poi) a
  6.     join (SELECT 1 as j,* from tbl_locations) b on a.j=b.j and a.region=b.Region
  7.     where a.ID=2
  8. )a
  9. where Distance<>0.0
  10. order by Distance
  11.  

Now all you need do is pivot that into 1 record and handle the -1 requirement
Since 2008 can do those things more easily than 2000 I will leave that to you.

I hope that I have understood you correctly and that this helps
Jan 12 '10 #5

P: 5
Hi.

I just thought I would post the final solution that I came up with for this problem.

Its pretty lengthy so I'll try my best to describe it.

Firstly, I created a view called vw_distance which was just all the pois joined to all the locations. This view had 3 columns: poiID, locationID and distance (distance being the distance between the location and the poi)

The next bit is where the magic happens:

I took the poi table and done a left join to it with the location table on region.
I then wrote a subquey that would get me the next closest location. It does this by selecting max(distance) from vw_distance, grouped by poiid. BUT, we dont just wantt he max distance, we want the max distance that is LOWER than the current distance, so, I joined to vw_distance again so that I could add a where clause that said get me the max(distance) where distance < the current distance. So, the subquery looks like this:
Expand|Select|Wrap|Line Numbers
  1. select a.poiid, MAX(a.distance) as dc 
  2.                     from vw_distance a
  3.                         left join vw_distance b
  4.                             on a.poiid = b.poiid
  5.                     where a.distance < b.distance
  6.                         and b.houseid=@id
  7.                     group by a.poiid
Once I had that, It's really easy to alter it to retrieve the next further location- just change the MAX to a MIN and the < to a >.

I joined these two subqueries to my original query and now i was able to pull back a list of all the POI, the distance from the current location, and the distance of the next closest and next furtherest location from the POI:

Expand|Select|Wrap|Line Numbers
  1.   select p.ID, p.name, dc.dc, 
  2.         dbo.fnc_calc_distance(p.lat, p.lng, s.lat, s.lng) as distance,
  3.         round(dbo.fnc_calc_distance(p.lat, p.lng, s.lat, s.lng)*20, 0) as time,
  4.         df.df
  5.     from tbl_poi p
  6.         left join tbl_locations s on p.region = s.region
  7.         left join (    select a.poiid, MAX(a.distance) as dc 
  8.                     from vw_distance a
  9.                         left join vw_distance b
  10.                             on a.poiid = b.poiid
  11.                     where a.distance < b.distance
  12.                         and b.houseid=@id
  13.                     group by a.poiid)dc 
  14.             on p.ID = dc.poiid
  15.         left join (    select a.poiid, min(a.distance) as df
  16.                     from vw_distance a
  17.                         left join vw_distance b
  18.                             on a.poiid = b.poiid
  19.                     where a.distance > b.distance
  20.                         and b.houseid=@id
  21.                     group by a.poiid)df 
  22.             on p.ID = df.poiid
Now that I had this, all I had to do was to get the IDs of the locations that had the said distances above.

This was relatively simple and achieved by joining good old trusty vw_distance back to my original query on distance. I had to do this twice again, one for closer and one for further.

So, all in all:

Expand|Select|Wrap|Line Numbers
  1.     select p.ID, p.name, dc.dc, cid.houseid as closer, 
  2.         dbo.fnc_calc_distance(p.lat, p.lng, s.lat, s.lng) as distance,
  3.         round(dbo.fnc_calc_distance(p.lat, p.lng, s.lat, s.lng)*20, 0) as time,
  4.         df.df, fid.houseid as further
  5.     from tbl_poi p
  6.         left join tbl_locations s on p.region = s.region
  7.         left join (    select a.poiid, MAX(a.distance) as dc 
  8.                     from vw_distance a
  9.                         left join vw_distance b
  10.                             on a.poiid = b.poiid
  11.                     where a.distance < b.distance
  12.                         and b.houseid=@id
  13.                     group by a.poiid)dc 
  14.             on p.ID = dc.poiid
  15.         left join (    select a.poiid, min(a.distance) as df
  16.                     from vw_distance a
  17.                         left join vw_distance b
  18.                             on a.poiid = b.poiid
  19.                     where a.distance > b.distance
  20.                         and b.houseid=@id
  21.                     group by a.poiid)df 
  22.             on p.ID = df.poiid
  23.         left join vw_distance cid
  24.             on p.ID = cid.poiid
  25.                 and dc.dc = cid.distance
  26.         left join vw_distance fid
  27.             on p.ID = fid.poiid
  28.                 and df.df = fid.distance
  29.     where
  30.         s.houseid = @id
This project is coming to an end and I've have had to overcome some pretty challenging stuff to get it this far.

If anyone can think of a better way to do this then please post!

Thanks
Jan 17 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.