Help with a very odd query

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

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

|||Can you please show your sample query? If you are generating the data then you can perform the duplicates elimination.|||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
)

|||

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

|||I have seen people use checksum before, but what exactly is it used for?
|||

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

|||

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