In this section, you will be executing spatial queries on our three tables. Here is some spatial query examples:
What is the distance from the center of city of "Los Angeles" to the center line of river of "Mississippi"
SELECT SDO_GEOM.SDO_DISTANCE(r.GEOM, c.GEOM, 0.5) as distance
FROM csci5715.RIVERS r, csci5715.CITIES c
WHERE r.name = 'Mississippi'
AND c.name = 'Los Angeles';
DISTANCE
1340744.42
Tasks
Provide the SQL statements and results to the following questions using spatial queries:
Note. Use tolerance value of 0.5, if unspecified.
Where is the centroid of "Belgium"?
Provide the Minimum Orthogonal Bounding Rectangles (MBR) for "United States"
Show the names of all countries and order them by area in ascending order.
Name the cities within 100000 units distance of the river of "Mississippi" (explicitly use tolerance of 0.5) and the corresonding countries they fall into. (Hint: SDO_CONTAINS, SDO_GEOM.SDO_DISTANCE)
Note. The query may not result any rows for the distance of 100000 units. Use of larger distances (e.g., 500000, 1000000) will give non-zero results.
What is the distance from the river "Mekong" to the city of "Hanoi"? (get tolerance from USER_SDO_GEOM_METADATA table) Note. You may need to use two tolerance values each for the city and the river.
What is the distance between the centroid of "United States" to the river "Mississippi"?
Find the name of the second longest river.
Which country in the COUNTRIES table has the largest area to population ratio in the year 2005?
http://www.spatial.cs.umn.edu/Courses/Fall22/5715/index.php?page=homeworks/lab