SOLUTION: Database Development | My Assignment Tutor

FACULTY OF COMPUTING, ENGINEERING and SCIENCEFinal mark awarded:_____ Assessment Cover Sheet and Feedback Form 2020/21 Module Code: IS2S565Module Title: Database DevelopmentModule Lecturer: Mitchel LangfordAssessment Title and Tasks: Spatial QueriesAssessment No. 3 of 3No. of pages submitted in total including this page: Completed by studentWord Count of submission (if applicable): Completed by studentDate Set: 23rd Feb 2021Submission Date: 25th Mar 2021Return Date: 26th Apr 2021 Part A: Record of Submission (to be completed by Student) Extenuating Circumstances If there are any exceptional circumstances that may have affected your ability to undertake or submit this assignment, make sure you contact the Advice Centre on your campus prior to your submission deadline. Fit to sit policy: The University operates a fit to sit policy whereby you, in submitting or presenting yourself for an assessment, are declaring that you are fit to sit the assessment. You cannot subsequently claim that your performance in this assessment was affected by extenuating factors. Plagiarism and Unfair Practice Declaration: By submitting this assessment, you declare that it is your own work and that the sources of information and material you have used (including the internet) have been fully identified and properly acknowledged as required[1]. Additionally, the work presented has not been submitted for any other assessment. You also understand that the Faculty reserves the right to investigate allegations of plagiarism or unfair practice which, if proven, could result in a fail in this assessment and may affect your progress. Details of Submission: Note that all work handed in after the submission date and within 5 working days will be capped at 40%[2]. No marks will be awarded if the assessment is submitted after the late submission date unless extenuating circumstances are applied for and accepted (Advice Centre to be consulted). You are required to acknowledge that you have read the above statements by writing your student number(s) in the box: Student Number(s): IT IS YOUR RESPONSIBILITY TO KEEP A RECORD OF ALL WORK SUBMITTED Part B: Marking and Assessment (to be completed by Module Lecturer)This assignment will be marked out of 100% This assignment contributes to 30% of the total module marks. Assessment Task: See details below Learning Outcomes to be assessed (as specified in the validated module descriptor http://icis.glam.ac.uk): LO2. To understand spatial database technology and develop the ability to analyse requirements and design an appropriate solution for a problem of defined scope using spatial design and modelling techniques. Assessment Task: In Brief: Develop and present three spatial queries, using the PostGIS spatial tables in the cw1920 schema of database usw_teaching. Present this in the form of a short scientific report. Each example should consist of: A concise statement to clearly outline the purpose of the query, and a justification as to why it is of use to pose this question. Also, identify the primary data inputs (i.e., which specific tables & fields of information will be required to answer it). The SQL query itself: This MUST be presented as selectable text (NOT a screenshot). It must be possible for me to cut-and-paste your query into pgAdmin so that I can test it against the usw_teaching database. Elaborate upon your query, explaining in simple English what it does, and how it does it. Identify the purpose of any spatial functions it contains. Try to deconstruct the script, breaking it down and explaining how it achieves its stated objective. You must convince me that you understand how your query works. Briefly interpret the results from running your script. What does it show? What do we learn? Present outputs as maps, tables, or summary statistics, as appropriate. In detail: The primary goal is for you to demonstrate your abilities in, and understanding of, constructing spatial queries using PostGIS, using information in the usw_teaching database. You should mirror the contents of usw_teaching in your own database (either on a home PC or using your account on ces-gis). You can do so by using the schema backup files supplied on Blackboard. The usw_teaching database remains available on server ces-gis via remote desktop connection if you prefer not to use a personal PC/laptop. The queries are left entirely for you to determine and develop. If you are unsure of their suitability, seek advice from the lecturer before embarking on your final choices. You should not repeat verbatim the spatial queries and examples presented in practical or lecture sessions associated with the module. You may, however, draw upon these examples and use them as a starting point when developing your own ideas and queries. Similarly, simply substituting datasets with alternative tables, or applying only minor variations to the parameters of queries that have already been presented will only be rewarded with modest marks. Higher marks require that your queries are substantively modified, or seek to provide answers to completely new questions. You queries should aim to demonstrate a range of analytical techniques and spatial functions. In other words, they should not all be variations on very much the same theme. It is a good idea if you can show some progression in their complexity; one can be relatively simple for example, while the other two should demonstrate greater sophistication. Submission Present your spatial queries in a short report, using the sections and structure outlined above. Keep the text style as concise and scientific, written in 3rd person/passive voice. You should not need to exceed 6 pages. You may include screenshots of QGIS maps to help explain your analysis, illustrate data used, or present outcomes. Screenshots are not essential, but are likely to assist in describing your work. Their cartographic quality is not judged, provided they remain essentially fit-for-purpose. Data You must use information in schema cw1920 in at least two queries. You may combine it with data in other schemas, most of which have been explored in lectures and practicals. Most data in cw1920 is self-explanatory, but a brief summary follows. Consult with the lecturer if you require any further clarification. air_benzene_18 air_nox_16 Points. Located at 1 km grid spacing. Air pollution levels recorded in 2016 banks_17 carehomes gp_surgeries hospitals pharmacies public_toilets secondary_schools Points. Represent the location of the corresponding service delivery sites urban_trees mountains Points. The first is a map of all trees found in urban areas. The second reports the location of all mountain peaks over 620m height in Wales nrw_forests nrw_nationalparkssssi_19wind_farms historic_landfills Polygons. Forests over 5 Ha in size owned by Natural Resources Wales; The boundaries of Welsh National Parks; Areas designated as being “sites of special scientific interest”; Areas designated for wind farm development; Areas that have previously been used as landfill sites. major_towns main_roads Self-explanatorynaw_mp Polygons. The constituency boundaries of Welsh Parliament MPs You have already seen most of the spatial tables in other schemas of usw_teaching. You may use these data too, provided the focus of your queries remains on data in cw1920. Schema admin holds administrative areas in Wales. Most likely to be useful are the Local Authority Districts (RCT, Cardiff, etc.) as shown here. Smaller administrative units go by strange names such as Middle Layer Super-Output Areas, Lower Layer Super Output Areas, and Output Areas. They form a nested hierarchy associated with UK Census data. Postcode tables represent the locations of 90,000+ Welsh postcodes, along with a simple population head count. Tables in schema census contain additional census data that can be linked to corresponding points/polygons via the common GSS code. Data in environ have an environmental bias: flood_watch shows areas liable to flooding from rivers or sea, while flood_warning areas are more inclusive (e.g. in coastal regions they include areas prone to spray or overtopping). There are also polygons of areas declared to be regions of Natural Beauty, the locations of all Primary Schools, and areas designated as built-up “urban”. Most tables in map are self-explanatory. All derive from OS OpenData. This includes rivers and lakes, woodlands, buildings, railway lines and stations, the road network, and overhead power lines. Tables buildings is particularly large so can make queries execute slowly. Data in schema other have various sources. Some table hold the locations of retail sites (eg burgerking, tesco and supermarket). The table dentist holds the same data that you imported in Exercise 3. And crime_sample the crime data you imported in Exercise 6 (January and July, 2016). Table airNOX holds interpolated NOX pollution concentrations on a 1Km grid spacing. Table sport_site holds the locations of various sports facilities in Wales; tennis courts, swimming pools, grass pitches, and so on. Many other datasets are available on-line. Sourcing your own data is not a requirement, but notify me if you do so as it will be rewarded for showing initiative and independent learning. Further Suggestions The intention is for you to develop your own ideas for spatial queries. This list provides some examples of what is possible, but please do not allow it to limit your imagination: – Examine the crime rates, for crimes of type X, comparing them between areas Y and area Z – Investigate average air pollution levels in proximity to schools or some other feature – Look at the distance to dentists in areas with high and low elderly populations – Analyse the prevalence of sports facilities/schools/other things in flood-prone areas – Examine the proportion of the population (or sub-group) that live close to… a railway station; the motorway; power-lines; or any other feature of interest. Grading Criteria and Feedback CriteriaFail (