How to solve the Landmarks Puzzle in SQL
April 20th, 2010 By Frank Zhou
The following is an puzzle posted by Itasofware on the web :
The file landmarks.xml contains a list of landmarks near ITA Software, along with their latitude and longitude in degrees. Write a program which allows users to choose a landmark and search for others that are nearby.
———————————————————————————————————————————————-
This problem is solved by using the following formula and assumptions:
The radius of the Earth is assumed to be 3,963.0 miles.
To convert latitude or longitude from decimal degrees to radians, divide the latitude and longitude values in this database by 57.29577951.
Distance (In Miles) Formula using radians:
3963.0 * arccos[sin(latitude1) * sin(latitude2) + cos(latitude1) * cos(latitude2) * cos(longitude2 - longitude1)]
COLUMN Input_landMark FORMAT A32
COLUMN LandMark_nearBy FORMAT A32
variable input_name varchar2(38)
variable num_landMark number
exec :input_name:='11 Tewksbury Towers'
exec :num_landMark := 10
-----------------------------------SQL Solution----------------------------------
WITH DATA AS
(SELECT landmark_Name, latitude/57.29577951 as latitude , longitude/57.29577951 as longitude
FROM XMLTABLE('//landmark'
passing httpuritype('http://www.itasoftware.com/careers/puzzles/landmarks.xml').getXML()
columns
landmark_Name varchar2(30) path 'name',
latitude number path 'latitude',
longitude number path 'longitude' )
)
SELECT CASE WHEN ROWNUM = 1 THEN Input_landMark END AS Input_landMark, LandMark_nearBy, distance
FROM
(SELECT A.landmark_Name AS Input_landMark, B.landmark_Name AS LandMark_nearBy,
3963 * ACOS (SIN(A.latitude) * SIN(B.latitude) + COS(A.latitude) * COS(B.latitude) * COS (B.longitude - A.longitude)) as distance
FROM DATA A, DATA B
WHERE A.landmark_Name = :input_name
AND A.landmark_Name != B.landmark_Name
ORDER BY distance ASC
)
WHERE ROWNUM <=:num_landMark;
INPUT_LANDMARK LANDMARK_NEARBY DISTANCE
-------------------------------- -------------------------------- ----------
11 Tewksbury Towers Dog Pound 1.28621194
Department of Public Works 1.30290585
Tewksbury Cemetery 2.51978566
Parlmont Park 3.49041395
River Place Towers 4.47299171
Avalon Drive 4.64667122
Drum Hill Shopping Center 6.35347443
Greenbriar Estates 6.66743013
Hallmark Gardens 8.02860427
Scotty Hollow Drive 8.56254678
10 rows selected.
exec :input_name:='Dog Pound'
PL/SQL procedure successfully completed.
SQL> /
INPUT_LANDMARK LANDMARK_NEARBY DISTANCE
-------------------------------- -------------------------------- ----------
Dog Pound Department of Public Works .274595687
11 Tewksbury Towers 1.28621194
Parlmont Park 2.2136777
Tewksbury Cemetery 2.49289556
River Place Towers 4.77456746
Avalon Drive 4.79217856
Drum Hill Shopping Center 5.99881959
Hallmark Gardens 7.01671677
Greenbriar Estates 7.02086724
Bedford Village 7.40305675
10 rows selected.
-------------------------------------------------------------------------------------
The "With DATA Clause" above can also be implemented as following :
WITH DATA AS
(SELECT EXTRACTVALUE(VALUE(P), '/landmark/name/text()') landmark_Name ,
EXTRACT(VALUE(P), '/landmark/longitude/text()').getNumberVal()/57.29577951 longitude,
EXTRACT(VALUE(P), '/landmark/latitude/text()').getNumberVal() /57.29577951 latitude
FROM TABLE(XMLSEQUENCE(HTTPURITYPE('http://www.itasoftware.com/careers/puzzles/landmarks.xml').GETXML().EXTRACT('//landmark'))) P
)