Database(spatial) assignment

The exact requirements of the project are given in the attached pdf file. The goal of this project is to design an application that queries a spatial database. This project will involve spatial data types using Oracle10g, Oracle Spatial features, and Java (JDBC). You are required to: 1. Design a schema to store given information about people, cars, and bikes, as well as create the corresponding tables in Oracle database on shams.usc.edu. 2. Write two Java programs to 1) store and 2) query your spatial database. Input Files: You will be given four files that contain information about the area between (0, 0) and (600, 400) (in terms of x and y). You need to read the input files (except the JPEG file) as following: a) Read the files line by line. b) Each line represents a tuple, containing different columns separated by comma. Use StringTokenizer to extract the columns of each tuple. c) Columns of each line are ID, T, X, and Y representing the position of object ID at time T as (X, Y). An example “12, 100, 23, 45??, represents that object with ID=12 has been at position (23, 45) at time 100. Required .sql files: You are required to create two .sql files: 1. [login to view URL]: This file should create all tables required. In addition, it can include constraints, indexes, and any other DDL statements you might need for your application. 2. [login to view URL]: This file should drop all tables and the other objects once created by your [login to view URL] file. Required Java Programs: You are required to implement two Java programs: a. [login to view URL]: This program should get the name of the input files as command line parameters and populate them in to your database. It should be executed as: > java populate [login to view URL] [login to view URL] [login to view URL] Note that every time you run this program, it should remove the previous data in your tables; otherwise the tables will have redundant data. b. [login to view URL]: This program should provide a GUI, similar to the figure 1, to query your database. ## Deliverables 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. 2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables): a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment. b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request. 3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. The exact requirements of the project are given in the attached pdf file.I want the project to be implemented as exactly given in the pdf file. I was unable to give the entire description as it does not fit in this. So please refer to the attached file as the compete description. The GUI should include: a) An 600x400 panel that shows the given jpeg file. b) A text field that shows the location (i.e., (x,y)) of the mouse as it moves over the satellite image (the lower left corner of the panel is the origin (0, 0) and the upper right corner is the point (600, 400)). c) 3 Check boxes that specify the features we are interested in. d) 4 Radio buttons that specify the active region on the satellite image. e) A push button to submit range queries. f) A push button to submit point queries. g) A text field to show submitted queries to the database. Figure 1 shows sample GUI Description of GUI 1: The GUI should show the given satellite image when the application is started up. The title of your main window should display your full name and email address. a) Active Features: multiple features can be checked and those checked features will be retrieved and shown on the image. Circle should be big enough, e.g. radius 5 to 10. b) Range Query: if the “Whole?? is selected as the range query, then the selected features that are in the whole satellite image should be retrieved from the database and shown. However, if the “Circle??, “Box?? or “Combination?? is the range query, then only the active features that are inside (or intersect with) the selected area should be retrieved and shown. c) In order to use a circle for a range query, “Circle?? radio button should first be selected, then the first left mouse click will specify the center for a circle and the second left click is the radius for the circle. Figure 2 shows an example when a circle is the range query and the people, bikes, and cars inside the selected circle are queried and shown on the image. d) In order to select a box, “Box?? radio button should first be selected, then the first and second click of left mouse button on the image, will specify the two opposite corners of the box. The box must be displayed as follows: Box White Show each vertex specified by a left mouse click with a white cross and 4 edges connecting the corners with a white line. e) In order to select differences between circle and box, “Combination?? radio button should first be selected, then the first left mouse click will specify the center for a circle and the second left click is the radius for the circle. The third and fourth left click will specify the box. Draw one figure (a circle or a box) include the other totally. The range query is on the difference area from bigger figure to smaller figure. Show each vertex specified by a left mouse click with a white cross and draw a circle with yellow line and draw a box with green line. Figure 2: Selected circle, people, bikes, and cars inside it. Queries: We will examine your homework as following: 1. For range queries: a. Whole Area Range Query: we select the “Whole?? as the range query, and then we select all or some of the features (i.e., people, cars, bikes), and then click on the “Submit?? button. Your program should submit separate queries for each feature. Each query returns all objects that are inside the whole area according to its most recent position (the position of the object corresponding to the maximum time). Your program should draw these objects on the satellite image. b. Circle Range Query: we select the “Circle?? as the range query, specify the center and radius of a circle by 2 clicking left mouse button on the image twice (your program should show a cross for each clicked corner), at this point your program should draw the circle, then we select some of the features, and then click on the “Submit?? button. Your program should submit separate queries for each feature. Each query returns all objects that are inside the circle according to its most recent position (the position of the object corresponding to the maximum time). Your program should draw these objects on the satellite image. c. Box Range Query: we select the “Box?? as the range query, specify the corners of the box by 2 clicking left mouse button on the image twice (your program should show a cross for each clicked corner), at this point your program should draw the box, then we select some of the features, and then click on the “Submit?? button. Your program should submit separate queries for each feature. Each query returns all objects that are inside the box according to its most recent position (the position of the object corresponding to the maximum time). Your program should draw these objects on the satellite image. d. Combination Range Query: we select the “Combination?? as the range query, specify the center and the radius of a circle by 2 clicking left mouse button on the image, then the next 2 clicks act as the corners of a box. (Your program should draw each point as a cross as they are being selected and draw the circle and box). Then, we select some of the features, and then click on the “Submit?? button. Your program should submit separate queries for each feature. Each query returns all objects that are inside the intersection between a circle and a box according to its most recent position (the position of the object corresponding to the maximum time). Your program should draw these objects on the satellite image. 2. For point queries (Nearest Neighbor queries): a. For any of the above scenarios (i.e., whether the whole area, a circle, a box, or a combination is selected as the range), we use the right mouse button to specify a location on the image as the “origin point?? (draw this new point with a cross with yellow color). b. Assume always “ALL?? features (people, bikes, and cars) will be chosen. Your program should consider all objects that are inside or intersect with the range according to their most recent position. Among them, it returns the closest object to the origin. c. Basically among those three features you need to find the closest one to origin point. For example say car is the closest one. Now you are left with people and bikes. And car becomes the origin point. d. Repeat the same process with the new origin point and two remaining features. Continuing our example say bikes is chosen, now this becomes the origin point and we are left with people. Have the bike as the origin point find the nearest people. e. Finally, your program should draw the trajectory with three lines with the colors say in the below table. And you need to print the positions of three points for people, car, and bike in the text field under Point [login to view URL] more description is given in the attached file. It does not fit in 8000 characters and hence I could not enter it here. ## Platform The project will be run using oracle 10g. The project will be run on a unix server using xwin32.