Results 1 to 14 of 14

Thread: Query Help

  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Unanswered: Query Help

    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. #2
    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. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not sure that old Pythagoras works on spherical coordinates, but the TOP 5 clause is what you are looking for.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    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. #5
    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. #6
    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. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    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. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    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. #11
    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. #12
    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. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT TOP 5 *
    FROM Table
    ORDER BY closestfac()
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    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.

    Thanx for all your help!!
    Last edited by boyzoid; 03-15-04 at 13:57.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •