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 ycoordinate 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.
CSCI 585
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.
● To use Oracle Express 11g on Mac, you may use a virtual machine. Please follow these
steps:
● Step 1. Install VirtualBox software from https://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(https://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 “sudo aptget install virtualboxguestx11” 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.
Deadline: Demo it to the grader in the quiz session on Dec 4th, 2015 5:00PM-6:50PM (Pacific
Time Zone)
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/
CSCI 585
