1. Registered User
Join Date
Mar 2004
Posts
7

I have a table that has 4 columns:

ID,Facility_name,latitude,longitude

I need a result set that returns the facility name, as well as the ID of 5 closest facilities based on the latitude and longitude. The result set would look like this:

facility_name,closest_facility,second_closest_faci lity,third_closest_facility,fourth_closest_facilit y,fifth_closest_facility

I already have the equation to convert the difference in lat and long into mileage. I have a query that will return the 5 closest facilities based on a chosen facility ("SELECT.....WHERE ID=6").

I am having a difficult time creating a query or stored procedure that will return the 5 closest facilities for all the records in the table.

Any help would be greatly appreciated.

2. Just DBA
Join Date
Jul 2002
Location
Village, MD
Posts
621
Do you mean something like this?

drop table #tmp
create table #tmp(id int,facility_name varchar(10),latitude int,longitude int)
insert #tmp values(1,'a',12,13)
insert #tmp values(2,'b',11,15)
insert #tmp values(3,'b',6,21)
insert #tmp values(4,'b',3,3)
insert #tmp values(5,'b',4,5)
insert #tmp values(6,'b',7,8)
insert #tmp values(7,'b',9,4)
insert #tmp values(8,'b',20,10)
insert #tmp values(9,'b',1,13)
insert #tmp values(10,'b',13,1)

declare @lat int,@long int
set @lat=5
set @long=6
select top 5 id,facility_name,latitude,longitude
from #tmp
order by power(abs(latitude-@lat),2)+power(abs(longitude-@long),2)

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Not sure that old Pythagoras works on spherical coordinates, but the TOP 5 clause is what you are looking for.

4. Registered User
Join Date
Mar 2004
Posts
7
I need to return the 5 closest facilities for ALL of the facilities in the table. So the result set would look like:

ID,facility,close1,close2,close3,close4,close5
1,"Macy's",2,4,5,3,6
2,"Sears",1,5,6,3,4
3,"McDonald's",4,5,6,1,2
4,"Burger King",2,1,5,6,3
5,"Home",6,4,3,2,1
6,"School",5,4,3,2,1

The five numbers would be the id of the 5 closest facilities. So, in my example the closest facilities to home, in order of closest to furthest, would be:

School,
Burger King,
Mc Donald's,
Sears,
Macy's

It doesn't matter if the closest facilities are in seperate columns or grouped in a list somehow, I just need to get the 5 closest for all facilities.

Thanx again.

5. Registered User
Join Date
Jan 2004
Posts
26
I am little confused...if you have a query 'select...where ID=6' can return the 5 closest facilities based on a chosen facility,then why can't the query return results for all records?(just remove the last 'where' clause in 'select...where ID=6')

or you mean you want a crosstab view after you get the 5 closest facilities in list?
Last edited by cinrain; 03-13-04 at 15:47.

6. Registered User
Join Date
Mar 2004
Posts
7
This is the query (in a stored procedure) that will return the 5 closest facilities for a given facility ID(the @lat and @long are the lat and long for the given facility which is @facID:

SELECT top 5 facilities.name,
ROUND((ACOS((SIN(@lat/57.2958) * SIN(lat/57.2958)) +
(COS(@lat/57.2958) * COS(lat/57.2958) *
COS(long/57.2958 - @long/57.2958)))) * 3963, 3) AS
distance
from facilities
and facid <> @facID
order by distance

How can I use this to return this information for ALL facilities, not just the one represented by @facID?

7. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
I'm sure somebody with more time and masochistic tendencies than me could figure out a way to do this as a single SELECT statement, but this sucker is going to run SLOW whatever you do.

For the sake of sanity (and reusability), just write your code as a UDF and reference it in your queries.

--Untested code--
declare Closest5Facilities(@facid)
returns @closest5 as table (name varchar(50), facid int, distance decimal(16,4))
as
insert into @closest5
SELECT top 5 facilities.name, facilities.facid
ROUND((ACOS((SIN(@lat/57.2958) * SIN(lat/57.2958)) +
(COS(@lat/57.2958) * COS(lat/57.2958) *
COS(long/57.2958 - @long/57.2958)))) * 3963, 3) AS
distance
from facilities
where facid <> @facID
order by distance
return @closest5
end
--
You could also write the UDF to return a single record with the five values as columns.

8. Registered User
Join Date
Jan 2004
Posts
26
how about use a cursor and dynamic SQL?

-----------------------------------------------------------------------------
not very sure...but I think it's impossible to do the whole work for all facilities in one SELECT statement

9. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
No, its possible. Just complicated and ineffecient with several layers of subqueries.

If you create the UDF, you can use it in a cursor if you like but it wouldn't be necessary. And like I said before, as a UDF you could reuse the algorithm in other code.

10. Registered User
Join Date
Mar 2004
Posts
7
Blindman, thanx.

How would I get this code into a UDF. I have never used them before and I am not quite sure of the syntax. I tried to copy-paste your code into a new UDF and I was getting syntax errors.

Thanx again!

11. Registered User
Join Date
Mar 2004
Posts
7
OK, I was able to successfully modify Blindman's code to create the UDF.

Here is the UDF:

CREATE FUNCTION closestFac (@facID int,@lat float,@long float)
RETURNS @closest5 table (facid int, distance decimal(16,4)) AS
BEGIN
insert @closest5
SELECT top 5 facilities.facid,
ROUND((ACOS((SIN(@lat/57.2958) * SIN(lat/57.2958)) +
(COS(@lat/57.2958) * COS(lat/57.2958) *
COS(long/57.2958 - @long/57.2958)))) * 3963, 3) AS
distance
from facilities
where facid <> @facID
order by distance
return
end

Now how do I call it? I have tried closestfac(). Any ideas?

Thanx.

12. Registered User
Join Date
Mar 2004
Posts
7
Nevermind, I got it.

However, I have one more question. How do use this function in a query to return the 5 closest for all facilities?

13. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
SELECT TOP 5 *
FROM Table
ORDER BY closestfac()

14. Registered User
Join Date
Mar 2004
Posts
7
I wound up modifying the UDF even more, and was able to return the results I wanted in the format I wanted.
The best format for the list of closest facilities was actually a comma delimited list.

Here is the modified UDF:

CREATE FUNCTION njmedpre91netfp.closestFacs (@facID int)
RETURNS varchar(50) AS
BEGIN
declare @lat float
declare @long float
declare @closest5 table (returnID int identity(1,1),facid int, distance decimal(16,4))
declare @returnFacList varchar(50)

set @lat = (select lat from facilities where facid = @facID)
set @long=(select long from facilities where facid = @facid)

insert @closest5
SELECT top 5 facilities.facid,
ROUND((ACOS((SIN(@lat/57.2958) * SIN(lat/57.2958)) +
(COS(@lat/57.2958) * COS(lat/57.2958) *
COS(long/57.2958 - @long/57.2958)))) * 3963, 3) AS
distance
from facilities
where facid <> @facID
order by distance

set @returnFacList =cast( (select facid from @closest5 where returnID = 1)as varchar(3))
set @returnFacList =@returnFacList +','+cast( (select facid from @closest5 where returnID = 2)as varchar(3))
set @returnFacList =@returnFacList +','+cast( (select facid from @closest5 where returnID = 3)as varchar(3))
set @returnFacList =@returnFacList +','+cast( (select facid from @closest5 where returnID = 4)as varchar(3))
set @returnFacList =@returnFacList +','+cast( (select facid from @closest5 where returnID = 5)as varchar(3))
return @returnFacList
end

I anyone knows a way to streamline this, please let me know.

I get the info I need by using this (it is simplified for ease of explanation)

select facid, closestFacs(facid)
from facilities

Blindman-This doesn't runn all that slow. Though, I only have 83 facilities in the table.