Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Puzzle Lite #2 4

Status
Not open for further replies.
gmmastros

gmmastros

Programmer
Feb 15, 2005
14,905
US
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...
Code: 
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...
Code: 
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
 
Sort by date Sort by votes
That's what I had in mind - derived table and State1<>State2 instead of distance> 0.


------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why
[banghead]
 
Upvote 0 Downvote
First attempt
Code: 
[COLOR=white]SELECT 
	a.Capital + ', ' + a.State As FromCapital, 
	b.Capital + ', ' + b.State As ToCapital, 
	Round(dbo.fnCalculateDistance(
		a.Longitude, 
		a.Latitude, 
		b.Longitude, 
		b.Latitude)/5,0) * 5 As Distance
	FROM 
		dbo.StateCapitals a, dbo.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]
Second attempt, with only one function call. Without vongrunts hint on "derived table", and some search I wouldn't have been able to do this
Code: 
[COLOR=white]SELECT 
	s.FromCaptial, s.ToCapital, s.Distance 
FROM
	(SELECT	
		a.Capital + ', ' + a.State As FromCaptial, 
		b.Capital + ', ' + b.State As ToCapital, 
		Round(dbo.fnCalculateDistance(
			a.Longitude, 
			a.Latitude, 
			b.Longitude, 
			b.Latitude)/5,0) * 5 As Distance,
		a.State As From_State
	FROM 
		dbo.StateCapitals a, dbo.StateCapitals b 
	WHERE
		a.State <> b.State		
	) AS s
WHERE
	s.Distance <= 100
ORDER BY 
	s.From_State, s.Distance[/color]

Roy-Vidar
 
Upvote 0 Downvote
All very good queries, and with small data sets, works just find (speed wise, that it). When there are more cities, performance becomes problematic with all queries shown. Good work, though. I noticed that you all worked pretty hard to get the distance function to only calculate once. It would have been time better spent reducing the number of calculation another way. By reducing the number of rows to calculate, you will gain more speed improvements.

Before awarding stars (and you'll all be getting them), let's see if we can improve this a little.

Here's a hint.
Code: 
Declare @HawaiiLatitude Decimal(8,5)
Declare @HawaiiLongitude Decimal(8,5)

Declare @AlaskaLatitude Decimal(8,5)
Declare @AlaskaLongitude Decimal(8,5)

Set @HawaiiLatitude  =   24.85983
Set @HawaiiLongitude = -168.02182

Set @AlaskaLatitude  =   58.37356
Set @AlaskaLongitude = -134.52395


Select dbo.fnCalculateDistance(@HawaiiLongitude - 1,
                               @HawaiiLatitude - 1,
                               @HawaiiLongitude + 1,
                               @HawaiiLatitude + 1)


Select dbo.fnCalculateDistance(@AlaskaLongitude - 1,
                               @AlaskaLatitude - 1,
                               @AlaskaLongitude + 1,
                               @AlaskaLatitude + 1)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
We should probably create a rule that people with more than 100 votes should not be allowed to participate in the SQL Lite/Diet puzzles
Yes or no? or they can show what they would have done but they can not get stars
what do you think?

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
Hint #2: don't hard-code Hawaii or Alaska or Madison vs Phoenix or whatever, this is cheating. What George was talking about is called "bounding boxes".

> Yes or no? or they can show what they would have done but they can not get stars

I agree. No stars for us. And we can do only hints here and there.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why
[banghead]
 
Upvote 0 Downvote
Look! There's a new forum just for puzzles. I suppose we should continue the puzzle threads in the new forum.

forum1551

Anyone want the honors of posting the first puzzle? Better hurry!

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
This I probably don't understand, but played around a little - is this what you mean?
Code: 
[COLOR=white]declare @Box decimal(5,2)
set @Box = 1.43
SELECT
    s.FromCaptial, s.ToCapital, s.Distance
FROM
    (SELECT    
        a.Capital + ', ' + a.State As FromCaptial,
        b.Capital + ', ' + b.State As ToCapital,
        Round(dbo.fnCalculateDistance(
            a.Longitude,
            a.Latitude,
            b.Longitude,
            b.Latitude)/5,0) * 5 As Distance,
        a.State As From_State
    FROM
        dbo.StateCapitals a, dbo.StateCapitals b
    WHERE
        a.State <> b.State AND
		a.Latitude - @Box <= b.Latitude AND	
		a.Latitude + @Box >= b.Latitude AND
		a.Longitude - @Box <= b.Longitude AND	
		a.Longitude + @Box >= b.Longitude 
    ) AS s
--WHERE
  --  s.Distance <= 100
ORDER BY
    s.From_State, s.Distance[/color]

Roy-Vidar
 
Upvote 0 Downvote
Close.

It's the right idea but the wrong implementation. Your query returns the wrong data, and you really want the difference betweent the lat's and the difference between the lon's to be less than X.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
> It's the right idea but the wrong implementation.

I'd say @Box value is slightly off - considering the fact Earth is not flat :)

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why
[banghead]
 
Upvote 0 Downvote
Here's what I ended up with.

Code: 
ALTER  Procedure TekTips_gmmastros
as
Select RTrim(A.Capital + ', ' + A.State) As FromCapital,
       B.Capital + ', ' + B.State As ToCapital,
       Convert(int, dbo.fnCalculateDistance(A.Longitude,
                                            A.Latitude,
                                            B.Longitude,
                                            B.Latitude) / 5) * 5  As Distance
From   StateCapitals A
       Inner Join StateCapitals B
         On A.State <> B.State
[!]         And Abs(A.Longitude - B.Longitude) < 2
         And Abs(A.Latitude - B.Latitude) < 2[/!]
Where	dbo.fnCalculateDistance(A.Longitude, 
                                A.Latitude, 
                                B.Longitude, 
                                B.Latitude) < 100
Order By A.State, Distance

GO

declare @S DateTime
Set @S = GetDate()
exec tektips_gmmastros
select datediff(Millisecond, @S, GetDate())

Notice that on the join, I check the difference in lat long, this prevents me from having to calculate the distance from Hawaii to Boston.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Vongrunt said:
I agree. No stars for us. And we can do only hints here and there.
Click to expand...

And what if we [small](having less than 100 stars )[/small] want to give you [small](having more than 100 stars )[/small] a star for your Valuable hints?

Code: 
[COLOR=white]I tried to add star image in this post to give you guys star like 
Vongrunt *
GMMastros *
SQLDenis * 

But could not. Please tell me how to  so I can give you star that way[/color]




Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell
 
Upvote 0 Downvote
You mean like this?

Vongrunt
star.gif

GMMastros
star.gif

SQLDenis
star.gif


Right click on a star and look at its properties:

[ignore][/ignore], then place it inside an img tag like this:

[ignore]
star.gif
[/ignore] and you get
star.gif



Hope this helps





[vampire][bat]
 
Upvote 0 Downvote
Thanks, I tried that, It did not show me the
star.gif
in preview.

OK, Here are the stars for the Masters
Vongrunt
star.gif

GMMastros
star.gif

SQLDenis
star.gif

EarthAndFire
star.gif
:) [small] I have given a one voteStar for pointing out my rounding mistake because of that I could get the star here [/small]



Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
602
pjw001
pjw001
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
560
Volvere
V
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
354
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
T-SQL Dialog Box
Replies
8
Views
330
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
398
SaltyTheFrog
SaltyTheFrog

Part and Inventory Search

Sponsor

Back
Top