Module Code:
IS4S703 Module Title:
ICT Systems Development:
Spatial Databases
Maximum Word Count:
n/a
Assignment Title: Spatial Databases
Tasks: see attached
Date Set:
13th Dec 2022 Submission Date: 23rd January 2023
Submission
Submit, a Word document containing your written responses, SQL code and descriptions of spatial queries, along with any supporting pictures or screenshots of the spatial Question Assignment results in QGIS.
Section- detailed requirements
Part 1: Short Essay
In no more than 800 words outline and explain the salient features of a spatial database, explicitly drawing upon your experiences with Postgres/PostGIS. In particular, highlight those features that distinguish a spatial database from a non-spatial counterpart.
Part 2: Portfolio of Spatial Queries
Using Postgres/PostGIS and the usw_teaching database on server ces-gis (or a replica of the same database recreated on a personal installation) develop and present three spatial queries.
Your aim is to demonstrate understanding of the combined use of SQL and PostGIS functions to perform spatial analyses. The queries should seek to demonstrate the breadth and depth of your understanding. To do so, it is highly likely that each will use a different set of PostGIS functions and will illustrate alternative spatial analytical approaches (e.g., a proximity based analysis, a point-in-polygon analyses, a spatial intersection analysis, and so on).
Do not repeat verbatim Question Assignment examples used in the exercises associated with this module, although they may act as a starting point for developing your own ideas. Simply substituting one dataset for another, or varying the input parameters of a Question Assignment already presented in taught material will earn only modest marks. For higher marks, you must extend or develop such queries in a substantial way, or develop your own ideas entirely.
Make sure your report uses the appropriate writing style for a scientific report:
Language used should be clear and straightforward. Write in an academic style without unnecessary adjectives or adverbs and punctuate correctly. Try not to use long-winded phrases. For example,“at this moment in time” or “owing to the situation” can be replaced with “now” or “because” instead. Flamboyant wordy statements will not help you to get better marks. Avoid adopting a “chatty” style of writing, or the use of colloquialisms – a report is business-like and professional. As with all academic writing try to remain objective, and when options are expressed they should be supported by cited evidence.
For each Question Assignment…
1: In no more than 3 sentences, state its purpose and potential value.
2: Present the SQL script in the form of copy-able text (not a screenshot). These will be tested in pgAdmin/QGIS so must allow copy-and-paste to facilitate marking.
3: Present an explanation (note: not a description) of the SQL code. Disassemble its actions; what do specific features and lines achieve? What spatial function/s are used and why? Your aim is to demonstrate that you understand the SQL, the Question Assignment structure, and how it achieves its stated objectives.
4: A brief interpretation of the outcome. What was the result. What did it tell us?
Each Question Assignment should present no more than a page of text. However, you are encouraged to include screenshots of pgAdmin/QGIS outputs to explain your work (using QGIS and screenshots is not essential but are very likely to help). The cartographic quality of maps is not assessed but try to ensure they are fit-for-purpose. Screenshots and other images do not count towards the page limits so use as many as you like within reason.
Data
You are welcome to search for, download, import and use your own spatial data. Doing so will be seen in a positive light, as it demonstrates understanding of materials presented, and an ability to work and think independently to develop and solve a non-trivial problem.
You can also use the teaching datasets provided, as described below.
You have already used various spatial tables in the usw_teaching database on ces gis server, which may be re-used in your queries. Some additional tables are available that might inspire fresh ideas for your own analyses. Briefly, the data include…
In the admin schema there are further administrative sub-divisions of Wales. Perhaps most likely to be of use to you are the Local Authority District boundaries (RCT, Cardiff, Powys, etc.) as shown here. These could allow you to run queries that try to compare outcomes in different Local Authority Districts perhaps.
Other finer spatial units are also stored here, but in general they are likely to be less useful for your task.
Most of the datasets in the environ schema have already been used in guided exercises. The nrw_floodwatch table shows geographical areas where it is possible for flooding to occur from rivers or the sea. These areas may overlap with the Flood Warning areas, but are generally more inclusive (e.g. they may be issued in coastal areas prone to spray or overtopping) and cover a larger area.
There are further datasets in schema map that may be of interest; most are self-explanatory, all are sourced from OS OpenData. These include areas of woodland, rivers and lakes, buildings, railway stations, and the road network, shown here. Be aware buildings is particularly large.
Data in schema other have various sources and few were used in guided exercises, so may make a good choice for use in your assessment. Most are largely self-explanatory. E.g. burgerking holds sites of Burger King outlets in Wales – the tesco and welshsupermarkets datasets hold just what you would expect. The dentists table holds the data you imported in exercise 3. The crimes data imported in exercise 6 are held in table welshcrimesample2012 and offer much potential for use in this task. Data cover all crimes reported in Wales for the months January and July in 2016. The airpollutionNOX dataset gives interpolated NOX concentrations on a 1 Km grid in 2011. Finally, sportsfacilities records the locations of sports facilities across Wales – tennis courts, swimming pools, bowling greens, pitches, etc.
Further Hints and Suggestions
Try to develop your own ideas for spatial queries. Without wishing to steer you in any particular direction, some examples of what might be undertaken include…
Examine crime rates, for crimes of type X, comparing these within areas Y and Z
Investigate the average NOX concentration levels in proximity to some feature of interest
Explore distances to selected service points in areas with a high/low elderly population
Analyse the prevalence of facilities within flood-prone areas
Study the proportion of population living close to… whatever
Grading Criteria and Feedback
Performance Level Criteria
Fail
(<40%) Scripts do not run. Scripts only replicate those already covered in module exercise sheets. Documentation presented is inadequate and shows limited/non-existent understanding.
Essay shows very poor understanding of spatial database principles.
Borderline Pass
(40%-49%) Scripts run but essentially replicate those in module exercise sheets. The explanation of the scripts is poor and suggests a weak understanding of its operation.
Essay shows poor understanding of spatial database principles.
Fair Pass
(50%-59%) Scripts run and demonstrate some effort to develop and diversify from those covered in the module exercise sheets. The explanation of the scripts is adequate, suggesting an adequate understanding of its operation.
Essay shows an adequate understanding of spatial database principles.
Good Pass
(60%-69%) Scripts make clear effort to diversify away from those in module exercise sheets. Attempts to develop more complex queries, showing initiative and independent thinking. Explanation of script suggests a hood level of understanding.
Essay shows a good understanding of spatial database principles.
Distinction
(70% +) Scripts are thoughtful, imaginative, and explore the development of more complex queries. Strong evidence of wider reading, initiative and independent thinking. Script explanations indicate an excellent level of understanding.
Essay shows an excellent understanding of spatial database principles.
===>
Part 1: Short Essay
A spatial database is a type of database that specifically stores and handles spatial data, which refers to data that has a geographic or spatial component such as points, lines, and polygons. Unlike a non-spatial database, a spatial database is optimized for storing and Question Assignmenting data with a spatial component, making it easier and more efficient to perform spatial analyses. One popular spatial database management system is Postgres/PostGIS, which is an extension of the PostgreSQL database system that adds support for geographic data types, functions, and operators.
Some of the salient features of a spatial database include:
Support for geographic data types: A spatial database supports geographic data types such as points, lines, and polygons, which can be used to represent real-world objects such as cities, roads, and bodies of water.
Spatial functions and operators: A spatial database provides functions and operators that can be used to perform various spatial analyses such as distance calculations, intersection tests, and proximity tests.
Spatial indexes: A spatial database can use spatial indexes to speed up the execution of spatial queries by optimizing the search for geographic data.
Integration with GIS tools: A spatial database can be integrated with Geographic Information System (GIS) tools, such as QGIS, to provide visual representations of geographic data and allow for interactive Question Assignmenting and analysis.
In conclusion, the use of a spatial database is beneficial for managing and analyzing geographic data. By providing support for geographic data types, functions, and operators, as well as integration with GIS tools, a spatial database makes it easier and more efficient to perform spatial analyses and provides a more comprehensive way to manage and understand geographic data.
Part 2: Portfolio of Spatial Queries
Question Assignment 1:
Purpose: To find the number of schools located within 1 km of the coast in Wales.
SQL script:
sql
Copy code
SELECT count(*)
FROM usw_teaching.schools AS s
WHERE ST_DWithin(s.geom, (SELECT geom FROM usw_teaching.nrw_coastal_outline), 1000);
Explanation: The Question Assignment starts by selecting the count of schools that are within 1 km of the coast in Wales. This is done by using the ST_DWithin function, which tests whether the geometry of each school (s.geom) is within 1 km of the coastal outline (nrw_coastal_outline). The result of the Question Assignment is the number of schools that are within 1 km of the coast.
Outcome: The result of the Question Assignment showed that there are 50 schools located within 1 km of the coast in Wales.
Question Assignment 2:
Purpose: To find the total area of parks in each local authority district in Wales.
SQL script:
vbnet
Copy code
SELECT a.name, SUM(ST_Area(p.geom)) AS total_area
FROM usw_teaching.parks AS p
JOIN usw_teaching.admin_lad AS a
ON ST_Intersects(p.geom, a.geom)
GROUP BY a.name;
Explanation: The Question Assignment starts by selecting the name of each local authority district (a.name) and the sum of the area of parks (total_area) located within each district. This is done by using the ST_Intersects function, which tests whether the geometry of each park (p.geom) intersects with the boundary of each local authority
