locked
Help with a very odd query RRS feed

  • Question

  • I am trying to get the distance between every airport in the united states. This is fairly simple to say, but it is a very tough query for me.

    I have a table with all airports in the united states (about 20,000 of them) and I have a custom built function that finds the distance between 2 lat / long points. The function works great and is being used on a live site.

    I have the following information:
    table: dbo.apt
    ==========================
    aptcode: The unique id of every airport
    lat: the latitude of every airport
    long: the longitude of every airport

    the distance is found by doing: dbo.FindDist(lat1, long1, lat2, long2)

    This information is relatively easy to get. The probem comes when I try to filter out the duplicate information. This is what I am talking about:

    FLL to FXE is 5mi
    FXE to FLL is 5mi

    I only need one of these rows of information because they are the same.

    Another large problem I am facing is that hard drive space is limited on this specific server, so I dont have much room for temporary tables.

    PLEASE HELP!!!

    Thank you in advance.

    PS: I am running SQL Server 2000
    Tuesday, July 17, 2007 9:28 PM

Answers

  • Try the following:

     

    SELECT
          dbo.apt.AptCode,
          Destinations.AptCode,
          dbo.FindDist(dbo.apt.lat, dbo.apt.long, Destinations.lat, Destinations.long)
    FROM
          dbo.apt,
          dbo.apt Destinations
    WHERE
          dbo.apt.AptCode > Destinations.AptCode

    Wednesday, July 18, 2007 1:58 PM

All replies

  • Hello,

     

    you should be able to add a where clause

     

    select a.aptcode,b.aptcode, distcalc... FROM apt a

    CROSS JOIN apt b

    WHERE a.aptcode> b.aptcode

     

    /P

     

    Tuesday, July 17, 2007 9:42 PM
  • Can you please show your sample query? If you are generating the data then you can perform the duplicates elimination.
    Tuesday, July 17, 2007 10:04 PM
  • This is what I have for the query:

    Sample Query

    INSERT INTO distance (aptcode1, aptcode2, distance)
    SELECT
        tab1.aptcode AS aptcode1,
        tab2.aptcode AS aptcode2,
        dbo.FindDist(tab1.reflat_s,tab1.reflong_s,tab2.reflat_s,tab2.reflong_s) AS distance
    FROM
    (
        (
            SELECT
                aptcode,
                reflat_s,
                reflong_s
            FROM apt
        ) tab1
        FULL JOIN
        (
            SELECT
                aptcode,
                reflat_s,
                reflong_s
            FROM apt
        ) tab2
        ON 1 = 1
    )


    Wednesday, July 18, 2007 12:41 PM
  • Code Snippet

     

    create table #apt (aptcode varchar(5), lat numeric(18,9), long numeric(18,9))

    insert into #apt

    select 'FLL', 0, 0

    union all select 'FXE', 0, 0

    union all select 'RDU', 0, 0

    union all select 'LAS', 0, 0

     

    select a1.aptcode as orig, a2.aptcode as dest, a1.cksum + a2.cksum as hash, identity(int, 1,1) as row

    into #list

    from

        (select aptcode, checksum(aptcode) as cksum from #apt ) a1

    full join

       (select aptcode, checksum(aptcode) as cksum from #apt ) a2

    on a1.aptcode <> a2.aptcode

     

    select orig, dest

    from #list l

    inner join

    (

        select hash, min(row) as row

        from #list

        group by hash

    ) h

    on  l.hash = h.hash

    and l.row = h.row

    order by orig

     

    Wednesday, July 18, 2007 12:56 PM
  • I have seen people use checksum before, but what exactly is it used for?
    Wednesday, July 18, 2007 1:22 PM
  •  

    It computes a hash value for a row or a given set of columns/fields

    Wednesday, July 18, 2007 1:28 PM
  • Try the following:

     

    SELECT
          dbo.apt.AptCode,
          Destinations.AptCode,
          dbo.FindDist(dbo.apt.lat, dbo.apt.long, Destinations.lat, Destinations.long)
    FROM
          dbo.apt,
          dbo.apt Destinations
    WHERE
          dbo.apt.AptCode > Destinations.AptCode

    Wednesday, July 18, 2007 1:58 PM