GEOM 1040 - Assignment 5

Assignment: Spatial Data and Asset / Facilities Management

Steps in Detail

Step 1: Topic Pre-Selection/Declaration

• Select a topic from the provided list on Moodle.
• Declare the topic title, description, and three nested spatial assets with table names, geometry types, and dimensions.
• Ensure spatial assets nest correctly:
  • Top Level: 3D volume.
  • Middle Level: 3D volume or 2D area (must nest within the top level using st_contains).
  • Bottom Level: 2D/3D point, line, or area (must nest within the middle level using st_contains).
• Avoid topics or designs too similar to class examples to ensure originality.

Step 2: Decisions

• List 7 decisions supported by your database. Include:
  • Context: Purpose of the decision and how the information will be used.
  • Threshold: Numeric value for comparison, determining outcomes.
• Include two decisions dependent on data from lower levels in the hierarchy.
• Use views for hierarchical queries:
  • Bottom level view feeds into the middle level.
  • Middle level view feeds into the top level.

Step 3: Pyramid

• Create a 3-level pyramid diagram with:
  • Asset/feature names corresponding to database tables.
  • Clear hierarchy showing nested relationships.
• Ensure names match the declared topic and spatial characteristics.

Step 4: Database Creation

• Write SQL scripts for the following:
  • Table Creation: Use proper naming conventions and geometry columns.
  • Constraints: Define primary/foreign keys and unique constraints.
  • Data Insertion:
    • At least 2 rows for the top level.
    • At least 3 rows for the middle level.
    • At least 9 rows for the bottom level.
    • All other tables require at least 3 rows.
  • Views: Create one for each level of the pyramid and an additional latest_parameters view.
  • Decision Queries: Write SQL to support the decisions listed in Step 2.
• Test all scripts via the Moodle test system for functionality.

Step 5: Map and 3D Visualization

• Create and upload a PDF containing:
  • A QGIS screenshot showing the spatial data layers and 2D map.
  • A 3D visualization screenshot created in FME.

Marking Process

• Scripts will be tested on an automated system to verify functionality.
• Pyramid hierarchy and table nesting will be validated.
• Queries will be checked for correctness, formatting, and adherence to decision requirements.
• Advanced queries must demonstrate hierarchical aggregation.

Marking Criteria

Examples of Decisions

1. Security Staff Allocation:

   • Purpose: Determine whether to allocate more budget for additional staff.
   • Threshold: 23 staff. Below 23: Allocate more budget. 23 or above: No action needed.

2. Shop Splitting for Storage:

   • Purpose: Decide whether to split the largest shop for storage or find alternatives.
   • Threshold: 100 sq m. Below 100 sq m: No split. 100 or above: Split.

3. Oven Maintenance:

   • Purpose: Compare maintenance times of current ovens with new ones to decide on purchase.
   • Threshold: 20 minutes. Below 20 minutes: Choose Samsung. 20 or above: Choose Zanussi.

4. Building Refurbishment:

   • Purpose: Assess whether the largest building's refurbishment cost fits within the budget.
   • Threshold: £200,205. Below £200,205: Proceed this year. £200,205 or above: Postpone to next year.

联系我们

WeChat：pythonyt001
Email: [email protected]