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:

• you want to implement a function that given two stations returns a connection between the two stations with the smallest number of train changes (i.e. minimize the number of times you need to change from one line to another line). Hint: this will require a different network from the one that we set up.
• for every line you have the details of each run, that is, you know the details of every train: what line does it belong to, at what time does it arrive at a particular stop, at what time does it leave a particular stop; how would you model this, so you can write a function that finds you the fastest connection between two given stations, assuming all trains run exactly on time and you need a minute to change trains at a stop.

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 ...)