这是一篇关于空间数据和资产/设施管理的计算机代写

Submission Method (links to all forms on Moodle):

o Pre-Submission –

▪ Declare your topic title, description and the names, geometry types and dimensions of your three location-based, nested, assets – to avoid risks of plagiarism your topic area should come from the existing list. Online form, first come first served

o Submission

▪ A PDF of your pyramid, uploaded via Moodle / Turnitin

▪ Completing an online form with information about the decisions you are making

▪ Five separate SQL scripts via an online form

▪ A PDF of your map uploaded via Moodle/Turnitin

▪ A PDF of your 3D visualisation uploaded via Moodle/Turnitin

This assignment is worth 70% of the marks for the module.

– An assignment is an independent piece of work:

o Your work should not be identical to, or similar to, that of any other student or the work we do in class. If two assignments are very similar we will follow UCL procedures for plagiarism – see the guidelines here:

https://www.ucl.ac.uk/students/exams-and-assessments/plagiarism

o Discussing your assignment work or sharing your work with other students is collusion.

– If you have questions about this assignment, please post them on Moodle

– That way everyone is given the same information

– That way I remember what I’ve said to you and don’t mark you down for doing something that I wasn’t expecting

– Any questions should be generic – as this is an assessment which will gauge how much you’ve learned during the module I won’t be able to solve very specific assignment-related problems for you.

– Do not post any part of your assignment answer on Moodle

– The deadline for posting questions is 5 days before the assignment

deadline

– This is a digital submission – it is up to you to ensure that the files you upload to Turnitin or the online submission process are not corrupt in any way (in Turnitin you might be able to do this by downloading the uploaded files to check them, for the online submissions you will receive an e-mail which you should keep as evidence of successful submission)

NB: You are limited to a maximum of 10 e-mails per day to avoid overloading the testing system

Database Design and Build (70%)

Overview

The assignment involves the selection of a location-based asset management topic of your choice for which you will create a hierarchical pyramid to show how the features (assets) and decisions based on information about those features nest upwards.

The pyramid should have 3 levels of spatial nesting

You will then make a list of 7 decisions. Two of the decisions must use data output from lower level decisions (i.e. bottom-> middle and middle -> top).

You will then create the physical database for your topic, insert some data and demonstrate using queries how this data can be used as evidence for the decisions.

Step 1 – Topic Pre-Selection/Declaration (online form, link on Moodle)

The topic areas are from the UK Valuation Office Agency (VoA) so you should check  their website here to find more details about the topic before you select it:

https://www.gov.uk/guidance/rating-manual-section-6-part-3-valuation-of-all

property-classes1

NB: The VoA is responsible for setting property tax on commercial properties in the

1. However, your decisions should NOT be about property tax valuation – the information on the VoA site should only be used to give you some ideas about the topic.

Select a topic and provide a topic title and list your three spatial asset tablenames and geometry types and dimensions. These must be nested (i.e. the top level contains the middle level and the middle level contains the bottom level)2 :

• The top level should be a 3D volume
• The middle level should be a 3D volume or a 2D area (provided it nests inside the top level using st_contains)

• The bottom level can be a 2D or 3D point or a 2D or 3D line or a 2D or area or a 3D volume (provided it nests inside the middle level using st_contains)

Not nesting the assets correctly will result in marks being lost for decisions as well

There is no specific deadline for this task but you should make sure you lock in your topic BEFORE doing any other work on the assignment!

You should not use a university or school example as this would be too similar to the example we covered in class and would be plagiarism. Examples similar to Adaptable Suburbs or the noise case study are also not allowed.

Step 2 – Decisions (online form, link from Moodle)

List the Decisions (link to online form will be given in Moodle)

1. Write a description of the 7 decisions that your database will support

a.The decisions need context and should include some information as to what information is needed but also WHY the information is needed, how it will be used by the decision maker – In other words, you need to be clear on what the information will actually be used for – what is being decided. See Appendix for further information.

2. You should have two decisions that build on information provided by a lower level query (i.e. three decisions in total). You should make use of VIEWS to achieve this.

Create a view for the decision corresponding to the bottom level of the pyramid, and for the decision query select from this view

a.Create a view for the decision corresponding to the middle level of the pyramid that includes data from the view from the bottom level of the pyramid, and for the decision query select from this view Create a view for the decision corresponding to the top level of the pyramid, that includes data from the middle level view, and for the decision query select from this view

Step 3 – Pyramid (turnitin upload)

1.Create a THREE LEVEL pyramid for this organisation – similar to the example from the Centennial case study. This should include the names of the spatial asset/features that are at the bottom, middle and top levels of your pyramid

2.These names must be IDENTICAL to the name of the table that stores data for this feature.

a.All table names should be lower case

b.All three tables (assets) must match the tables you declared in the presubmission stage

c.All three features must be spatial – i.e. mappable (they can be 2D or 3D depending on how they were originally declared)

d.The features must nest – i.e. features at the lowest level must be contained inside features at the middle level, and features at the middle level contained inside features at the upper level.

Include a cover sheet in this PDF as Moodle requires a minimum of 35 words for plagiarism checks.

Step 4 – Database creation (5 script files, online form)

For this part of the assignment:

1. Script creation as follows:
2. Create an SQL script that contains the SQL used to create the tables in your database system. Call this script: createtable_ucfsxxx.txt (where ucfsxxx is your UCL login). All table names should be lower case, use _ to separate out any words (not spaces or -)

• Make sure you use addGeometryColumn to add location columns – the SQL testing scripts will not work if this is not present

• Make sure you use the parameters approach as per the in-class example

a.Create a separate SQL script for all the constraints. Call this script:

createconstraints_ucfsxxx.txt

b.Create a separate SQL script to populate each table with data – call this script

insertdata_ucfsxxx.txt:

• A minimum of 2 rows of data for the top level of the pyramid
• A minimum of 3 rows of data for the middle level (nested within the top level data)

• A minimum of 9 rows of data for the bottom level (nested within the 3 rows of the middle level)

• A minimum of THREE rows of data for all other tables.

The data should be sufficient to allow you to test out the SQL for your listed decisions. The resulting data for each decision should contain  at least one row of data

So that we can test your work independently, your SQL must create

ALL the data required from scratch – don’t use any data that has been imported via QGIS / sourced from third parties. The spatial data you create does not need to be very sophisticated in terms of geometry complexity.

All data should use a projected coordinate system (i.e. not lat/lng3 – if you don’t know the projected coordinate system for your location use British National Grid)

1. Upload a script that creates a series of views that will help to simplify your queries – minimally, you should have 3 views, one for each level of the pyramid that aggregates from the lower levels. You should also have a latest_parameters view. Your file should only contain views – do not include the select statements to test the views. Call this createviews_ucfsxxx.txt

e.Create a script file with the 7 SQL queries that provide data used to support the decisions you listed in the first part of the assignment. Call this decisions_ucfsxxx.txt

The answers to each decision should contain at least one row of data Three of the seven statements should be just select from view  statements

2. Use the provided test system (link on Moodle) to upload and test your scripts. You will receive an e-mail report each time you run the test. Keep this as evidence that you have submitted your scripts.