SQL Puzzle Lite #2 4

[gmmastros]

The purpose of this puzzle is to determine which U.S. State capitals are within 100 miles of another U.S. State capital. The following script will create a StateCapitals table, and a calculate distance function.

Here are the rules...

1. Code should work on SQL 2000.
2. 1 SQL query, no temp tables, no table variables.
3. Ordered by state first, then by distance.
4. Distance rounded to the nearest 5 miles.

Nobody post code for a couple days (Let's say Thursday).

The output should look like...

[tt]
FromCapital ToCapital Distance
--------------------- ------------------------- --------
Hartford, Connecticut Providence, Rhode Island 65
Hartford, Connecticut Albany, New York 90
Hartford, Connecticut Boston, Massachusetts 95
[/tt]

The data...

Create Table StateCapitals(State VarChar(20), Capital VarChar(30), Latitude Decimal(8,5), Longitude Decimal(8,5))
Insert Into StateCapitals(State, Capital, Latitude, Longitude) 
Select 'Alabama', 'Montgomery',32.35699,-86.25782 Union All
Select 'Alaska', 'Juneau',58.37356,-134.52395 Union All
Select 'Arizona', 'Phoenix',33.70397,-112.35184 Union All
Select 'Arkansas', 'Little Rock',34.75600,-92.28483 Union All
Select 'California', 'Sacramento',38.38046,-121.55541 Union All
Select 'Colorado', 'Denver',39.72630,-104.85681 Union All
Select 'Connecticut', 'Hartford',41.78007,-72.67710 Union All
Select 'Delaware', 'Dover',39.15638,-75.49549 Union All
Select 'Florida', 'Tallahassee',30.41851,-84.20338 Union All
Select 'Georgia', 'Atlanta',33.89125,-84.07456 Union All
Select 'Hawaii', 'Honolulu',24.85983,-168.02182 Union All
Select 'Idaho', 'Boise',43.60377,-116.27292 Union All
Select 'Illinois', 'Springfield',39.82084,-89.59898 Union All
Select 'Indiana', 'Indianapolis',39.77509,-86.13216 Union All
Select 'Iowa', 'Des Moines',41.67269,-93.57217 Union All
Select 'Kansas', 'Topeka',38.98808,-95.78066 Union All
Select 'Kentucky', 'Frankfort',38.22812,-84.86971 Union All
Select 'Louisiana', 'Baton Rouge',30.44924,-91.18561 Union All
Select 'Maine', 'Augusta',44.35164,-69.80377 Union All
Select 'Maryland', 'Annapolis',38.96731,-76.61014 Union All
Select 'Massachusetts', 'Boston',42.37057,-71.02696 Union All
Select 'Michigan', 'Lansing',42.59918,-84.37197 Union All
Select 'Minnesota', 'St. Paul',44.94446,-93.09311 Union All
Select 'Mississippi', 'Jackson',32.29110,-90.19269 Union All
Select 'Missouri', 'Jefferson City',38.49403,-92.16519 Union All
Select 'Montana', 'Helena',46.70934,-112.19762 Union All
Select 'Nebraska', 'Lincoln',40.86514,-96.82313 Union All
Select 'Nevada', 'Carson City',39.15449,-119.73074 Union All
Select 'New Hampshire', 'Concord',43.23031,-71.53610 Union All
Select 'New Jersey', 'Trenton',40.28053,-74.71202 Union All
Select 'New Mexico', 'Santa Fe',35.68695,-105.93727 Union All
Select 'New York', 'Albany',42.61485,-73.97081 Union All
Select 'North Carolina', 'Raleigh',35.77376,-78.60418 Union All
Select 'North Dakota', 'Bismarck',46.98121,-100.50272 Union All
Select 'Ohio', 'Columbus',40.04011,-82.89722 Union All
Select 'Oklahoma', 'Oklahoma City',35.49161,-97.56282 Union All
Select 'Oregon', 'Salem',44.90472,-122.92172 Union All
Select 'Pennsylvania', 'Harrisburg',40.26459,-76.86964 Union All
Select 'Rhode Island', 'Providence',41.82275,-71.41445 Union All
Select 'South Carolina', 'Columbia',33.98745,-81.02486 Union All
Select 'South Dakota', 'Pierre',44.33407,-100.09420 Union All
Select 'Tennessee', 'Nashville',36.16569,-86.77810 Union All
Select 'Texas', 'Austin',30.32637,-97.77126 Union All
Select 'Utah', 'Salt Lake City',40.75610,-111.90072 Union All
Select 'Vermont', 'Montpelier',44.19906,-72.55964 Union All
Select 'Virginia', 'Richmond',37.52425,-77.49316 Union All
Select 'Washington', 'Olympia',46.98477,-122.86908 Union All
Select 'West Virginia', 'Charleston',38.32895,-81.60509 Union All
Select 'Wisconsin', 'Madison',43.06956,-89.42386 Union All
Select 'Wyoming', 'Cheyenne',41.25173,-104.56264

The calculate Distance function...

Create  Function fnCalculateDistance(@Longitude1 Float, @Latitude1 Float, @Longitude2 Float, @Latitude2 Float)
Returns Float
AS
Begin

    Declare @DeltaX Float
    Declare @DeltaY Float
    Declare @DeltaXMeters Float
    Declare @DeltaYMeters Float
    Declare @MetersPerDegreeLong Float
    Declare @CenterY Float
    Declare @gEARTH_CIRCUM_METRES Float
    
    Set @gEARTH_CIRCUM_METRES = 6378007 * 2 * 3.14159265
    
    Set @DeltaX = Abs(@Longitude2 - @Longitude1)
    Set @DeltaY = Abs(@Latitude2 - @Latitude1)
    Set @CenterY = (@Latitude1 + @Latitude2) / 2
    Set @MetersPerDegreeLong = (Cos(@CenterY * (3.14159265 / 180)) * @gEARTH_CIRCUM_METRES) / 360
    Set @DeltaXMeters = @DeltaX * @MetersPerDegreeLong
    Set @DeltaYMeters = @DeltaY * 111113.519
    Return Sqrt(@DeltaXMeters * @DeltaXMeters + @DeltaYMeters * @DeltaYMeters) / 1609.344
   
End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[yelworcm]

Nice to have the function for calculating distances. Thanks for posting that. When do you want the answer by?

 

[yelworcm]

OK, so if I read the post.....Thursday it is ;-)

 

[SajidAttar]

George,

Is provided output sample exactly correct for 'Hartford'?
I am getting below output for provided FromCapital . .

FromCapital            ToCapital                 Distance    
---------------------  ------------------------  ----------- 
Hartford, Connecticut  Providence, Rhode Island  65
Hartford, Connecticut  Albany, New York          87
Hartford, Connecticut  Boston, Massachusetts     94

My query reported 20 rows . . and above three records are on sequence number 13, 14 and 15 . . am I correct?


Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[chrissie1]

You should get 2450 rows, don't forget distance rounded to the nearest 5 miles. these are the first 15 for hartford.

Hartford, Connecticut	Providence, Rhode Island	65
Hartford, Connecticut	Albany, New York	90
Hartford, Connecticut	Boston, Massachusetts	95
Hartford, Connecticut	Concord, New Hampshire	115
Hartford, Connecticut	Trenton, New Jersey	150
Hartford, Connecticut	Montpelier, Vermont	165
Hartford, Connecticut	Augusta, Maine	230
Hartford, Connecticut	Dover, Delaware	235
Hartford, Connecticut	Harrisburg, Pennsylvania	245
Hartford, Connecticut	Annapolis, Maryland	285
Hartford, Connecticut	Richmond, Virginia	390
Hartford, Connecticut	Raleigh, North Carolina	525
Hartford, Connecticut	Charleston, West Virginia	530
Hartford, Connecticut	Columbus, Ohio	550

Christiaan Baes
Belgium

"My new site" - Me

 

[chrissie1]

sorry the first 14 then.

Christiaan Baes
Belgium

"My new site" - Me

 

[earthandfire]

chrissie, re-read George's original spec.

I get 20 rows

FromCapital                    ToCapital                      Distance    
------------------------------ ------------------------------ ----------- 
Hartford, Connecticut          Providence, Rhode Island       65
Hartford, Connecticut          Albany, New York               90
Hartford, Connecticut          Boston, Massachusetts          95
Dover, Delaware                Annapolis, Maryland            60
Dover, Delaware                Trenton, New Jersey            90
Annapolis, Maryland            Dover, Delaware                60
Annapolis, Maryland            Harrisburg, Pennsylvania       90
Boston, Massachusetts          Providence, Rhode Island       45
Boston, Massachusetts          Concord, New Hampshire         65
Boston, Massachusetts          Hartford, Connecticut          95
Concord, New Hampshire         Boston, Massachusetts          65
Concord, New Hampshire         Montpelier, Vermont            85
Concord, New Hampshire         Providence, Rhode Island       95
Trenton, New Jersey            Dover, Delaware                90
Albany, New York               Hartford, Connecticut          90
Harrisburg, Pennsylvania       Annapolis, Maryland            90
Providence, Rhode Island       Boston, Massachusetts          45
Providence, Rhode Island       Hartford, Connecticut          65
Providence, Rhode Island       Concord, New Hampshire         95
Montpelier, Vermont            Concord, New Hampshire         85

(20 row(s) affected)

 

[chrissie1]

oops, but to my defense, it wasn't in the rules.

but surely there shoud only be 10 since

Hartford, Connecticut Albany, New York 90

is the same as

Albany, New York Hartford, Connecticut 90




Christiaan Baes
Belgium

"My new site" - Me

 

[gmmastros]

Chrissie,

You are correct about Hartford to Albany being the same as Albany to Hartford. However, I wanted the results the way that earthandfire presented them.

I had a '1 day gig' writing a similar report for a customer last friday. It was, obviously, more complicated than this. It involved 5000 zipcodes representing locations for my customers customers. They wanted to know which city would be the best choice to host a conference. The idea being, "The closer they are to the conference location, the more likely they will be to attend."

With the information presented the way earthandfire did, it's easier to tell that there are 3 capitals close to Hartford, but only 1 capital close to Albany.

My query reported 20 rows . . and above three records are on sequence number 13, 14 and 15 . . am I correct?

Almost. The ordering should be by state first, then capital.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SajidAttar]

Christiaan : Offcourse I am getting 2450 records (if I remove the criteria for 100 miles per George ;-))

EarthAndFire: ok, I am getting the same result set but with one difference. function fnCalculateDistance is returning 97 (If rounded to 5, it is 100) as distance for "Concord, New Hampshire" and "Providence, Rhode Island" and in your result set it is 95.

George: Sorry, I oversight your post so I was sorting data on First column and Third column (From and Distance)

Query Plan

 [COLOR=white]
  |--Sort(ORDER BY:([a].[State] ASC, [a].[Capital] ASC, [Expr1004] ASC))
       |--Compute Scalar(DEFINE:([Expr1002]=[a].[Capital]+', '+[a].[State], [Expr1003]=[b].[Capital]+', '+[b].[State], [Expr1004]=Convert([dbo].[fnCalculateDistance](Convert([a].[Longitude]), Convert([a].[Latitude]), Convert([b].[Longitude]), Convert([b].[
            |--Nested Loops(Inner Join, WHERE:([a].[State]<>[b].[State] AND [dbo].[fnCalculateDistance](Convert([a].[Longitude]), Convert([a].[Latitude]), Convert([b].[Longitude]), Convert([b].[Latitude]))<101))
                 |--Table Scan(OBJECT:([Test_HL7].[dbo].[StateCapitals] AS [b]))
                 |--Table Scan(OBJECT:([Test_HL7].[dbo].[StateCapitals] AS [a]))

[/color]

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[earthandfire]

SajidAttar, check your rounding code. 97 rounded to the nearest 5 is 95 not 100. Additionally I tok "within 100 miles" to be <100 and not <101 as you have - although both return the same 20 records.

 

[SajidAttar]

Oops!!! Thanks for focusing me on Nearest part. .
I have update my query . . .


Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[chrissie1]

it's thursday so here goes

[white]
SELECT 	s1.Capital + ', ' + s1.State AS FromCapitale
	, s2.Capital + ', ' + s2.State AS ToCapitale
	, convert(int,round(dbo.fnCalculateDistance(s1.longitude,s1.latitude,s2.longitude,s2.latitude)/5,0))*5 AS Distance
FROM 	StateCapitals s1, Statecapitals s2
WHERE dbo.fnCalculateDistance(s1.longitude,s1.latitude,s2.longitude,s2.latitude) > 0 and dbo.fnCalculateDistance(s1.longitude,s1.latitude,s2.longitude,s2.latitude) < 100
ORDER BY fromcapitale, distance
[/white]

Christiaan Baes
Belgium

"My new site" - Me

 

[vongrunt]

How about using dbo.fnCalculateDistance(...) only once?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[chrissie1]

mmmmm.

Nope.

Christiaan Baes
Belgium

"My new site" - Me

 

[earthandfire]

chrissie

3. Ordered by state first, then by distance.

 

[chrissie1]

details, details.

Christiaan Baes
Belgium

"My new site" - Me

 

[earthandfire]

Mine:

[COLOR=white]
select 
	A.Capital + ', ' + A.State as FromCapital,
	B.Capital + ', ' + B.State as ToCapital, 
	convert(int, 
		dbo.fnCalculateDistance(A.Longitude, A.Latitude,
					 B.Longitude, B.Latitude)+ 2.5) / 5 * 5 as Distance
from 
	StateCapitals A, 
	StateCapitals B
where 
	A.State <> B.State and 
		dbo.fnCalculateDistance(A.Longitude, A.Latitude, 
					B.Longitude, B.Latitude) < 100
order by A.State, Distance
[/color]

 

[chrissie1]

And with one function call and better sorted, I think it's even faster

[white]
SELECT 	FromCapitale + ', ' + FromState
	, ToCapitale + ', ' + ToState
	, Distance
FROM (
SELECT 	s1.Capital FromCapitale
	, s1.State FromState
	, s2.Capital ToCapitale 
	, s2.State ToState
	, convert(int,round(dbo.fnCalculateDistance(s1.longitude,s1.latitude,s2.longitude,s2.latitude)/5,0))*5 AS Distance
FROM 	StateCapitals s1, Statecapitals s2
) t1
WHERE Distance > 0 and Distance < 100
ORDER BY fromState, distance
[/white]

Christiaan Baes
Belgium

"My new site" - Me

 

[SajidAttar]

Here are mine queries (second as suggested by Vongrunt)

[COLOR=white] 

select 	a.Capital + ', ' + a.State FromCapital, 
	b.Capital + ', ' + b.State ToCapital,  
	(convert(int,round(dbo.fnCalculateDistance(A.longitude,A.latitude,b.longitude,b.latitude)/5,0))*5) AS Distance
From StateCapitals a, StateCapitals b
where not (a.State = b.State) 
      and dbo.fnCalculateDistance(a.Longitude, a.Latitude, b.Longitude, b.Latitude) < 100
order by a.State, a.Capital

-- Here is using fnCalculateDistance function at once

select FroCap + ', ' + FroState as FromCapital, ToCap + ', ' + ToState as ToCapital, Distance from 
(
select 	a.State as FroState , 
	a.Capital as FroCap ,
	b.State as ToState ,  
	b.Capital as ToCap ,
	(convert(int,round(dbo.fnCalculateDistance(A.longitude,A.latitude,b.longitude,b.latitude)/5,0))*5) AS Distance
From StateCapitals a, StateCapitals b
where not (a.State = b.State) 

) c
where Distance < 100
order by FroState, FroCap
[/color]

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell