Spatial and Document Database

1. Install the PostGIS spatial database software (if not already completed during week 7);

2. Extend their own existing assignment 1 PostgreSQL relational database to a PostGIS spatial database;

3. Write SQL commands in pgAdmin4 to create spatial data in the extended database;

4. Write spatial queries using SQL;

5. Install the MongoDB document database software, MongoDBCompass GUI and associated software (if not

already completed in week 8);

6. Understand the embedded schema proposed and understand the examples given in Appendix A:

7. Use MongoDBCompass or mongosh to:

a. Create a document database;

b. Create a collection in that document database;

c. Create documents within that collection;

d. Write MongoDB commands to populate the collection;

e. Write MQL to query the database.

You believe that spatial information could be very important to the operations of AppliancesToYourDoor. They could

use this information for customer analysis, delivery analysis and other parts of their business. To introduce the

concept you decide to provide an example in the existing database. You suggest that the warehouse table should be

populated with spatial data and as a starting point, you decide that an outline of the warehouse itself, the location of

the nearest weighbridge and a location and elevation for the office of the warehouse is needed. You decide to

implement these changes and then demonstrate some of the functionality available with this information and hope

that AppliancesToYourDoor can then see the benefit in other areas of their business and would ask to you develop

this spatial aspect further. At the moment though you are only going to concentrate on the warehouse as your spatial focus. To that end you determine, using Google Earth the office, warehouse and weighbridge points for warehouses

for three of the eastern seaboard warehouses.

The table below summarises this information (all coordinates are in (longitude, latitude (WSG-84 in decimal

degrees), elevation in metres)*:

Part B

You also understand that AppliancesToYourDoor feel they have a data requirement that hasn’t been met. Deliveries

are conducted according to a schedule and this was explained in the case study for the first assignment. What was

missed though was a record of the log of events that occur on a delivery. Drivers and jockeys do other things

besides deliveries. They might, for example have mechanical issues, fill up with fuel, have other short breaks and

meal breaks or they might contact the office about some issue or other. At present, each truck has a log/diary that

the driver and jockey are supposed to fill in but some employees don’t do this and some keep notes on their

company supplied mobile phone instead. They also often take photos for installation purposes and for other

information. AppliancesToYourDoor are frustrated about all the various bits and pieces of information that they need

to access to know what happened on a delivery schedule and want some sort of standardized data repository that

they can update and query.

You determine that this log of events would be a good candidate for recording in a document database. You also

know of an example logging app that could be installed on each employee’s company supplied mobile phone. This

app allows real time download of the logged information but users can perform a bulk download at the end of the

schedule if they need to. You know that you can configure the app to meet the needs of the logging but that is not

your focus now. You want to understand the data needs of this functionality so you have collected some sample

information of some delivery schedule logs and based on this have decided the data could be arranged and stored in

the following manner:

SQL Servidor de Microsoft SQL Administración de bases de datos Programación de bases de datos MySQL

Nº del proyecto: #34818913

Sobre el proyecto