Speed Up this Distance Query

[jkelly2956]

Can anyone give me any suggestions on speeding this query up. I have indexes on the lat and longs in both tables. Basically I have a list of places in W_Data....and a list of alternate names for those places in ULocator. I want to do a search for all places where a name contains 'al' and if the original name or one of its alternates is a hit.. I want to show the original name and lat and long.

SELECT wd.pName,wd.DD_Lat,wd.DD_Long
FROM W_Data wd
LEFT JOIN ULocator ul ON ( 69.09 * DEGREES( ACOS( SIN( RADIANS( ul.Latitude ) ) * SIN( RADIANS( wd.DD_Lat ) ) + COS( RADIANS( ul.Latitude ) ) * COS( RADIANS( wd.DD_Lat ) ) * COS( RADIANS( ul.Longitude - wd.DD_Long ) ) ) ) ) < .1
WHERE ul.pName
LIKE '%al%' OR wd.pName
LIKE '%al%'

 

[maswien]

Try following, let me know if it's more effective than the original, thanks.


select
t1.pName,
t1.DD_Lat,
t1.DD_Long
from
(
SELECT
pName,
DD_Lat,
DD_Long
FROM
W_Data wd
where wd.pName LIKE '%al%'
) t1 left join
(
SELECT
Latitude,
Longitude
FROM
ULocator
where pName LIKE '%al%'
) t2
on ( 69.09 * DEGREES( ACOS( SIN( RADIANS( t2.Latitude ) ) * SIN( RADIANS( t1.DD_Lat ) ) + COS( RADIANS( t2.Latitude ) ) * COS( RADIANS( t1.DD_Lat ) ) * COS( RADIANS( t2.Longitude - t1.DD_Long ) ) ) ) ) < .1

 

[maswien]

Sorry, it's not right. please ignore that, by the way, can you post a little more background about what this SQL used for? Thanks.

 

[petermeachem]

I don't know how quick all those trig functions are but...

What I used to do on flight sims to get round the relatively huge amount of time trig and multiplies took is to not work it out exactly. The distance is roughly abs(lat1 - lat2 ) + abs(long1 - long2). Make this a bit smarter if you need by multiplying the delta long by cos average lat. Use the first two elements of a series for the cos, can't remember that bit. If the places are pretty close dispense with the average lat and just use one of them. You'll get an answer in degrees (I assume that is what your lat longs are in)

I hope this is helpful. I spent 15 years doing navigation stuff and this is the first time anyone has asked anything relevant that I've seen.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[thendrickson]

Using wild cards in the beginning of a search string also slows things down.

Would it be possible to perform the search somewhat differently?

Just a thought. Might not be possible or practical in your case

 

[dbomrrsm]

Dont know if this would be of any help or if it would whether it needs tweaking for your needs but here goes:

--This procedure is used to calc miles distance from two points on earth. You can get latitude and longitude for places on earth, then just use this procedure to calc how many miles are in between.
--Great FOR calculating travel miles (as the crow flys).

CREATE PROC dbo.aw_MilesLatLong (
@P_Lat1 decimal(8,4) =0 ,
@P_Long1 decimal(8,4) =0 ,
@P_Lat2 decimal(8,4) =0 ,
@P_Long2 decimal(8,4) =0 ,
@P_DiffMiles decimal(10,2) =0 output
)
As


BEGIN
/*************************************************************/
/* Routine TO calc miles distance FROM two points on earth */
/* specified IN latitude/longitude pairs.*/
/* Inputs are "degrees.minutes" ie: 33.0654 */
/*************************************************************/
SET nocount ON
DECLARE @Lat1 decimal(28,10)
DECLARE @Long1 decimal(28,10)
DECLARE @Lat2 decimal(28,10)
DECLARE @Long2 decimal(28,10)

DECLARE @d decimal(28,10)
/* DEFAULT RETURN value */
SET @P_DiffMiles = 0

/* CONVERT TO radians */
SET @Lat1 = @P_Lat1 / 57.2958
SET @Long1 = @P_Long1 / 57.2958
SET @Lat2 = @P_Lat2 / 57.2958
SET @Long2 = @P_Long2 / 57.2958

/* Calc distance */
SET @d = (Sin(@Lat1) * Sin(@Lat2)) + (Cos(@Lat1) * Cos(@Lat2) * Cos(@Long2 - @Long1))

/* CONVERT TO miles */
IF @d <> 0


BEGIN
SET @P_DiffMiles = 3958.75 * Atan(Sqrt(1 - power(@d, 2)) / @d)
END
end

 

[SQLSister]

Why on earth are you joining on a calculation? IF this is something you will need to frequently call, it might be better to precalculate in your table at the time the records are entered. This would have to make for an extremely slow join as it stands.

Wildcard searches that use the wildcard inthe first character mean that the indexes cannot be used. THis too will slow things down.



Questions about posting. See faq183-874

 

[jkelly2956]

Basically I have a list of City's with the the english spelling (main city list). I also obtained a list of alternate city spellings(with cyrllic etc) that I want to allow searches by as well but match to the main city.


The reason I am joining on a calculation is I want to join the two records if they are no further than a tenth of a mile from each other. I obtained this data from a third party and the only thing they have in common is lat and long...but they are not always exact ( 12.12222 vs 12.12320). I think I will take SQLSister's advice and run a query that calculates which main city the alternates are close to..and then mark the alternates with a key from the main city.

 

[thendrickson]

Why the 'al'? or was that just an example?

 

[jkelly2956]

Yes, the %al% was just an example.

 

[SQLSister]

J kelly, if this list is not being continallly updated, you might consider going through once and marking which city that each alternate spelling relates to. Again that gets you out of using that slow search on wildcards.

Questions about posting. See faq183-874

 

[dokR]

Seeing this thread, I wonder whether somebody also has the formula for the great-circle distance in nautical miles between aircraft flying at different altitudes?