Another group project at Edinburgh, this time as part of an advanced database class. The project brief was very open – develop a project that stores spatial data in an Oracle database, analyses the data, and displays the results of that analysis on a website. We interpreted those instructions loosely (!) and chose to build a version of the game “Battleships”. This is the first project that I have written about here that didn’t involve any work in ArcMap or other desktop GIS software. Instead, the course, and the project, were all about handling spatial data in the databases that underpin GIS software, with a particular focus on the Oracle database.
For those not familiar with the game, the idea is to sink your opponent’s ships by firing shots at a board containing the (hidden) location of those ships. Our gameplay screen as it appears at the start of a game is shown below:
This is a simple web page, rendered from a Python script, using SVG (scalable vector graphics) images to draw the playing areas, grid lines, and your ships. Each board has dimensions of 500×500 pixels, and this defines the co-ordinate system for placing ships and locating shots. Our Oracle database contains co-ordinate and ancillary data for a total of 40 ships, in eight sets of five ships. At the start of the game each player (you and the computer opponent) is randomly assigned one of these sets of ships.
Gameplay data is stored in three Oracle database tables – one for shots, one for ships, and one for ship types. The data model for the database is illustrated here:
You fire shots at your opponent’s ships using the HTML form at the top of the page. This form entry is passed to the Python script, which interacts with the database and executes the computer turn according to the flow chart below. Executing this gameplay logic in the script was perhaps the most challenging aspect of the project.
A number of spatial queries of the SDO_GEOMETRY objects that hold ship and shop co-ordinate information are executed by this script:
- a check that the co-ordinates entered by the player do not overlap a 25-pixel buffer around any previous shot that hit a ship;
- an intersection query between shots and the opposing player’s ships; and
- a nearest neighbour query to identify the distance from each shot that misses to the closest opposing player’s unsunk ship.
The turn of the computer is controlled by the Python script, which uses NumPy to generate random co-ordinates for the computer to fire at. The area to be targeted by successive shots is adjusted based on the result of the most recent shot (miss, hit, or hit and sunk) and the distance from missed shots to the nearest ship.
The images below capture two scenes from a game in progress. Each player’s shots are listed in reverse chronological order – your shots to the left of the target board and the computer’s to the right of the home board. In this game the computer’s first shot struck a ship, resulting in further targeting of that area of the board and sinking of that ship with their first three shots of the game. The next two computer shots (#7 & #8) were misses, but close enough to a ship (within 50 pixels) to warrant further targeting of that area. The next five shots struck the same ship, sinking it, before a return to a randomly targeted shot that was a miss.
This was an interesting and challenging project to work on, which introduced me to working with SVGs and further enhanced my abilities to use the Python programming language to render, and accept input from, webpages, as well as access and manipulate spatial data held in Oracle database tables.
Euan's GIS Projects 