Description

The goal of this assignment is to design and query a spatial database using Oracle Express 11g. We will NOT accept other databases (e.g., MySQL, PostgreSQL, etc.).

Project specification:
Panthera, the world’s leading cat conservation organization, is focusing on saving endangered Lions in African national park. They have divided the park into multiple regions and purchased a number of ambulances to provide emergency health care service to Lions. Ambulances are parked at various locations in the park to cover the nearby lions. Park also has several ponds where Lions go to drink water.
You are given 4 files for this project:

● region.txt
● lion.txt
● ambulancearea.txt ● pond.txt

These files store the spatial data in the following format:

● region.txt. Each region is represented by a 2-dimensional polygon. Each line in this file represents a region and the meanings of the columns are: (1) Region ID; (2) Number of vertices on the polygon (denoted n); (3) The following 2n columns are the coordinates of the vertices, respectively, where the x-coordinate and y-coordinate of each vertex is represented by two consecutive columns. For example, the line “r1, 4, 100, 120, 150, 130, 120, 200, 120, 220” represents a region with region ID “r1”. It has 4 vertices whose coordinates are (100, 120), (150, 130), (120, 200) and (120, 220), respectively.

● lion.txt. Each Lion is represented as a 2-dimensional point and each line represents a Lion. The columns are: (1) The Lion ID; (2) The x-coordinate of the Lion; (3) The y- coordinate of the Lion.

● pond.txt: Each pond is represented by a circle and each line in the file represents a
pond. The columns are: (1) pond id; (2) The x-coordinate of the centre of the pond; (3) The y-coordinate of the centre of the pond; (4) radius of the pond.
● ambulancearea.txt: Imagine an ambulance area to be circule where centre represents the location of the ambulance and radius represent the area covered by the ambulance. Each line in the file represents an ambulance area. The columns are: (1) ambulance id; (2) The x-coordinate of the ambulance location; (3) The y-coordinate of the ambulance location; (4) radius it covers.

Assignment:
Submit three SQL files as follows:

File 1) createdb.sql (30 pts)
● We will use this SQL file to create and populate the database that is used for this project on Oracle Express 11g.
● You need to design the tables and assign data types to attributes such that the information of the lions, regions and ambulances can be accessed and manipulated.
● You must use spatial data types such as SDO_GEOMETRY to store location data. For example, rather than defining two integers to store x and y coordinates, you need to create one column of SDO_GEOMETRY type and store both coordinates as a point object.
● You must create spatial index for each tablename-geometry column.

File 2) dropdb.sql (10pts)
● This file will be used to clean up all tables, indices and other objects that are created by createdb.sql.

File 3) queries.sql (60 pts)
This file should contain the following queries. Each query worth 10 points.

a) Find all lions inside the query window (150, 200) (400, 350). The coordinates indicate (x, y) values of the lower left and upper right vertices of query window, respectively.

b) Find all the lions which are at most 150 units away from the ponds: P1.

d) Find 3 nearest ponds to the lion: L2. Nearest neighbors must be ordered from the closest to the furthest one.

f) List all the lions and ponds where lion is inside the pond.(use spatial join)

h) List all the region/s which has at-least one lion but no pond.

i) Find all the lions which are not in the coverage area of any amubulance.

Useful Links:
● Oracle Express 11g Download: http://www.oracle.com/technetwork/database/database-technologies/express-edition/ downloads/index.html

● Oracle Spatial Tutorial:
http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/toc.htm

Notes for Mac Users:
● Note: Though we are providing following instructions to help you in the installation of oracle db on Mac but please don’t expect us to answer queries related to installation on d2l/email.
● Step 1. Install VirtualBox software from h ttps://www.virtualbox.org/ ● Step2. Download Ubuntu “12.04 64 bit”:
http://releases.ubuntu.com/12.04/ubuntu-12.04.5-desktop-amd64.iso
● Step3: Install “Ubuntu 12.04 64 bit” inside virtualBox by creating a virtual machine(h ttps://docs.oracle.com/cd/E26217_01/E26796/html/qs-create-vm.html) ● Step4. Do the following on Ubuntu
○ Open terminal
○ run “sudo apt-get update”
○ run “sudo apt-get upgrade”
○ run “s udo apt­get install virtualbox­guest­x11” to fix full screen resolution issue ○ Follow the blog post to install oracle db: http://meandmyubuntulinux.blogspot.com/2012/05/installing-oracle-11g-r2-exp ress.html

● Note: instead of installing Ubuntu inside VirtualBox you can also install Windows inside VirtualBox and then install oracle db on the windows. VIterbi students can download windows for free by following instructions on the link:
http://viterbi.usc.edu/resources/vit/services/dreamspark.htm.

Extra Credit(30 points):
Note: This is not mandatory and please do it only after completing Mandatory part of the homework 5.

Description: In this part of the assignment you’ll write a Java program to create an application with a GUI, which allows users to interact with the spatial data provided in the first part of the homework. You will use JDBC in your java program to communicate with the oracle database.
Specification:
When the user runs your java program, it must fetch all regions, all ponds and all lions from the oracle database and show them in the GUI. Here are the original colors that you should use to show the geometries:
● boundary/border of each region must be displayed in black color
● interior of each regions must be displayed in white color
● boundary/border of each pond must be displayed in black color
● interior of each pond must be displayed in blue color
● each lion must be displayed in green color
The GUI should interact with the user in the following way:
● Display a checkbox with title “show lions and ponds in the selected region”
● If the checkbox “show lions and ponds in selected region” is checked and the user clicks on a region, the GUI must show all the lions and ponds inside the region in red color.
● After clicking on one region, if the user clicks on another region, lions and ponds in the previously clicked region must be reset to their original colors.
● If the user unchecks the checkbox “show lions and ponds in selected region”, all the lions and ponds must be reset to their original colors.

Useful Links:
● Java 2D api tutorial https://docs.oracle.com/javase/tutorial/2d/index.html
● JDBC tutorial https://docs.oracle.com/javase/tutorial/jdbc/