We finished talking about spatial networks in Oracle; you can find all of this material in chapter 10 of Pro Oracle Spatial, by Kothuri, Godfrind and Beinat , Apress, 2004 (available online through books 24 x 7 at the library, also through books.google.com).
The material on Oracle Spatial is based on chapters 4, 7, 8, and 9 from the same book.
Submission: you can submit the homework by hardcopy in class or by sending it to me as an email.
1. [Networks, 25pt] Modify our CTA network and tables as follows:
a) [8pt] Add a---fictitious---crimson line that runs downtown in a circle: Adams, Jackson, Clark, State and Adams (I know that this would seriously mess with existing downtown infrastructure).
b) [7pt] For the tables add the following heuristics for travel time: standing at each stop or changing to a different line takes 2 minutes, a ride between two stops takes 5 minutes.
c) [8pt] Write a function that given two stations calculates the fastest (in terms of the times you specified in b) route(s) between the stations and prints it out.
2. [Circles, 15pt] In Oracle Spatial, circles are a special type of polygon (gtype 2003 with descriptor triplet (1,1003, 4). Circles are described by three distinct points on the circumference of the circle. Write a function that as input takes coordinates of the center of the circle and its radius and constructs an sdo_geometry object corresponding to that circle.
3. [my_world, 20pt] Modern life has made it into our parts of the woods, and cell-phone towers are being erected in my_world.
a) [5pt] To my_world add a new table for cell-phone towers. Each cell-phone tower has a name, a base location (a point) and a radius in which it operates. Add 4 towers to our landscape (with varying radii, choose them so the following queries have interesting answers), and write queries for the following:
b) [5pt] Determine the points of interest that are not covered by any cell-phone tower.
c) [5pt] Determine regions not covered by the cell-phone tower. (That is, calculate an sdo_geometry object of that region; use set operations.)
d) [5pt] Given a point as an input (x and y coordinate), determine the closest cell-phone tower covering that point. Return null if the point is not covered by a cell-phone tower.
4. [Extra Credit] Describe in reasonable details (but don't implement) solutions to the following problems using a spatial network model and shortest_path analysis:
5. [Extra Credit] A point in our world is on an island, if it is entirely surrounded by a lake; write a function (using PL/SQL) that takes as input an entry from my_poi and determines whether that point is on an island. (Without hardcoding the answer ...)